oracle统计几组,oracle根据条件统计各组记录数量

1. 根据具体条件统计这批数据落到每组的数量

select to_char(TIMESTAMP, 'yyyy-mm-dd') column1, column2, column3,

count(CASE WHEN DURATION<30*60 THEN 1 END) less30,

count(CASE WHEN DURATION>=30*60 and DURATION<60*60*1000 THEN 1 END) more30less60,

count(CASE WHEN DURATION>=60*60 and DURATION<120*60*1000 THEN 1 END) more60less120,

count(CASE WHEN DURATION>=120*60 and DURATION<360*60*1000 THEN 1 END) more120less360,

count(CASE WHEN DURATION>=360*60 THEN 1 END) more360

from user_table1

where to_char(TIMESTAMP, 'yyyy-mm-dd') in ('2019-09-16', '2019-09-15')

group by to_char(TIMESTAMP, 'yyyy-mm-dd'), column2, column3;

2. 将上面的数量merge into到另一个新的table(存在即跟新，不存在即插入)

merge into user_table2 t1

using (select to_char(TIMESTAMP, 'yyyy-mm-dd') column1,

column2,

column3,

count(CASE WHEN DURATION < 30 * 60 THEN 1 END) less30,

count(CASE WHEN DURATION >= 30 * 60 and DURATION < 60 * 60 * 1000 THEN 1 END) more30less60,

count(CASE WHEN DURATION >= 60 * 60 and DURATION < 120 * 60 * 1000 THEN 1 END) more60less120,

count(CASE WHEN DURATION >= 120 * 60 and DURATION < 360 * 60 * 1000 THEN 1 END) more120less360,

count(CASE WHEN DURATION >= 360 * 60 THEN 1 END) more360

from user_table1

where to_char(TIMESTAMP, 'yyyy-mm-dd') in ('2019-09-16', '2019-09-15')

group by to_char(TIMESTAMP, 'yyyy-mm-dd'), column2, column3) t2

on (t1.column1 = t2.column1 and t1.column2 = t2.column2 and t1.column3 = t2.column3)

WHEN MATCHED THEN

update set t1.less30 = t2.less30,

t1.more30less60 = t2.more30less60,

t1.more60less120 = t2.more60less120,

t1.more120less360 = t2.more120less360,

t1.more360 = t2.more360

WHEN NOT MATCHED THEN

INSERT (t1.column1, t1.column2, t1.column3, t1.LESS30, t1.MORE30LESS60, t1.MORE60LESS120, t1.MORE120LESS360, t1.MORE360)

values (t1.column1, t2.column2, t2.column3, t2.less30, t2.more30less60, t2.more60less120, t2.more120less360, t2.more360);