可视化mysql分析_用可视化的方式，理解 Mysql 的窗口函数

这两天在学习 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)。