SQL改写案例1

一开发哥们找我改写SQL，他写的逻辑始终不对，安排！

-- 他写的SQL：
-- order_id 是主键

with a as (
select str_to_date(regist_time,'%Y-%m-%d') as regist_time
from d 
where str_to_date(regist_time,'%Y-%m-%d') between '2022-01-01' and '2022-02-01'
group by str_to_date(regist_time,'%Y-%m-%d')
),
b as (
select str_to_date(regist_time,'%Y-%m-%d') as regist_time,order_id
from d 
where handle_dept = 'aaaaaa管理局'
and get_type = '互联网xcxsdg'
),
c as (
select str_to_date(regist_time,'%Y-%m-%d') as regist_time,order_id
from d 
where handle_dept = 'aaaaaa管理局'
and get_type = 'rkv程序'
)
select a.regist_time,count(b.order_id) pc,count(c.order_id) wxxch
from a
left join b on a.regist_time = b.regist_time
left join c on a.regist_time = c.regist_time
group by a.regist_time
order by a.regist_time asc

这条SQL的逻辑是要在 '2022-01-01' and '2022-02-01' 的数据中求出条件为1：handle_dept = 'aaaaaa管理局' and get_type = '互联网xcxsdg'和
条件为2：handle_dept = 'aaaaaa管理局' and get_type = 'rkv程序'。
平时经常和他交流，有时候他看我优化SQL 经常用到 CTE 表达式，然而现在他也学会了这么玩，但是也分情况来使用。
但是像这种SQL只查询一张表的情况下是不需要用到 CTE 表达式 加 left join , 如果写法不妥当，容易让数据翻倍，本来简单的逻辑就复杂化了。

-- 然后在他原有的SQL上进行等价改写：

WITH a AS (SELECT str_to_date(regist_time, '%Y-%m-%d') AS regist_time
           FROM d
           WHERE str_to_date(regist_time, '%Y-%m-%d') BETWEEN '2022-01-01' AND '2022-02-01'
           GROUP BY str_to_date(regist_time, '%Y-%m-%d')),
     b AS (SELECT str_to_date(regist_time, '%Y-%m-%d') AS regist_time, order_id
           FROM d
           WHERE handle_dept = 'aaaaaa管理局'
             AND get_type = '互联网xcxsdg'),
     c AS (SELECT str_to_date(regist_time, '%Y-%m-%d') AS regist_time, order_id
           FROM d
           WHERE handle_dept = 'aaaaaa管理局'
             AND get_type = 'rkv程序')
SELECT a.regist_time, COUNT(DISTINCT b.order_id) pc, COUNT(DISTINCT c.order_id) wxxch
FROM a
         LEFT JOIN b ON a.regist_time = b.regist_time
         LEFT JOIN c ON a.regist_time = c.regist_time
GROUP BY a.regist_time
ORDER BY a.regist_time ASC;

这样一来，顺利解决的这个问题。

 

最后给了他一个我等价改写的版本：

select str_to_date(regist_time, '%Y-%m-%d') as regist_time,
       count((case when handle_dept = 'aaaaaa管理局' and get_type = '互联网xcxsdg' then order_id end)) as pc,
       count((case when handle_dept = 'aaaaaa管理局' and get_type = 'rkv程序' then order_id end)) as wxxch
from d 
where str_to_date(regist_time, '%Y-%m-%d') between '2022-01-01' and '2022-02-01'
group by str_to_date(regist_time, '%Y-%m-%d')；

这种才生产代码上是最正确的写法。