33、SQL数据处理与滚动分析实用指南

最新推荐文章于 2025-11-25 04:07:54 发布

redis7keeper

最新推荐文章于 2025-11-25 04:07:54 发布

阅读量23

点赞数

CC 4.0 BY-SA版权

分类专栏：掌握Spark SQL实战精髓文章标签： SQL 窗口函数 PERCENTILE_CONT

本文链接：https://blog.youkuaiyun.com/redis7keeper/article/details/153172379

掌握Spark SQL实战精髓专栏收录该内容

39 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

SQL数据处理与滚动分析实用指南

1. 显示中位数

在实际业务分析中，有时需要展示每个客户的所有销售中，中位数折扣与实际折扣之间的差异。可以使用 SQL 从数据集中分离出中位数，示例代码如下：

SELECT     DISTINCT  CustomerName
           ,TotalSalePrice
           ,TotalSalePrice - PERCENTILE_CONT(0.5) 
           WITHIN GROUP(ORDER BY TotalSalePrice) 
                        OVER(PARTITION BY CustomerName) 
               AS SaleToMedianDelta
FROM       allsales;

1.1 工作原理

在查询中获取纯粹的统计结果，例如找到中位数（而不是平均值）并将其与销售价格进行比较。虽然 SQL 没有像 AVG() 函数那样的中位数函数，但可以使用内置的 PERCENTILE_CONT() 函数来计算中位数。计算中位数需要使用以下 SQL 元素：
- PERCENTILE_CONT(0.5) ：该函数用于在数据集中查找百分位数。由于要查找中位数，所以在括号内输入 0.5 。
- WITHIN GROUP ：用于指定要从中提取中位数的数字字段，并且必须包含 ORDER BY 子句。
- OVER (PARTITION BY) ：与其他窗口函数一样， PERCENTILE_CONT() 允许将数据集分割成子组，以查找 PARTITION BY 子句后指定的每个字段（或字段组合）的中位数。
在这个例子中，查询连接了 Sales 和 Customer 表，因为它们包含所需的字段。选择 CustomerName 和 SalePrice 字段后，添加提取每个客户中位数的子句，然后从每辆车的销售价格中减去该中位数。这意味着在 WITHIN GROUP 参数中指定 SalePrice 字段，并在 OVER 子句中按 CustomerName 进行分区。

1.2 注意事项

PERCENTILE_CONT() 函数的结果是一个计算值，而不是数据元素。

1.3 核心知识

概念	描述
`RANK()`	根据用户定义的标准对数据进行分层。
`OVER`	被 `RANK()` 函数用于引入排名标准。
`PARTITION BY`	被 `RANK()` 函数用于将数据分成多个层次结构。
`DENSE_RANK()`	对数据进行分层，排名无间隙。
`NTILE()`	将有序分区中的行分配到指定数量的组中。
`PERCENTILE_CONT()`	用于计算中位数等。
`WITHIN GROUP`	被 `PERCENTILE_CONT()` 函数用于返回中位数。

2. 滚动分析

在数据分析中，大多数指标并非孤立存在，通常需要比较这些指标与其他数据，并观察它们随时间的变化，以识别趋势、跟踪增长并为预测建立坚实的事实基础。Databricks 提供了一系列函数来帮助展示数据随时间或任何序列的演变，这些函数易于使用，可实现多种运行计算，如运行总计、运行平均值和运行计数。

2.1 添加运行总计

销售总监要求提供 2017 年的销售列表，其中包括截至目前的销售运行总计和平均销售。可以使用窗口函数来实现，示例代码如下：

SELECT       InvoiceNumber 
            ,DATE_FORMAT(SaleDate, "dd-MM-yy") AS 
DateOfSale
            ,SalePrice
            ,SUM(SalePrice) 
            OVER (ORDER BY SaleDate ASC) AS AccumulatedSales
            ,AVG(SalePrice) 
            OVER (ORDER BY SaleDate ASC) 
                 AS AverageSalesValueToDate
