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

た入场券 2023-10-04 17:29 101阅读 0赞

**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;

![4eba7c767d8b48079483714cb63fedb7.png][]

**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

![7ff1a3e021e952e11d2e6c1690ccd40b.png][]

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

1 select \* from stu

![e0a91439fed991d9e2248d0bf91ca1b7.png][]

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

![e56ceb30a3c73d7272a4cc84ba3beadd.png][]

[4eba7c767d8b48079483714cb63fedb7.png]: https://img-blog.csdnimg.cn/4eba7c767d8b48079483714cb63fedb7.png
[7ff1a3e021e952e11d2e6c1690ccd40b.png]: https://img-blog.csdnimg.cn/img_convert/7ff1a3e021e952e11d2e6c1690ccd40b.png
[e0a91439fed991d9e2248d0bf91ca1b7.png]: https://img-blog.csdnimg.cn/img_convert/e0a91439fed991d9e2248d0bf91ca1b7.png
[e56ceb30a3c73d7272a4cc84ba3beadd.png]: https://img-blog.csdnimg.cn/img_convert/e56ceb30a3c73d7272a4cc84ba3beadd.png