欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle按年、月、日、周等统计数据

程序员文章站 2024-03-25 21:27:46
...

在很多项目中都会有数据统计的功能,如按照年、月、周、日统计某个用户提交的数量;或者直接统计指定年、月、周或者日新增的数量。最近我接触的一个项目,客户就要求根据月和周统计每个单位提价提交的数量。

按年统计

select 
  o.id,
  o.name,
  to_char(a.create_date,'yyyy'),
  count(*)
from news a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id,o.name,to_char(a.create_date,'yyyy')

这里就是将创建时间格式化为年形式,按照单位id分组,查询出提交的数量。

Oracle按年、月、日、周等统计数据

按月统计

这里直接将上面的的日期格式改为yyyy-MM形式即可。

select 
  o.id,
  o.name,
  to_char(a.create_date,'yyyy-MM'),
  count(*)
from news a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id,o.name,to_char(a.create_date,'yyyy-MM')

Oracle按年、月、日、周等统计数据

按周统计

按周统计可以显示两种效果,一种是显示出该日期的周一的日期,也就是自然周的日期,另外一种是按周自然周统计。

按自然周统计

select 
  o.id,
  o.name,
  to_char(a.create_date,'ww'),
  count(*)
from t_dzjg_news a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id,o.name,to_char(a.create_date,'ww')

这里的ww是获取该日期的在年中的第几周。

Oracle按年、月、日、周等统计数据

按自然周的日期统计

这里处理起来可以会比较繁琐,首先要获取日期的所在周的周一的日期,然后根据周一日期分组查询:

---获取指定日期的周一的日期
select trunc(sysdate,'ww') from dual;

select to_char(trunc(sysdate,'ww'),'yyyy-MM-dd') from dual;

---将创建时间格式为周一的形式,再去查找
select 
  o.id,
  to_char(trunc(a.create_date,'ww'),'yyyy-MM-dd') AS 周一,
  count(*)
from t_dzjg_news a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id,to_char(trunc(a.create_date,'ww'),'yyyy-MM-dd')

这里使用到一个函数trunc():该函数类似截取函数,按指定的格式截取输入的数据。这里将时间截取为显示周的形式。

按季度统计

在oracle中q为时间的季度如:

select to_char(sysdate,'q') from dual;--取得当前时间的季度

所以上面的sql可以改为:

select 
  o.id,
  to_char(a.create_date,'q') AS 季度,
  count(*)
from t_dzjg_news a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id, to_char(a.create_date,'q')

按天统计

这个就几乎没啥好说的了:

select 
  o.id,
  to_char(a.create_date,'yyyy-MM-dd') AS 日期,
  count(*)
from t_dzjg_news a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id, to_char(a.create_date,'yyyy-MM-dd')

总结

对于数据库数据的统计,这些还只是很简单基础。这几天通过写那些统计功能,虽然写的头大,但是对数据库知识越来越深。对于写复杂的sql语句,要细分问题,一步步的查询,最后将一步步分析的简单的查询组合成想要的复杂查询,可以说是从小到大,切忌从大到小。

最后

关于Oracle数据库中对时间常用的操作可以查看我之前的一篇博客:

Oeacle常见的日期处理