开发过程中遇到的SQL优化

需求描述 对一些业务数据以报表的形式进行展示，在项目上线前统计数据不准确（忽略了节假日的统计）和查询速度慢。统计的是职员处理业务的执行率（默认发公告给你，你在正常工作日的一天之内处理就是及时处理）。原始版本 日期格式的操作用的是to_char，导致索引没有命中，查询较慢。报表的页面引入多余的js，使得后台数据返回前台，前台加载数据的时候还要几秒。优化后的版本 涉及到的日期格式用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 最终版本 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个工作后是几月几号用的是递归。感觉这样的做法很繁琐而且效率很慢，但是没有什么其他的好办法。