FROM        allsales
WHERE       YEAR(SaleDate) = 2017
ORDER BY    SaleDate;

2.1.1 工作原理

这个查询的核心是从 AllSales 表中获取销售日期和值的列表，并过滤数据以限制结果为 2017 年的销售。然后，通过添加 SUM() 和 AVG() 函数的窗口版本来增强输出。 OVER 子句使这些函数从数据集的开始到当前记录进行计算，从而实现运行总计和平均销售的计算。

graph LR
    A[开始] --> B[选择数据]
    B --> C[过滤数据]
    C --> D[排序数据]
    D --> E[添加窗口函数]
    E --> F[输出结果]

2.1.2 注意事项

由于要列出每个销售的单独记录，显示一些能让读者区分每个记录的信息（如发票号码）会很有帮助。
为了展示目的可以格式化日期，但也可以保留原始日期数据。
在单个查询中使用多个运行总计时，确保它们在 OVER 运算符中使用相同的 ORDER BY 子句，否则可能得到不理想的结果。
运行总计需要别名才能在输出中显示列标题。

2.2 在聚合查询中使用窗口函数

CFO 要求提供一份报告，显示 2016 年每天的采购成本以及当年的累计成本。以下 SQL 代码可以实现这一需求：

SELECT      DateBought
            ,SUM(Cost) AS PurchaseCost
            ,SUM(SUM(Cost)) 
               OVER (ORDER BY DateBought ASC) AS CostForTheYear
FROM        Stock
WHERE       YEAR(DateBought) = 2016
GROUP BY    DateBought
ORDER BY    DateBought;

2.2.1 工作原理

此 SQL 通过按 DateBought 字段对 Stock 表中的数据进行分组，显示该字段以及 Cost 字段的 SUM() 。为了避免数据过多，将输出限制为 2016 年的采购。然后，使用 SUM()…OVER 窗口函数提供运行总计。由于这是一个分组查询，需要将 SUM(Cost) 函数再次聚合为 SUM(SUM(Cost)) 。

2.2.2 注意事项

在使用窗口函数聚合数据时，将 SUM() 函数嵌套在另一个 SUM() 函数中可能看起来很奇怪，但如果不这样做，查询可能会失败。因为在分组查询中，运行总计使用的数据也必须进行分组。

2.3 分组运行总计

有时需要在某些关键数据发生变化时重置分组元素，例如新的一年开始时。当销售总监要求提供显示截至目前所有销售以及每年销售运行计数的列表时，可以使用窗口函数来实现。示例代码如下：

SELECT       DATE_FORMAT(SaleDate, "dd-MM-yy") AS 
DateOfSale
            ,CustomerName, Town
            ,SalePrice
            ,COUNT(SalesDetailsID) 
             OVER (PARTITION BY YEAR(SaleDate) 
                   ORDER BY SaleDate ASC) 
                        AS AnnualNumberOfSalesToDate
FROM        allsales
ORDER BY    SaleDate;

2.3.1 工作原理

此查询首先连接 Customer 、 Sales 和 SalesDetails 表，以返回每次销售的销售和客户信息。不应用任何过滤条件，以便查看 Prestige Cars 开始交易以来的所有销售。查询按日期排序，以确保数据清晰易懂。
添加销售运行计数器的步骤如下：
1. 使用 COUNT() 函数计算销售数量。
2. 添加 OVER 子句将其扩展为窗口函数。
3. 通过唯一的字段组合（ SaleDate 字段和 SalesDetailsID 字段）对结果计数器进行排序，以确保不聚合数据，显示每个单独的记录。
4. 添加 PARTITION BY 子句，以便当 PARTITION BY 子句后指定的字段（年份）发生变化时，计数器重新开始。

2.3.2 注意事项

在 OVER 函数的 ORDER BY 子句中使用适当的字段以获得所需的输出。如果使用错误的字段，可能无法返回预期结果。在这个例子中，使用 SalesDetailsID 字段确保每个记录单独输出，而不进行聚合。
如果在查询中使用 SaleDate 字段而不是 SalesDetailsID 字段，Databricks 会计算全年有销售的天数，由于可能存在一天内多次销售或无销售的情况，结果记录集将不同。
确保在查询中重复使用用于排序数据的字段，以确保输出与实际数据分析在视觉上一致。

