34、SQL 窗口函数：数据处理与分析的强大工具

SQL 窗口函数：数据处理与分析的强大工具

1. ROW_NUMBER() 函数的使用

1.1 功能概述

在处理数据时，源数据并不总是包含所有所需元素。有时，我们需要对记录进行编号以单独识别每一行。 ROW_NUMBER() 函数可以在不影响底层数据的情况下实现这一功能。

1.2 示例查询

以下是一个示例查询，从 AllSales 表开始，该表包含要输出的字段（ SaleDate 、 SalePrice 、 CustomerName 、 Town 和 MakeName ）。

SELECT 
    SaleDate, 
    SalePrice, 
    CustomerName, 
    Town, 
    MakeName,
    COUNT(*) OVER (PARTITION BY YEAR(SaleDate) ORDER BY SaleDate) AS SalesPerDayPerYear,
    ROW_NUMBER() OVER (ORDER BY SaleDate ASC) AS RecordID
FROM 
    AllSales;

1.3 函数解释

• COUNT() ：显示每年每天的销售数量。该函数按年份分区，因此每年变化时计数器会重新开始，并按日期排序。
• ROW_NUMBER() ：显示一个递增的唯一编号，用于标识输出的每条记录。该函数需要一个 OVER 子句，其中包含 ORDER BY 语句。为了确保这些虚拟 ID 按可理解的顺序编号， ROW_NUMBER() 函数按销售日期排序。由于我们希望为所有源数据定义一个连续的编号范围，因此不向窗口函数添加 PARTITION BY 子句。

1.4 注意事项

• ROW_NUMBER() 与 COUNT() 不同，尽管表面上相似。例如，对于 2015 年 4 月 30 日的两条记录， COUNT() 窗口函数显示当天到目前为止的总销售数量为 12，而 ROW_NUMBER() 函数为每条记录分配不同的编号。
• 如果需要指定一个完整的编号系统，可以扩展 ROW_NUMBER() 函数的排序顺序。例如：

ROW_NUMBER() OVER (ORDER BY SaleDate, Make, Model ASC) AS SalesCounter

2. 显示缺失数据的记录

2.1 需求背景

CEO 需要 2016 年的每周销售日历，希望看到一年中所有周的列表，无论该周是否有销售。

2.2 示例 SQL

WITH Tally_CTE
AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY StockCode) AS Num
    FROM 
        Stock
    LIMIT 
        52
)
SELECT 
    CTE.Num, 
    SLS.SalesForTheWeek
FROM 
    Tally_CTE CTE
LEFT OUTER JOIN  
    (
        SELECT 
            SUM(TotalSalePrice) AS SalesForTheWeek,
            DATE_PART("WEEK", SaleDate) AS WeekNo
        FROM 
            Sales
        WHERE 
            YEAR(SaleDate) = 2016
        GROUP BY 
            DATE_PART("WEEK", SaleDate)
    ) SLS
ON 
    CTE.Num = SLS.WeekNo;

2.3 工作原理

• CTE 生成编号列表 ： Tally_CTE 是一个公共表表达式，用于生成一个编号列表。通过 ROW_NUMBER() 函数为 Stock 表中的记录编号，并限制结果为 52 条，以对应一年的周数。
• 左外连接 ：将 Tally_CTE 与一个子查询进行左外连接，子查询计算 2016 年每周的销售总额。左外连接确保即使某周没有销售记录，也会在结果中显示该周的编号。

2.4 操作步骤

1. 创建 Tally_CTE ，生成编号列表。
2. 创建子查询，计算 2016 年每周的销售总额。
3. 使用左外连接将 Tally_CTE 与子查询连接起来。

2.5 注意事项

• 如果使用单个表作为编号表的基础，该表必须包含至少与所需顺序列表相同数量的记录。
• 确保用于连接派生表和编号数据集的日期字段为 DATE 数据类型，必要时进行转换。

3. 显示完整日期范围及相关数据

3.1 需求背景

销售总监需要一份 2017 年 1 月 1 日至 6 月 30 日的每日销售列表。

3.2 示例 SQL

WITH Tally_CTE
AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY ST1.StockCode) - 1 AS Num
    FROM 
        Stock ST1
    JOIN 
        Stock ST2
    LIMIT 
        10000
),
DateRange_CTE
AS
(
    SELECT 
        DATE_ADD('2017-01-01', Num) AS DateList 
    FROM 
        Tally_CTE 
    WHERE 
        Num <= DATEDIFF('2017-06-30', '2017-01-01')
)
SELECT 
    CAST(DateList AS DATE) AS SaleDate, 
    SalesPerDay
FROM 
    DateRange_CTE CTE
LEFT OUTER JOIN  
    (
        SELECT 
            CAST(SaleDate AS DATE) AS DateOfSale,
            SUM(TotalSalePrice) AS SalesPerDay
        FROM 
            Sales
        GROUP BY 
            CAST(SaleDate AS DATE)
    ) SLS
ON 
    CTE.DateList = SLS.DateOfSale;

3.3 工作原理

