如何使ORACLE query返回每半小时的值？
如何使ORACLE query返回每半小时的值？
提问于 2018-07-23 04:48:01
当我运行这个query时，我得到了显示记录时间的数据。我想在没有数据的时候看到零。
SELECT x.TIMESLOT AS "FROM", SUM(x.VALUE) AS x.VALUE
FROM
(SELECT TO_CHAR (dd.create_dt, 'HH24') || DECODE (SIGN (TO_CHAR (dd.create_dt, 'MI') - 30),-1, '01','31') AS TIMESLOT, COUNT(*) AS VALUE FROM deposits LEFT JOIN d_user u ON u.ID = dd.USER_ID WHERE dd.create_dt BETWEEN :p_from_dt AND :p_to_dt AND dd.ACTIVE_IND = 'Y' GROUP BY TO_CHAR (dd.create_dt, 'HH24') || DECODE (SIGN (TO_CHAR (dd.create_dt, 'MI') - 30),-1, '01','31')
UNION ALL
SELECT TO_CHAR (wd.create_dt, 'HH24') || DECODE (SIGN (TO_CHAR (wd.create_dt, 'MI') - 30), -1, '01', '31') AS TIMESLOT, COUNT(*) AS VALUE
FROM wages wd
WHERE wd.create_dt BETWEEN :p_from_dt AND :p_to_dt
GROUP BY TO_CHAR (wd.create_dt, 'HH24') || DECODE (SIGN (TO_CHAR (wd.create_dt, 'MI') - 30), -1, '01','31')) x
GROUP BY x.TIMESLOT
ORDER BY x.TIMESLOTOUTPUT
FROM VALUE
0731 9
1101 7
1331 8
1401 9
1431 3
1631 1
我正试着把它放出来
OUTPUT
FROM VALUE
0601 0
0631 0
0701 0
0731 9
0801 0
0831 0
0901 0
0931 0
1001 0
1031 0
1101 7
1131 0
1201 0
1231 0
1301 0
1331 8
1401 9
1431 3
1501 0
1531 0
1601 0
1631 1
1701 0
1731 0
1801年0
1831 0
1901年0
1931年0
2001年0
2031 0
2101 0
2131 0
2201 0
回答 1
Stack Overflow用户
发布于 2018-07-23 14:06:35
可以使用以下query生成所有半小时间隔的列表：
SELECT TO_CHAR( TO_DATE( '0601', 'HH24MI' ) + (LEVEL - 1)/48, 'HH24MI' ) AS timeslot
FROM DUAL
CONNECT BY
LEVEL <= 33;然后，可以将其添加到查询中LEFT OUTER JOIN如下所示：
WITH half_hours AS (
SELECT TO_CHAR( TO_DATE( '0601', 'HH24MI' ) + (LEVEL - 1)/48, 'HH24MI' ) AS timeslot
FROM DUAL
CONNECT BY
LEVEL <= 33
), data AS (
<your_query>
)
SELECT h.timeslot,
COALESCE( d.value, 0 ) AS value
FROM half_hours h
LEFT OUTER JOIN data d
ON h.timeslot = d.timeslot;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100005625
复制相关文章
相似问题