SQL进阶——开窗函数

开窗函数

注：一般是要看到统计/排序等结果又要看到各行的详细数据则用开窗函数

汇总计算

sum(字段) over (partition by 分组字段)
count(字段) over (partition by 分组字段)
avg(字段) over (partition by 分组字段)
……

sum(字段) over (partition by 分组字段 order by 排序字段 desc/asc)
count(字段) over (partition by 分组字段 order by 排序字段 desc/asc)
avg(字段) over (partition by 分组字段 order by 排序字段 desc/asc)
……

排序和排名

rank() over(partition by 分组字段 order by 排序字段desc/asc)
要求排序且要看到排名（分数一样排名也一样，会跳名次数）
dense_rank() over(partition by 分组字段 order by 排序字段 desc/asc)
不会跳过排名，即使有相同值的记录
row_number() over(partition by 分组字段 order by 排序字段 desc/asc)
为每一行分配一个唯一的顺序号，而不关心是否有相同值的记录

行偏移函数

lag(展示字段名,前多少位数,默认值) over(partition by 分组字段 order by 排序字段)
lead(展示字段名,后多少位数,默认值) over(partition by 分组字段 order by 排序字段)

开窗函数 Lag 和 Lead 的作用是获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。

答疑

那是不是就意味着只要排序的适合用rank,需要其他求和，求平均的情况用相应的函数以及over(partition by……)实现

确实如此，你可以根据具体的需求选择适当的窗口函数来实现不同的功能。以下是一些更详细的指导原则：

排序和排名

● RANK(): 当你需要根据某个或某些字段的值对数据进行排名，并且希望相同值的记录有相同的排名时使用，且需要考虑排名跳跃（即相同值之后的排名不是连续的），则使用 RANK()。
举例：1，1，1，4，5，6，6，8
● DENSE_RANK(): 与 RANK() 类似，但是不会跳过排名，即使有相同值的记录。
举例：1，1，1，2，3，4，4，5
● ROW_NUMBER(): 如果你需要为每一行分配一个唯一的顺序号，而不关心是否有相同值的记录，可以使用 ROW_NUMBER()。
举例：1，2，3，4，5，6，7

汇总计算

● SUM(): 当你需要计算某字段值的累积总和时使用。
● AVG(): 计算某字段值的平均值。
● MIN() / MAX(): 分别用于找到某字段值的最小值和最大值。
统计计数
● COUNT(): 用于计算某字段非空值的个数，或者计算所有行的数量（COUNT(*)）。

使用 OVER (PARTITION BY … ORDER BY …) 子句

● PARTITION BY: 将数据划分为多个逻辑分区，窗口函数将在每个分区内独立执行。
● ORDER BY: 在每个分区内对数据进行排序，这对于像 RANK(), ROW_NUMBER() 这样的函数尤为重要，因为它们的结果依赖于行的顺序。

示例

假设我们有一个员工表 employees，包含 employee_id, name, department, salary 字段，我们可以使用这些窗口函数来进行各种分析：
● 排名：

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

这将返回每个部门内部按薪水降序排列的员工排名。
● 累积求和：

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY employee_id) as cumulative_salary
FROM employees;

这将显示每个部门内按员工ID顺序累积的薪水总额。
● 平均值：

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees;

这将显示每个部门的平均薪水。

● Lead and Lag：

SELECT name, department, salary,
       LEAD(salary) OVER (PARTITION BY department ORDER BY salary DESC) as next_salary,
       LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) as previous_salary
FROM employees;

这将显示每个部门内按薪水降序排列的员工的下一个和前一个薪水。

● 最大值和最小值：

SELECT name, department, salary,
       MAX(salary) OVER (PARTITION BY department) as max_salary,
       MIN(salary) OVER (PARTITION BY department) as min_salary
FROM employees;

这将显示每个部门的最高和最低薪水。

● 行数：

SELECT name, department, salary,
       COUNT(*) OVER (PARTITION BY department) as total_employees
FROM employees;

这将显示每个部门的员工总数。

● 百分比：

SELECT name, department, salary,
       salary / SUM(salary) OVER (PARTITION BY department) * 100 as salary_percentage
FROM employees;

这将显示每个员工在其部门薪水总额中所占的百分比。

可以根据自己的需要灵活使用。