SQL知识之窗口函数

前言：窗口函数属于SQL的高级查询功能，有些数据库的低版本可能还不支持，如MySQL5.7版本暂不支持，SQL Server是支持的。

1、窗口函数的简单介绍

• 窗口函数即 Window Function，也称为OLAP函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。
• 窗口函数与聚合函数类似，但是每一行数据都生成一个结果。
• 聚合函数可以将多行数据按照规定聚合为一行，一般来说聚合后的行数要少于聚合前的行数，但是有时我们想要既显示聚合前的数据，又要显示聚合后的数据，这时便引入了窗口函数。
• 窗口函数在SELECT时执行的，位于ORDER BY之前。

2、累计计算窗口函数

2.1 普通累计

• 适用于聚合函数，如 sum() over(partition by … order by …)
• partition by 起到了分组的作用
• order by 按照什么顺序进行累加，升序ASC、降序DESC，默认升序

# 以年为分组，按月份累加计算营业额
SELECT month,
		amount,
		sum(amount) over (partition by year order by month)
FROM trade

2.2 移动累计

• 普通累计基础上加上限制计算移动平均的范围，如移动的累计求近三个月营业额的情况
• sum() over(partition by … order by …rows between… preceding… and…following)
• rows between：包含的行
• preceding：前面的行；unbounded preceding：前面的所有行
• following：后面的行；unbounded following：后面的所有行

rows between unbounded  preceding and current row   # 包括本行和之前的所有行
rows between 3  preceding and current row       # 包括本行以内和前三行
rows between current row and unbounded following   # 包括本行和之后所有的行
rows between 3  preceding and 1 following      #  从前三行到下一行


# 以年为分组，移动的累计求近三个月营业额的情况
SELECT month,
		amount,
		sum(amount) over (partition by year order by month
		rows between 2  preceding and current row)
FROM trade

3、排序窗口函数

• 有三种排序窗口函数：row_number() 、 rank() 、 dense_rank()
• 都是返回相应规则的排序序号
• row_number() 括号内是不加任何字段名称的，rank() 和dense_rank()也是一样。

3.1 row_number()

• row_number() over(partition by … order by )
• 按partition by 分组，order by 排序，分组之间的排序互相独立。
• 为查询出来的每一行记录生成一个序号，依次排序且不会重复，相同值取不同序号，不存在序号跳跃。

SELECT *,
		ROW_NUMBER() OVER(PARTITION BY goods ORDER BY trade_date DESC) as rank
FROM trade

3.2 rank()

• rank() over(partition by … order by )
• 按partition by 分组，order by 排序，分组之间的排序互相独立。
• 为查询出来的每一行记录生成一个序号，相同记录取相同序号，存在序号跳跃，如有两个第一名时接下来就是第三名。
• rank()返回的排名和我们上学时学生成绩表排名一样，会有相同排名，也会有跳跃排名

SELECT *,
		RANK() OVER(PARTITION BY goods ORDER BY trade_date DESC) as rank
FROM trade

3.3 dense_rank()

• dense_rank() over(partition by … order by )
• 按partition by 分组，order by 排序，分组之间的排序互相独立。
• 为查询出来的每一行记录生成一个序号，相同记录取相同序号，不存在序号跳跃，如有两个第一名时接下来仍然还是第二名。

SELECT *,
		DENSE_RANK() OVER(PARTITION BY goods ORDER BY trade_date DESC) as rank
FROM trade

4、切分窗口函数

• ntile(n) over(partition by … order by …)
• 按partition by 分组，order by 排序，将数据切分成 n 份，返回当前属于第几份
• 如果切片不均匀，剩余数据会平分到前面的几份。
• 常用于取前百分之几或几分之几的数据

# 将支付金额按顺序分成5份，即分成5个等级
SELECT *,
		NTILT(5) OVER(PARTITION BY goods ORDER BY amount DESC) as level
FROM trade

5、偏移窗口函数

• 偏移窗口函数包括lag() 、lead()
• 偏移窗口函数，可以在同一次查询中取出同一个字段的前N行和前N行的数据作为独立的列

5.1 lag()

• lag(col,offset, default) over(partition by … order by …) ，用于取出当前行的前N行
• col是字段名称，offset是偏移量，default是偏移后取不到值时的默认值

SELECT id,
		LAG(name, 1, name) OVER(ORDER BY id) as lag_name
FROM person

5.2 lead()

• lead(col,offset, default) over(partition by … order by …) ，用法和lag()相同，只不过它用于取出当前行的后N行

• col是字段名称，offset是偏移量，default是偏移后取不到值时的默认值

SELECT id,
		LEAD(name, 1, name) OVER(ORDER BY id) as lead_name
FROM person

6、first_value() , last_value()

6.1 first_value()

• first_value() over(partition by … order by …)
• 取分组内排序后，截止到当前行，第一个值

SELECT id,
		 FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM person

6.2 last_value()

• last_value() over(partition by … order by …)
• 取分组内排序后，截止到当前行，最后一个值

SELECT id,
		 LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM person