开发过程中遇到的SQL优化

1. 需求描述
   对一些业务数据以报表的形式进行展示，在项目上线前统计数据不准确（忽略了节假日的统计）和查询速度慢。统计的是职员处理业务的执行率（默认发公告给你，你在正常工作日的一天之内处理就是及时处理）。
2. 原始版本
   日期格式的操作用的是to_char，导致索引没有命中，查询较慢。报表的页面引入多余的js，使得后台数据返回前台，前台加载数据的时候还要几秒。
3. 优化后的版本
   涉及到的日期格式用to_date，处理及时不及时考虑到节假日。

SELECT DECODE(TT.TASK_NUM_TOTAL, NULL, 0, TT.TASK_NUM_TOTAL) TASK_NUM_TOTAL,
       DECODE(TT.TASK_NUM_COMP, NULL, 0, TT.TASK_NUM_COMP) TASK_NUM_COMP,
       DECODE(TT.TASK_NUM_TIME, NULL, 0, TT.TASK_NUM_TIME) TASK_NUM_TIME,
       DECODE(TT.TASK_NUM_TOTAL,
              0,
              0,
              round((TT.TASK_NUM_TIME * 100) / TT.TASK_NUM_TOTAL, 2)) || '%' TASK_NUMBER_TIME_PERCENT,
       DECODE(TT.TASK_NUM_TOTAL,
              0,
              0,
              round((TT.TASK_NUM_TIME * 100) / TT.TASK_NUM_TOTAL, 2)) TASK_NUMBER_TIME_SCALE,
       TT.FULLNAME,
       TT.USERID
  FROM (SELECT DECODE(T.TASK_NUM_TOTAL, NULL, 0, T.TASK_NUM_TOTAL) TASK_NUM_TOTAL,
               DECODE(T.TASK_NUM_COMP, NULL, 0, T.TASK_NUM_COMP) TASK_NUM_COMP,
               DECODE(T.TASK_NUM_TIME, NULL, 0, T.TASK_NUM_TIME) TASK_NUM_TIME,
               V.FULLNAME,
               V.USERID
          FROM (SELECT A.TASK_NUM_TOTAL,
                       B.TASK_NUM_COMP,
                       C.TASK_NUM_TIME,
                       A.USERID
                  FROM (SELECT COUNT(O.TC_TASK_CONTENT_ID) TASK_NUM_TOTAL, O.TASK_HANDLE_PERSON USERID
                          FROM TC_TASK_CONTENT O
                          WHERE 1 = 1
                          AND O.TASK_CREATE_DATE >= TO_DATE(?, 'YYYY-MM-DD')
                          AND O.TASK_CREATE_DATE < TO_DATE(?, 'YYYY-MM-DD')+1 

                         GROUP BY O.TASK_HANDLE_PERSON) A,
                       (SELECT COUNT(O.TC_TASK_CONTENT_ID) TASK_NUM_COMP, O.TASK_HANDLE_PERSON USERID
                          FROM TC_TASK_CONTENT O
                         WHERE O.TASK_STATE = '2'
                          AND O.TASK_CREATE_DATE >= TO_DATE(?, 'YYYY-MM-DD')
                          AND O.TASK_CREATE_DATE < TO_DATE(?, 'YYYY-MM-DD')+1 

                         GROUP BY O.TASK_HANDLE_PERSON) B,
                       (SELECT COUNT(O.TC_TASK_CONTENT_ID) TASK_NUM_TIME, O.TASK_HANDLE_PERSON USERID
                          FROM TC_TASK_CONTENT O
                         WHERE O.TASK_STATE = '2'
                          AND O.TASK_CREATE_DATE >= TO_DATE(?, 'YYYY-MM-DD')
                          AND O.TASK_CREATE_DATE < TO_DATE(?, 'YYYY-MM-DD')+1 

                           AND TRUNC(O.ACTUAL_FINISH_DATE-O.TASK_CREATE_DATE)<=1
                         GROUP BY O.TASK_HANDLE_PERSON) C
                 WHERE A.USERID = B.USERID(+)
                   AND B.USERID = C.USERID(+)) T,
               USERS_ORGS UO,
               USER_ U,
               (SELECT ORGANIZATIONID
                  FROM ORGANIZATION_ T
                CONNECT BY PRIOR T.ORGANIZATIONID = T.PARENTORGANIZATIONID
                  START WITH ORGANIZATIONID = '"+MapUtil.getString(inputData, "ORGANIZATIONID")+"') ORG,
               V_USER_ORG V
         WHERE UO.ORGANIZATIONID = ORG.ORGANIZATIONID
           AND T.USERID(+) = U.USERID
           AND U.USERID = UO.USERID
           AND V.USERID = U.USERID
        UNION ALL
        SELECT SUM(DECODE(T.TASK_NUM_TOTAL, NULL, 0, T.TASK_NUM_TOTAL)) TASK_NUM_TOTAL,
               SUM(DECODE(T.TASK_NUM_COMP, NULL, 0, T.TASK_NUM_COMP)) TASK_NUM_COMP,
               SUM(DECODE(T.TASK_NUM_TIME, NULL, 0, T.TASK_NUM_TIME)) TASK_NUM_TIME,
               '合计' FULLNAME,
               111111 USERID
          FROM (SELECT A.TASK_NUM_TOTAL,
                       B.TASK_NUM_COMP,
                       C.TASK_NUM_TIME,
                       A.USERID
                  FROM (SELECT COUNT(O.TC_TASK_CONTENT_ID) TASK_NUM_TOTAL, O.TASK_HANDLE_PERSON USERID
                          FROM TC_TASK_CONTENT O
                          WHERE 1 = 1
                          AND O.TASK_CREATE_DATE >= TO_DATE(?, 'YYYY-MM-DD')
                          AND O.TASK_CREATE_DATE < TO_DATE(?, 'YYYY-MM-DD')+1 

                         GROUP BY O.TASK_HANDLE_PERSON) A,
                       (SELECT COUNT(O.TC_TASK_CONTENT_ID) TASK_NUM_COMP, O.TASK_HANDLE_PERSON USERID
                          FROM TC_TASK_CONTENT O
                         WHERE O.TASK_STATE = '2'
                          AND O.TASK_CREATE_DATE >= TO_DATE(?, 'YYYY-MM-DD')
                          AND O.TASK_CREATE_DATE < TO_DATE(?, 'YYYY-MM-DD')+1 

                         GROUP BY O.TASK_HANDLE_PERSON) B,
                       (SELECT COUNT(O.TC_TASK_CONTENT_ID) TASK_NUM_TIME, O.TASK_HANDLE_PERSON USERID
                          FROM TC_TASK_CONTENT O
                         WHERE O.TASK_STATE = '2'

                          AND O.TASK_CREATE_DATE >= TO_DATE(?, 'YYYY-MM-DD')
                          AND O.TASK_CREATE_DATE < TO_DATE(?, 'YYYY-MM-DD')+1 
                           AND TRUNC(O.ACTUAL_FINISH_DATE-O.TASK_CREATE_DATE)<=1
                         GROUP BY O.TASK_HANDLE_PERSON) C
                 WHERE A.USERID = B.USERID(+)
                   AND B.USERID = C.USERID(+)) T,
               USERS_ORGS UO,
               USER_ U,
               (SELECT ORGANIZATIONID
                  FROM ORGANIZATION_ T
                CONNECT BY PRIOR T.ORGANIZATIONID = T.PARENTORGANIZATIONID
                  START WITH ORGANIZATIONID = '"+MapUtil.getString(inputData, "ORGANIZATIONID")+"') ORG,
               V_USER_ORG V
         WHERE T.USERID(+) = U.USERID
           AND U.USERID = UO.USERID
           AND UO.ORGANIZATIONID = ORG.ORGANIZATIONID
           AND V.USERID = U.USERID) TT

