SQL性能优化：复杂CASE WHEN语句的重构
在编写统计报表程序时，遇到了关于SQL性能的问题。当前的SQL查询使用了大量的CASE WHEN子句进行条件判断和求和，可能造成性能瓶颈。问题在于针对'pq'和'test1'字段的多个组合条件进行了重复计算。为提升效率，可以考虑使用更高效的查询方式，如预计算表、存储过程、或者通过JOIN和位运算来减少计算量。
大家好，我在写一个片区统计的报表程序，但总觉得使用sum((case when...这种语句在性能上不是太好，请大家出一下主意。
语句如下:
select
SUM((CASE When pq in ('p01') and test1 in('001') Then 1 Else 0 End)) into p01 ,
SUM((CASE When pq in ('p01') and test1 in('002') Then 1 Else 0 End)) into p02 ,
SUM((CASE When pq in ('p01') and test1 in('003') Then 1 Else 0 End)) into p03 ,
SUM((CASE When pq in ('p01') and test1 in('004') Then 1 Else 0 End)) into p04 ,
SUM((CASE When pq in ('p01') and test1 in('005') Then 1 Else 0 End)) into p05 ,
SUM((CASE When pq in ('p01') and test1 in('006') Then 1 Else 0 End)) into p06 ,
SUM((CASE When pq in ('p01') and test1 in('007') Then 1 Else 0 End)) into p07 ,
SUM((CASE When pq in ('p01') and test1 in('008') Then 1 Else 0 End)) into p08 ,
SUM((CASE When pq in ('p01') and test1 in('009') Then 1 Else 0 End)) into p09 ,
SUM((CASE When pq in ('p01') and test1 in('010') Then 1 Else 0 End)) into p10 ,
SUM((CASE When pq in ('p01') and test1 in('012') Then 1 Else 0 End)) into p11 ,
SUM((CASE When pq in ('p01') and test1 in('013') Then 1 Else 0 End)) into p12 ,
SUM((CASE When pq in ('p01') and test1 in('014') Then 1 Else 0 End)) into p13 ,
SUM((CASE When pq in ('p01') and test1 in('015') Then 1 Else 0 End)) into p14 ,
SUM((CASE When pq in ('p01') and test1 in('016') Then 1 Else 0 End)) into p15 ,
,
SUM((CASE When pq in ('p02') and test1 in('001') Then 1 Else 0 End)) into pz01 ,
SUM((CASE When pq in ('p02') and test1 in('002') Then 1 Else 0 End)) into pz02 ,
SUM((CASE When pq in ('p02') and test1 in('003') Then 1 Else 0 End)) into pz03 ,
SUM((CASE When pq in ('p02') and test1 in('004') Then 1 Else 0 End)) into pz04 ,
SUM((CASE When pq in ('p02') and test1 in('005') Then 1 Else 0 End)) into pz05 ,
SUM((CASE When pq in ('p02') and test1 in('006') Then 1 Else 0 End)) into pz06 ,
SUM((CASE When pq in ('p02') and test1 in('007') Then 1 Else 0 End)) into pz07 ,
SUM((CASE When pq in ('p02') and test1 in('008') Then 1 Else 0 End)) into pz08 ,
SUM((CASE When pq in ('p02') and test1 in('009') Then 1 Else 0 End)) into pz09 ,
SUM((CASE When pq in ('p02') and test1 in('010') Then 1 Else 0 End)) into pz10 ,
SUM((CASE When pq in ('p02') and test1 in('012') Then 1 Else 0 End)) into pz11 ,
SUM((CASE When pq in ('p02') and test1 in('013') Then 1 Else 0 End)) into pz12 ,
SUM((CASE When pq in ('p02') and test1 in('014') Then 1 Else 0 End)) into pz13 ,
SUM((CASE When pq in ('p02') and test1 in('015') Then 1 Else 0 End)) into pz14 ,
SUM((CASE When pq in ('p02') and test1 in('016') Then 1 Else 0 End)) into pz15 ,
,
,
SUM((CASE When pq in ('p03') and test1 in('001') Then 1 Else 0 End)) into pz01a ,
SUM((CASE When pq in ('p03') and test1 in('002') Then 1 Else 0 End)) into pz02a ,
SUM((CASE When pq in ('p03') and test1 in('003') Then 1 Else 0 End)) into pz03a ,
SUM((CASE When pq in ('p03') and test1 in('004') Then 1 Else 0 End)) into pz04a ,
SUM((CASE When pq in ('p03') and test1 in('005') Then 1 Else 0 End)) into pz05a ,
SUM((CASE When pq in ('p03') and test1 in('006') Then 1 Else 0 End)) into pz06a ,
SUM((CASE When pq in ('p03') and test1 in('007') Then 1 Else 0 End)) into pz07a ,
SUM((CASE When pq in ('p03') and test1 in('008') Then 1 Else 0 End)) into pz08a ,
SUM((CASE When pq in ('p03') and test1 in('009') Then 1 Else 0 End)) into pz09a ,
SUM((CASE When pq in ('p03') and test1 in('010') Then 1 Else 0 End)) into pz10a ,
SUM((CASE When pq in ('p03') and test1 in('012') Then 1 Else 0 End)) into pz11a ,
SUM((CASE When pq in ('p03') and test1 in('013') Then 1 Else 0 End)) into pz12a ,
SUM((CASE When pq in ('p03') and test1 in('014') Then 1 Else 0 End)) into pz13a ,
SUM((CASE When pq in ('p03') and test1 in('015') Then 1 Else 0 End)) into pz14a ,
SUM((CASE When pq in ('p03') and test1 in('016') Then 1 Else 0 End)) into pz15a ,
,
,
SUM((CASE When pq in ('p04') and test1 in('001') Then 1 Else 0 End)) into pz01ab ,
SUM((CASE When pq in ('p04') and test1 in('002') Then 1 Else 0 End)) into pz02ab ,
SUM((CASE When pq in ('p04') and test1 in('003') Then 1 Else 0 End)) into pz03ab ,
SUM((CASE When pq in ('p04') and test1 in('004') Then 1 Else 0 End)) into pz04ab ,
SUM((CASE When pq in ('p04') and test1 in('005') Then 1 Else 0 End)) into pz05ab ,
SUM((CASE When pq in ('p04') and test1 in('006') Then 1 Else 0 End)) into pz06ab ,
SUM((CASE When pq in ('p04') and test1 in('007') Then 1 Else 0 End)) into pz07ab ,
SUM((CASE When pq in ('p04') and test1 in('008') Then 1 Else 0 End)) into pz08ab ,
SUM((CASE When pq in ('p04') and test1 in('009') Then 1 Else 0 End)) into pz09ab ,
SUM((CASE When pq in ('p04') and test1 in('010') Then 1 Else 0 End)) into pz10ab ,
SUM((CASE When pq in ('p04') and test1 in('012') Then 1 Else 0 End)) into pz11ab ,
SUM((CASE When pq in ('p04') and test1 in('013') Then 1 Else 0 End)) into pz12ab ,
SUM((CASE When pq in ('p04') and test1 in('014') Then 1 Else 0 End)) into pz13ab ,
SUM((CASE When pq in ('p04') and test1 in('015') Then 1 Else 0 End)) into pz14ab ,
SUM((CASE When pq in ('p04') and test1 in('016') Then 1 Else 0 End)) into pz15ab ,
...
from test
扫一扫
319
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
