SQL在DBA手里-改写篇

背景

最近运营需要做月报汇总交易情况，之前一直是他们手工出的数据，他们想做成月初自动发送邮件，从而减轻他们的工作量。于是他们提供SQL我们在邮件服务器配置做定时发送任务。

表介绍（表及字段已做脱敏处理）

• trans_profits
  交易毛利表：仅记录每天毛利数据
• trans_offline_order
  线下订单表：记录线下订单情况
• trans_online_order
  线上订单表：记录线上订单情况

SQL “变装”过程

原始：SQL

• 缺点：不易读，查询套子查询
• 查询解读：将线下及线上订单“交易笔数”“交易金额”数据合并再与毛利表按“交易日期”关联查询，显示：“交易笔数”，“交易金额”，“毛利金额”，“月份”
  –注：线上线下订单表为原始数据，毛利表为汇算后的数据，因此毛利表无需count(*)统计交易笔数；

 

select d.month       as 月,round(s.count/10000 , 2) ||'万'      as 交易笔数,round(s.amt/10000 , 2) ||'万'        as 交易金额,round(d.profits_amt/10000 , 2) ||'万' as 毛利金额
from (SELECT to_char(trans_time, 'yyyyMM') as month,sum(profits_amt) as profits_amtFROM trans_profits -- 交易毛利表where trans_time >=  to_date('20240101', 'yyyyMMdd')and   trans_time <   to_date('20241231', 'yyyyMMdd')group by to_char(trans_time, 'yyyyMM')) dleft join (select month,sum(count) as count,sum(amt) as amtfrom (SELECT to_char(trans_time, 'yyyyMM') as month,count(1) as count,sum(trans_amt) as amtFROM trans_offline_order  -- 线下订单表where trans_cd = '00'and trans_time >= to_TIMESTAMP('20240101', 'yyyyMMdd')and trans_time <  to_TIMESTAMP('20241231', 'yyyyMMdd')group by to_char(trans_time, 'yyyyMM')union allSELECT to_char(trans_time, 'yyyyMM') as month,count(1) as count,sum(trans_amt) AS amtFROM trans_online_order  -- 线上订单表 WHERE trans_type IN ('01', '02')and trans_cd = '00'and trans_time >= to_TIMESTAMP('20240101', 'yyyyMMdd')and trans_time <  to_TIMESTAMP('20241231', 'yyyyMMdd')group by to_char(trans_time, 'yyyyMM')) tgroup by month) son d.month = s.monthorder by 1;

“变装”：SQL

• 优点：查询简洁易懂
• 查询解读：将线上、线下及毛利表进行数据合并，其中计算“交易笔数”线上、线下虚拟出列为ct 值为1标记，毛利表因为不需要记得笔数因此ct值标记为0，最后汇总时用sum(ct)列即可得到“交易笔数”。

SELECT 
substr(t.trans_time,0,6) 月,round(sum(ct) /10000 , 2) ||'万'         as 交易笔数,round(sum(trans_amt)/10000 , 2) ||'万'   as 交易金额,round(sum(profits_amt)/10000 , 2) ||'万' as 毛利金额
FROM (
SELECT to_char(trans_time,'yyyymmdd') trans_time,1 ct,trans_amt,0 profits_amt
FROM trans_offline_order  -- 线下订单表
where trans_cd = '00'and trans_time >= to_TIMESTAMP('20240101', 'yyyyMMdd')and trans_time <  to_TIMESTAMP('20241231', 'yyyyMMdd')
union all
SELECT to_char(trans_time,'yyyymmdd') trans_time,1 ct,trans_amt,0 profits_amt
FROM trans_online_order  -- 线上订单表 
WHERE trans_type IN ('01', '02')and trans_cd = '00'and trans_time >= to_TIMESTAMP('20240101', 'yyyyMMdd')and trans_time <  to_TIMESTAMP('20241231', 'yyyyMMdd')
union all 
SELECT to_char(trans_time,'yyyymmdd') trans_time,0 ct,0 trans_amt,profits_amt
FROM trans_profits  -- 交易毛利表
where trans_time  >= to_date('20240101', 'yyyyMMdd')and trans_time <   to_date('20241231', 'yyyyMMdd')) tGROUP BY substr(t.trans_time,0,6)ORDER BY 1 ;

执行计划对比

• Statistics 资源消耗 相同；
• | Rows | Bytes | Cost (%CPU)| Time | 这几项明显“变装”后更优于原SQL写法，原SQL写法甚至还用到了TempSpc的耗；
• 执行时间“变装”后慢了10+ms但影响不大；
  – 注（疑惑）：明明从执行计划来分析“变装”后的SQL更优，为啥会变慢了呢？

  

总结

SQL在其它部门的作用是以实现需求为主，但在DBA手里需要考虑在不改变需求结果的前提下，要让SQL更具有可读性及良好的性能。