SQL窗口函数整理（校招or实习）

SQL窗口函数干货整理(实习/校招；持续更新ing)

窗口函数通用格式如下

function(args) over ([partition by  expression] [order by expression [ASC/DESC]][frame])

function-功能函数
partition by-分组
order by-排序依据
ASC/DESC-默认ASC升序
frame-滑窗

1. 排序函数

row_number()  序号不重复，序号连续
rank()   序号可以重复，序号不连续
dense_rank()  序号可以重复，序号连续

不分组排序：leetcode178
分组排序：leetcode184

2. 聚合函数实现滑动窗口函数

聚合函数：

COUNT(col)-返回所有非空值的个数（空值不计入）
AVG(col)-返回平均值
SUM(col)-返回总和
MAX(col)-返回最大值
MINKcol)-返回最小值
FIRST(col)-返回第一个记录的值
LAST(col)-返回最后一个记录的值

滑动行范围的常用表达:

{RANGE|ROWS} frame_start -指定行
{RANGE|ROWS} BETWEEN frame_start AND frame_endUNBOUNDED PRECEDING-开始结束都指定
expression PRECEDING -- only allowed in RoWs mode-向上两行2 PRECEDING
CURRENT ROW-从开始到当前行
expression FOLLOWING -- only allowed in ROws mode-向下两行2 FOLLOWING
UNBOUNDED FOLLOWING-从当前行到结尾

range与rows区别：

例子：(近三个月的平均值)

SELECT product2 , 
	year_month3 ,
	gmv ,
	avg(gmv) OVER (PARTITION BY department, product ORDER BY year_month ROWs 2 PRECEDING) AS avg_gmv
FROM product

3. 前后函数

可用来求环比同比_lag()/lead()

lag(expression,n):返回当前行的前n行
lead(expression,n):返回当前行的后n行

知识普及：
**同比（同比增长率）**是以上年同期为基期相比较，即本期某一时间段与上年某一时间段相比，如2018年4月份与2017年4月份相比较。
**环比（环比增长率）**是与上一个相邻统计周期相比较，即n月与第n-1月的比较。 如2018年4月份与2018年3月份相比较。

注：当数据表的日期不连续时可以join一个万年历表格，这样就可以用lag或者lead取对应行计算环比/同比啦。

4. 求top10%的信息

取百分数，主要是对标产品池量级

percent_rank()-(分组内当前行的RANK值-1)/(分组内总行数-1)
cume_dist()-小于等于当前值的行数/分组内总行数
row_number()

用row_number排序也可以实现：两个select嵌套+ceiling向上取整

5. NTILE(n)函数

功能:
将排序分区中的行划分为特定数量的组。
对于每一行，NTILE()函数返回一个桶号，表示行所属的组。

• 如果分区行的数量不能被整除n，则NTILE()函数将生成两个大小的组，差异为1；
• 较大的组总是以ORDER BY子句指定的顺序位于较小的组之前；
• 如果分区行的总数可被整除n，则行将在组之间平均分配