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); |