• 第一个 CTE（Tally_CTE） ：通过 ROW_NUMBER() 函数生成一个大的编号列表（这里为 10000 个），并将其起始编号调整为 0。使用两个表的交叉连接（ CROSS JOIN ）来生成足够多的记录。
• 第二个 CTE（DateRange_CTE） ：使用 DATE_ADD() 函数根据 Tally_CTE 中的编号生成从 2017 年 1 月 1 日开始的日期序列，并使用 DATEDIFF() 函数限制日期范围到 2017 年 6 月 30 日。
• 最终查询 ：将 DateRange_CTE 与一个子查询进行左外连接，子查询计算每天的销售总额。

3.4 操作步骤

1. 创建 Tally_CTE ，生成编号列表。
2. 创建 DateRange_CTE ，根据编号列表生成日期序列。
3. 创建子查询，计算每天的销售总额。
4. 使用左外连接将 DateRange_CTE 与子查询连接起来。

3.5 注意事项

• 交叉连接（笛卡尔积）在创建编号表时很有用，但在进行精确查询时可能会有风险。
• 确保用于连接派生表和编号数据集的日期字段为 DATE 数据类型，必要时进行转换。

3.6 流程图

graph TD;
    A[Tally_CTE] --> B[生成编号列表];
    B --> C[调整起始编号为 0];
    D[Stock 表交叉连接] --> B;
    E[DateRange_CTE] --> F[根据编号生成日期序列];
    B --> F;
    F --> G[限制日期范围];
    H[Sales 表子查询] --> I[计算每天销售总额];
    G --> J[左外连接];
    I --> J;
    J --> K[输出结果];

4. 比较当前记录与前一记录的数据

4.1 需求背景

销售总监希望跟踪客户的消费情况，了解每次销售与前一次销售的价格差异。

4.2 示例 SQL

SELECT 
    CustomerName,
    SaleDate,
    TotalSalePrice,
    TotalSalePrice - LAG(TotalSalePrice, 1) OVER (PARTITION BY CustomerName ORDER BY SaleDate) AS DifferenceToPreviousSalePrice
FROM 
    allsales
ORDER BY 
    SaleDate;

4.3 工作原理

• LAG() 函数用于返回前一个记录的值。在这个例子中，它返回同一客户前一次销售的价格。
• PARTITION BY CustomerName 确保 LAG() 函数只在同一客户的记录中查找前一个记录。
• ORDER BY SaleDate 按销售日期对记录进行排序。

4.4 注意事项

• LAG() 函数中的字段不一定要出现在 SELECT 子句中，但必须在 FROM 子句的表中。
• 如果不想显示第一次销售的记录（即没有前一次销售价格差异的记录），可以使用子查询过滤掉 NULL 值。例如：

SELECT *
FROM
(
    SELECT 
        CustomerName,
        SaleDate,
        TotalSalePrice,
        TotalSalePrice - LAG(TotalSalePrice, 1) OVER (PARTITION BY CustomerName ORDER BY SaleDate) AS PreviousSalePriceDifference
    FROM 
        allsales
) SQ
WHERE 
    PreviousSalePriceDifference IS NOT NULL
ORDER BY 
    SaleDate;

4.5 操作步骤

1. 编写主查询，包含 LAG() 函数计算价格差异。
2. 如果需要过滤第一次销售的记录，使用子查询和 WHERE 子句。
3. 对结果按销售日期排序。

4.6 表格总结

函数	用途	示例
ROW_NUMBER()	为记录编号	ROW_NUMBER() OVER (ORDER BY SaleDate ASC)
COUNT()	统计分组内记录数量	COUNT(*) OVER (PARTITION BY YEAR(SaleDate) ORDER BY SaleDate)
LAG()	返回前一个记录的值	LAG(TotalSalePrice, 1) OVER (PARTITION BY CustomerName ORDER BY SaleDate)

5. 使用 FIRST_VALUE() 和 LAST_VALUE() 函数比较不同时间的数据

5.1 需求背景

Prestige Cars 的销售总监希望找出每个客户购买每辆车的初始和最终销售价格，并将这些价格与当前销售价格进行比较，以进一步挖掘客户消费潜力，最大化每位客户的收入。

5.2 示例 SQL

