【格物篇41】“一通百通”,SQL窗口函数计算同环比
XI LE JUN
喜乐君
喜乐君,Tableau咨询顾问、商业数据分析师,从文科“窜入”大数据领域,致力于业务为中心的大数据分析体系建设与分享。
Tableau Zen Master 2021, Visionary 2022
爱好读书、写作;放过羊、爬过山;写过CSSCI,拿过国家奖学金
《数据可视化分析:Tableau原理与实践》
《业务可视化分析:从问题到图形的Tableau方法》
个人主页:http:www.yupengwu.com
“学而不思则罔,思而不学则怠”
喜乐君注:近期在学习Tableau表计算的过程中,用新学习的SQL知识做对比,二者底层简直如出一辙,相互结合,有助于增进对业务分析和技术的理解。特此,以同环比为例,介绍SQL中 LAG偏移函数的使用。
01
—
SQL的偏移函数
图9‑16 SQL中主要的偏移类窗口函数
PATITION BY控制窗口计算的范围,如果省略,则以全部数据结束为边界
ORDER BY控制窗口计算的方向;如果省略空,则默认按照数据源次序
如果OVER之后部分全部为空{ },则以整个表为边界,查询的默认次序为方向
02
—
单一维度
问题4: 各订单年度 的 销售额总和 及 同比差异
这个问题只有一个维度字段,相对而言比较简单。在SQL中,LAG函数默认查询偏移一位的聚合值,如果偏移更多需要增加偏移量参数。这里的关键是OVER之后的偏移查询方向需要指定为年度,从而正确获得“上一个年度的销售额”。如图9-17所示,左侧OVER函数之后完全为空,查询按照默认的查询次序(正好是年度的倒序);右侧增加了ORDER BY设置,默认按照升序ASC排列。
图9‑17 借助lag和over,完成偏移计算
02
—
增加PARTITION分区
问题5: 各细分市场、各订单年度的 销售额总和 及同比增长率%
LAG(SUM(销售额), 1 )
OVER { -- 查找上一个聚合销售,以细分为边界,以订单年度(升序)为依据
PARTITION BY 细分
ORDER BY订单年度
}
图9‑18 使用窗口计算获得偏移值,然后计算增长率
谁和谁比较,谁就是依据(方向)——同比是年与年比较,故【年度】是方向order by
谁和谁不能比较,谁就是范围(分区)——细分和细分不能比较,故【细分】是分区
方向的尽头,就是分区的开始——2020年的尽头是下个细分的2018,不能跨越
本章会在9.3小节,进一步总结窗口计算和表计算背后的设置方法。
02
—
两个排序依据
问题6: 华东地区中,各订单年度、各订单季度 的 销售额总结 及 季度环比
图9‑19 使用SQL的窗口函数返回上一个聚合值
图9‑20排序字段的更换会引起截然不同的计算逻辑
这个过程,与9.2.3小节中Tableau表计算的设置如出一辙,特定维度中的多个字段都是表计算的依据,谁要和谁先比较,对应的字段应该在下面,即“深度优先”的原则,其背后对应的正是SQL中的ORDER BY子句次序。在这个过程中,读者应该可以感受到SQL语言的优雅和简洁,而且可以辅助理解Tableau表计算的设置过程和底层逻辑。
理论上,数据分析的计算都可以使用SQL完成,不过,现实的分析中很多业务用户把它习惯性地视为IT专属工具而敬而远之,殊不知伴随技术的快速发展和从业者心知的日渐提高,往日的生涩工具已经越来越简洁好用。在全球化面前,地球是平的;在数据爆炸面前,技术也将加速平民化。
虽然SQL通过程序语言控制输出,并没有类似于Excel的透视表、Tableau的工作表的“可视化区域”,因此使用偏离类计算不像聚合函数善于控制。不过一旦理解了它们的原理,分析师就可以进一步驾驭Tableau的表计算,并在复杂的业务面前,借助ETL工具(比如Prep Builder)将复杂逻辑转移到数据表阶段,从而简化问题的复杂性。
Tableau Zen Master 2021
Tableau Visionary 2022
………… …………