oracle 按旬统计并且每月小计 行转列 PIVOT函数 与分组小计 ROLLUP 函数

数据库版本:oralce  11g 

表名WM_TD_WATER_DAILY 结构如下,我把没用到的字段隐藏了,全放出来不太好哈.

MONITOR_ID为BI_TB_MONITOR_BI的ID ,用于关联站点名称(SHORT_NAME).

业务需求 :现有日水量表,每条数据记录着一天的用水量,bi_tb_monitor_bi中有一下九个站点

 全部SQL如下,下面有SQL分解,看起来能清晰很多

SELECT MON, CASE
         WHEN MON IS NULL AND DATETIME IS NULL THEN
          '累计水量'
         WHEN MON IS NOT NULL AND DATETIME IS NULL THEN
          '小计'
         ELSE
          CASE
            WHEN INSTR(DATETIME, '1旬') > 0 THEN
             REPLACE(DATETIME, '1旬', '上旬')
            WHEN INSTR(DATETIME, '2旬') > 0 THEN
             REPLACE(DATETIME, '2旬', '中旬')
            WHEN INSTR(DATETIME, '3旬') > 0 THEN
             REPLACE(DATETIME, '3旬', '下旬')
          END
       END DATETIME,
       SUM(NVL(车逻洞, 0)) 车逻洞,
       SUM(NVL(车逻闸, 0)) 车逻闸,
       SUM(NVL(车逻, 0)) 车逻,
       SUM(NVL(南关, 0)) 南关,
       SUM(NVL(头闸, 0)) 头闸,
       SUM(NVL(周山洞, 0)) 周山洞,
       SUM(NVL(界首小闸, 0)) 界首小闸,
       SUM(NVL(子英闸, 0)) 子英闸,
       SUM(NVL(周山, 0)) 周山,
       SUM(NVL(车逻洞, 0)) + SUM(NVL(车逻闸, 0)) + SUM(NVL(南关, 0)) +
       SUM(NVL(头闸, 0)) + SUM(NVL(周山洞, 0)) + SUM(NVL(界首小闸, 0)) +
       SUM(NVL(子英闸, 0)) + SUM(NVL(周山, 0)) 合计
  FROM 
  (
  SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,
               TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,
               TO_CHAR(MONITOR_DATE, 'mm') || '月' ||
               DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10), 0, '1旬' ，1, '2旬', '3旬') DATETIME,
               SUM(车逻洞) / 10000 车逻洞,
               SUM(车逻闸) / 10000 车逻闸,
               SUM(车逻) / 10000 车逻,
               SUM(南关) / 10000 南关,
               SUM(头闸) / 10000 头闸,
               SUM(周山洞) / 10000 周山洞,
               SUM(界首小闸) / 10000 界首小闸,
               SUM(子英闸) / 10000 子英闸,
               SUM(周山) / 10000 周山
          FROM 
          (
          SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILY
                  FROM WM_TD_WATER_DAILY T
                  LEFT JOIN BI_TB_MONITOR_BI B
                    ON T.MONITOR_ID = B.ID
                 WHERE T.REGION_ID = '6-100-018'
                   AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') AND
                       TO_DATE('2018-12', 'yyyy-MM')
          )
        PIVOT(SUM(WATER_DAILY)
           FOR SHORT_NAME IN('车逻洞' 车逻洞,
                            '车逻闸' 车逻闸,
                            '车逻' 车逻,
                            '南关' 南关,
                            '头闸' 头闸,
                            '周山洞' 周山洞,
                            '界首小闸' 界首小闸,
                            '子英闸' 子英闸,
                            '周山' 周山))
         GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),
                  TO_CHAR(MONITOR_DATE, 'mm'),
                  DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),
                         0,
                         '1旬' ，1,
                         '2旬',
                         '3旬')
         ORDER BY MON
  )
 GROUP BY ROLLUP(MON, DATETIME)

SQL分解 

第一层SQL:

只做最基本的查询,并把条件都写好

 SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILY
                  FROM WM_TD_WATER_DAILY T
                  LEFT JOIN BI_TB_MONITOR_BI B
                    ON T.MONITOR_ID = B.ID
                 WHERE T.REGION_ID = '6-100-018'
                   AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') AND
                       TO_DATE('2018-12', 'yyyy-MM')  

查询结果如下(只插入了两个站点的数据<车逻洞>,<车逻闸>): 

 

第二层SQL:

用decode(trunc((to_char(monitor_date,'dd')-1)/10),0,'1旬'，1,'2旬','3旬') 把旬分组,

行转列用了 PIVOT (sum(water_daily) for short_name in ('车逻洞' 车逻洞, '车逻闸' 车逻闸,'车逻' 车逻,
 '南关' 南关,'头闸' 头闸,'周山洞' 周山洞,'界首小闸' 界首小闸,'子英闸' 子英闸,'周山' 周山))

每个sum后除以10000是业务需求

SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,
               TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,
               TO_CHAR(MONITOR_DATE, 'mm') || '月' ||
               DECODE(TRUNC((TO_CHAR(MONITOR_DATE,'dd') - 1) / 10), 0, '1旬' ，1, '2旬', '3旬')DATETIME,
               SUM(车逻洞) / 10000 车逻洞,
               SUM(车逻闸) / 10000 车逻闸,
               SUM(车逻) / 10000 车逻,
               SUM(南关) / 10000 南关,
               SUM(头闸) / 10000 头闸,
               SUM(周山洞) / 10000 周山洞,
               SUM(界首小闸) / 10000 界首小闸,
               SUM(子英闸) / 10000 子英闸,
               SUM(周山) / 10000 周山
          FROM 
          (
          SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILY
                  FROM WM_TD_WATER_DAILY T
                  LEFT JOIN BI_TB_MONITOR_BI B
                    ON T.MONITOR_ID = B.ID
                 WHERE T.REGION_ID = '6-100-018'
                   AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') AND
                       TO_DATE('2018-12', 'yyyy-MM')
          )
        PIVOT(SUM(WATER_DAILY)
           FOR SHORT_NAME IN('车逻洞' 车逻洞,
                            '车逻闸' 车逻闸,
                            '车逻' 车逻,
                            '南关' 南关,
                            '头闸' 头闸,
                            '周山洞' 周山洞,
                            '界首小闸' 界首小闸,
                            '子英闸' 子英闸,
                            '周山' 周山))
         GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),
                  TO_CHAR(MONITOR_DATE, 'mm'),
                  DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),
                         0,
                         '1旬' ，1,
                         '2旬',
                         '3旬')
         ORDER BY MON

查询结果如下:

最后一层SQL:

用了 GROUP BY ROLLUP (MON,DATETIME)  按MON,DATETIME分组合计

SELECT CASE
         WHEN MON IS NULL AND DATETIME IS NULL THEN
          '累计水量'
         WHEN MON IS NOT NULL AND DATETIME IS NULL THEN
          '小计'
         ELSE
          CASE
            WHEN INSTR(DATETIME, '1旬') > 0 THEN
             REPLACE(DATETIME, '1旬', '上旬')
            WHEN INSTR(DATETIME, '2旬') > 0 THEN
             REPLACE(DATETIME, '2旬', '中旬')
            WHEN INSTR(DATETIME, '3旬') > 0 THEN
             REPLACE(DATETIME, '3旬', '下旬')
          END
       END DATETIME,
       SUM(NVL(车逻洞, 0)) 车逻洞,
       SUM(NVL(车逻闸, 0)) 车逻闸,
       SUM(NVL(车逻, 0)) 车逻,
       SUM(NVL(南关, 0)) 南关,
       SUM(NVL(头闸, 0)) 头闸,
       SUM(NVL(周山洞, 0)) 周山洞,
       SUM(NVL(界首小闸, 0)) 界首小闸,
       SUM(NVL(子英闸, 0)) 子英闸,
       SUM(NVL(周山, 0)) 周山,
       SUM(NVL(车逻洞, 0)) + SUM(NVL(车逻闸, 0)) + SUM(NVL(南关, 0)) +
       SUM(NVL(头闸, 0)) + SUM(NVL(周山洞, 0)) + SUM(NVL(界首小闸, 0)) +
       SUM(NVL(子英闸, 0)) + SUM(NVL(周山, 0)) 合计
  FROM 
  (
  SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,
               TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,
               TO_CHAR(MONITOR_DATE, 'mm') || '月' ||
               DECODE(TRUNC((TO_CHAR(MONITOR_DATE,'dd') - 1) / 10), 0, '1旬' ，1, '2旬', '3旬')DATETIME,
               SUM(车逻洞) / 10000 车逻洞,
               SUM(车逻闸) / 10000 车逻闸,
               SUM(车逻) / 10000 车逻,
               SUM(南关) / 10000 南关,
               SUM(头闸) / 10000 头闸,
               SUM(周山洞) / 10000 周山洞,
               SUM(界首小闸) / 10000 界首小闸,
               SUM(子英闸) / 10000 子英闸,
               SUM(周山) / 10000 周山
          FROM 
          (
          SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILY
                  FROM WM_TD_WATER_DAILY T
                  LEFT JOIN BI_TB_MONITOR_BI B
                    ON T.MONITOR_ID = B.ID
                 WHERE T.REGION_ID = '6-100-018'
                   AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') AND
                       TO_DATE('2018-12', 'yyyy-MM')
          )
        PIVOT(SUM(WATER_DAILY)
           FOR SHORT_NAME IN('车逻洞' 车逻洞,
                            '车逻闸' 车逻闸,
                            '车逻' 车逻,
                            '南关' 南关,
                            '头闸' 头闸,
                            '周山洞' 周山洞,
                            '界首小闸' 界首小闸,
                            '子英闸' 子英闸,
                            '周山' 周山))
         GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),
                  TO_CHAR(MONITOR_DATE, 'mm'),
                  DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),
                         0,
                         '1旬' ，1,
                         '2旬',
                         '3旬')
         ORDER BY MON
  )
 GROUP BY ROLLUP(MON, DATETIME)

查询结果如下:

select后的不加case when的效果如下

mon用于第二层的排序 省略掉了

总结

这段SQL中用到了以下函数:

TO_DATE(),TO_CHAR(),BETWEEN,TRUNC(),DECODE(),NVL(),SUM(),PIVOT(  FOR  ),CASE  WHEN  THEN  ELSE  END,ROLLUP()

欢迎指正并优化SQL!