Over开窗语句_0ver() 开窗-CSDN博客

本文链接：https://blog.csdn.net/paul50060049/article/details/102921623

本文详细介绍了SQL中的开窗语句，包括窗口分区、窗口排序和窗口框架的概念，并通过多个示例展示了如何使用Range和Rows进行窗口限定。此外，还探讨了Rank、Dense_Rank、First_Value、Last_Value、LAG、LEAD以及PERCENT_RANK和PERCENTILE_CONT等函数在求和、分组排序、对比分析等方面的应用。

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

开窗语句

开窗函数支持分区、排序和框架三种元素，其语法格式如下：
  OVER (
         [ <PARTITION BY clause> ]
         [ <ORDER BY clause> ]
         [ <ROW or Range clause> ]
        )

窗口分区：

就是将窗口指定列具有相同值的那些行进行分区，分区与分组比较类似，但是分组指定后对于整个SELECT语句只能按照这个分组，不过
分区可以在一条语句中指定不同的分区。

  <PARTITION BY clause> ::=
  PARTITION BY value_expression , ... [ n ]

窗口排序：

分区之后可以指定排序列，那么在窗口计算之前，各个窗口的行的逻辑顺序将确定。

  <ORDER BY clause> ::=
  ORDER BY order_by_expression
      [ COLLATE collation_name ]
      [ ASC | DESC ]
      [ ,...n ]

窗口框架：

框架是对窗口进行进一步的分区，框架有两种范围限定方式：一种是使用Rows子句，通过指定当前行之前或之后的固定数目的行来限制分区中的行数；
另一种是Range子句，按照排序列的当前值，根据相同值来确定分区中的行数。

示例01: 创建Test表(fName,fClass,fScore)

DROP TABLE Test;
WITH DA (fName,fClass,fScore,fClassName) AS (
  SELECT 'XS001', 12, 74, '计算机系2020级2班' UNION
  SELECT 'XS002', 11, 95, '计算机系2020级1班' UNION
  SELECT 'XS003', 11, 95, '计算机系2020级1班' UNION
  SELECT 'XS004', 11, 80, Null                UNION
  SELECT 'XS005', 12, 92, Null                UNION
  SELECT 'XS006', 13, 99, '计算机系2020级3班' UNION
  SELECT 'XS007', 13, 99, '计算机系2020级3班' UNION
  SELECT 'XS008', 13, 45, Null                UNION
  SELECT 'XS009', 13, 55, '计算机系2020级3班' UNION
  SELECT 'XS010', 13, 78, '计算机系2020级3班'
)
SELECT * INTO Test FROM DA;

示例02: Range,Rows的用法

Select fName,fClass,fScore,
  Sum(fScore) Over(Order By fScore) C1,
  Sum(fScore) Over(Order By fScore Range Between unbounded Preceding AND Current Row) C2,
  Sum(fScore) Over(Order By fScore Rows  Between unbounded Preceding AND Current Row) C3,
  Sum(fScore) Over(Order By fScore Rows  Between 1 Preceding AND 2 Following) C4,
  Sum(fScore) Over(Order By fScore Rows  Between 1 Preceding AND Current Row) C5,
  Sum(fScore) Over(Order By fScore Rows  unbounded Preceding) C6
From Test

-- 运行结果
fName fClass  fScore  C1  C2  C3  C4  C5  C6
---------------------------------------------
XS008   13      45    45  45  45  174 45  45
XS009   13      55    100 100 100 252 100 100
XS001   12      74    174 174 174 287 129 174
XS010   13      78    252 252 252 324 152 252
XS004   11      80    332 332 332 345 158 332
XS005   12      92    424 424 424 362 172 424
XS002   11      95    614 614 519 381 187 519
XS003   11      95    614 614 614 388 190 614
XS006   13      99    812 812 713 293 194 713
XS007   13      99    812 812 812 198 198 812
---------------------------------------------

结果分析：
Range 是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内。
Rows 是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算。（与当前行的值无关，只与排序后的行号相关）

【C1】未指定窗口，所以默认为 Range unbounded Preceding AND Current Row，故此与【C2】值相同。
【C2】Range BETWEEN unbounded preceding AND Current Row 表示指定取值范围为:当前名次与之前所有名次的值。
【C3】Rows BETWEEN unbounded preceding AND Current Row 表示指定取值范围为:当前行与当前行前面的所有行的值。
【C4】Rows BETWEEN 1 preceding AND 2 following 表示指定取值范围为:当前行与前一行和后两行的值。
【C5】Rows BETWEEN 1 preceding AND Current Row 表示指定取值范围为:当前行与前一行的值。
【C6】Rows unbounded Preceding 表示指定取值范围为:当前行与前一行的值,默认到当前行.

示例03: 分组排序

-- 查询各个班级的第一名：分组排名
WITH PM (fName,fClass,fScore,fRanking) AS
(
  select fName,fClass,fScore,rank() over(partition by fClass order by fScore desc)
  from Test
)
SELECT * FROM PM WHERE fRanking=1;
-- 运行结果
-----------------------------------
fName fClass  fScore  fRanking
XS002   11      95      1
XS003