分级汇总实现的3种方法比较

最新推荐文章于 2022-01-18 17:09:07 发布

l1t

最新推荐文章于 2022-01-18 17:09:07 发布

阅读量2.4k

点赞数

文章标签： statistics access sql loops table nested

本文链接：https://blog.youkuaiyun.com/l1t/article/details/651089

版权

本文对比了三种实现分级汇总的方法，包括通过UNION ALL、CASE WHEN结合ROLLUP及使用SUM(CNT)的方式，详细展示了每种方法的SQL代码及执行效果。在小数据量下，第三种方法虽然一致性获取和物理读取较多，但执行速度较快。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

分级汇总实现的3种方法比较

代码:--------------------------------------------------------------------------------
select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
(
(select substr(z01_08,1,1)||'00' code ,count(*) cnt
from cj601
group by substr(z01_08,1,1))
union
(select substr(z01_08,1,2)||'0' code ,count(*) cnt
from cj601
group by substr(z01_08,1,2))
union
(select substr(z01_08,1,3) code ,count(*) cnt
from cj601
group by substr(z01_08,1,3))
)
c, djzclx b where c.code=b.reg_code;
代码   登记注册类型                            家数
------ --------------------------------------- ---------
100    内资企业
110      国有企业
120      集体企业
130      股份合作企业
140      联营企业
141        国有联营企业
142        集体联营企业
143        国有与集体联营企业
149        其他联营企业
150      有限责任公司
151        国有独资公司
159        其他有限责任公司
160      股份有限公司
170      私营企业
171        私营独资企业
172        私营合伙企业
173        私营有限责任公司
174        私营股份有限公司
200    港、澳、台商投资企业
210      合资经营企业(港或澳、台资)
220      合作经营企业(港或澳、台资)
230      港、澳、台商独资经营企业
240      港、澳、台商投资股份有限公司
300    外商投资企业
310      中外合资经营企业
320      中外合作经营企业
330      外资企业
340      外商投资股份有限公司

----
lastwinner

type: substr(z01_08,1,1)||'00'
subtype : substr(z01_08,1,2)||'0'
sub-subtype : substr(z01_08,1,3)

select ..........
group by rollup(type, subtype, sub-subtype)

试试看

代码:
--------------------------------------------------------------------------------
select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
(
select
case when code3 is not null then code3
     when code2<>'0' then code2
else code1
end code,cnt
from (
select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,count(*) cnt
    from j601
    group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3))
) where code2<>code3 or code3 is null and code1<>'00'
)
c, djzclx b where c.code=b.reg_code
order by 1
;
最终版14.89秒

代码:--------------------------------------------------------------------------------
select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
(
select
case when code3 is not null then code3
     when code2<>'0' then code2
else code1
end code,cnt
from (
select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,sum(cnt) cnt
    from (select substr(z01_08,1,3) z01_08,count(*) cnt from j601 group by substr(z01_08,1,3))
    group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3))
) where code2<>code3 or code3 is null and code1<>'00'
)
c, djzclx b where c.code=b.reg_code
order by 1
;
在小一些的数据量上的执行情况

代码:--------------------------------------------------------------------------------
已连接。
SQL> set autot on
SQL> set timi on
SQL> select code 代码 , substrb(' ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
2 (
3 (select substr(z01_08,1,1)||'00' code ,count(*) cnt
4 from cj601
5 group by substr(z01_08,1,1))
6 union
7 (select substr(z01_08,1,2)||'0' code ,count(*) cnt
8 from cj601
9 group by substr(z01_08,1,2))
10 union
11 (select substr(z01_08,1,3) code ,count(*) cnt
12 from cj601
13 group by substr(z01_08,1,3))
14 )
15 c, djzclx b where c.code=b.reg_code;

已选择28行。

已用时间: 00: 00: 01.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     VIEW
   3    2       SORT (UNIQUE)
   4    3         UNION-ALL
   5    4           SORT (GROUP BY)
   6    5             TABLE ACCESS (FULL) OF 'CJ601'
   7    4           SORT (GROUP BY)
   8    7             TABLE ACCESS (FULL) OF 'CJ601'
   9    4           SORT (GROUP BY)
10    9             TABLE ACCESS (FULL) OF 'CJ601'
11    1     TABLE ACCESS (BY INDEX ROWID) OF 'DJZCLX'
12   11       INDEX (UNIQUE SCAN) OF 'SYS_C002814' (UNIQUE)

Statistics
----------------------------------------------------------
        199 recursive calls
          0 db block gets
      13854 consistent gets
       2086 physical reads
          0 redo size
       1480 bytes sent via SQL*Net to client
        514 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          8 sorts (memory)
          0 sorts (disk)
         28 rows processed

SQL> select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
2 (
3 select
4 case when code3 is not null then code3
5       when code2<>'0' then code2
6 else code1
7 end code,cnt
8   from (
9 select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,count(*) cnt
10      from cj601
11      group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3))
12 ) where code2<>code3 or code3 is null and code1<>'00'
13 )
14 c, djzclx b where c.code=b.reg_code
15 order by 1
16 ;

已选择28行。

已用时间: 00: 00: 00.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     NESTED LOOPS
   3    2       VIEW
   4    3         FILTER
   5    4           SORT (GROUP BY ROLLUP)
   6    5             TABLE ACCESS (FULL) OF 'CJ601'
   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'DJZCLX'
   8    7         INDEX (UNIQUE SCAN) OF 'SYS_C002814' (UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       4628 consistent gets
        701 physical reads
          0 redo size
       1480 bytes sent via SQL*Net to client
        514 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
         28 rows processed

SQL> select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
2 (
3 select
4 case when code3 is not null then code3
5       when code2<>'0' then code2
6 else code1
7 end code,cnt
8   from (
9 select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,sum(cnt) cnt
10      from (select substr(z01_08,1,3) z01_08,count(*) cnt from cj601 group by substr(z01_08,1,3))
11      group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3))
12 ) where code2<>code3 or code3 is null and code1<>'00'
13 )
14 c, djzclx b where c.code=b.reg_code
15 order by 1
16 ;

已选择28行。

已用时间: 00: 00: 00.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     NESTED LOOPS
   3    2       VIEW
   4    3         FILTER
   5    4           SORT (GROUP BY ROLLUP)
   6    5             VIEW
   7    6               SORT (GROUP BY)
   8    7                 TABLE ACCESS (FULL) OF 'CJ601'
   9    2       TABLE ACCESS (BY INDEX ROWID) OF 'DJZCLX'
10    9         INDEX (UNIQUE SCAN) OF 'SYS_C002814' (UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       4628 consistent gets
        705 physical reads
          0 redo size
       1480 bytes sent via SQL*Net to client
        514 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
         28 rows processed

SQL>

第3种的一致性取和物理读都超过第2种，不过还是快一些