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

好的，各位技术界的弄潮儿们，大家好！我是你们的老朋友，人称“代码诗人”的程序猿大刘。今天，咱们不聊那些高深莫测的算法，也不谈那些让人头大的框架，咱们来点轻松愉快的，聊聊SQL里的“魔法师”——窗口函数！🧙‍♂️

想象一下，你正在参加一个盛大的舞会，每个人都在翩翩起舞。窗口函数就像是一位神奇的摄影师，他能在舞池中选取一个“窗口”，记录下窗口内舞者的精彩瞬间。而这个“窗口”可以灵活移动，让摄影师捕捉到不同舞者在不同时刻的风采。是不是很有趣？

一、 窗口函数：SQL世界的“任意门”

传统的聚合函数，比如SUM、AVG、MAX等等，它们就像是“黑洞”，一旦吸入数据，就会把原始数据压缩成一个单一的数值。这意味着，你丢失了宝贵的细节信息。

而窗口函数则不同，它就像一扇“任意门”，允许你在不改变原始数据的情况下，进行复杂的计算。它能穿透每一行数据，观察周围的“邻居”，并根据这些“邻居”的信息，来增强当前行的数据。

举个例子，假设我们有一张记录员工工资的表employees：

employee_id	employee_name	department	salary
1	张三	研发部	8000
2	李四	研发部	10000
3	王五	市场部	12000
4	赵六	市场部	15000
5	田七	研发部	9000

如果我们想知道每个员工的工资与所在部门的平均工资相比如何，传统的做法是先计算每个部门的平均工资，然后将结果与原始表连接。这个过程不仅繁琐，而且效率低下。

而有了窗口函数，一切都变得简单起来：

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS department_avg_salary
FROM
    employees;

这条SQL语句就像一句咒语，瞬间将每个员工的工资与所在部门的平均工资并排显示：

employee_id	employee_name	department	salary	department_avg_salary
1	张三	研发部	8000	9000
2	李四	研发部	10000	9000
5	田七	研发部	9000	9000
3	王五	市场部	12000	13500
4	赵六	市场部	15000	13500

看到没？我们没有改变原始数据，但却获得了每个员工所在部门的平均工资信息。这就是窗口函数的魔力！✨

二、 窗口函数的“三板斧”：PARTITION BY, ORDER BY, ROWS/RANGE BETWEEN

窗口函数之所以强大，是因为它提供了三个核心的“法宝”：

1. PARTITION BY (分组)：就像舞会的不同区域，将数据按照指定的列进行分组。每个分组就是一个独立的“窗口”。
2. ORDER BY (排序)：在每个“窗口”内，按照指定的列进行排序。就像舞池中的舞者按照身高排列，方便摄影师捕捉精彩瞬间。
3. ROWS/RANGE BETWEEN (定义窗口范围)：定义“窗口”的大小。你可以指定“窗口”包含当前行之前/之后的多少行数据，或者指定一个基于值的范围。

这三个“法宝”可以灵活组合，创造出各种各样的“窗口”，满足你千奇百怪的分析需求。

让我们逐一深入了解这三个“法宝”。

1. PARTITION BY：分组的艺术

PARTITION BY子句就像一个分拣机，将数据按照指定的列进行分组。每个分组就是一个独立的“窗口”。如果你不指定PARTITION BY子句，那么整个数据集就是一个“窗口”。

还是以employees表为例，如果我们想计算每个部门工资最高的员工，可以使用以下SQL语句：

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) AS department_max_salary
FROM
    employees;

这条SQL语句会将数据按照department列进行分组，然后在每个分组内找到工资最高的员工。

employee_id	employee_name	department	salary	department_max_salary
1	张三	研发部	8000	10000
2	李四	研发部	10000	10000
5	田七	研发部	9000	10000
3	王五	市场部	12000	15000
4	赵六	市场部	15000	15000

注意，这里我们只是找到了每个部门工资最高的工资值，而不是具体的员工信息。如果想获取具体的员工信息，可以使用子查询或者ROW_NUMBER()函数，后面我们会详细介绍。

2. ORDER BY：排序的魅力

ORDER BY子句就像一个指挥棒，在每个“窗口”内，按照指定的列进行排序。它可以让你轻松地计算排名、累积和等指标。

例如，如果我们想计算每个员工在所在部门的工资排名，可以使用以下SQL语句：

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
    employees;

这条SQL语句会将数据按照department列进行分组，然后在每个分组内按照salary列降序排序，并计算每个员工的工资排名。

employee_id	employee_name	department	salary	salary_rank
2	李四	研发部	10000	1
5	田七	研发部	9000	2
1	张三	研发部	8000	3
4	赵六	市场部	15000	1
3	王五	市场部	12000	2

RANK()函数会根据排序结果，为每一行分配一个排名。如果存在相同的值，则会跳过后续的排名。例如，如果有两个员工的工资相同，且都排在第一名，那么下一个员工的排名将会是第三名。

除了RANK()函数，还有其他一些常用的排名函数：

• ROW_NUMBER()：为每一行分配一个唯一的序号，从1开始。
• DENSE_RANK()：与RANK()函数类似，但不会跳过后续的排名。
• NTILE(n)：将数据分成n组，并为每一行分配一个组号。

3. ROWS/RANGE BETWEEN：窗口的边界

ROWS/RANGE BETWEEN子句就像一把剪刀，可以精确地控制“窗口”的大小。它可以让你定义“窗口”包含当前行之前/之后的多少行数据，或者指定一个基于值的范围。

ROWS BETWEEN是基于行数的，而RANGE BETWEEN是基于值的。

常用的窗口范围包括：

