记一次关于with cube的SQL优化记录

客户有个近2000行的代码，需要统一特定组合的数据，由于计算字段太多，用了很多中间表，耗时1h

 

优化步骤：

1. 因为之前有跑过，中间表存在，首先查出每个步骤跑的时间。

查看表名和对应的数据行数
select  a.name as '表名',b.rows as '表数据行数',a.crdate
from sysobjects a inner join sysindexes b
on a.id = b.id
where   a.type = 'u'
and b.indid in (0,1)
--and a.name  like 's_report%'
order by b.rows desc

中间表是通过select into表名生成的，生成这个表的时间就是下个表的创建时间-这个表的创建时间。时间差为耗时。找到耗时多的步骤，发现均为result表，result表示通过union all 且带有with cube生成的。

研究后面的代码，计算total组合有a, b,c,d,e. 

所以将union all with cube 改成如下：

group by grouping sets((a.CounterBA,a.storeid,a.cityname, a.citytier,a.RSM,a.AM,a.BAS,a.TrainerID),(a.citytier,a.cityname,a.rsm),(a.rsm,a.am,a.bas),(a.rsm,a.am),a.citytier,a.trainerid,a.rsm,(a.storeid,a.cityname, a.citytier,a.RSM,a.AM,a.BAS,a.TrainerID),())

 

 

 

 

验证组合相加：  每个注释为一个组合，去掉所有注释的行数=单个注释行数相加，其中()为总ttl一条

with origion as (
select a.CounterBA,grouping(counterba) cbg,a.storeid,grouping(storeid) sg,
a.cityname,grouping(cityname) cng, a.citytier , grouping(citytier) ctg,
a.RSM ,grouping(rsm) rsmg,a.AM,grouping(am) amg,a.BAS, grouping(a.BAS) basg, a.TrainerID,grouping(trainerId) tig
--,count(distinct a.customerid) NewRepeat, avg(age) avgage
--into S_Report_Result_NewRepeat
from groupby a
--group by a.CounterBA,a.storeid,a.cityname, a.citytier,a.RSM,a.AM,a.BAS,a.TrainerID
group by grouping sets((a.CounterBA,a.storeid,a.cityname, a.citytier,a.RSM,a.AM,a.BAS,a.TrainerID),(citytier,cityname,rsm),(rsm,am,bas),(rsm,am),citytier,trainerid,rsm,(a.storeid,a.cityname, a.citytier,a.RSM,a.AM,a.BAS,a.TrainerID),())
) select * from origion
where 1=1  and
 --(citytier,cityname,rsm)
 --cbg=1 and sg=1 and amg=1 and basg=1 and tig=1  and ctg=0 and cng=0 and rsmg=0
 --(rsm,am)
 --cbg=1 and sg=1  and basg=1 and tig=1  and ctg=1 and cng=1 and rsmg=0 and  amg=0
 --(rsm,am,bas)  
 --cbg=1 and sg=1 and cng=1 and ctg=1 and tig=1  and  basg=0 and  amg=0 and rsmg=0
 --citytier
 --cbg=1 and sg=1 and amg=1 and basg=1 and tig=1  and ctg=0 and cng=1 and rsmg=1
 --trainerid
 --cbg=1 and sg=1 and amg=1 and basg=1 and tig=0  and ctg=1 and cng=1 and rsmg=1
 --rsm
 --cbg=1 and sg=1 and amg=1 and basg=1 and tig=1  and ctg=1 and cng=1 and rsmg=0
 --非counterba
 -- cbg=1 and sg=0 and amg=0 and basg=0 and tig=0  and ctg=0 and cng=0 and rsmg=0
 --and ( (cbg=0 and sg=0 and cng=0 and ctg=0 and rsmg=0 and amg=0 and basg=0 and tig=0))