最近工作中遇到一个需求场景,需要用VBA实现excel的透视表的形式。即分组汇总运算,如求和、求平均等。当然使用VBA的透视表代码可以实现,但本着学习的原则,我又用了SQL的方式实现,思路是值得参考的。
1、问题描述
用SQL查询语句,实现excel的透视表形式的结果。具体实现效果就是,每一层级的小组最下边,要计算呈现该层级组别的汇总数据。问题的难点就是在SQL查询结果中,插入每个维度的汇总结果。
2、解决思路
直接在改变SQL结果是不现实的,用SQL的窗口函数好像也并没有很方便,所以采用迂回的方法,把各层级分组的汇总结果,用SQL分次计算,然后通过union拼凑,最后通过排序实现汇总行的插入。
3、解决方案
sql代码编写
select [部门],[国家],[账号国家],0 as order_num1,0 as order_num2
sum([gmv]),sum([order_qty]),sum([sale_qty])
from table
group by [部门],[国家],[账号国家]
union
select [部门],[国家],1 as order_num1,0 as order_num2
sum([gmv]),sum([order_qty]),sum([sale_qty])
from table
group by [部门],[国家]
union
select [部门],2 as order_num1,1 as order_num2
sum([gmv]),sum([order_qty]),sum([sale_qty])
from table
group by [部门]
order by [部门],order_num2,[国家],order_num1
代码解析
如上面代码,[部门],[国家],[账号国家],3个是维度数据,gmv,order_qty,sale_qty是指标数据。
第一段sql是统计3个维度的聚合计算,
第二段sql是舍弃[账号国家],统计[部门],[国家]维度的聚合计算,相当于[国家]维度的汇总行计算。
第三段sql是舍弃[国家],[账号国家]2个维度,统计[国家]维度的聚合计算,相当于[部门]维度的汇总行计算。
三段代码通过union 进行拼合。
order_num1和order_num2是排序标识,用于union 之后通过排序把第二三段sql计算的汇总行插入到第一段代码中,完成透视表的分组汇总行计算。