• UNBOUNDED PRECEDING：表示窗口从第一行开始。
• UNBOUNDED FOLLOWING：表示窗口到最后一行结束。
• CURRENT ROW：表示当前行。
• n PRECEDING：表示当前行之前的n行。
• n FOLLOWING：表示当前行之后的n行。
• BETWEEN n PRECEDING AND m FOLLOWING：表示当前行之前的n行和之后的m行。

例如，如果我们想计算每个员工及其前后各一位员工的工资总和，可以使用以下SQL语句：

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS salary_sum
FROM
    employees;

这条SQL语句会按照employee_id列进行排序，然后计算每个员工及其前后各一位员工的工资总和。

employee_id	employee_name	department	salary	salary_sum
1	张三	研发部	8000	18000
2	李四	研发部	10000	27000
3	王五	市场部	12000	27000
4	赵六	市场部	15000	36000
5	田七	研发部	9000	24000

注意，第一行和最后一行由于缺少前/后的数据，所以salary_sum的值会略有不同。

三、 窗口函数的“十八般武艺”：常用函数详解

除了前面提到的AVG、MAX、RANK等聚合函数和排名函数，窗口函数还支持许多其他的函数，可以满足你各种各样的分析需求。

这里我们介绍一些常用的窗口函数：

• 聚合函数：SUM、AVG、MIN、MAX、COUNT
• 排名函数：RANK、DENSE_RANK、ROW_NUMBER、NTILE
• 值函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE

1. 值函数：穿越时空的“信使”

值函数可以让你访问“窗口”中其他行的数据。它们就像穿越时空的“信使”，可以将其他行的数据带到当前行。

• LAG(column, n, default_value)：获取当前行之前n行的指定列的值。如果当前行之前不足n行，则返回default_value。
• LEAD(column, n, default_value)：获取当前行之后n行的指定列的值。如果当前行之后不足n行，则返回default_value。
• FIRST_VALUE(column)：获取“窗口”中第一行的指定列的值。
• LAST_VALUE(column)：获取“窗口”中最后一行的指定列的值。
• NTH_VALUE(column, n)：获取“窗口”中第n行的指定列的值。

例如，如果我们想计算每个员工的工资与上个月工资的差额，可以使用以下SQL语句：

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY employee_id) AS previous_salary,
    salary - LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY employee_id) AS salary_difference
FROM
    employees;

这条SQL语句会将数据按照department列进行分组，然后在每个分组内按照employee_id列排序，并计算每个员工的工资与上个月工资的差额。如果某个员工是该部门的第一个员工，则其上个月的工资默认为0。

四、 窗口函数的高级应用：复杂分析案例

窗口函数不仅可以用于简单的聚合和排名，还可以用于解决一些复杂的分析问题。

1. 计算移动平均值

移动平均值是一种常用的时间序列分析方法，可以平滑数据，并揭示潜在的趋势。

例如，如果我们有一张记录每日销售额的表sales：

date	sales_amount
2023-01-01	100
2023-01-02	120
2023-01-03	150
2023-01-04	130
2023-01-05	160
2023-01-06	140
2023-01-07	170

我们可以使用窗口函数计算7日移动平均值：

SELECT
    date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average_7d
FROM
    sales;

这条SQL语句会按照date列进行排序，然后计算每个日期及其前6天销售额的平均值。

2. 查找连续登录的用户

在用户行为分析中，我们经常需要查找连续登录的用户。

例如，如果我们有一张记录用户登录信息的表login_logs：

user_id	login_date
1	2023-01-01
1	2023-01-02
1	2023-01-03
2	2023-01-01
2	2023-01-03
3	2023-01-05
3	2023-01-06
3	2023-01-07

我们可以使用窗口函数查找连续登录3天以上的用户：

WITH ranked_logs AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS row_num
    FROM
        login_logs
),
date_diffs AS (
    SELECT
        user_id,
        login_date,
        DATE(login_date, '-' || (row_num - 1) || ' days') AS date_diff
    FROM
        ranked_logs
),
consecutive_counts AS (
    SELECT
        user_id,
        login_date,
        COUNT(*) OVER (PARTITION BY user_id, date_diff) AS consecutive_days
    FROM
        date_diffs
)
SELECT DISTINCT user_id
FROM consecutive_counts
WHERE consecutive_days >= 3;

这条SQL语句使用了多个CTE（Common Table Expression），逐步计算每个用户的连续登录天数。

• ranked_logs：为每个用户的登录记录分配一个序号。
• date_diffs：计算每个登录日期与序号之间的差值。如果用户连续登录，则差值相同。
• consecutive_counts：统计每个用户的相同差值的数量，即连续登录天数。

最后，我们筛选出连续登录3天以上的用户。

五、 窗口函数的注意事项

虽然窗口函数非常强大，但在使用时也需要注意一些事项：

• 性能问题：窗口函数可能会影响查询性能，特别是在处理大数据集时。需要仔细评估查询计划，并进行必要的优化。
• 语法差异：不同数据库系统对窗口函数的支持程度可能有所不同。需要查阅相关文档，了解具体语法和限制。
• 逻辑错误：窗口函数的逻辑比较复杂，容易出错。需要仔细检查SQL语句，并进行充分的测试。

六、 总结：掌握窗口函数，成为SQL大师！

窗口函数是SQL中的一项强大的技术，可以让你在不改变原始数据的情况下，进行复杂的计算和分析。掌握窗口函数，可以让你在数据分析的道路上如虎添翼，成为真正的SQL大师！ 🏆

希望今天的分享能帮助大家更好地理解和应用窗口函数。记住，学习编程就像练武功，需要不断练习和实践。只有多写代码，多思考问题，才能真正掌握这项技术。

下次再见！👋