SQL等价改写优化案例精选第二部【精兵简政】① 莫名其妙的行列转换

梁敬彬梁敬弘兄弟出品

往期回顾

SQL等价改写优化案例精选第一部【一统天下】① count(case when)的合并魔力
SQL等价改写优化案例精选第一部【一统天下】②分析函数的穿透之力
SQL等价改写优化案例精选第一部【一统天下】③ group by (case when)的聚合艺术
SQL等价改写优化案例精选第一部【一统天下】④ or 与 union all的优化交响

在SQL优化的世界里，"精兵简政"是一个值得铭记的核心理念。它意味着我们应当明确原始需求，去除多余操作，少走弯路。优秀的SQL不仅仅是能够得到正确结果的SQL，更是通过最直接、最高效的方式得到结果的SQL。本文将通过一个典型的行列转换案例，展示如何应用"精兵简政"原则优化查询性能。

二．精兵简政

在数据分析和报表生成过程中，行列转换是一个常见需求。然而，开发人员常常在实现这类需求时走弯路，创建了不必要的复杂查询。今天我们要分析的案例就是这样一个典型示例。

案例5 行列转换此处是必须的吗？

with  dcc_sys_log_tmp 
as  (select * from dcc_sys_log where peer_id = 'SR2@001.ChinaTelecom.com'   
           and log_time >= TO_DATE('2010-05-02', 'YYYY-MM-DD')
           and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1 
     ),
dcc_ne_log_tmp 
as  (select * from dcc_ne_log where peer_id = 'SC2.ChinaTelecom.com'   
           and log_time >= TO_DATE('2010-05-02', 'YYYY-MM-DD')
           and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1 
     ),
A AS (select peer_id,
             sum(dk_cnt) dk_cnt, 
             sum(send_normal) send_normal, 
             sum(recv_normal) recv_normal, 
             sum(recv_timeout) recv_timeout,
             sum(send_timeout) send_timeout, 
             sum(exception_cnt) exception_cnt, 
             sum(unrecvcca_cnt) unrecvcca_cnt, 
             sum(unsendcca_cnt) unsendcca_cnt 
             from (select peer_id,
                          case when type = '断开次数' then cnt else 0 end dk_cnt, 
                          case when type = '发送正常包个数' then cnt else 0 end send_normal,
                          case when type = '接受正常包个数' then cnt else 0 end recv_normal,
                          case when type = '等待CCA应答超时包个数' then cnt else 0 end recv_timeout, 
                          case when type = '消息包发送超时个数' then cnt else 0 end send_timeout, 
                          case when type = '异常包的个数' then cnt else 0 end exception_cnt, 
                          case when type = '接收CCA不可达包个数' then cnt else 0 end unrecvcca_cnt,
                          case when type = '发送CCA不可达包个数' then cnt else 0 end unsendcca_cnt
                          from (select peer_id,
                                       '断开次数' type, 
                                        nvl(count(1), 0) cnt 
                                        from dcc_sys_log_tmp 
                                        where action = 'disconnect'
                                        and cause like '对端被关闭%' 
                                        group by peer_id 
                                  union all 
                                   select peer_id,
                                          '发送正常包个数' type, 
                                           nvl(count(1), 0) 
                                           from dcc_ne_log_tmp
                                           where result = 1
                                           and direction = 'send'
                                           group by peer_id 
                                   union all 
                                     select peer_id,'接受正常包个数' type,
                                             nvl(count(1),0) 
                                             from dcc_ne_log_tmp 
                                             where result = 1
                                             and direction = 'recv' 
                                             group by peer_id 
                                   union all 
                                     select peer_id,'等待CCA应答超时包个数' type,
                                              nvl(count(1),0)
                                              from dcc_ne_log_tmp 
                                              where svcctx_id like 'SR-Timeout%' 
                                              group by peer_id 
                                    union all 
                                      select peer_id,'消息包发送超时个数' type,
                                              nvl(count(1),0) 
                                              from dcc_sys_log_tmp 
                                              where cause = '发送消息包超时' 
                                              group by peer_id 
                                    union all 
                                      select peer_id,'异常包的个数' type,
                                             nvl(count(1),0)
                                             from dcc_ne_log_tmp 
                                             where result = 0 
                                             group by peer_id 
                                    union all 
                                       select peer_id,
                                              '接收CCA不可达包个数' type,
                                              nvl(count(1),0)
                                              from dcc_ne_log_tmp 
                                              where result = 1 
                                              and direction = 'recv' 
                                              and result_code= 'DIAMETER_UNABLE_TO_DELIVER' 
                                              group by peer_id
                                    union all 
                                       select peer_id,
                                             '发送CCA不可达包个数' type,
                                              nvl(count(1),0)
                                              from dcc_ne_log_tmp
                                              where result = 1 
                                              and direction = 'send' 
                                              and result_code= 'DIAMETER_UNABLE_TO_DELIVER'
                                              group by peer_id))
              group by peer_id )
     select peer_id 网元名称,
            nvl(dk_cnt,0) 断开次数, 
            nvl(send_normal,0) 发送正常包个数, 
            nvl(recv_normal,0) 接受正常包个数, 
            nvl(recv_timeout,0) 等待CCA应答超时包个数, 
            nvl(send_timeout,0) 消息包发送超时个数, 
            nvl(exception_cnt,0) 异常包的个数, 
            nvl(unrecvcca_cnt,0) 接收CCA不可达包个数, 
            nvl(unsendcca_cnt,0) 发送CCA不可达包个数 
     from (select a.* 
             from A , dcc_ne_info b 
             where a.peer_id(+) =b.peer_id 
           union all 
           select a.*
             from A , dcc_ne_info b 
             where a.peer_id=b.peer_id(+) 
             and b.peer_id is null
            )