1. 最终版本

WITH ALL_ORG AS
 (SELECT ORGANIZATIONID,
         DECODE(LEVEL, 2, ORGANIZATIONID, 3, T.PARENTORGANIZATIONID) THIRDID
    FROM ORGANIZATION_ T
  CONNECT BY PRIOR T.ORGANIZATIONID = T.PARENTORGANIZATIONID
   START WITH ORGANIZATIONID = '5272'),
ORG_SUM AS
 (SELECT DECODE(T.TASK_NUM_TOTAL, NULL, 0, T.TASK_NUM_TOTAL) TASK_NUM_TOTAL,
         DECODE(T.TASK_NUM_COMP, NULL, 0, T.TASK_NUM_COMP) TASK_NUM_COMP,
         DECODE(T.TASK_NUM_TIME, NULL, 0, T.TASK_NUM_TIME) TASK_NUM_TIME,
         (SELECT OZ.NAME
            FROM ORGANIZATION_ OZ
           WHERE OZ.ORGANIZATIONID = T.THIRDID) FULLNAME,
         T.THIRDID
    FROM (SELECT A.TASK_NUM_TOTAL,
                A.TASK_NUM_COMP,
                 A.TASK_NUM_TIME,
                 A.THIRDID
            FROM (SELECT SUM(1) TASK_NUM_TOTAL,
                               SUM(CASE
                                     WHEN TRUNC(O.ACTUAL_FINISH_DATE -
                                                O.TASK_CREATE_DATE) <= 1 THEN
                                      1
                                     ELSE
                                      0
                                   END) TASK_NUM_TIME,
                               SUM(DECODE(TASK_STATE, '2', 1, 0)) TASK_NUM_COMP,
                               O.TASK_HANDLE_PERSON USERID, TT.THIRDID
                          FROM TC_TASK_CONTENT O,USERS_ORGS UO,ALL_ORG TT
                         WHERE O.TASK_HANDLE_PERSON = UO.USERID
                     AND UO.ORGANIZATIONID = TT.ORGANIZATIONID
                     AND TT.THIRDID IS NOT NULL
                           AND O.TASK_CREATE_DATE >= TO_DATE('2017-01-01', 'YYYY-MM-DD')
                           AND O.TASK_CREATE_DATE <
                               TO_DATE('2017-12-31', 'YYYY-MM-DD') + 1
                         GROUP BY TT.THIRDID) A) T),
