Oracle拆列自定义分组查询

数据需求：
1.拆散columnvalue2（指标值）列为独立列，以特殊符号（逗号）区分

原始数据如下：

拆列后数据，如下：

2.在拆列的基础上查询地铁线路与线路区间晚点分档分组数据，分组要求如下：  

columnvalue2:指标值

条件	分档
columnvalue2/60 > 0 and columnvalue2/60 <= 2	0-2
columnvalue2/60 > 2 and columnvalue2/60 <= 3	2-3
columnvalue2/60 > 3 and columnvalue2/60 <= 5	3-5
columnvalue2/60 > 5 and columnvalue2/60 <= 15	5-15
else	15-∞

---------------------------------漂亮的分隔线-------------------------------------

分析过程：
1.首先，我们要知道怎么去拆列？
  在Oracle中果一个字符串中有像逗号或其它符号分隔，你想把它折分成列，如’first field, second field , third field’,
拆成如：
first field
second field
third field

可以通过创建临时表的方式，通过一个通用拆列SQL去解决，见下图：

2.拆列后，我们要知道在拆列的基础上查询地铁线路与线路之间的自定义晚点分档分组数据？
A,分档条件可以通过case when去分组判断  
B,线路与线路的合集可以考虑使用union连接  

C,columnvalue2（指标值）在数据库中存为字符串，需要使用to_number转换并格式化

---------------------------------漂亮的分隔线--------------------------------------

具体应用方案
第一种,初始化方案
思路分析：
1.拆列并创建临时表，存储查询需要用的基础数据
2.使用union合并线路区间
3.使用left join连接线路区间，case when筛选分档数据，使用to_number对指标值进行类型转换并格式化
4.创建临时表语句级源表插入后触发器，即源表插入数据后一次性更新临时表数据

创建拆列临时表SQL：

CREATE TABLE tb_tep AS WITH temp0 AS
  (SELECT LEVEL lv
   FROM dual CONNECT BY LEVEL <= 100)
SELECT columnvalue4,
       substr(t.vals,instr(t.vals, ',', 1, tv.lv) + 1, instr(t.vals, ',', 1, tv.lv + 1) -(instr(t.vals, ',', 1, tv.lv) + 1)) AS columnvalue2
FROM
  (SELECT columnvalue4,
          ',' || columnvalue2 || ',' AS vals,
                                 length(columnvalue2 || ',') - nvl(length(REPLACE(columnvalue2, ',')), 0) AS cnt
   FROM z_gettraindelay) t
JOIN temp0 tv ON tv.lv <= t.cnt
ORDER BY 1;

查询结果:

线路区间的晚点分档查询SQL：

SELECT a.xl,
       zz.columnvalue4,
       zz.he,
       zz.hehe
FROM
  (SELECT '一号线' xl,
                1 sx
   FROM dual
   UNION ALL SELECT '二号线' xl,
                          2 sx
   FROM dual
   UNION ALL SELECT '三号线' xl,
                          3 sx
   FROM dual
   UNION ALL SELECT '三北线' xl,
                          4 sx
   FROM dual
   UNION ALL SELECT '四号线' xl,
                          5 sx
   FROM dual
   UNION ALL SELECT '五号线' xl,
                          6 sx
   FROM dual
   UNION ALL SELECT '六号线' xl,
                          7 sx
   FROM dual
   UNION ALL SELECT '八号线' xl,
                          8 sx
   FROM dual
   UNION ALL SELECT '广佛线' xl,
                          9 sx
   FROM dual
   UNION ALL SELECT 'APM线' xl,
                           10 sx
   FROM dual) a
LEFT JOIN
  (SELECT z.columnvalue4,
          z.he,
          count(*) hehe
   FROM
     (SELECT columnvalue4, CASE WHEN to_number (columnvalue2,'999999999999')/60 > 0
      AND to_number (columnvalue2,'999999999999')/60 <= 2 THEN '0-2' WHEN to_number (columnvalue2,'999999999999')/60 > 2
      AND to_number (columnvalue2,'999999999999')/60 <= 3 THEN '2-3' WHEN to_number (columnvalue2,'999999999999')/60 > 3
      AND to_number (columnvalue2,'999999999999')/60 <= 5 THEN '3-5' WHEN to_number (columnvalue2,'999999999999')/60 > 5
      AND to_number (columnvalue2,'999999999999')/60 <= 15 THEN '5-15' ELSE '15-∞' END he
      FROM TB_TEP) z
   GROUP BY z.columnvalue4,
            z.he) zz ON a.xl=zz.columnvalue4
