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

做报表统计时会经常用到 周，月，季度，年进行分组统计，所以结合网络搜索推荐的sql，总结如下：

-- 按天统计
select to_char(t.CREATED+15/24, "YYYY-MM-DD") as 天,sum(1) as 数量
from TB_EXT_TRADE t
WHERE
	t.TID LIKE "SC%" OR t.TID LIKE "WSC%"
group by to_char(t.CREATED+15/24, "YYYY-MM-DD") --trunc(t.CREATED, "DD")
ORDER by 天 NULLS  LAST;

-- 按自然周的日期统计 
select to_char(next_day(t.CREATED+15/24 - 7,2),"YYYY-MM-DD") AS 周,sum(1) as 数量
from TB_EXT_TRADE t
WHERE
	t.TID LIKE "SC%" OR t.TID LIKE "WSC%"
group by to_char(next_day(t.CREATED+15/24 - 7,2),"YYYY-MM-DD")
ORDER BY 周;

-- 按自然周统计 
select to_char(t.CREATED,"iw") AS 周,sum(1) as 数量
from TB_EXT_TRADE t
WHERE
	t.TID LIKE "SC%" OR t.TID LIKE "WSC%"
group by to_char(t.CREATED,"iw")
ORDER BY 周;

-- 按自然月统计 
select to_char(t.CREATED,"YYYY-MM") as 月份,sum(1) as 数量
from TB_EXT_TRADE t
WHERE
	t.TID LIKE "SC%" OR t.TID LIKE "WSC%"
GROUP BY
	to_char(t.CREATED,"YYYY-MM") -- to_char(t.CREATED+15/24,"yyyy-mm") 不大准确
ORDER BY 月份;

-- 按季统计 
select to_char(t.CREATED,"q") 季度,sum(1) as 数量
from TB_EXT_TRADE t
WHERE
	t.TID LIKE "SC%" OR t.TID LIKE "WSC%"
group by to_char(t.CREATED,"q")
ORDER BY 季度 NULLS  LAST;

--按年统计 
select to_char(t.CREATED,"yyyy") AS 年度,sum(1) as 数量
from TB_EXT_TRADE t
WHERE
	t.TID LIKE "SC%" OR t.TID LIKE "WSC%"
group by to_char(t.CREATED,"yyyy")
ORDER BY 年度;