窗口函数：破解复杂SQL查询的秘密武器

窗口函数：破解复杂SQL查询的秘密武器

引用

CSDN

等

11

来源

1.

https://blog.csdn.net/weixin_42698221/article/details/142631055

2.

https://blog.csdn.net/He_200988/article/details/139297098

3.

https://blog.csdn.net/weixin_50680035/article/details/138151589

4.

https://blog.csdn.net/qq_36825778/article/details/139637929

5.

https://blog.csdn.net/weixin_44512005/article/details/139477668

6.

https://blog.csdn.net/u012561308/article/details/144820274

7.

https://juejin.cn/post/7389902952700084278

8.

https://www.cnblogs.com/lubians/p/18160720

9.

https://www.alibabacloud.com/help/zh/sls/user-guide/window-functions

10.

https://juejin.cn/post/7408452427513626662

11.

https://www.xiaoyizhiqu.com/xyzq_news/article/6749b8c84ddd79f11a1e5b15

在SQL查询中，窗口函数（Window Functions）是一种强大的工具，它允许在保持数据行独立性的同时，对数据集中的行进行复杂的计算。与传统的聚合函数不同，窗口函数不会减少返回的行数，而是为每一行数据都返回一个结果。这种特性使得窗口函数在数据分析和处理中具有广泛的应用场景。

窗口函数的基本概念

窗口函数的基本语法结构如下：

<窗口函数> OVER (
  [PARTITION BY column1, column2, ...]
  [ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
  [frame_clause]
)

• 窗口函数：如ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG()、SUM()、AVG()、MIN()、MAX()、COUNT()等。
• PARTITION BY：可选。指定分区列，将结果集分成多个分区，每个分区内的行独立计算窗口函数。如果省略，则整个结果集视为一个分区。
• ORDER BY：在大多数窗口函数中都是必需的，用于指定分区内行的排序方式。
• frame_clause：定义窗口框架，即指定窗口函数操作的行的范围。

窗口函数 vs 聚合函数

窗口函数和聚合函数在SQL查询中扮演着不同的角色：

• 聚合函数：将多条记录聚合为一条数据。它们通常用于对一组值进行计算，并返回计算后的单个值。聚合函数包括求和（SUM）、求平均数（AVG）、求最大值（MAX）、求最小值（MIN）以及计数（COUNT）等。
• 窗口函数：每条记录都会执行，有多少条数据还会返回多少条数据。窗口函数兼具分组和排序的功能，但不具备汇总功能。窗口函数可以在原表上增加字段，并在新字段上显示结果，为每一行数据都返回一个结果。

窗口函数的核心功能

窗口函数的主要功能包括：

1. 排名和排序：

   • ROW_NUMBER()：为每个分区内的行提供一个唯一的序号。
   • RANK()：在分区内对行进行排名，相同值的行会有相同的排名，排名之间会有间隔。
   • DENSE_RANK()：类似于RANK()，但排名之间不会有间隔。

2. 累积计算：

   • SUM()：计算分区内值的总和。
   • AVG()：计算分区内值的平均值。

3. 时间序列分析：

   • LEAD()：获取分区内当前行的下一行的值。
   • LAG()：获取分区内当前行的上一行的值。

4. 分组分析：

   • NTILE()：将分区内的行分成指定数量的近似相等的排名组。
   • CUME_DIST()：计算当前行在其分区中的累积分布位置。
   • PERCENT_RANK()：计算当前行的百分比排名。

实际案例分析

假设我们有一个销售数据表sales，包含sale_date（销售日期）、region（地区）和amount（销售额）。我们可以使用窗口函数来解决以下问题：

1. 计算每个地区的销售额排名：

SELECT 
  region, 
  sale_date, 
  amount, 
  RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region 
FROM 
  sales;

这个查询会为每个地区内的销售额进行排名。

2. 计算每个地区每天的累计销售额：

SELECT 
  region, 
  sale_date, 
  SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS cumulative_sales 
FROM 
  sales;

这里，我们计算了每个地区按日期排序的累计销售额。

3. 使用窗口框架计算过去7天内的累计销售额：

SELECT 
  region, 
  sale_date, 
  SUM(amount) OVER ( 
    PARTITION BY region 
    ORDER BY sale_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 
  ) AS rolling_7_day_sales 
FROM 
  sales;

在这个查询中，ROWS BETWEEN 6 PRECEDING AND CURRENT ROW定义了窗口框架，表示从当前行之前的6行到当前行本身，共计7天的数据用于计算累计销售额。

窗口函数是SQL中非常强大的工具，允许你在保持数据行独立性的同时，对数据集中的行进行复杂的计算。通过合理使用分区、排序和窗口框架，你可以解决许多复杂的数据分析问题。