业务需求:查询在开始时间和结束时间内每天的专题流量。专题可能会存在某天没人访问的情况,这时候查出来的数据不是连续的。
想法是先查询这个时间段连续的时间数据,再关联业务。
SELECT
A .RIQI,
“NVL” (SUM(t1.iss_visitnum), ‘0’) AS “num”
FROM
(
SELECT
“TO_CHAR” (SHOW_TIME, ‘yyyy-mm-dd’) AS “RIQI”
FROM
(
SELECT
TRUNC (
TO_DATE (‘2019-08-01’, ‘yyyy-mm-dd’),
‘dd’
) + ROWNUM - 1 AS SHOW_TIME
FROM
DUAL CONNECT BY ROWNUM <= TRUNC (
TO_DATE (‘2019-08-31’, ‘yyyy-mm-dd’),
‘dd’
) - TRUNC (
TO_DATE (‘2019-08-01’, ‘yyyy-mm-dd’),
‘dd’
) + 1
)
) A
LEFT JOIN T_ISU_ISSUEINFO t1 ON TO_CHAR (
t1.CREATE_DATE,
‘yyyy-mm-dd’
) = A .RIQI
GROUP BY
A .RIQI
ORDER BY
A .RIQI