SQL改写案例1

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

-- 他写的SQL：

# order_id 是主键

with a as (
select str_to_date(regist_time,'%Y-%m-%d') as regist_time
from inno_busi_info_all
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 inno_busi_info_all
where handle_dept = '越秀区市场监督管理局'
and get_type = '互联网平台'
),
c as (
select str_to_date(regist_time,'%Y-%m-%d') as regist_time,order_id
from inno_busi_info_all
where handle_dept = '越秀区市场监督管理局'
and get_type = '微信小程序'
)
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 = '越秀区市场监督管理局' and get_type = '互联网平台'  的数量 和 条件为2 handle_dept = '越秀区市场监督管理局' and get_type = '微信小程序'。

平时经常和他交流，有时候他看我优化SQL 经常用到 CTE 表达式，然而现在他也学会了这么玩，但是也分情况来使用。
但是像这种SQL只查询一张表的情况下是不需要用到 CTE 表达式 加 left join , 如果写法不妥当，容易让数据翻倍，本来简单的逻辑就复杂化了。

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

with a as (
select str_to_date(regist_time,'%Y-%m-%d') as regist_time
from inno_busi_info_all
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 inno_busi_info_all
where handle_dept = '越秀区市场监督管理局'
and get_type = '互联网平台'
),
c as (
select str_to_date(regist_time,'%Y-%m-%d') as regist_time,order_id
from inno_busi_info_all
where handle_dept = '越秀区市场监督管理局'
and get_type = '微信小程序'
)
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


这样一来，数据都形成了 1 的关系，顺利解决的这个问题。


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

select str_to_date(regist_time, '%Y-%m-%d') as regist_time,
       count((case when handle_dept = '越秀区市场监督管理局' and get_type = '互联网平台' then order_id end)) as pc,
       count((case when handle_dept = '越秀区市场监督管理局' and get_type = '微信小程序' then order_id end)) as wxxch
from inno_busi_info_all
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')；



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