一、窗口函数
窗口函数也称为OLAP(Online Anallytical Processing)函数,意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。
窗口函数语法格式:
< 窗口函数 > OVER ([ PARTITION BY < 列清单 >]ORDER BY <排序用列清单>)注:[ ] 中的内容可以省略
SQL语言中的窗口函数是一种功能强大的工具,用于对结果集中的数据进行分组、排序和聚合操作。窗口函数兼具分组和排序两种功能。
窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下。
以下是一些常见的窗口函数及其函数说明和用法结构:
1、RANK( )序号函数-并列排序:
函数说明:为结果集中的每一行分配一个排名值,相同值的行具有相同的排名,并且可能有间隔。RANK 是用来计算记录排序的函数。(1,2,2,4,5)
函数用法结构:RANK( ) OVER ( PARTITION BY column1
ORDER BY column2 DESC )
- PARTITION BY 能够设定排序的对象范围。
- ORDER BY 能够指定按照哪一列、何种顺序进行排序。
- 由于专用窗口函数无需参数,因此通常括号中都是空的。
- 将聚合函数作为窗口函数使用时,会以当前记录为基准来决定汇总对象的记录。
SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
ORDER BY sale_price
) AS ranking
FROM Product;
使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中,PARTITION BY 并不是必需的,即使不指定也可以正常使用窗口函数。
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
2、ROW_NUMBER( )序号函数-顺序排序:
函数说明:为结果集中的每一行分配一个唯一的数字标识。(1,2,3,4,5)
函数用法结构:ROW_NUMBER() OVER (PARTITION BY column1
ORDER BY column2 )注:赋予唯一的连续位次。例)有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位……
3、DENSE_RANK()序号函数-并列排序:
函数说明:为结果集中的每一行分配一个排名值,相同值的行具有相同的排名,没有间隔。 函数用法结构:DENSE_RANK() OVER (PARTITION BY column1
ORDER BY column2 )
注:即使存在相同位次的记录,也不会跳过之后的位次。
例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位……
比较RANK、DENSE_RANK、ROW_NUMBER的结果
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking, # 并列排序
DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking, #并列排序
ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num #顺序排序
FROM Product;
注:使用 RANK 或 ROW_NUMBER 时无需任何参数,只需要像 RANK ()或者 ROW_NUMBER() 这样保持括号中为空就可以了。
4、NTILE()分位数函数:
函数说明:用于将数据分成指定数量的近似相等的组。这个函数常用于数据的分位数计算或 者等级划分。
函数用法结构:NTILE(n) OVER (
PARTITION BY column1
ORDER BY column2
)
5、LAG()前后函数:
函数说明:获取结果集中当前行之前指定偏移量的行的值。返回当前行的前n行的exper的值
函数用法结构:LAG ( COLUMN, OFFSET, default_value )
OVER ( PARTITION BY column1
ORDER BY column2
)
6、LEAD()前后函数:
函数说明:用于获取当前行之后的一行或多行的值,返回当前n行的expr的值。
函数用法结构:LEAD(column, offset, default) OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
7、FIRST_VALUE()头尾函数:
函数说明:获取结果集中每个分组的第一行的值。
函数用法结构:FIRST_VALUE(column)OVER (
[PARTITION BY partition_expression ]
ORDER BY sort_expression [ASC | DESC]
)
8、LAST_VALUE()头尾函数:
函数说明:获取结果集中每个分组的最后一行的值。
函数用法结构:LAST_VALUE(column) OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
SELECT
product_id,
product_name,
price,
LAST_VALUE(price) OVER (PARTITION BY product_category ORDER BY price DESC) as last_price
FROM
products;
上述查询将返回每个产品的产品ID、产品名称、价格以及按产品类别分组并按价格降序排序的最后一个价格。通过使用 LAST_VALUE() 函数,我们可以轻松地获取窗口中当前行的最后一个值。
二、窗口函数使用的聚合函数
所有的聚合函数都能用作窗口函数,其语法和专用窗口函数完全相同。
1、SUM()求和函数:
函数说明:计算结果集中每个分组的指定列的总和。
函数用法结构:SUM(column) OVER (PARTITION BY column1 ORDER BY column2)我们得到的并不仅仅是合计值,而是按照 ORDER BY 子句指定的 product_id 的升序进行排列。
在按照时间序列的顺序,计算各个时间的销售额总额等的时候,通常都会使用这种称为 累计 的统计方法。
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
2、AVG()求平均值函数:
函数说明:计算结果集中每个分组的指定列的平均值。
函数用法结构:AVG(column) OVER (PARTITION BY column1 ORDER BY column2)
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;
三、窗口函数的适用范围
目前为止我们学过的函数大部分都没有使用位置的限制,最多也就是在 WHERE 子句中使用聚合函数时会有些注意事项。但是,使用窗口函数的位置却有非常大的限制。
原则上窗口函数只能在SELECT子句中使用。
原因:在 DBMS 内部,窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。在得到排序结果之后,如果通过 WHERE 子句中的条件除去了某些记录,或者使用GROUP BY 子句进行了汇总处理,则排序结果也无法使用。
在 SELECT 子句之外“使用窗口函数是没有意义的”
窗口函数可以根据指定的PARTITION BY子句对数据进行分组,然后按照指定的ORDER BY子句排序。它们可以在SELECT语句中使用,为结果集中的每一行计算一个值,并将结果作为额外的列返回。窗口函数可以在聚合函数内使用,也可以与其他普通列一起使用,以实现更复杂的数据分析和报表需求。