Oracle 分组统计，按照天、月份周和自然周、月、季度和年

1.按天

select to_char(t.STARTDATE+15/24, 'YYYY-MM-DD') as 天,sum(1) as 数量
from HOLIDAY t
group by to_char(t.STARTDATE+15/24, 'YYYY-MM-DD') --
ORDER by 天 NULLS  LAST;

select trunc(t.STARTDATE, 'DD') as 天,sum(1) as 数量
from HOLIDAY t
group by trunc(t.STARTDATE, 'DD') --
ORDER by 天 NULLS  LAST;

2.按周

select to_char(next_day(t.STARTDATE+15/24 - 7,2),'YYYY-MM-DD') AS 周,sum(1) as 数量 
from HOLIDAY t 
group by to_char(next_day(t.STARTDATE+15/24 - 7,2),'YYYY-MM-DD')ORDER BY 周;

-- 按自然周统计 
select to_char(t.STARTDATE,'iw') AS 周,sum(1) as 数量
from HOLIDAY t

group by to_char(t.STARTDATE,'iw')
ORDER BY 周;

3.按自然月

select to_char(t.STARTDATE,'YYYY-MM') as 月份,sum(1) as 数量
from HOLIDAY t

GROUP BY
	to_char(t.STARTDATE,'YYYY-MM')
ORDER BY 月份;

4.按季度

select to_char(t.STARTDATE,'q') 季度,sum(1) as 数量
from HOLIDAY t
group by to_char(t.STARTDATE,'q')
ORDER BY 季度 NULLS  LAST;

5.按年

select to_char(t.STARTDATE,'yyyy') AS 年度,sum(1) as 数量
from HOLIDAY t
group by to_char(t.STARTDATE,'yyyy')
ORDER BY 年度;