ORDER BY a.sx

查询结果:

临时表语句级源表插入后Trigger：

CREATE OR REPLACE TRIGGER TRG_Temp AFTER
INSERT ON Z_GETTRAINDELAY BEGIN 
--清空数据
DELETE
FROM tb_tep; 
--重新插入数据
INSERT INTO tb_tep WITH temp0 AS
  (SELECT LEVEL lv
   FROM dual CONNECT BY LEVEL <= 100)
SELECT columnvalue4,
       substr(t.vals,instr(t.vals, ',', 1, tv.lv) + 1, instr(t.vals, ',', 1, tv.lv + 1) -(instr(t.vals, ',', 1, tv.lv) + 1)) AS columnvalue2
FROM
  (SELECT columnvalue4,
          ',' || columnvalue2 || ',' AS vals,
                                 length(columnvalue2 || ',') - nvl(length(REPLACE(columnvalue2, ',')), 0) AS cnt
   FROM z_gettraindelay) t
JOIN temp0 tv ON tv.lv <= t.cnt
ORDER BY 1; END TRG_Temp;

第二种,优化方案
思路分析：
1.使用with tb as (), tb2 as(), tb3 as()的临时表形式优化初始化方案

2.避免创建临时表，摒弃创建触发器更新临时表数据

优化整合SQL：

WITH temp0 AS
  ( SELECT LEVEL lv
   FROM dual CONNECT BY LEVEL <= 100 ),
     temp1 AS
  ( SELECT columnvalue4,
           substr(t.vals,instr(t.vals, ',', 1, tv.lv) + 1,instr(t.vals, ',', 1, tv.lv + 1)-(instr(t.vals, ',', 1, tv.lv) + 1)) AS columnvalue2
   FROM
     ( SELECT columnvalue4,
              ',' || columnvalue2 || ',' AS vals,
                                     length(columnvalue2 || ',') - nvl(length(REPLACE(columnvalue2, ',')), 0) AS cnt
      FROM z_gettraindelay z ) t
   JOIN temp0 tv ON tv.lv <= t.cnt
   ORDER BY 1),
     temp2 AS
  ( SELECT z.columnvalue4,
           z.he,
           count(*) hehe
   FROM
     ( SELECT columnvalue4, CASE WHEN to_number (columnvalue2,'999999999999')/60 > 0
      AND to_number (columnvalue2,'999999999999')/60 <= 2 THEN '0-2' WHEN to_number (columnvalue2,'999999999999')/60 > 2
      AND to_number (columnvalue2,'999999999999')/60 <= 3 THEN '2-3' WHEN to_number (columnvalue2,'999999999999')/60 > 3
      AND to_number (columnvalue2,'999999999999')/60 <= 5 THEN '3-5' WHEN to_number (columnvalue2,'999999999999')/60 > 5
      AND to_number (columnvalue2,'999999999999')/60 <= 15 THEN '5-15' ELSE '15-∞' END he
      FROM temp1) z
   GROUP BY z.columnvalue4,
            z.he ),
     temp3 AS
  ( SELECT '一号线' xl,
                 1 sx
   FROM dual
   UNION ALL SELECT '二号线' xl,
                          2 sx
   FROM dual
   UNION ALL SELECT '三号线' xl,
                          3 sx
   FROM dual
   UNION ALL SELECT '三北线' xl,
                          4 sx
   FROM dual
   UNION ALL SELECT '四号线' xl,
                          5 sx
   FROM dual
   UNION ALL SELECT '五号线' xl,
                          6 sx
   FROM dual
   UNION ALL SELECT '六号线' xl,
                          7 sx
   FROM dual
   UNION ALL SELECT '八号线' xl,
                          8 sx
   FROM dual
   UNION ALL SELECT '广佛线' xl,
                          9 sx
   FROM dual
   UNION ALL SELECT 'APM线' xl,
                           10 sx
   FROM dual )
SELECT a.xl,
       zz.columnvalue4,
       zz.he,
       zz.hehe
FROM temp3 a
LEFT JOIN temp2 zz ON a.xl=zz.columnvalue4
ORDER BY a.sx

查询结果：