窗口函数之rows的用法(读书笔记二)

最新推荐文章于 2025-05-27 16:50:09 发布

原创最新推荐文章于 2025-05-27 16:50:09 发布 · 1.4k 阅读

1 ·

CC 4.0 BY-SA版权

读书笔记（开发）专栏收录该内容

27 篇文章

订阅专栏

本文详细解析了SQL窗口函数ROWS的用法，包括分组、排序、累计求和等多种应用场景，通过具体代码实例帮助理解如何在查询中应用ROWS函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--rows的具体用法

SELECT
emp_id,emp_name,dept_id,hire_date,salary,
--首先按dept_id进行分组，其次按照hire_date进行排序，然后再把入职时间小于等于自身的所有员工薪资进行累计
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date) sum_salary_part_order,
--按照dept_id进行分组，并统计该部门下所有员工的薪资
SUM(salary) OVER(PARTITION BY dept_id) sum_salary_part,
--直接按照hire_date进行分组，再把入职时间小于等于自身的所有员工薪资进行累计
SUM(salary) OVER(ORDER BY hire_date) sum_salary_order,
--后面均为首先按dept_id进行分组，其次按照hire_date进行排序，且所有统计不能跨越其所在分区，故不再重复
--窗口范围为该分区的第一行和该分区的最后一行，与sum_salary_part等同
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum_1,
--窗口范围为该分区的第一行和本行，与sum_salary_part_order等同
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_2,
--窗口范围为该分区的第一行和本行的前一行，统计的是第一行和本行前一行薪资的累计
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND 1/*value_expr*/ PRECEDING) sum_3,
--窗口范围为该分区的第一行和本行的后一行，统计的是第一行和本行后一行薪资的累计
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND 1/*value_expr*/ FOLLOWING) sum_4,
--窗口范围为本行和该分区的最后一行，统计的是大于等于本记录hire_date之后的所有薪资
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) sum_5,
--窗口范围是本行，所以与本行薪资一样
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN CURRENT ROW AND CURRENT ROW) sum_6,
--窗口范围为本行和本行的后一行，统计本行和后一行的薪资累计
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN CURRENT ROW AND 1/*value_expr*/ FOLLOWING) sum_7,
--窗口范围为本行和本行的后一行，统计的本行前一行和本分区最后一行的薪资累计，如本行为分区首行，则直接从本行开始算起
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN 1/*value_expr*/ PRECEDING AND UNBOUNDED FOLLOWING) sum_8,
--窗口范围为该分区的第一行和本行前一行，统计的是本行前一行和本行的薪资累计
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN 1/*value_expr*/ PRECEDING AND CURRENT ROW) sum_9,
--窗口范围为该分区的本行前value_expr1到本行前value_expr2的累计，本例为本行前2行和前1行的累计，强调value_expr1>value_expr2
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN 2/*value_expr1*/ PRECEDING AND 1/*value_expr2*/ PRECEDING) sum_10,
--窗口范围为该分区的本行前value_expr1到本行后value_expr2的累计，本例为本行前1行和后2行的累计
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN 1/*value_expr*/ PRECEDING AND 2/*value_expr*/ FOLLOWING) sum_11,
--窗口范围为该分区的本行后一行和本区最后一行，统计的是本行后一行和本区最后一行的薪资累计
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN 1/*value_expr*/ FOLLOWING AND UNBOUNDED FOLLOWING) sum_12,
--窗口范围为该分区的本行后value_expr1到本行后value_expr2的累计，本例为本行后1行和后2行的累计，强调value_expr1<=value_expr2
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS BETWEEN 1/*value_expr1*/ FOLLOWING AND 2/*value_expr2*/ FOLLOWING) sum_13,
--窗口范围为该分区的第一行，结束行默认本行，与sum_salary_part_order,sum_2等同
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS UNBOUNDED PRECEDING) sum_14,
--窗口范围仅为当前行，所以与本行薪资一样，与sum_6一样
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS CURRENT ROW) sum_15,
--窗口范围为该分区的第一行和本行前一行，统计的是本行前一行和本行的薪资累计
SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date 
ROWS 1/*value_expr*/ PRECEDING) sum_16
FROM employeeinfo
ORDER BY dept_id,hire_date;