这两天在学习 MySQL 窗口函数,看到一篇国外文章 How Window Functions Work ,用动图的方式讲解窗口函数,帮助我更好地理解了,现分享给大家。下面我只是简单地翻译了一下,并对内容做了些许删减,如有错误,请大家海涵,也烦请指正。
什么是窗口函数?
窗口函数基于对数据的子集或 "窗口 "进行的计算创建一个新列。这个窗口从特定列上的第一行开始,除非你限制窗口的大小,否则窗口的大小会增加。
SELECT 'Day', 'Mile Driving',SUM('Miles Driving')
OVER(ORDER BY 'Day') AS 'Running Total'FROM 'Running total mileage visual';
在这里,对窗口中的数据进行聚合。窗口的行数在增加,所以它聚合更多的数据。
如果我们将窗口限制为3行高,我们可以得到一个连续3天平均收入。
SELECT 'Day', 'Daily Revenue',AVG('Daily Revenue')
OVER(ORDER BY 'Day' ROWS 2 PRECEDING)AS '3 Day Average'
FROM 'Running Average Example';
窗口从第一行开始,然后增长到它的固定大小,然后整个窗口也随之移动。
窗口函数也可以用 partition by 对数据分组。它首先对数据进行分组,然后在这些分组上应用聚合函数,将结果放在该组中每一行的新列中。
SELECT 'Day', 'Weekend', 'Daily Revenue',SUM('Daily Revenue')
OVER(PARTITION BY 'Weekend') AS 'Total'FROM 'Partitioned Total Example';
可以看到某家店在周末和工作日两个时间段的收入对比。
窗口函数的聚合函数的区别
窗口函数与聚合函数非常相似,实际上每个窗口函数都在其中应用了一个聚合函数。他们不同之处在于:
输出:(窗口函数不会减少输出的行,而是创建一个完整的输出列。)聚合函数输出单行,减少原表行数
窗口函数产生一个新的数据列,该列的行数与原表相同。
对数据进行子集化:聚类函数应用于分组数据或整个数据集中的数据。
窗口函数应用于一个窗口内的数据。窗口可以很灵活,可以控制在特定的行数上,也可以适用于分组。
让我们来看看两者的区别。
窗口函数
SELECT 'Day', 'Mile Driving',SUM('Miles Driving')
OVER(ORDER BY 'Day') AS 'Running Total'FROM 'Running total mileage visual';
聚合函数
SELECT SUM('Miles Driving') AS 'Sum of Miles Driving'
FROM 'Running total mileage visual';
创建窗口函数的关键字OVER - 表示窗口函数的开始,这将使聚合的结果作为一个列添加到输出表中。
PARTITION BY 在表中创建数据组,聚合结果将被执行。
ORDER BY - 根据给定的列对数据进行排序。
语法如下:
SELECT '(Optional: The data you want to select)',
[aggregate function]'(The column to perform the aggregate function on) '
OVER(Optional: PARTITION BY and/or ORDER BY)
AS'(Descriptive name)'
FROM '(corresponding table)';
对窗口行数进行控制
使用关键字指定窗口的行数。ROW n PRECEDING - 定义当前行之前要包括的行数
ROW n FOLLOWING - 定义当前行之后要包括的行数
SELECT *,AVG('Daily Revenue')
OVER(ROWS 2 PRECEDING)
AS '3 Day Average'
FROM 'Running Average Example'
SELECT *,AVG('Daily Revenue')
OVER(ROWS 2 FOLLOWING)
AS '3 Day Average'
FROM 'Running Average Example'
使用 Partition by 和 Order by
数据如下
只使用 order by, 会创建移动平均(moving average)
SELECT 'Date', 'Steps Taken',AVG('Steps Taken')
OVER(ORDER BY Date)
AS 'Average Steps Taken'
FROM 'Steps Taken Daily';
使用 order by 和 partition by
SELECT 'Date', 'Weekend', 'Steps Taken',AVG('Steps Taken')
OVER(PARTITION BY 'Weekend' ORDER BY Date)
AS 'Average Steps Taken'
FROM 'Steps Taken Daily';
可以看到,当与PARTITION BY一起使用时,ORDER BY并没有创建移动平均(moving average)。