0


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

标签: sql 窗口函数

“SQL窗口函数可视化详解”的评论:

还没有评论