https://www.cnblogs.com/h-kang/p/10916727.html
一、MySQL & HiveSQL
1、窗口函数语法
FUNCTION_NAME([argument_list])
OVER (
[PARTITION BY window_partition,…]
[ORDER BY window_ordering, … [ASC|DESC]])
[ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

示例：sum(sale) over(partition by city order by year rows between 1 preceding and 1 following)

窗口函数基于当前行的概念，当前行属于某个窗口，窗口范围由over里的如下关键字来指定：

partition by 子句：窗口按照哪个字段分组,可以多个字段

order by 子句：窗口按照哪个字段排序，可以和partition by 子句配合使用，也可以单独使用。另外，可以按照多列进行排序，用逗号隔开，可搭配asc,desc，如 order by age asc,gender desc
使用order by时 最后select结果输出时也是按照排序好的输出。

窗口分区frame子句：对当前分区在进行细分，获取当前分区的一个子集，定义子集的规则，通常用作滑动窗口使用。具体使用方式如下：

frame子句有两种方式选取子集：
①rows between frame_start and frame_end 是基于行号筛选
②range between frame_start and frame_end 是基于值的范围筛选

frame_start 和 frame_end支持如下关键字：
1、current row ：当前行
2、unbounded preceding ：分区中的第一行
3、unbounded following：分区中的最后一行
4、n preceding：当前行之前的n（数字或表达式）行
5、n following：当前行之后的n（数字或表达式）行

注：以上三个子句都可以单独使用，或不使用（over里无参数表示分区是整体）;
但如果有frame子句，常结合order by 子句使用（如果不排序滑窗计算无意义）；
如果在order by子句后面没有指定frame窗口子句，则默认为：range between unbounded preceding and current row 或则是 rows between unbounded preceding and current row

2、 常用的窗口函数
普通排序类：rank()、dense_rank()、row_number()
分布排序类：percent_rank()、cume_dist()
聚合类：sum(列名)、avg(列名)、count(列名)，max(列名)，min(列名），注意不支持count(distinct 列名)
相对引用类：lag(参数)、lead参数)、first_value(列名)、last_value(列名)、nth_value(参数))、ntitle(参数)
函数名 是否需要参数 参数形式 含义
row_number 否 不重复不间断的序号，不管排序的值是否有相同值
rank 否 重复间断的序号，并列的采用相同序号，如1,1,3,4
dense_rank 否 重复不间断的序号，并列的采用相同序号，如1,1,2,3
percent_rank 否 分组内当前行的RANK值-1/分组内总行数-1
cume_dist 否 如果升序排列，则统计小于等于当前值的行数/分组内总行数；如果降序排列，则统计大于等于当前值得行数/分组内总行数
sum/avg等聚合类 是 col列名 对分组内求聚合
lag 是 3个参数：（col,[N,[default]]） 偏移函数，从当前行开始往前取N行。第一个参数列名，第二个参数偏移量，第三个参数超出记录窗口时的默认值，N默认为1，default默认为null
lead 是 3个参数：（col,[N,[default]]） 偏移函数，从当前行开始往后取N行。第一个参数列名，第二个参数偏移量，第三个参数超出记录窗口时的默认值，N默认为1，default默认为null
first_value 是 col列名 返回分区中截止到当前行不为null的第一值
last_value 是 col列名 返回分区中截止到当前行不为null的的最后一个值，等价于rows between unbounded preceding and current row
nth_value 是 2个参数：(col,N) 返回分区中截止到当前行的第N行的值
ntitle 是 N 将分组数据按照顺序切成n片，返回当前切片值，如果切片不均匀，优先增加编号小的分布。类似于等频分箱得意思；需求场景比如按某列排序后取一部分数据，比如看前1/3的数据。
注意，排序类函数不支持frame子句

3、 窗口函数在整体运行顺序中所在位置
…->where -> group by ->聚合函数 -> having -> 窗口函数 -> select->…

所以是可以在整体group by之后在使用窗口函数，最终显示的行数也是group by 之后的行数
如下，求总成绩排名：

SELECT s_id
,SUM(score)总成绩
,RANK()over(ORDER BY SUM(score) DESC )排名 FROM sc
GROUP BY s_id;
1
2
3
4
4、例题
Hive Sql 经典面试题
如下都使用此题数据
数据准备（使用mysql计算的）

create table acess_time (username varchar(20),month varchar(20),num int);
insert into acess_time
values (‘A’,’2020-01’,5),
(‘A’,’2020-01’,15),
(‘B’,’2020-01’,5),
(‘A’,’2020-01’,8),
(‘B’,’2020-01’,25),
(‘A’,’2020-01’,5),
(‘A’,’2020-02’,3),
(‘A’,’2020-02’,7),
(‘B’,’2020-02’,10),
(‘B’,’2020-02’,5);
1
2
3
4
5
6
7
8
9
10
11
12
便捷方法：
理解先运行聚合函数在运行窗口函数

select
username
,month
,sum(num) as month_cnt
,sum(sum(num))over(partition by username order by month rows ) as cum_cnt
— 注意order by 后未接frame子句，表示rows between unbounded preceding and current row ,相当于cumsum
from acess_time
group by username,month;
1
2
3
4
5
6
7
8
结果：

二、Python-Pandas
对标sql的窗户函数功能拆解在Python中的实现
数据使用上面例子数据：

1、逐条显示聚合结果，不合并显示，使用transform
data.groupby([‘user’])[‘num’].transform(‘sum’)

结果

0 43
1 43
2 43
3 43
4 43
5 43
6 45
7 45
8 45
9 45
Name: num, dtype: int64
1
2
3
4
5
6
7
8
9
10
11
12
13
2、对标SQL窗口函数partition by ，使用groupby
3、对标SQL窗口函数order by ，使用sort_valules
4、对标SQL窗口函数frame，使用rolling,expanding
具体使用参考我的另一篇文章：时间日期处理-pandas

rolling和expanding已经是实现了groupby().transform()的功能。
rolling还可以处理时序问题，sql窗口函数不可以；但rolling函数在移动窗口大小选择上不如sql窗口灵活。

5、对标sql窗口函数函数功能
聚合类sum等，python 中sum等聚合函数可实现
lag/lead函数，python中shift函数可以实现，仍见另一篇时间日期处理-pandas
排序类rank,dense_rank(),row_number()，python中使用rank()函数
按升序排序
rank(method,ascending=True)
按降序排序
rank(method,ascending=False)

method方法 含义
method=‘min’ 等同于sql的rank
method= ‘dense’ 等同于sql的dense_rank
method= ‘firsrt’ 等同于sql的row_number
first_value等目前没有发现python自带函数，但可以自己定义来写，用rolling().apply(自定义函数)，或groupby().transform(自定义函数)来实现。
参考文章：
————————————————
版权声明：本文为CSDN博主「胡桃夹子zy」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/hutao_ljj/article/details/115792178