窗口函数（Window Functions）的原理与复杂分析应用

窗口函数：SQL界的“任意门”，带你穿梭数据世界！ 🚪✨

各位观众老爷们，大家好！我是你们的老朋友，穿梭于代码海洋的探险家——Bug猎手。今天，咱们不聊那些枯燥的算法，也不谈那些高深的架构，咱们来聊聊SQL世界里一个神奇的存在，一个能让你在数据分析时拥有“任意门”般能力的秘密武器——窗口函数（Window Functions）。

想象一下，你是一位公司的销售经理，想要了解每个月的销售额，同时还想知道这个月的销售额在整个季度中的排名，以及与上个月相比增长了多少。如果用传统的 SQL 语句，那简直是一场噩梦！你可能需要用到子查询、自连接等各种技巧，写出来的代码不仅冗长难懂，效率也堪忧。

但是，如果你掌握了窗口函数，这一切都将变得轻而易举！它就像一位贴心的管家，在你查询数据的同时，还能帮你进行各种复杂的统计和分析。准备好了吗？让我们一起打开这扇“任意门”，探索窗口函数的奥秘吧！

1. 什么是窗口函数？🤔

别被“窗口”这个词吓到，它其实很简单。你可以把窗口函数想象成一个特殊的函数，它作用于一个由查询结果集定义的“窗口”上，而不是像聚合函数那样作用于整个分组。

窗口，就是数据集中与当前行相关的一组行。这个“窗口”可以是整个结果集，也可以是按照某个字段分组后的子集。窗口函数可以访问窗口中的数据，进行各种计算，然后将结果添加到当前行。

举个栗子：

假设我们有一张销售数据表 sales，包含以下字段：

sale_id (销售ID)
sale_date (销售日期)
product_id (产品ID)
sales_amount (销售额)

sale_id	sale_date	product_id	sales_amount
1	2023-01-01	A	100
2	2023-01-05	B	150
3	2023-01-10	A	200
4	2023-01-15	C	120
5	2023-02-01	B	180
6	2023-02-10	A	250
7	2023-02-15	C	140
8	2023-03-01	A	300
9	2023-03-10	B	220
10	2023-03-15	C	160

现在，我们想知道每个月的销售额，以及每个月的销售额占整个季度总销售额的比例。用窗口函数，只需要一行 SQL 就能搞定！

SELECT
    sale_date,
    SUM(sales_amount) OVER (PARTITION BY strftime('%Y-%m', sale_date)) AS monthly_sales,
    SUM(sales_amount) OVER () AS total_quarter_sales,
    SUM(sales_amount) OVER (PARTITION BY strftime('%Y-%m', sale_date)) * 1.0 / SUM(sales_amount) OVER () AS monthly_sales_ratio
FROM
    sales;

是不是感觉很神奇？✨ 让我们来解读一下这段代码：

SUM(sales_amount) OVER (PARTITION BY strftime('%Y-%m', sale_date))：这就是窗口函数！它计算每个月的销售额。PARTITION BY strftime('%Y-%m', sale_date) 指定了窗口的划分方式，也就是按照月份进行分组。
SUM(sales_amount) OVER ()：这个窗口函数计算整个季度的总销售额。注意，这里没有 PARTITION BY 子句，这意味着窗口是整个结果集。

2. 窗口函数的语法结构 📝

窗口函数的通用语法结构如下：

function_name(arguments) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [frame_clause]
)

function_name(arguments)：这是窗口函数本身，可以是聚合函数（如 SUM, AVG, MAX, MIN, COUNT），也可以是排序函数（如 ROW_NUMBER, RANK, DENSE_RANK），还可以是取值函数（如 LAG, LEAD, FIRST_VALUE, LAST_VALUE）。
OVER()：这是窗口函数最关键的部分，它定义了窗口的范围。
- PARTITION BY column1, column2, ...：可选子句，用于将结果集划分为多个分区。窗口函数将分别应用于每个分区。如果没有 PARTITION BY 子句，则窗口是整个结果集。
- ORDER BY column3, column4, ...：可选子句，用于指定窗口内数据的排序方式。排序对于某些窗口函数（如排序函数和取值函数）非常重要。
- frame_clause：可选子句，用于定义窗口的帧（frame）。帧是当前行周围的一组行，用于进行计算。如果没有 frame_clause，则默认帧是整个分区（如果指定了 PARTITION BY）或整个结果集（如果没有指定 PARTITION BY）。

