Oracle数据库按月统计（候，旬，月，季，年）

本文链接：https://blog.youkuaiyun.com/qq_16645423/article/details/79471335

本文提供了在Oracle数据库中按不同时间维度（月、年、旬、候、季）进行数据统计的具体SQL语句实例，方便开发者根据不同业务需求快速实现时间粒度的数据聚合。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 Oracle数据库按月统计

SELECT TO_CHAR(ds.date_time,'YYYY-MM'),count(*)
FROM tab_name   ds
GROUP BY TO_CHAR(ds.date_time,'YYYY-MM')  
ORDER BY TO_CHAR(ds.date_time,'YYYY-MM')

 Oracle数据库按年统计

SELECT TO_CHAR(ds.date_time,'YYYY'),count(*)
FROM tab_name   ds
GROUP BY TO_CHAR(ds.date_time,'YYYY')  
ORDER BY TO_CHAR(ds.date_time,'YYYY') ASC NULLS  LAST

 Oracle数据库按旬统计

--查询上旬
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'上旬',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='01' and TO_CHAR(ds.date_time,'DD')< '11'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计中旬
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'中旬',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='11' and TO_CHAR(ds.date_time,'DD')< '21'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询条件下旬
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'下旬',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='21'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM')

 Oracle数据库按候统计（
 气象中,五天为1候,1年72候.1个月为6候.如果1个月为31天,则最后1候为6天.
 ）

--查询1候
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'1',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='01' and TO_CHAR(ds.date_time,'DD')< '06'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计1候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'2',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='06' and TO_CHAR(ds.date_time,'DD')< '11'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计3候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'3',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='11' and TO_CHAR(ds.date_time,'DD')< '16'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计4候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'4',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='16' and TO_CHAR(ds.date_time,'DD')< '21'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计5候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'5',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='21' and TO_CHAR(ds.date_time,'DD')< '26'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询条件6候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'6',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='26'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM')

 Oracle数据库按季统计

--查询春季
SELECT TO_CHAR(ds.date_time,'YYYY'),'春季',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'MM')>='03' and TO_CHAR(ds.date_time,'MM')< '06'
GROUP BY TO_CHAR(ds.date_time,'YYYY') 
--查询统计夏季
union
SELECT TO_CHAR(ds.date_time,'YYYY'),'夏季',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'MM')>='06' and TO_CHAR(ds.date_time,'MM')< '09'
GROUP BY TO_CHAR(ds.date_time,'YYYY') 
--查询统计秋季
union
SELECT TO_CHAR(ds.date_time,'YYYY'),'秋季',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'MM')>='09' and TO_CHAR(ds.date_time,'MM')< '12'
GROUP BY TO_CHAR(ds.date_time,'YYYY') 
--查询统计冬季
union
SELECT TO_CHAR(ds.date_time,'YYYY'),'冬季',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'MM')>='12' or TO_CHAR(ds.date_time,'MM')< '03'
GROUP BY TO_CHAR(ds.date_time,'YYYY')

http://blog.youkuaiyun.com/qq_16645423