【PGCCC】使用 Postgres 进行数据分析的窗口函数

SQL 在处理单行数据时，甚至在聚合多行数据时都很有意义。但是，当您想比较已计算的行之间的数据时会发生什么？或者创建数据组并进行查询？输入窗口函数。

窗口函数往往会让人感到困惑 - 但它们是 SQL 中用于数据分析的非常棒的工具。最好的部分是，您不需要图表、花哨的 BI 工具或 AI 即可为利益相关者获取一些可操作且有用的数据。窗口函数可让您快速：

计算累计总数
提供数据组/分区的汇总统计信息
创建排名
执行滞后/超前分析，即将两组独立的数据相互比较
计算移动/滚动平均值

本文将通过简单的电子商务数据架构，演示各种窗口函数及其实际应用。

什么是窗口函数？

Window 函数的部分OVER是创建窗口。令人讨厌的是，window 这个词在任何函数中都没有出现。通常，OVER 部分由另一个函数（聚合函数或数学函数）开头。通常还有一个框架，用于指定您正在查看的行ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。

窗口函数 vs where 子句

窗口函数乍一看有点像 where子句，因为它们查看的是一组数据。但它们确实不同。窗口函数更适合于需要查看多组数据或多组数据的情况。有些情况下您可以使用其中任何一种。一般来说：

当您需要根据条件过滤行时使用WHERE子句。

当您需要对过滤后剩余的行执行计算，而不从结果集中删除任何行时，请使用窗口函数。

窗口函数的实际应用

1.累计

这是一个简单的入门步骤。让我们请求订单、客户数据、订单总额，然后是订单的累计总额。这将向我们显示某个日期范围内的订单总额。

SELECTSUM(total_amount) OVER (ORDER BY order_date) AS running_total,order_date,order_id,customer_id,total_amount
FROMorders
ORDER BYorder_date;

 running_total |     order_date      | order_id | customer_id | total_amount
---------------+---------------------+----------+-------------+--------------349.98 | 2024-08-21 10:00:00 |       21 |           1 |       349.981249.96 | 2024-08-22 11:30:00 |       22 |           2 |       899.981284.94 | 2024-08-23 09:15:00 |       23 |           3 |        34.981374.93 | 2024-08-24 14:45:00 |       24 |           4 |        89.991524.92 | 2024-08-25 08:25:00 |       25 |           5 |       149.991589.90 | 2024-08-26 12:05:00 |       26 |           6 |        64.98

这里发生的事情是，每个数据帧都是现有行加上之前的行。这种计算一次只进行一个切片，您可能在文档中看到它被称为虚拟表。下面是一个图表，可以让您大致了解每个数据帧是如何成为一组行的，这些行由函数聚合而成SUM OVER。
在这里插入图片描述

2.第一个值和最后一个值

窗口函数可以查看数据组，因此说出特定的客户 ID 并为您提供一些信息，例如他们的第一笔和最后一笔订单、总额以及最近 10 笔订单的信息。

