【SQL】17.数据分析利器---窗口函数（二）

上篇介绍了窗口函数的语法以及三种排序相关的函数，还没看的记得先看下，今天继续讲窗口函数。

我们常用的聚合函数，像sum、max、avg这些都可以用作窗口函数，下面就用这张test表中的数据详细详细演示一下不同的情形：

计算总销售额

如果要在每一行后面加上所有月份的总销售额total_sales_volume可以使用如下的窗口函数：

SELECT *,
	SUM(sales_volume) OVER () AS total_sales_volume
FROM dbo.test;

其中sum是求和，over()中不添加参数，则默认对所有数据进行求和，可以看到新加的一列total_sales_volume，每一行的结果都是5859400。

每个季度的总销售额

年度总销售额知道了，但如果想求出每个季度的销售总额应该怎么办呢？回忆一下之前的聚合函数，是不是得根据季度来进行分组？在窗口函数中，PARTITION BY就可以对数据进行分组，将数据拆分成一个个窗口，sql如下：

SELECT *,
    SUM(sales_volume) OVER () AS total_sales_volume,
    SUM(sales_volume) OVER (PARTITION BY quarter) AS  quarter_sales_volume
FROM dbo.test;

PARTITION BY的功能与GROUP BY的类似，指定按照哪一列进行分组，只是PARTITION BY没有对原数据进行压缩，而是在原表的基础上增加了一列。上面的sql用 quarter分组，则将原来的12行数据拆分成了4个窗口，在小窗口里面对每个季度的值进行计算。

累计销售额

年度和季度的总销售额我们求出来了，但如果想求出累计至当月的销售额，比如说从1月到3，到8月累计销量分别是多少，类似从开始到指定日期的总进度，应该怎么求呢？即对窗口里的数据来进行移动统计，这时候就得加上另外一个关键字：ORDER BY了。sql如下：

SELECT *,
    SUM(sales_volume) OVER () AS total_sales_volume,
    SUM(sales_volume) OVER (ORDER BY sales_date) AS current_sales_volume,
    SUM(sales_volume) OVER (PARTITION BY quarter) AS  quarter__sales_volume,
    SUM(sales_volume) OVER (PARTITION BY quarter ORDER BY sales_date) AS  quarter_current__sales_volume
FROM dbo.test;

上面的current_sales_volume是截止到当月，这一年的销量，可以看到每个月的值都是逐行累加的。quarter_current__sales_volume是在每个季度这个小组中，对销量进行累加，比如2月的季度销量就是1月的加上2月的，但是到4月份了，季度累计销量会重新计算，因为是第二季度了。

通过上面4个SQL语句，大家应该能看出来：加不加PARTITION BY、加不加ORDER BY结果分别有什么区别了。如果把上面这4种情况都理解了，那么常见的求和、分组求和应该没什么问题了。

如果再拓展一点的话，就是开窗函数其实除了上面那种常见的写法外，后面还可以指定分行，只是通常被省略了，如果补充完整应该是：

OVER (PARTITION BY quarter ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

可以看出上面补充完整后计算的结果和我们刚求出来的是一样。只是我们可以指定求多少行到多少行的累计数据，比如说我上图中最后注释的那行代码，相对于提供了移动的窗口，如果感兴趣的话，可以自己尝试一下。只是这种写法很少用到，只要了解即可。

上面通过sum给大家演示了开窗函数的具体用法，只要求和会了，剩下的avg求平均，max、min求最大最小值都是一样的道理，相信大家没什么问题。

偏移开窗函数

还有一种偏移类型的开窗函数，如果我们要取当前记录的前/后几位的数据，需要用到偏移函数lag/lead。lag是在当前行之前查找，lead是在当前行之后查找。比如求环比增长率，就需要知道当前月的销售额和前一个月的销售额，那么就可以用偏移函数,sql如下：

SELECT *,
	LAG(sales_volume, 1) OVER (ORDER BY sales_date) AS pre_date_sales_volume
FROM dbo.test;

函数的第一个参数是我们要处理的列sales_volume，第二个参数是偏移量，当然如果不指定的话默认也是1，还有第三个参数是在处理的偏移量没有返回值时所给的默认值，比如第一行没有前一行，返回NULL，我们可以设定如果没有前一个值，返回0，那么sql就可以改成：

SELECT *,
	LAG(sales_volume,1,0) OVER (ORDER BY sales_date) AS pre_date_sales_volume
FROM dbo.test;

通过上面的偏移函数，我们就很容易解决同比增长、环比增长这种情形。还有lead是取向后偏移的值，用法也是同理。