2.4 在子查询中应用窗口函数

CEO 要求获取截至目前的总销售额，并显示每年按价值计算的销售运行总计。以下 SQL 代码可以实现这一需求：

SELECT       DATE_FORMAT(SaleDate, "dd-MM-yyyy") AS 
DateOfSale
            ,DailyCount AS NumberOfSales
            ,SUM(DailyCount) OVER 
                   (PARTITION BY YEAR(SaleDate) ORDER BY SaleDate 
ASC) 
                   AS AnnualNumberOfSalesToDate
            ,SUM(DailySalePrice) OVER 
                   (PARTITION BY YEAR(SaleDate) ORDER BY SaleDate 
ASC) 
                   AS AnnualSalePriceToDate
FROM        (
             SELECT       SA.SaleDate
                         ,COUNT(SalesDetailsID) AS 
DailyCount
                         ,SUM(SalePrice) AS 
DailySalePrice
             FROM        SalesDetails SD
             INNER JOIN  Sales AS SA 
                         ON SA.SalesID = SD.SalesID
             GROUP BY    SA.SaleDate
            ) DT
ORDER BY    SaleDate;

2.4.1 工作原理

实现此查询的最佳方法是将任务分为两部分：
1. 创建一个派生表，计算每天的销售数量和价值。
2. 创建一个外部查询，返回当年的累计销售以及派生表中的销售总收入运行总计。
子查询是一个聚合查询，按日期分组并提供销售计数和销售价值总和。不应用任何过滤条件，包括自开始交易以来的所有销售。
外部查询使用子查询生成的三个元素：
- SaleDate ：按原样显示在输出中，并用于排序查询结果。
- DailyCount ：按原样输出，显示每天的销售数量。
- DailyCount ：在窗口函数中重用，提供截至目前的销售运行总计。通过按销售日期应用 COUNT() 函数实现运行总计，并使用 PARTITION BY 子句按年份分区，使计数在每年重新开始。
- DailySalePrice ：在另一个窗口函数中重用，按销售日期排序并按年份分区，显示每年截至目前的累计销售。

2.4.2 优点

使用派生表进行初始聚合是准备用于提供运行总计的初始数据的有效方法。还可以通过选择和执行派生表的基础查询来测试初始数据，从而在构建查询时检查过程的每个部分。

2.5 动态添加唯一 ID

销售员工要求创建一个唯一编号系统，以顺序识别销售，避免计费系统中出现的复杂标识符。以下 SQL 代码可以在不更改源数据的情况下添加此功能：

SELECT       DATE_FORMAT(SaleDate, "dd-MM-yyyy") AS 
DateOfSale
            ,SalePrice, CustomerName, Town, 
MakeName
            ,COUNT(SalesDetailsID) 
              OVER (PARTITION BY YEAR(SaleDate) 
              ORDER BY SaleDate ASC) 
                 AS AnnualNumberOfSalesToDate
            ,ROW_NUMBER() OVER (ORDER BY SaleDate ASC) 
                 AS SalesCounter        
FROM        allsales
ORDER BY    SaleDate;

通过使用 ROW_NUMBER() 函数，可以为每个销售记录动态分配一个唯一的编号，方便销售员工识别和管理销售信息。

综上所述，SQL 中的窗口函数和相关操作提供了强大的数据处理和分析能力。通过合理运用这些技术，可以深入挖掘数据的价值，为业务决策提供有力支持。在实际应用中，需要注意各种函数的使用方法和注意事项，以确保得到准确和有用的分析结果。

2.6 显示缺失数据的记录

在某些情况下，数据集中可能存在缺失值。为了更全面地分析数据，需要显示包含缺失数据的记录。以下是一个示例，假设存在一个 Sales 表，其中可能存在某些日期没有销售记录的情况，我们要显示完整的日期范围及相关数据。

