WITH T AS (select id,value,TO_CHAR(collect_date||' '||collect_time,'YYYY-MM-DD HH24:MI') D FROM TABLE)
SELECT ID,VALUE,TO_CHAR(D,'YYYY-MM-DD') collect_date,TO_CHAR(D,'HH24:MI') collect_time,
FROM (
SELECT ID,SUM(VALUE) VALUE,TRUNC(D)+1-TRUNC((TRUNC(D)+1-D)*24*2)/2/24 D
FROM T
GROUP BY ID,TRUNC(D)+1-TRUNC((TRUNC(D)+1-D)*24*2)/2/24)
SELECT ID,VALUE,TO_CHAR(D,'YYYY-MM-DD') collect_date,TO_CHAR(D,'HH24:MI') collect_time,
FROM (
SELECT ID,SUM(VALUE) VALUE,TRUNC(D)+1-TRUNC((TRUNC(D)+1-D)*24*2)/2/24 D
FROM T
GROUP BY ID,TRUNC(D)+1-TRUNC((TRUNC(D)+1-D)*24*2)/2/24)
直接计算 TRUNC((collect_time - TO_DATE('00:00','HH24:MI')) *24*2) 就是分组时段了,为什么还要先和 collect_date 拼起来?
(SELECT SUM(b.value)
FROM t_1 b
WHERE a.id = b.id
AND to_date(b.collect_date || ' ' || b.collect_time, 'yyyy-mm-dd hh24:mi') > to_date(a.collect_date || ' ' || a.collect_time, 'yyyy-mm-dd hh24:mi') - 30 / 1440
AND to_date(b.collect_date || ' ' || b.collect_time, 'yyyy-mm-dd hh24:mi') <= to_date(a.collect_date || ' ' || a.collect_time, 'yyyy-mm-dd hh24:mi')) VALUE,
collect_date,
collect_time
FROM t_1 a
WHERE to_char(to_date(collect_date || ' ' || collect_time, 'yyyy-mm-dd hh24:mi'), 'mi') IN ('00', '30')
ORDER BY to_date(collect_date || ' ' || collect_time, 'yyyy-mm-dd hh24:mi')