OGR_ALL AS
 (SELECT S.*
    FROM ORG_SUM S)
SELECT DECODE(TT.TASK_NUM_TOTAL, NULL, 0, TT.TASK_NUM_TOTAL) TASK_NUM_TOTAL,
       DECODE(TT.TASK_NUM_COMP, NULL, 0, TT.TASK_NUM_COMP) TASK_NUM_COMP,
       DECODE(TT.TASK_NUM_TIME, NULL, 0, TT.TASK_NUM_TIME) TASK_NUM_TIME,
       DECODE(TT.TASK_NUM_TOTAL,
              0,
              0,
              ROUND((TT.TASK_NUM_TIME * 100) / TT.TASK_NUM_TOTAL, 2)) || '%' TASK_NUMBER_TIME_PERCENT,
       DECODE(TT.TASK_NUM_TOTAL,
              0,
              0,
              ROUND((TT.TASK_NUM_TIME * 100) / TT.TASK_NUM_TOTAL, 2)) TASK_NUMBER_TIME_SCALE,
       TT.FULLNAME,
       TT.THIRDID
  FROM OGR_ALL TT

附带查询的结果图：

5. 讨论
判断某一天是否属于上班时间？知道某一天，这一天的5个工作日是几月几号？现在对于工作日的判断是将一年的日期放进表里面维护，判断5个工作后是几月几号用的是递归。感觉这样的做法很繁琐而且效率很慢，但是没有什么其他的好办法。