帧（Frame）的定义：

帧的定义可以使用以下两种方式：

ROWS BETWEEN: 定义帧的行数范围。
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 从分区的第一行到当前行。
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 从当前行到分区的最后一行。
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 当前行前一行到当前行后一行。
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: 整个分区。
RANGE BETWEEN: 定义帧的值范围，需要指定 ORDER BY 子句。
- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 从分区的第一行到当前行（基于排序字段的值）。
- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 从当前行到分区的最后一行（基于排序字段的值）。
- RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING: 当前行排序字段值减10到当前行排序字段值加10范围内的行。

3. 窗口函数的类型 🗂️

窗口函数可以分为以下几类：

聚合窗口函数： SUM, AVG, MAX, MIN, COUNT 等。它们与普通的聚合函数类似，但是它们不会将结果集分组，而是将聚合结果添加到每一行。
排序窗口函数： ROW_NUMBER, RANK, DENSE_RANK, NTILE 等。它们用于对窗口内的数据进行排序，并为每一行分配一个排名。
取值窗口函数： LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE 等。它们用于访问窗口内其他行的数据。

让我们逐一了解这些窗口函数的用法：

3.1 聚合窗口函数

聚合窗口函数可以计算窗口内数据的聚合值，并将结果添加到每一行。

例如：

SELECT
    sale_date,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY strftime('%Y-%m', sale_date) ORDER BY sale_date) AS running_monthly_sales
FROM
    sales;

这个查询计算每个月的累计销售额。PARTITION BY strftime('%Y-%m', sale_date) 将数据按照月份进行分组，ORDER BY sale_date 指定了窗口内数据的排序方式。

3.2 排序窗口函数

排序窗口函数可以对窗口内的数据进行排序，并为每一行分配一个排名。

ROW_NUMBER(): 为窗口内的每一行分配一个唯一的序号，从 1 开始。
RANK(): 为窗口内的每一行分配一个排名。如果存在相同的排名，则跳过后续的排名。
DENSE_RANK(): 为窗口内的每一行分配一个排名。如果存在相同的排名，则不会跳过后续的排名。
NTILE(n): 将窗口内的数据分成 n 组，并为每一行分配一个组号。

例如：

SELECT
    sale_date,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS sales_dense_rank,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_row_number
FROM
    sales;

这个查询计算每个销售记录的排名，分别使用 RANK, DENSE_RANK, 和 ROW_NUMBER 函数。

3.3 取值窗口函数

取值窗口函数可以访问窗口内其他行的数据。

LAG(column, n, default_value): 访问窗口内当前行之前第 n 行的 column 的值。如果不存在第 n 行，则返回 default_value。
LEAD(column, n, default_value): 访问窗口内当前行之后第 n 行的 column 的值。如果不存在第 n 行，则返回 default_value。
FIRST_VALUE(column): 访问窗口内第一行的 column 的值。
LAST_VALUE(column): 访问窗口内最后一行的 column 的值。
NTH_VALUE(column, n): 访问窗口内第 n 行的 column 的值。

例如：

SELECT
    sale_date,
    sales_amount,
    LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS previous_sales_amount,
    sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS sales_difference
FROM
    sales;

这个查询计算每个销售记录与前一个销售记录的销售额差异。LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) 访问前一个销售记录的销售额，如果不存在前一个销售记录，则返回 0。

4. 窗口函数的复杂分析应用 🚀

窗口函数在复杂的数据分析场景中有着广泛的应用，例如：

