求思路，求平均到各天的时间段

Question

本帖最后由 qdyu00 于 2014-12-21 16:03:23 编辑

Answer 1

with T AS (
SELECT TO_DATE('20141201','YYYYMMDD')+ROWNUM-1 D FROM DUAL
CONNECT BY  TO_DATE('20141201','YYYYMMDD')+ROWNUM-1<= TO_DATE('20141231','YYYYMMDD')
)
SELECT TO_CHAR(T.D,'YYYYMM') DATE,TO_CHAR(T.D,'DD') TIME,
SUM(
    A.amount/(A.ENDTIME-A.STARTTIME)*(LEAST(T.D+1,A.ENDTIME)-GREATEST(T.D,A.STARTTIME))
) AS amount
FROM T LEFT JOIN TABLE1 A
ON T.D>=TRUNC(A.STARTTIME) AND T.D<TRUNC(A.ENDTIME)+1
GROUP BY T.D

Answer 2

思路一样，调整下就OK了
with T AS (
SELECT TO_DATE('2014120100','YYYYMMDDHH24')+(ROWNUM-1)/24 D FROM DUAL
CONNECT BY  TO_DATE('2014120100','YYYYMMDDHH24')+(ROWNUM-1)/24<= TO_DATE('2014120123','YYYYMMDDHH24')
)
SELECT TO_CHAR(T.D,'YYYYMMDD') DATE,TO_CHAR(T.D,'HH24') TIME,
SUM(
    A.amount/(A.ENDTIME-A.STARTTIME)*(LEAST(T.D+1/24,A.ENDTIME)-GREATEST(T.D,A.STARTTIME))
) AS amount
FROM T LEFT JOIN TABLE1 A
ON T.D>=TRUNC(A.STARTTIME) AND T.D<TRUNC(A.ENDTIME)+1/24
GROUP BY T.D