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

[qdyu00]

原表
TABLE 1


欲求20141204 一天中各小时的平均量
跨天的也需要平均到各天的时间段中。
date time amount
201412 01 ?
201412 02 ?
201412 03 ?
201412 04 ?


求大神

[bw555]

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

[qdyu00]

引用 1 楼 bw555 的回复:

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

大神，没看到懂，能详细介绍一下吗？请赐教

[qdyu00]

引用 1 楼 bw555 的回复:

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

大神，我要的是 每天，到小时的汇总数据，可以生成一个到小时的时间序列吗？

[bw555]

思路一样，调整下就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