SQL窗口函数可视化详解

本文总计 1100 字，预计阅读需要 5 分钟

“Window”或 <Aggregate> OVER (PARTITION ... SORT BY ...) 是SQL中高级并且非常实用的函数。尽管大多数教程未能准确地可视化窗口函数是如何逐步工作的，所以我整理了一些不错的 GIF，它们可以对窗口函数更完整的可视化。

这篇文章让可以更直观地了解 SQL窗口函数的窗口是如何构造、移动和计算的

出于说明目的，我们这里有一个每天完成的工作表

Day：上班日期
Start：工作开始时间
Duration：完成的工作小时数

与具有分组的普通聚合函数(每个组只返回一行)不同，窗口函数返回所有行，每个行都包含一个自定义的窗口操作的计算值，窗口函数使用OVER关键字及其参数来定义计算窗口。

在SQL中窗口函数有三个主要部分:

• 分组:这定义了每一行所属的组(PARTITION BY)
• 排序:对每组中的值进行排序，并使窗口在每组中递增(Order BY)
• 范围:用于在每组(ROWS或Range)中进一步窗口大小定义。

OVER

" Over将聚合函数转换为窗口函数"

我们在duration列上定义了一个简单的SUM 函数没有PARTITION BY，这意味着每一行的窗口是相同的也就是我们整个表。

SELECT
    day,
    duration,
    SUM(duration) OVER() AS total_duration
FROM
    work;

每一行的结果都是所有行的和

OVER (PARTITION BY ..)

使用一些分组(或者应该使用“分区”来避免混淆，这里的关键字是PARTITION也是为了和Group关键字区分，但是实际的操作是类似的 )

下面的查询按行开始时间对持续时间求和。在使用PARTITION时，是根据BY后面指定的列进行分组的

SELECT
    day,
    start,
    duration,
    SUM(duration) OVER(PARTITION BY start) AS sum_duration_by_start
FROM
    work;

每一行的计算窗口正好是start列进行分组后的大小，同一组中的所有行都以整个组作为窗口!

OVER (order by )

在前面的示例中介绍了PARTITION PARTITION ，现在让我们看看ORDER BY

ORDER BY将对各自组中的行进行排序(排序和分组也可以一起工作，下一个实例说)。ORDER BY还将强制窗口在每个组中展开(一次一行)。

SELECT
    day,
    duration,
    SUM(duration) OVER(ORDER BY day) AS sum_duration_order_by_day
FROM
    work;

我们没有指定PARTITION BY所以窗口还是所有数据

OVER (PARTITION BY .. SORT BY ..)

我们把上面两个整合

• 在窗口中计算 SUM
• PARTITION BY start
• ORDER BY day

SELECT
    day,
    start,
    duration,
    SUM(duration) OVER(PARTITION BY start ORDER BY day) 
      AS sum_duration_sort_by_day
FROM
    work;

看看结果：

窗口在每个组的开始时重置回大小 1，并在组中的每一行将其大小递增地扩展。

OVER 中还有有更复杂的 ROWS 和 RANGE参数 ，ROWS 和 RANGE 允许我们对窗口大小进行更细粒度的控制。

使用 ROWS 移动聚合

假设我们想看看过去两天做了多少工作时间！这可以通过在大小为 2 的移动窗口上定义 sum 来完成。

SELECT
    day,
    duration,
    SUM(duration) OVER(ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS moving_sum
FROM
    work;

窗口大小为 2 进行移动（或滚动）求和。

将PARTITION、ORDER、ROWS 合起来使用，查询（一个毫无意义的查询）定义了一个窗口求和（以这个确切的顺序）：

SELECT
    day,
    start,
    duration,
    SUM(duration) OVER(PARTITION BY start ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS crazy
FROM
 work;

这里的分组和排序仍然按照 PARTITION BY 和 ORDER BY 的规则，但是在每个组中构建窗口不再是增量扩展，而是遵循 ROWS 定义的约束，该约束指定窗口大小为 2（同组中的当前行和前一行)

这可以在第 7 天（第 4 行）看到，它的窗口大小为 2，尽管它是总窗口大小为 3 ，但是通过ROWS约束支取了2个值。

作者：Trần Hoàng Long