2.6.1 实现步骤

首先，生成一个完整的日期范围。可以使用序列生成函数或者临时表来实现。
然后，将生成的日期范围与 Sales 表进行左连接，以确保所有日期都被包含在内。
最后，处理可能出现的缺失值，例如用 0 填充销售金额。

示例代码如下：

-- 假设我们要显示 2023 年 1 月 1 日至 2023 年 12 月 31 日的销售数据
WITH DateRange AS (
    SELECT sequence(to_date('2023-01-01'), to_date('2023-12-31'), interval 1 day) AS dates
),
AllDates AS (
    SELECT explode(dates) AS SaleDate FROM DateRange
)
SELECT 
    ad.SaleDate,
    COALESCE(s.SalePrice, 0) AS SalePrice
FROM 
    AllDates ad
LEFT JOIN 
    Sales s ON ad.SaleDate = s.SaleDate
ORDER BY 
    ad.SaleDate;

2.6.2 代码解释

DateRange 公共表表达式（CTE）：使用 sequence 函数生成 2023 年 1 月 1 日至 2023 年 12 月 31 日的日期序列。
AllDates CTE：使用 explode 函数将日期序列展开成单独的日期记录。
SELECT 语句：通过左连接将完整的日期范围与 Sales 表关联起来，并使用 COALESCE 函数处理可能的缺失值。

2.7 与前一记录比较数据

在分析数据时，经常需要比较当前记录与前一记录的数据，以观察数据的变化趋势。可以使用 LAG() 函数来实现这一需求。

2.7.1 示例代码

SELECT 
    InvoiceNumber,
    SaleDate,
    SalePrice,
    LAG(SalePrice, 1, 0) OVER (ORDER BY SaleDate) AS PreviousSalePrice,
    SalePrice - LAG(SalePrice, 1, 0) OVER (ORDER BY SaleDate) AS PriceDifference
FROM 
    Sales
ORDER BY 
    SaleDate;

2.7.2 代码解释

LAG(SalePrice, 1, 0) ： LAG 函数用于获取前一记录的 SalePrice 值。第一个参数是要获取的列名，第二个参数是偏移量（这里是 1，表示前一个记录），第三个参数是当没有前一记录时的默认值（这里是 0）。
PriceDifference ：计算当前销售价格与前一销售价格的差值。

2.8 使用 `FIRST_VALUE()` 和 `LAST_VALUE()` 函数比较数据

FIRST_VALUE() 和 LAST_VALUE() 函数可以用于获取一组有序记录中的第一个和最后一个值，从而实现跨时间的数据比较。

2.8.1 示例代码

