【Leetcode】SQL窗口函数

1. 简单介绍

• 概念：窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。窗口函数作用于一个数据行集合。

• 类型：
  (1) 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
  (2) 聚合函数，如sum. avg, count, max, min等

2. 应用场景

每组内的排名问题、Top N问题，直接用GROUP BY会聚合成一行。窗口函数不减少原表的行数。

3. 例子

1. 组内排名：先分组partition by，排名依据order by

SELECT *, 
rank() over (partition by 班级
ORDER BY 成绩 DESC) AS ranking
FROM 班级表

• partition by分组后的结果称为“窗口”。

<窗口函数> over (partition by <用于分组的列名>                order by <用于排序的列名>)

2. 三种rank

• rank 并列名次占用下一名次的位置，5558
• dense_rank 并列名词不占用下一名次的位置，5556
• row_number 按照行数增加，5678

3. 聚合函数作为窗口函数

• 求和、平均、计数、最大最小值，都是针对自身记录、以及自身记录之上的所有数据进行计算得到结果

• 作用：可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

• partition子句可省略，省略就是不指定分组，只排序。

4. 业务向的SQL题：查询用户最长连续登录天数

1. 对用户ID做分组，组内按登录时间正序，用窗口函数加row number
2. 做差，日期-行数，同一组内若差一致则表示日期前后连续，按照差的大小分组，计算组内行数即为连续天数
3. 选出各个用户ID里最长的连续天数

SELECT UID, MAX(continuous_days)
FROM 
(SELECT UID, 
date_sub(date1 - sort) AS login_group, 
MIN(date1) AS start_date,
MAX(date1) AS end_date,
COUNT(*) AS continuous_days
FROM
(SELECT UID, date1, ROW_NUMBER() OVER (PARTITION by UID ORDER BY date1) AS sort
FROM user_login)
GROUP BY UID, login_group)
GROUP BY UID

（写于电话面试被问到窗口函数后哑口无言的这一天，我该怎么告诉面试官现在我好像会写窗口函数了呢qwq）

【参考博客】
1.https://blog.csdn.net/ganghaodream/article/details/100083543
2.https://zhuanlan.zhihu.com/p/92654574