计算移动平均值： 可以使用聚合窗口函数和帧（frame）定义来计算移动平均值。
计算累计百分比： 可以使用聚合窗口函数和 ORDER BY 子句来计算累计百分比。
查找每个分组中的前 N 个记录： 可以使用排序窗口函数和 WHERE 子句来查找每个分组中的前 N 个记录。
计算同比和环比： 可以使用取值窗口函数来计算同比和环比。
计算用户留存率： 可以使用取值窗口函数和子查询来计算用户留存率。

案例一：计算移动平均值

假设我们想计算每个月的 3 个月移动平均销售额。

SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM
    sales;

AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 计算从当前行往前数 2 行的平均销售额。

案例二：查找每个产品销售额最高的前 2 个销售记录

SELECT
    sale_date,
    product_id,
    sales_amount
FROM (
    SELECT
        sale_date,
        product_id,
        sales_amount,
        RANK() OVER (PARTITION BY product_id ORDER BY sales_amount DESC) AS sales_rank
    FROM
        sales
) AS ranked_sales
WHERE
    sales_rank <= 2;

这个查询首先使用排序窗口函数 RANK() 计算每个产品销售额的排名，然后使用 WHERE 子句筛选出排名在前 2 位的销售记录。

案例三：计算用户留存率

假设我们有一张用户行为表 user_actions，包含以下字段：

user_id (用户ID)
action_date (行为日期)
action_type (行为类型，例如：注册、登录、购买)

我们想计算每个月注册用户的次月留存率。

WITH MonthlyRegistrations AS (
    SELECT
        strftime('%Y-%m', action_date) AS registration_month,
        COUNT(DISTINCT user_id) AS registered_users
    FROM
        user_actions
    WHERE
        action_type = '注册'
    GROUP BY
        strftime('%Y-%m', action_date)
),
MonthlyActiveUsers AS (
    SELECT
        strftime('%Y-%m', action_date) AS active_month,
        user_id
    FROM
        user_actions
    WHERE
        action_type = '登录'
    GROUP BY
        strftime('%Y-%m', action_date), user_id
)
SELECT
    mr.registration_month,
    mr.registered_users,
    COUNT(DISTINCT mau.user_id) AS retained_users,
    CAST(COUNT(DISTINCT mau.user_id) AS REAL) / mr.registered_users AS retention_rate
FROM
    MonthlyRegistrations mr
LEFT JOIN
    MonthlyActiveUsers mau ON mr.registration_month = strftime('%Y-%m', date(mau.active_month, '-1 month'))
GROUP BY
    mr.registration_month, mr.registered_users;

这个查询首先使用 CTE (Common Table Expression) 分别计算每个月的注册用户数和活跃用户数，然后使用 LEFT JOIN 和 GROUP BY 子句计算每个月注册用户的次月留存率。

5. 窗口函数的注意事项 ⚠️

窗口函数只能出现在 SELECT 列表中或 ORDER BY 子句中。
窗口函数不能嵌套使用。
窗口函数不能与 GROUP BY 子句一起使用。
窗口函数的性能可能受到数据量和窗口大小的影响。

6. 总结 🎉

窗口函数是 SQL 中一个非常强大的工具，它可以让你在查询数据的同时进行各种复杂的统计和分析。掌握窗口函数，可以大大提高你的 SQL 编程效率，让你在数据分析的道路上如虎添翼！💪

希望今天的分享能帮助大家更好地理解和应用窗口函数。记住，熟能生巧！多练习，多思考，你也能成为 SQL 大师！

下次再见，祝大家编程愉快！ 💻😊

窗口函数：SQL界的“任意门”，带你穿梭数据世界！ 🚪✨

1. 什么是窗口函数？🤔

2. 窗口函数的语法结构 📝

3. 窗口函数的类型 🗂️

3.1 聚合窗口函数

3.2 排序窗口函数

3.3 取值窗口函数

4. 窗口函数的复杂分析应用 🚀

5. 窗口函数的注意事项 ⚠️

6. 总结 🎉

发表回复 取消回复

发表回复取消回复