sql over(partition by) 开窗函数的使用_sql over part

patition by  在group by分组汇总的前提下，再汇总一次

create table student_score
(
    id      serial,
    class   varchar(54),
    name    varchar(54),
    subject varchar(54),
    score   float
);
 
create unique index student_score_id_uindex
    on student_score (id);
 
alter table student_score
    add constraint student_score_pk
        primary key (id);
 
 
 
insert into student_score(class, name, subject, score)
values ('一年级1班', '小明', '语文', 100.0),
       ('一年级1班', '小明', '英语', 80.0),
       ('一年级1班', '小明', '数学', 90.0),
       ('一年级1班', '小红', '语文', 98.0),
       ('一年级1班', '小红', '数学', 85.0),
       ('一年级1班', '小红', '英语', 90.0),
       ('一年级2班', '小夏', '语文', 88.0),
       ('一年级2班', '小夏', '数学', 85.0),
       ('一年级2班', '小夏', '英语', 89.0),
       ('一年级2班', '小黄', '语文', 70.0),
       ('一年级2班', '小黄', '数学', 88.0),
       ('一年级2班', '小黄', '英语', 67.0);
 
 
truncate student_score;
 
-- 窗口函数over 表示在...范围内
-- partition by 1 可以理解为将他们都归到一个分组里面
-- partition by name 是根据name再次进行分组，然后统计总分
select class,
       name,
       sum(score)                                个人总分,
       sum(sum(score)) over (partition by name)  个人总分,
       sum(sum(score)) over (partition by class) 班级总分,
       sum(sum(score)) over (partition by 1)     年级总分
from student_score
group by class, name;
 
select name,
       sum(score)                            个人总分,
       sum(sum(score)) over (partition by 1) 年级总分
from student_score
group by name;

1、patition by 1   汇总所有数据

 1 select * from stu 

select
class,
sum(score) 班级总分,
sum(sum(score)) over(partition by 1) 年级总分,
cast(cast(sum(score)*100/nullif(sum(sum(score)) over(partition by 1),0) as numeric(18,2)) as varchar(50))+'%' as 班级总分占比
from stu
group by class

2、patition by 字段   根据该字段汇总

 1 select * from stu 

select
class,
name,
sum(score) 个人总分,
sum(sum(score)) over(partition by name) 个人总分,
sum(sum(score)) over(partition by class) 班级总分,
sum(sum(score)) over(partition by 1) 年级总分
from stu
group by class,name