窗口函数和存储过程的使用

--窗口函数
-- 创建表
create table class(
stu_no int primary key,
class_no varchar(55) not null,
grade numeric
)

-- 数据插入
insert into class values(10001,'class1',67.5),(10002,'class1',85.2)
insert into class values(10003,'class1',70.5),(10004,'class1',95)

insert into class values(10005,'class1',70.5),(10006,'class1',95)
insert into class values(10007,'class1',70.5),(10008,'class1',95)

insert into class values(20001,'class2',80.5),(20002,'class2',90)
insert into class values(20003,'class2',82.5),(20004,'class2',91)
insert into class values(30001,'class3',80.5),(30002,'class3',90)
insert into class values(30003,'class3',82.5),(30004,'class3',91)



-- 专用窗口函数rank(),partition by 分组不改变分组的行数
select *,rank() over (partition by class_no order by grade desc) 班级排名 from class

-- rank()和dense_rank()的区别，rank并列名次会占用下一名次的位置，dense_rank不占用下一名次的位置,row_number() 不考虑排名情况，只显示行所在行数
select *,rank() over (order by grade desc) rank,dense_rank() over (order by grade desc) dense_rank,row_number() over (order by grade desc) from class

-- partition 子句可以省略，省略就是不分组

-- 聚合函数使用窗口函数,窗口函数根据排序字段，聚合当前行及以上的数据

select  *,sum(grade) over (order by stu_no desc) current_sum,avg(grade) over (order by stu_no desc) current_avg from class

-- 存储过程 delimiter 将结束符号修改为$$
create or replace function demo(p1_in decimal)
returns integer as $$

declare
    sql text ;
    counts integer;
begin
sql:='select count(1) from class where grade >' || p1_in;
execute sql into counts;
return counts;
end;
$$ language plpgsql;


select *,demo(grade) 大于分值人数 from class

---------------------------------------------------------------------
select date_part('year','2024-05-01 00:00:00'::timestamp-'2021-05-01 00:00:00'::timestamp)
select date_part('year','2024-05-01 00:00:00'::timestamp)
select date_part('month','2024-05-01 00:00:00'::timestamp)

-- 生成时间序列
create or replace function generate_date(beg_data varchar,end_data varchar,wd varchar)
returns integer as $$
declare
    days integer;
    counts integer;
    sql text;
    i integer;
    sql1 text;
    rs timestamp;
begin
    
    sql:='select date_part('||wd||','||end_data||'::timestamp-'||beg_data||'::timestamp)';
    execute sql into days;
    i=0;
    truncate table "days_date";
    while i <= days
    LOOP 
    sql1 = 'select '||beg_data||'::timestamp + '||''''||i||' DAY'||'''';
    execute sql1 into rs;
    insert into "days_date" values(rs);
    i=i+1;
    END LOOP;
return days;
end;
$$ language plpgsql;

select generate_date('''2021-08-01 00:00:00''','''2021-12-10 00:00:00''','''day''')

-- 时间+1天
select '2021-08-01 00:00:00'::timestamp + interval '131 day'
select '2021-08-01 00:00:00'::timestamp + ('131 day')::interval
select '2021-08-01 00:00:00'::timestamp + '131 day'  
-- '转义方式
SELECT ''''||'131'||' DAY'||''''