SELECT 
    CustomerName,
    SaleDate,
    SalePrice,
    FIRST_VALUE(SalePrice) OVER (PARTITION BY CustomerName ORDER BY SaleDate) AS FirstSalePrice,
    LAST_VALUE(SalePrice) OVER (PARTITION BY CustomerName ORDER BY SaleDate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSalePrice
FROM 
    Sales
ORDER BY 
    CustomerName, SaleDate;

2.8.2 代码解释

FIRST_VALUE(SalePrice) ：获取每个客户的第一笔销售价格。
LAST_VALUE(SalePrice) ：获取每个客户的最后一笔销售价格。需要注意的是， LAST_VALUE 函数默认是在当前行结束窗口，所以需要使用 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 来指定窗口范围为整个分区。

2.9 显示指定数量记录的滚动平均值

滚动平均值可以帮助我们观察数据的短期趋势。以下是一个显示指定数量记录的滚动平均值的示例。

2.9.1 示例代码

SELECT 
    InvoiceNumber,
    SaleDate,
    SalePrice,
    AVG(SalePrice) OVER (ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS RollingAverage
FROM 
    Sales
ORDER BY 
    SaleDate;

2.9.2 代码解释

AVG(SalePrice) OVER (ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ：计算当前记录及其前两条记录的销售价格的平均值。

2.10 显示每个客户的第一笔销售和最后四笔销售

有时候需要关注每个客户的首笔销售和最近的几笔销售情况。可以使用窗口函数来实现这一需求。

2.10.1 示例代码

WITH RankedSales AS (
    SELECT 
        CustomerName,
        SaleDate,
        SalePrice,
        ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY SaleDate) AS SaleRank,
        ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY SaleDate DESC) AS ReverseSaleRank
    FROM 
        Sales
)
SELECT 
    CustomerName,
    SaleDate,
    SalePrice
FROM 
    RankedSales
WHERE 
    SaleRank = 1 OR ReverseSaleRank <= 4
ORDER BY 
    CustomerName, SaleDate;

2.10.2 代码解释

RankedSales CTE：使用 ROW_NUMBER() 函数为每个客户的销售记录进行排名，分别按销售日期升序和降序排名。
SELECT 语句：筛选出排名为 1（第一笔销售）或降序排名小于等于 4（最后四笔销售）的记录。

2.11 计算累积分布

累积分布可以帮助我们了解数据在某个范围内的分布情况。可以使用 PERCENT_RANK() 函数来计算累积分布。

2.11.1 示例代码

SELECT 
    SaleDate,
    SalePrice,
    PERCENT_RANK() OVER (ORDER BY SalePrice) AS CumulativeDistribution
FROM 
    Sales
ORDER BY 
    SalePrice;

2.11.2 代码解释

PERCENT_RANK() ：计算每个销售价格在所有销售价格中的相对排名，即累积分布。

2.12 使用 `LAG()` 函数处理字母数据

LAG() 函数不仅可以用于数字数据，还可以用于字母数据。以下是一个示例，比较当前客户名称与前一个客户名称。

2.12.1 示例代码

SELECT 
    CustomerName,
    LAG(CustomerName, 1, '') OVER (ORDER BY CustomerName) AS PreviousCustomerName
FROM 
    Customers
ORDER BY 
    CustomerName;

2.12.2 代码解释

LAG(CustomerName, 1, '') ：获取前一个客户名称，如果没有前一个记录，则返回空字符串。

总结

本文详细介绍了 SQL 中用于数据处理和滚动分析的多种技术，包括显示中位数、滚动分析的各个方面（如添加运行总计、分组运行总计、在聚合查询和子查询中使用窗口函数等），以及处理缺失数据、比较数据、计算滚动平均值、显示特定销售记录、计算累积分布等操作。以下是本文涵盖的主要技术点总结：
| 技术点 | 描述 |
| ---- | ---- |
| PERCENTILE_CONT() | 计算中位数 |
| SUM() OVER | 添加运行总计 |
| AVG() OVER | 计算运行平均值 |
| COUNT() OVER | 计算运行计数 |
| PARTITION BY | 分组窗口函数 |
| LAG() | 获取前一记录的值 |
| FIRST_VALUE() | 获取第一个值 |
| LAST_VALUE() | 获取最后一个值 |
| ROW_NUMBER() | 生成唯一编号 |
| PERCENT_RANK() | 计算累积分布 |

通过合理运用这些技术，可以深入挖掘数据的价值，为业务决策提供有力支持。在实际应用中，需要根据具体需求选择合适的函数和方法，并注意各种函数的使用方法和注意事项，以确保得到准确和有用的分析结果。

graph LR
    A[数据处理与滚动分析] --> B[显示中位数]
    A --> C[滚动分析]
    C --> C1[添加运行总计]
    C --> C2[聚合查询中使用窗口函数]
    C --> C3[分组运行总计]
    C --> C4[子查询中应用窗口函数]
    C --> C5[动态添加唯一 ID]
    C --> C6[显示缺失数据记录]
    C --> C7[与前一记录比较数据]
    C --> C8[使用 FIRST_VALUE 和 LAST_VALUE 比较数据]
    C --> C9[显示滚动平均值]
    C --> C10[显示特定销售记录]
    C --> C11[计算累积分布]
    C --> C12[使用 LAG 处理字母数据]