发布于 由admin

窗口函数的高级应用：分组排名、累计求和与移动平均

各位亲爱的程序员朋友们，大家好！我是你们的老朋友，人称“Bug终结者”的程序猿老王。今天，咱们不聊那些深奥的算法，也不谈那些高冷的架构，咱们来聊点接地气，但又威力无穷的宝贝——窗口函数！

想象一下，你是一位古代的将军，手下千军万马。你想知道谁是跑得最快的士兵，谁的战斗力最强，或者想看看整个军团的战斗力趋势。如果你只能一个一个地比较，那得累死你！而窗口函数，就像你手中的一个魔法水晶球，能让你轻松洞察整个数据集的“全局”信息，同时还能关注到每个个体的“局部”特点。

今天，我们就一起探索窗口函数的“高级”应用，让你的SQL技能瞬间提升一个档次！准备好了吗？让我们扬帆起航，驶向窗口函数的星辰大海！🚀

一、 什么是窗口函数？（温故而知新）

在深入高级应用之前，我们先简单回顾一下窗口函数的基本概念。窗口函数，顾名思义，就是在某个“窗口”内进行计算的函数。这个“窗口”可以是整个表，也可以是按照某种规则划分的子集（分组）。

与传统的聚合函数（如SUM(), AVG(), COUNT()）不同，窗口函数不会将多行数据聚合成一行，而是为每一行数据都返回一个计算结果。也就是说，窗口函数是在保留原始行的基础上，附加一些额外的信息。

举个例子，假设我们有一张销售表 sales：

order_id product sales_amount order_date
1 A 100 2023-01-01
2 B 150 2023-01-02
3 A 200 2023-01-03
4 B 120 2023-01-04
5 A 180 2023-01-05

如果我们想计算每天的销售额占总销售额的比例，用传统的聚合函数就比较麻烦，需要先计算总销售额，再进行连接查询。而使用窗口函数，一行代码就能搞定：

SELECT
    order_id,
    product,
    sales_amount,
    order_date,
    sales_amount / SUM(sales_amount) OVER () AS sales_percentage
FROM
    sales;

这里的 SUM(sales_amount) OVER () 就是一个窗口函数。OVER () 表示整个表都是一个窗口。它会计算整个表的销售总额，并为每一行数据都返回这个总额，然后我们就可以计算比例了。

看到这里，你是不是觉得窗口函数有点意思了？别急，精彩的还在后面！

二、 高级应用之一：分组排名 (Group Ranking)

分组排名，顾名思义，就是在每个分组内进行排名。这在很多场景下都非常有用，比如：

  • 找出每个部门工资最高的员工： 你想知道哪个部门是“卷王”部门吗？🏆
  • 统计每个地区销售额最高的商品： 看看哪个地区的消费者最“壕”！💰
  • 对学生成绩进行分班排名： 谁是学霸，一目了然！🤓

窗口函数提供了几个排名函数：

  • RANK(): 排名相同会跳过后续排名，例如：1, 1, 3
  • DENSE_RANK(): 排名相同不会跳过后续排名，例如：1, 1, 2
  • ROW_NUMBER(): 按照顺序依次排名，不会重复，例如：1, 2, 3
  • NTILE(n): 将数据分成n组，并为每组分配一个组号。

我们还是以销售表 sales 为例，假设我们想找出每个产品销售额最高的订单：

SELECT
    order_id,
    product,
    sales_amount,
    order_date,
    RANK() OVER (PARTITION BY product ORDER BY sales_amount DESC) AS product_rank
FROM
    sales;
  • PARTITION BY product: 按照 product 列进行分组，这意味着每个产品都会有自己的排名。
  • ORDER BY sales_amount DESC: 按照 sales_amount 列降序排序，销售额最高的排在前面。

运行结果如下：

order_id product sales_amount order_date product_rank
3 A 200 2023-01-03 1
5 A 180 2023-01-05 2
1 A 100 2023-01-01 3
2 B 150 2023-01-02 1
4 B 120 2023-01-04 2

我们可以看到，每个产品都按照销售额进行了排名。如果想只保留每个产品销售额最高的订单，可以将其作为子查询，然后筛选 product_rank = 1 的结果：

SELECT
    order_id,
    product,
    sales_amount,
    order_date
FROM
    (
        SELECT
            order_id,
            product,
            sales_amount,
            order_date,
            RANK() OVER (PARTITION BY product ORDER BY sales_amount DESC) AS product_rank
        FROM
            sales
    ) AS ranked_sales
WHERE
    product_rank = 1;

是不是很简单？分组排名，让你的数据分析更加精准！

三、 高级应用之二：累计求和 (Cumulative Sum)

累计求和，顾名思义，就是对某个数值列进行累计求和。这在很多场景下都非常有用，比如：

  • 统计每日累计销售额： 了解销售额的增长趋势。📈
  • 计算用户累计消费金额： 分析用户的消费习惯。 🛍️
  • 跟踪项目累计完成进度： 掌握项目的整体进展。 🗓️

窗口函数提供了 SUM() OVER (ORDER BY ...) 来实现累计求和。

我们还是以销售表 sales 为例，假设我们想计算每日的累计销售额：

