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)
小结
窗口函数是 SQL 中功能强大且灵活的工具,尤其适用于复杂的数据分析任务。通过使用这些函数,您可以轻松执行数据的累计计算、滞后分析、排名生成和滚动统计,而无需借助复杂的外部工具。充分掌握这些函数的使用,将极大地提升您在 SQL 数据分析中的效率和能力。
#PG证书#PG考试#postgresql培训#postgresql考试#postgresql认证