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