SELECT 
    CustomerName,
    MakeName,
    ModelName,
    FIRST_VALUE(TotalSalePrice) OVER (PARTITION BY CustomerName, MakeName, ModelName ORDER BY SaleDate) AS InitialSalePrice,
    LAST_VALUE(TotalSalePrice) OVER (PARTITION BY CustomerName, MakeName, ModelName ORDER BY SaleDate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FinalSalePrice,
    TotalSalePrice AS CurrentSalePrice
FROM 
    allsales
ORDER BY 
    CustomerName, MakeName, ModelName, SaleDate;

5.3 工作原理

• FIRST_VALUE() 函数 ：该函数用于返回分区内按指定排序顺序的第一个记录的值。在这个例子中，它返回每个客户购买每辆车的初始销售价格。通过 PARTITION BY CustomerName, MakeName, ModelName 对数据进行分组，确保只在同一客户购买同一辆车的记录中查找初始价格。 ORDER BY SaleDate 按销售日期对分组内的记录进行排序，使得第一个记录就是最早的销售记录。
• LAST_VALUE() 函数 ：用于返回分区内按指定排序顺序的最后一个记录的值。这里需要注意的是， LAST_VALUE() 函数默认的窗口范围是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，这意味着它只会返回当前行之前的最后一个值。为了获取整个分区内的最后一个值，我们需要将窗口范围指定为 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 。这样，它就能返回每个客户购买每辆车的最终销售价格。

5.4 操作步骤

1. 编写主查询，选择需要的字段，包括客户姓名、车辆品牌、车型、初始销售价格、最终销售价格和当前销售价格。
2. 使用 FIRST_VALUE() 函数计算每个客户购买每辆车的初始销售价格。
3. 使用 LAST_VALUE() 函数计算每个客户购买每辆车的最终销售价格，并指定窗口范围为 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 。
4. 对结果按客户姓名、车辆品牌、车型和销售日期进行排序。

5.5 注意事项

• FIRST_VALUE() 和 LAST_VALUE() 函数的性能可能会受到数据量和分区大小的影响。如果数据量较大，建议对表进行适当的索引优化，以提高查询性能。
• 在使用 LAST_VALUE() 函数时，务必注意窗口范围的设置。如果不指定窗口范围，可能会得到不符合预期的结果。

5.6 表格总结

函数	用途	示例
FIRST_VALUE()	返回分区内按指定排序顺序的第一个记录的值	FIRST_VALUE(TotalSalePrice) OVER (PARTITION BY CustomerName, MakeName, ModelName ORDER BY SaleDate)
LAST_VALUE()	返回分区内按指定排序顺序的最后一个记录的值	LAST_VALUE(TotalSalePrice) OVER (PARTITION BY CustomerName, MakeName, ModelName ORDER BY SaleDate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

5.7 流程图

graph TD;
    A[allsales 表] --> B[按客户、品牌、车型分区];
    B --> C[按销售日期排序];
    C --> D[FIRST_VALUE() 计算初始价格];
    C --> E[LAST_VALUE() 计算最终价格];
    D --> F[选择字段];
    E --> F;
    A --> F;
    F --> G[按客户、品牌、车型、销售日期排序];
    G --> H[输出结果];

总结

6.1 窗口函数的重要性

通过上述多个示例，我们可以看到窗口函数在 SQL 数据处理和分析中具有重要作用。 ROW_NUMBER() 函数可以为记录添加唯一编号，方便数据的识别和排序；利用窗口函数处理缺失数据，能够确保数据的完整性，满足各种业务需求； LAG() 函数可以比较相邻记录的数据，帮助我们分析数据的变化趋势； FIRST_VALUE() 和 LAST_VALUE() 函数则可以帮助我们获取特定分组内的初始和最终数据，为业务决策提供有力支持。

6.2 操作要点回顾

• 在使用窗口函数时，要注意 PARTITION BY 和 ORDER BY 子句的使用。 PARTITION BY 用于对数据进行分组， ORDER BY 用于指定分组内的排序顺序，它们共同决定了窗口函数的计算范围和结果。
• 对于一些特殊的窗口函数，如 LAST_VALUE() ，需要特别注意窗口范围的设置，确保得到正确的结果。
• 在处理日期数据时，要确保日期字段的数据类型一致，必要时进行类型转换，以保证连接和计算的准确性。

6.3 未来应用展望

随着数据量的不断增加和业务需求的日益复杂，窗口函数的应用场景将更加广泛。例如，在金融领域，可以使用窗口函数分析股票价格的波动趋势；在电商领域，可以分析用户的购买行为和消费习惯。掌握窗口函数的使用，将有助于我们更好地处理和分析数据，为业务发展提供更有价值的见解。

6.4 表格汇总窗口函数

函数	用途	关键参数	示例代码
ROW_NUMBER()	为记录添加唯一编号	ORDER BY	ROW_NUMBER() OVER (ORDER BY SaleDate ASC)
COUNT()	统计分组内记录数量	PARTITION BY , ORDER BY	COUNT(*) OVER (PARTITION BY YEAR(SaleDate) ORDER BY SaleDate)
LAG()	返回前一个记录的值	PARTITION BY , ORDER BY , 字段, 偏移值	LAG(TotalSalePrice, 1) OVER (PARTITION BY CustomerName ORDER BY SaleDate)
FIRST_VALUE()	返回分区内第一个记录的值	PARTITION BY , ORDER BY	FIRST_VALUE(TotalSalePrice) OVER (PARTITION BY CustomerName, MakeName, ModelName ORDER BY SaleDate)
LAST_VALUE()	返回分区内最后一个记录的值	PARTITION BY , ORDER BY , 窗口范围	LAST_VALUE(TotalSalePrice) OVER (PARTITION BY CustomerName, MakeName, ModelName ORDER BY SaleDate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

通过对这些窗口函数的学习和实践，我们可以更加高效地处理和分析数据，为业务决策提供更有力的支持。希望大家在实际工作中能够灵活运用这些函数，解决各种复杂的数据处理问题。