Oracle 按不同时间分组统计
1、按年
select to_char(record_date,'yyyy'), sum(col_8) as total_money from table_name where group by to_char(record_date,'yyyy')
2、按月
select to_char(record_date,'yyyy-mm'), sum(col_8) as total_money from table_name where group by to_char(record_date,'yyyy-mm')
3、按季
select to_char(record_date,'yyyy-Q'), sum(col_8) as total_money from table_name where group by to_char(record_date,'yyyy-Q')
4、按周
select to_char(record_date,'yyyy-IW'), sum(col_8) as total_money from table_name where group by to_char(record_date,'yyyy-IW')
5、按小时
select to_char(record_date,'yyyy-mm-dd hh24'), sum(col_8) as total_money from table_name where group by to_char(record_date,'yyyy-mm-dd hh24')
6、按分钟
select to_char(record_date,'yyyy-mm-dd hh24:mi'), sum(col_8) as total_money from table_name where group by to_char(record_date,'yyyy-mm-dd hh24:mi') SELECT trunc(sysdate-1) + (ROWNUM - 1) / 24 / 60 AS STAT_TIME FROM DUAL d CONNECT BY ROWNUM <= 1440 +(trunc(sysdate, 'MI') - (trunc(sysdate))) * 24 * 60
7、按5分钟
SELECT trunc(sysdate) + (ROWNUM - 1) / 24/12 AS STAT_TIME FROM DUAL CONNECT BY ROWNUM <=(trunc(sysdate, 'MI') - (trunc(sysdate))) * 24 * 12
按5分钟统计,中间没数据的时间会缺少 select to_char(j.record_date,'yyyymmdd') as st_date , sum(j.col_8*j.col_9*j.col_10) as mer_value , to_char(j.record_date,'hh24') as STAT_HOUR , floor(to_char(j.record_date,'mi')/5) as min_flag , '昨日' as report_name from c , j where c.col_4 = j.col_4 and c.col_5 = j.col_5 and c.col_6 = j.col_6 and j.actiontype = 217 and j.record_date between trunc(sysdate-1) and trunc(sysdate) group by to_char(j.record_date,'yyyymmdd'), to_char(j.record_date,'hh24'), floor(to_char(j.record_date,'mi')/5) ORDER BY st_date, STAT_HOUR, min_flag
select STAT_TIME tdate, 'test' app_id, goods_type, --c.col_4 as goods_id, --c.col_5 as goods_currency_type --c.col_6 as from ( SELECT trunc(sysdate-1) + (ROWNUM - 1) / 24 / 12 AS STAT_TIME FROM DUAL d CONNECT BY ROWNUM <= 288 +(trunc(sysdate, 'MI') - (trunc(sysdate))) * 24 * 12 + 1 ), ( select t.goods_type,t.goods_id,t.goods_currency_type from mid_minutes t where tdate >= trunc(sysdate) -1 and app_id = 'test' group by t.goods_type,t.goods_id,t.goods_currency_type ) c
8、按十分钟
SELECT trunc(sysdate) + (ROWNUM - 1) / 24/6 AS STAT_TIME FROM DUAL CONNECT BY ROWNUM <=(trunc(sysdate, 'MI') - (trunc(sysdate))) * 24 * 6
按5分钟统计,把时间转换成分钟准点函数
create or replace function trunc_minute(v_date date) return date as begin return to_number(trunc(to_char(v_date, 'mi')/5))*5/(24*60) + trunc(v_date, 'hh24'); end;
9、按半小时
select to_date(to_char(j.tdate, 'yyyy-mm-dd hh24:') || case when to_char(j.tdate, 'mi') < '30' then '00' else '30' end, 'yyyy-mm-dd hh24:mi') as tdate, j.goods_id, sum(j.buy_count) as buy_count, sum(j.total_price) as total_price from tmp_goods_mis j where j.tdate >= trunc(sysdate) - 1 group by j.goods_id to_char(j.tdate, 'yyyy-mm-dd hh24:') || case when to_char(j.tdate, 'mi') < '30' then '00' else '30' end
posted on 2020-01-05 20:01 morgan363 阅读(1084) 评论(0) 编辑 收藏 举报