SQL 常用函数

开窗函数子句 rows、range

关键字

• rows ：指行，包含边界行
• range ：数值范围，包含边界值
• Unbounded ：是无边界
• Preceding ：向上，减去，提前
• Following ：向下，加上，延后
• Current row ：当前行

语法使用说明

unbound和current row

其中数值1和2可以替换成任意值，也可以直接使用unbounded和current row ，其中unbounded表示不做限制，current row 表示当前行

rows between unbounded preceding and unbounded following

含义：按照分组内全部行求和，不做任何限制

rows between unbounded preceding and current row

含义：从分组内排序的起始行到当前行

rows unbounded preceding

含义：(同上)从分组内排序的起始行到当前行

range between unbounded preceding and unbounded following

含义：按照分组内全部行求和，不做任何限制

range between unbounded preceding and unbounded following

含义：从分组内排序的起始行的值到当前行的值

用例:

| Id | Month | salary     |
| -- | ----- | ---------- |
| 1  | 8     | 90         |
| 1  | 7     | 90         |
| 1  | 4     | 60         |
| 1  | 3     | 40         |
| 1  | 2     | 30         |
| 1  | 1     | 20         |
| 2  | 2     | 30         |
| 2  | 1     | 20         |
| 3  | 4     | 70         |
| 3  | 3     | 60         |
| 3  | 2     | 40         |

计算出每个员工的 累计工资汇总, 每月统计当前月份最近的三个月薪资之和, 说明: 当8月份时, 统计8月,7月,6月 工资总和...每月以此类推, 不考虑跨年

# 用法一
SELECT 
    Id, Month
    , SUM(Salary) OVER (PARTITION BY Id ORDER BY Month range 2 PRECEDING) AS Salary
    , rank() OVER (PARTITION BY Id ORDER BY Month DESC) AS r
FROM Employee
ORDER BY Id, Month desc

# 用法二
SELECT 
    Id, Month
    , SUM(Salary) OVER (PARTITION BY Id ORDER BY Month desc range between  0 PRECEDING and 2 following ) AS Salary
    , rank() OVER (PARTITION BY Id ORDER BY Month DESC) AS r
FROM Employee
ORDER BY Id, Month desc