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分组,查询出提交的数量。
按月统计
这里直接将上面的的日期格式改为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')
按周统计
按周统计可以显示两种效果,一种是显示出该日期的周一的日期,也就是自然周的日期,另外一种是按周自然周统计。
按自然周统计
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
是获取该日期的在年中的第几周。
按自然周的日期统计
这里处理起来可以会比较繁琐,首先要获取日期的所在周的周一的日期,然后根据周一日期分组查询:
---获取指定日期的周一的日期
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数据库中对时间常用的操作可以查看我之前的一篇博客:
上一篇: Matlab--用最小二乘法确定指数函数的系数(涉及牛顿法解非线性方程组)
下一篇: 大数定律