SELECTFIRST_VALUE(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS first_order_date,LAST_VALUE(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS last_order_date,o.order_id,o.customer_id,o.order_date,o.total_amount
FROMorders o
ORDER BYo.order_date DESC;

 first_order_date   |   last_order_date   | order_id | customer_id |     order_date      | total_amount
---------------------+---------------------+----------+-------------+---------------------+--------------2024-08-30 17:50:00 | 2024-09-19 18:50:00 |       50 |          10 | 2024-09-19 18:50:00 |       149.982024-08-29 13:10:00 | 2024-09-18 14:10:00 |       49 |           9 | 2024-09-18 14:10:00 |       199.982024-08-28 10:20:00 | 2024-09-17 11:20:00 |       48 |           8 | 2024-09-17 11:20:00 |       139.992024-08-27 16:35:00 | 2024-09-16 17:35:00 |       47 |           7 | 2024-09-16 17:35:00 |       249.982024-08-26 12:05:00 | 2024-09-15 13:05:00 |       46 |           6 | 2024-09-15 13:05:00 |        89.98

3.使用 date_trunc GROUP BY CTE 和窗口函数

date_trunc是一个非常方便的 Postgres 函数，可以汇总时间单位、小时、天、周、月。与GROUP BYCTE 中的结合使用时，您可以按日、月、周、年等创建非常简单的汇总统计数据。

当您将 date_trunc GROUP BY 分区与窗口函数结合使用时，会发生一些非常神奇的事情，您可以直接从数据库中获取现成的汇总统计数据。在我看来，这是 Postgres 窗口函数最强大的功能之一，它真正让您更上一层楼。

以下是一个以 CTE 开头的示例查询，调用 date_trunc 按每日总数对订单进行汇总。查询的第二部分是窗口函数，按降序排列销售额，并按销售额最高的日期进行排序。

WITH DailySales AS (SELECTdate_trunc('day', o.order_date) AS sales_date,SUM(o.total_amount) AS daily_total_salesFROMorders oGROUP BYdate_trunc('day', o.order_date)
)
SELECTsales_date,daily_total_sales,RANK() OVER (ORDER BY daily_total_sales DESC) AS sales_rank
FROMDailySales
ORDER BYsales_rank;

  sales_date      | daily_total_sales | sales_rank
---------------------+-------------------+------------2024-09-02 00:00:00 |           2419.97 |          12024-09-01 00:00:00 |           1679.94 |          22024-08-22 00:00:00 |            899.98 |          32024-09-07 00:00:00 |            699.95 |          42024-09-10 00:00:00 |            659.96 |          52024-09-09 00:00:00 |            499.94 |          62024-09-06 00:00:00 |            409.94 |          72024-08-30 00:00:00 |            349.99 |          8

RANK我应该注意到，这使用了窗口函数中一个非常有用的数学函数。

4.滞后分析 (LAG)

让我们使用 date_trunc CTE 做更多事情。现在我们知道我们有按天划分的数据，我们可以使用窗口函数来计算这些组之间的变化。例如，我们可以查看与前一天相比的销售额差异。在此示例中，LAG查看销售日期并与前一天进行比较。

WITH DailySales AS (SELECTdate_trunc('day', o.order_date) AS sales_date,SUM(o.total_amount) AS daily_total_salesFROMorders oGROUP BYdate_trunc('day', o.order_date)
)
SELECTsales_date,daily_total_sales,LAG(daily_total_sales) OVER (ORDER BY sales_date) AS previous_day_sales,daily_total_sales - LAG(daily_total_sales) OVER (ORDER BY sales_date) AS sales_difference
FROMDailySales
ORDER BYsales_date;

     sales_date      | daily_total_sales | previous_day_sales | sales_difference
---------------------+-------------------+--------------------+------------------2024-08-21 00:00:00 |            349.98 |                    |2024-08-22 00:00:00 |            899.98 |             349.98 |           550.002024-08-23 00:00:00 |             34.98 |             899.98 |          -865.002024-08-24 00:00:00 |             89.99 |              34.98 |            55.012024-08-25 00:00:00 |            149.99 |              89.99 |            60.002024-08-26 00:00:00 |             64.98 |             149.99 |           -85.01

LEAD以相同的方式工作，在数据集中向前看。 5.计算滚动平均值

使用同一天的组，我们还可以计算出滚动平均值。该函数接受7 天的滚动销售平均值作为AVG输入。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

WITH DailySales AS (SELECTdate_trunc('day', o.order_date) AS sales_date,SUM(o.total_amount) AS daily_total_salesFROMorders oGROUP BYdate_trunc('day', o.order_date)
)
SELECTsales_date,daily_total_sales,AVG(daily_total_sales) OVER (ORDER BY sales_dateROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_average_7_days
FROMDailySales
ORDER BYsales_date
LIMIT 10;

     sales_date      | daily_total_sales | rolling_average_7_days
---------------------+-------------------+------------------------2024-08-21 00:00:00 |            349.98 |   349.98000000000000002024-08-22 00:00:00 |            899.98 |   624.98000000000000002024-08-23 00:00:00 |             34.98 |   428.31333333333333332024-08-24 00:00:00 |             89.99 |   343.73250000000000002024-08-25 00:00:00 |            149.99 |   304.98400000000000002024-08-26 00:00:00 |             64.98 |   264.98333333333333332024-08-27 00:00:00 |            249.98 |   262.84000000000000002024-08-28 00:00:00 |            129.99 |   231.41285714285714292024-08-29 00:00:00 |            179.98 |   128.55571428571428572024-08-30 00:00:00 |            349.99 |   173.5571428571428571
(10 rows)