[SQL随笔] 关于 XXX over( partition by XXX)的记录,ps:（也就是开窗函数）

擦，从来没想到sql的水这么深，/(ㄒoㄒ)/~~

刚接触这个确实有些懵圈

over( partition by ) 函数

简介

在 ISO SQL中被称为开窗函数，在orcale中被称为分析函数，Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

资料：
用的示例数据来自：https://www.cnblogs.com/lcngu/p/5335170.html

添加一些测试数据

create database `test` character set 'utf8' collate 'utf8_general_ci';

drop table if exists test.t_person;
create table test.t_person (fname varchar(20),fcity varchar(20),fage int,fsalary int);

insert into test.t_person(fname,fcity,fage,fsalary)
values('tom','beijing',20,3000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('tim','chengdu',21,4000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('jim','beijing',22,3500);
insert into test.t_person(fname,fcity,fage,fsalary)
values('lily','london',21,2000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('john','newyork',22,1000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('yaoming','beijing',20,3000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('swing','london',22,2000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('guo','newyork',20,2800);
insert into test.t_person(fname,fcity,fage,fsalary)
values('yuqian','beijing',24,8000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('ketty','london',25,8500);
insert into test.t_person(fname,fcity,fage,fsalary)
values('kitty','chengdu',25,3000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('merry','beijing',23,3500);
insert into test.t_person(fname,fcity,fage,fsalary)
values('smith','chengdu',30,3000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('bill','beijing',25,2000);
insert into test.t_person(fname,fcity,fage,fsalary)
values('jerry','newyork',24,3300);

开窗函数的语法：
over(partition by 列名1,列名2 …… order by 列名3,列名4 …… [desc])

括号中的两个关键词partition by和order by可以只出现一个。

partition by和order by后面的列名可以根据需求设定任意数量个列名。

order by后面可以选择是否跟desc，加上为倒序排序（从大到小），不加则默认为从小到大排序。

over()前面是一个函数。

row_number over (partition by fage order by fage desc)

为每个分区中的每一行都添加一个编号（有序）

例如：

1. 按月分区，每个月有几天，对这几天的数据排序，并生成序号

2. 依据示例数据可以有以下操作：按fage分区后依据fsalary进行排序
   如图：

   select
       row_number() over (partition by t1.fage order by t1.fsalary) as number,
          t1.fname, fcity, fage, fsalary, fname, fcity, fage, fsalary
   from test.t_person t1;
   

   

3. 其它示例：
   具看这个手机tt.fk_user_id 本月or本周，受理过了多少工单，运用这个函数可以做到。
   https://blog.csdn.net/qq_37545120/article/details/83994074

资料：
OVER(PARTITION BY)函数介绍
https://www.cnblogs.com/lcngu/p/5335170.html

row_number() over partition by 分组聚合
https://www.cnblogs.com/starzy/p/11146156.html

使用开窗函数计算当前月到1月的累计值

sum(t2.lfimg_month) over(partition by t2.werks,substr(t2.year_month,1,4) order by t2.year_month)
lfimg_month：是每个月的值
over(partition by t2.werks,substr(t2.year_month,1,4) order by t2.year_month)按年度、werks进行分组后依据月份进行排序，对每个分组中的werks值分别进行求和

select
t2.year_month
,t2.werks
,t2.lfimg_month
,sum(t2.lfimg_month) over(partition by t2.werks,substr(t2.year_month,1,4) order by t2.year_month)
from
(
select
substr(t1.lfdat,1,6) as year_month
,t1.werks as werks
,sum(t1.lfimg) as lfimg_month
from
db_dw_dev.dwb_inv_delivery_certificate_f t1
where
t1.wbstk='C'
and t1.lgort='1030'
group by
t1.werks
,substr(t1.lfdat,1,6)
-- ,t1.lfimg
) t2
order by
substr(t2.year_month,1,6)
desc;

不使用开窗函数的情况下计算当前月到1月的累计值

1.对每天的数据进行汇总，得到每月的数据

select
t2.year_month
,t2.werks
,t2.lfimg_month
from
(
select
substr(t1.lfdat,1,6) as year_month
,t1.werks as werks
,sum(t1.lfimg) as lfimg_month
from
db_dw_dev.dwb_inv_delivery_certificate_f t1
where
t1.wbstk='C'
and t1.lgort='1030'
group by
t1.werks
,substr(t1.lfdat,1,6)
-- ,t1.lfimg
) t2
order by
substr(t2.year_month,1,6)
desc;

结果如下：

2. 使用左联结，自己关联自己。联结的条件为右表的月份大于等于左表的一月，小于等于左表的当前月，同时用年相等和werks相等作为mapping条件

select
*
from
(select
t2.year_month
,t2.werks
,t2.lfimg_month
from
(
select
substr(t1.lfdat,1,6) as year_month
,t1.werks as werks
,sum(t1.lfimg) as lfimg_month
from
db_dw_dev.dwb_inv_delivery_certificate_f t1
where
t1.wbstk='C'
and t1.lgort='1030'
group by
t1.werks
,substr(t1.lfdat,1,6)
-- ,t1.lfimg
) t2
order by
substr(t2.year_month,1,6)
desc) m1
    left join
(select
t2.year_month
,t2.werks
,t2.lfimg_month
from
(
select
substr(t1.lfdat,1,6) as year_month
,t1.werks as werks
,sum(t1.lfimg) as lfimg_month
from
db_dw_dev.dwb_inv_delivery_certificate_f t1
where
t1.wbstk='C'
and t1.lgort='1030'
group by
t1.werks
,substr(t1.lfdat,1,6)
-- ,t1.lfimg
) t2
order by
substr(t2.year_month,1,6)
desc) m2 on
    m2.year_month>=concat(substr(m1.year_month,1,4),'01')
    and m2.year_month<=m1.year_month
    and substr(m2.year_month,1,4)=substr(m1.year_month,1,4) and m1.werks=m2.werks;

结果如下：

3. 最后再对以上结果按m1.year_month，m1.werks，m1.lfimg_month进行分组后对m2.lfimg_month求和，即可得到当月的值和当月到1月的累计值。

资料

Hive第六天——Hive函数（开窗函数之累计统计）
https://blog.csdn.net/weixin_45473750/article/details/101553475