跳至正文

“一通百通”,SQL窗口函数计算同环比

本文来自微信公众号,转到博客

喜乐君注:近期在学习Tableau表计算的过程中,用新学习的SQL知识做对比,二者底层简直如出一辙,相互结合,有助于增进对业务分析和技术的理解。特此,以同环比为例,介绍SQL中 LAG偏移函数的使用。

01—SQL的偏移函数

既然使用Excel、SQL和Tableau完成“合计百分比”计算,类似的,能否在SQL中实现“同比、环比”逻辑呢?同环比的关键是查询偏移的聚合值。SQL中的窗口函数可以实现二次聚合、偏移、递归等运算。以Mysql为例,其中就包含LAG、LEAD,FIRST_VALUE以及LAST_VALUE等多个可以偏移查询函数。如图9-16所示,这里使用一组数据,简要介绍这些函数的逻辑。

图9‑16 SQL中主要的偏移类窗口函数

SQL窗口函数的关键是设置OVER语法,它由两个部分构成,与Tableau的理念同宗同脉。

  • PATITION BY控制窗口计算的范围,如果省略,则以全部数据结束为边界
  • ORDER BY控制窗口计算的方向;如果省略空,则默认按照数据源次序
  • 如果OVER之后部分全部为空{ },则以整个表为边界,查询的默认次序为方向

这里使用SQL函数,分别完成9.2小节中的问题,结合案例介绍OVER的使用方法:

02—单一维度

问题4: 各订单年度 的 销售额总和 及 同比差异

这个问题只有一个维度字段,相对而言比较简单。在SQL中,LAG函数默认查询偏移一位的聚合值,如果偏移更多需要增加偏移量参数。这里的关键是OVER之后的偏移查询方向需要指定为年度,从而正确获得“上一个年度的销售额”。如图9-17所示,左侧OVER函数之后完全为空,查询按照默认的查询次序(正好是年度的倒序);右侧增加了ORDER BY设置,默认按照升序ASC排列。

图9‑17 借助lag和over,完成偏移计算

在使用窗口函数时,应该避免OVER为空的不稳定查询方式,默认的排序总是难以保证完全正确。同时, LAG()函数可以增加偏移量参数查询,比如LAG(SUM(销售额), 2 ),这里省略了1。这里OVER之后省略了PARTITION BY,因此计算的依据可以抵达最后一个聚合值,即以整个聚合表的边界为范围,也就是整个数据表为范围。

02—增加PARTITION分区

  问题5: 各细分市场、各订单年度的 销售额总和 及同比增长率%

这里的问题需要明确指明计算的范围,从而确保同比计算沿着订单年度计算、并以每个“细分市场”为范围边界。因此,窗口计算的关键是如下的部分:

LAG(SUM(销售额), 1  ) 
OVER  {  -- 查找上一个聚合销售,以细分为边界,以订单年度(升序)为依据
PARTITION BY 细分
ORDER BY订单年度
}

 这个偏移的聚合销售额再与默认的“各细分、各年度销售额”计算,就是增长率,这个过程如图9-18所示:

图9‑18 使用窗口计算获得偏移值,然后计算增长率

初学者容易混淆范围和依据字段,这个过程与 Tableau中完全一致,笔者常如下提醒自己:

  • 谁和谁比较,谁就是依据(方向)——同比是年与年比较,故【年度】是方向order by
  • 谁和谁不能比较,谁就是范围(分区)——细分和细分不能比较,故【细分】是分区
  • 方向的尽头,就是分区的开始——2020年的尽头是下个细分的2018,不能跨越

本章会在9.3小节,进一步总结窗口计算和表计算背后的设置方法。

02—两个排序依据

问题6: 华东地区中,各订单年度、各订单季度 的 销售额总结 及 季度环比在这个问题中,谁和谁比较?季度的环比当然是季度比较。4季度的结束是下一年度的1季度,可以跨越吗?当然可以,年季度的组合是有连续性、可比性的,因此年也是计算依据。年和季度都是计算的依据,它们的尽头是整个数据表的边界,因此默认数据表为范围。

这种分析方法适用于Tableau表计算,也完全适用于SQL窗口计算。如图9-19所示,这里ORD ER BY后先按照年度排序、再按照季度排序,在此基础上获得上一个聚合销售额的偏移。PARTITION BY省略,因此计算可以到整个查询表的尽头,即整个数据表为范围。

图9‑19 使用SQL的窗口函数返回上一个聚合值

在这里,特别注意排序的年、季度是有先后次序的,先按照前面的字段排序,再对其中的结果嵌套第二次排序。如果排序的字段位置颠倒,排序的结果会直接影响偏移计算的结果。

如图9-20所示,这里ORDER BY之后季度在年度之前,因此偏移的结果是先对同一季度中查找上一年度,年度的尽头是下个季度的开始。每个季度中的偏移计算,是季度同比,但是跨年显然就没有业务意义了。

图9‑20排序字段的更换会引起截然不同的计算逻辑

这个过程,与9.2.3小节中Tableau表计算的设置如出一辙,特定维度中的多个字段都是表计算的依据,谁要和谁先比较,对应的字段应该在下面,即“深度优先”的原则,其背后对应的正是SQL中的ORDER BY子句次序。在这个过程中,读者应该可以感受到SQL语言的优雅和简洁,而且可以辅助理解Tableau表计算的设置过程和底层逻辑。

理论上,数据分析的计算都可以使用SQL完成,不过,现实的分析中很多业务用户把它习惯性地视为IT专属工具而敬而远之,殊不知伴随技术的快速发展和从业者心知的日渐提高,往日的生涩工具已经越来越简洁好用。在全球化面前,地球是平的;在数据爆炸面前,技术也将加速平民化。

虽然SQL通过程序语言控制输出,并没有类似于Excel的透视表、Tableau的工作表的“可视化区域”,因此使用偏离类计算不像聚合函数善于控制。不过一旦理解了它们的原理,分析师就可以进一步驾驭Tableau的表计算,并在复杂的业务面前,借助ETL工具(比如Prep Builder)将复杂逻辑转移到数据表阶段,从而简化问题的复杂性。