问题分析：不必要的行列转换

仔细分析上面的SQL，我们可以清晰地看到一个核心问题：不必要的行列转换。这个问题具体体现在以下几个部分：

行转列过程 - 最内层子查询实现了行转列（垂直拆分）：

select peer_id, '断开次数' type, nvl(count(1), 0) cnt 
from dcc_sys_log_tmp 
where action = 'disconnect' and cause like '对端被关闭%' 
group by peer_id 
union all 
select peer_id, '发送正常包个数' type, nvl(count(1), 0) 
from dcc_ne_log_tmp
where result = 1 and direction = 'send'
group by peer_id 
-- 更多 UNION ALL 语句...

这段代码将不同条件下的计数结果垂直排列，每种类型一行，形成了一个竖表。

列转行过程 - 中间层子查询使用CASE WHEN实现了列转行（水平合并）：

select peer_id,
       case when type = '断开次数' then cnt else 0 end dk_cnt, 
       case when type = '发送正常包个数' then cnt else 0 end send_normal,
       -- 更多 CASE WHEN 语句...
from (/* 上面行转列的查询 */)

这段代码又将竖表转回横表，与最初需要的格式一致。

汇总过程 - 最外层使用SUM聚合函数对转换后的结果进行汇总：

select peer_id,
       sum(dk_cnt) dk_cnt, 
       sum(send_normal) send_normal,
       -- 更多 SUM 聚合...
from (/* 上面列转行的查询 */)
group by peer_id

这整个过程可以概括为：先将原始数据转为竖表（行转列），然后再将竖表转回横表（列转行），完全走了一个不必要的弯路。

优化思路：直接条件聚合

既然我们最终需要的是每个网元的各类统计指标（横表形式），我们可以直接在源表上使用条件聚合函数来计算，无需经过行列转换的中间步骤。

核心优化思路是将：

-- 这种结构：
select peer_id, '类型A' type, count(*) cnt from table where 条件A group by peer_id
union all 
select peer_id, '类型B' type, count(*) cnt from table where 条件B group by peer_id
-- 然后再用CASE WHEN把type转为列

-- 替换为这种直接聚合的结构：
select peer_id,
       count(case when 条件A then 1 end) as 类型A计数,
       count(case when 条件B then 1 end) as 类型B计数
from table
group by peer_id

优化后的SQL代码

with dcc_sys_log_union
as (select peer_id,
       COUNT(CASE WHEN action = 'disconnect' and cause like '对端被关闭%' THEN 1 END) dk_cnt,
       COUNT(CASE WHEN cause = '发送消息包超时'  THEN 1 END) send_timeout
from dcc_sys_log where peer_id = 'SR2@001.ChinaTelecom.com'   
           and log_time >= TO_DATE('2010-05-02', 'YYYY-MM-DD')
           and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1 
group by peer_id ),
dcc_ne_log_union
as 
(select peer_id,
       COUNT(CASE WHEN direction = 'send' and result=1 then 1 end ) send_normal,
       COUNT(CASE WHEN direction = 'recv' and result=1 then 1 end ) recv_normal,
       COUNT(CASE WHEN svcctx_id like 'SR-Timeout%'  then 1 end ) recv_timeout,
       COUNT(CASE WHEN  result=0 then 1 end ) exception_cnt,
       COUNT(CASE WHEN direction = 'recv' and result=1 and result_code= 'DIAMETER_UNABLE_TO_DELIVER' then 1 end ) unrecvcca_cnt,
       COUNT(CASE WHEN direction = 'send' and result=1 and result_code= 'DIAMETER_UNABLE_TO_DELIVER' then 1 end ) unsendcca_cnt
from dcc_ne_log 
     where peer_id = 'SR2@001.ChinaTelecom.com'   
           and log_time >= TO_DATE('2010-05-02', 'YYYY-MM-DD')
           and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1  
group by peer_id ),
t as
(select  t1.* ,t2.dk_cnt,t2.send_timeout 
from dcc_ne_log_union t1 ,dcc_sys_log_union t2
where t1.peer_id=t2.peer_id(+))
select peer_id 网元名称,
       nvl(t.dk_cnt,0) 断开次数,
       nvl(t.send_normal,0) 发送正常包个数,
       nvl(t.recv_normal,0) 接受正常包个数,
       nvl(t.recv_timeout,0) 等待CCA应答超时包个数,
       nvl(t.send_timeout,0) 消息包发送超时个数, 
       nvl(t.exception_cnt,0) 异常包的个数,
       nvl(t.unrecvcca_cnt,0) 接收CCA不可达包个数,
       nvl(t.unsendcca_cnt,0) 发送CCA不可达包个数
 from t full outer  join   dcc_ne_info b
using (peer_id);

总结

本案例展示了SQL优化中的一个典型误区——不必要的行列转换。原SQL将数据先转为竖表再转回横表，走了一条完全没必要的弯路。这种"莫名其妙"的设计不仅增加了代码复杂度，还严重影响了查询性能。

优化后的方案直接采用条件聚合，一步到位满足了业务需求。这正是"精兵简政"理念的精髓所在：不是能做出来就行，而是要用最简单直接的方式做出来。

在SQL编写中，我们需要警惕这种无意识的复杂化倾向。有时候，我们习惯性地复制既有模式，却忘记思考这种模式对当前问题是否真的适用。真正的SQL优化不仅关注"怎么做"，更要思考"是否需要这么做"。

行列转换只是众多"莫名其妙"操作中的一种。在后续案例中，我们将继续探讨如何识别和消除SQL中的无效操作，使我们的查询既能达成目标，又能保持高效简洁。

未完待续…

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列