SELECT
    order_date,
    SUM(sales_amount) AS daily_sales,
    SUM(sales_amount) OVER (ORDER BY order_date) AS cumulative_sales
FROM
    sales
GROUP BY
    order_date
ORDER BY
    order_date;
  • SUM(sales_amount) OVER (ORDER BY order_date): 按照 order_date 列进行排序，并计算累计销售额。

运行结果如下：

order_date daily_sales cumulative_sales
2023-01-01 100 100
2023-01-02 150 250
2023-01-03 200 450
2023-01-04 120 570
2023-01-05 180 750

我们可以看到，cumulative_sales 列显示了每日的累计销售额。通过这个数据，我们可以清晰地看到销售额的增长趋势。

如果想计算每个产品的累计销售额，只需要加上 PARTITION BY product 即可：

SELECT
    order_date,
    product,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product ORDER BY order_date) AS cumulative_sales
FROM
    sales
ORDER BY
    product,
    order_date;

累计求和，让你的数据分析更加动态！

四、 高级应用之三：移动平均 (Moving Average)

移动平均，也称为滑动平均，是一种平滑时间序列数据的常用方法。它可以消除短期波动，突出长期趋势。这在很多场景下都非常有用，比如：

  • 分析股票价格趋势： 过滤掉短期噪音，关注长期走向。 📈📉
  • 预测网站流量： 预测未来的用户访问量。 🌐
  • 监控传感器数据： 检测异常情况。 📡

窗口函数提供了 AVG() OVER (ORDER BY ... ROWS BETWEEN ...) 来实现移动平均。

我们还是以销售表 sales 为例，假设我们想计算过去3天的销售额移动平均值：

SELECT
    order_date,
    SUM(sales_amount) AS daily_sales,
    AVG(SUM(sales_amount)) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM
    sales
GROUP BY
    order_date
ORDER BY
    order_date;
  • AVG(SUM(sales_amount)) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW): 按照 order_date 列进行排序，并计算过去3天的销售额平均值。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 指定了窗口的范围，表示从当前行往前数2行，到当前行。

运行结果如下：

order_date daily_sales moving_average
2023-01-01 100 100
2023-01-02 150 125
2023-01-03 200 150
2023-01-04 120 156.66666666666666
2023-01-05 180 166.66666666666666

我们可以看到，moving_average 列显示了过去3天的销售额移动平均值。通过这个数据，我们可以更清晰地看到销售额的长期趋势，而不会被每日的波动所干扰。

ROWS BETWEEN 子句可以灵活地定义窗口的范围，例如：

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 从第一行到当前行。
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 从当前行到最后一行。
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 当前行和前后各一行。

移动平均，让你的数据分析更加平滑！

五、 更多窗口函数 (More Window Functions)

除了上面介绍的排名函数、累计求和函数和移动平均函数之外，窗口函数还提供了很多其他有用的函数，比如：

  • FIRST_VALUE(column) OVER (...): 返回窗口中第一行的指定列的值。
  • LAST_VALUE(column) OVER (...): 返回窗口中最后一行的指定列的值。
  • LAG(column, n) OVER (...): 返回窗口中当前行之前第n行的指定列的值。
  • LEAD(column, n) OVER (...): 返回窗口中当前行之后第n行的指定列的值。

这些函数可以帮助你更灵活地处理数据，满足各种各样的分析需求。

六、 窗口函数的注意事项 (Important Notes)

在使用窗口函数时，需要注意以下几点：

  • OVER() 子句是必须的： 窗口函数必须包含 OVER() 子句，用于指定窗口的范围和排序方式。
  • PARTITION BY 子句是可选的： 可以使用 PARTITION BY 子句将数据分成多个分组，并在每个分组内进行计算。
  • ORDER BY 子句是可选的： 可以使用 ORDER BY 子句指定窗口内的排序方式。
  • ROWS BETWEEN 子句是可选的： 可以使用 ROWS BETWEEN 子句定义窗口的范围。
  • 性能问题： 窗口函数可能会影响查询性能，特别是当处理大数据集时。因此，需要谨慎使用，并进行性能测试。

七、 总结 (Conclusion)

窗口函数是SQL中一个非常强大的工具，它可以让你轻松地进行分组排名、累计求和、移动平均等高级数据分析操作。掌握窗口函数，可以极大地提高你的SQL技能，让你成为一名真正的数据分析专家！

希望今天的讲解对你有所帮助。记住，学习编程就像攀登一座高山，需要不断地学习和实践。不要害怕困难，勇往直前，你一定能到达成功的顶峰！ ⛰️

最后，祝大家编码愉快，Bug少少！ 👋

附录：练习题 (Practice Problems)

为了巩固你今天所学的知识，这里提供一些练习题，供你练习：

  1. 找出每个月销售额最高的商品。
  2. 计算每个用户的累计消费金额，并找出消费金额超过1000元的忠实用户。
  3. 计算过去7天的网站流量移动平均值，并预测未来3天的流量。
  4. 找出每个部门工资高于平均工资的员工。

希望这些练习题能帮助你更好地掌握窗口函数。加油！💪

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注