ORACLE按照时间分组进行统计
在日常的需求中,会有很多类似的场景,客户要求根据年、月、日,甚至每小时进行数据统计,经过多方研究,总结了一下几个方面:
# 按年统计
```sql
select a.work,to_char(a.CREATE_TIME, 'yyyy'), count(1)
from TEST a
group by a.work,to_char(a.CREATE_TIME, 'yyyy')
order by a.work,to_char(a.CREATE_TIME, 'yyyy');
```
# 按月统计
```sql
select to_char(a.CREATE_TIME, 'yyyy-MM'), count(1)
from TEST a
group by to_char(a.CREATE_TIME, 'yyyy-MM')
order by to_char(a.CREATE_TIME, 'yyyy-MM');
```
# 按日统计
```sql
select to_char(a.CREATE_TIME, 'yyyy-MM-dd'), count(1)
from TEST a
group by to_char(a.CREATE_TIME, 'yyyy-MM-dd')
order by to_char(a.CREATE_TIME, 'yyyy-MM-dd');
```
# 按周统计
```sql
select to_char(a.CREATE_TIME, 'ww'), count(1)
from TEST a
group by to_char(a.CREATE_TIME, 'ww')
order by to_char(a.CREATE_TIME, 'ww');
--这里的ww表示该日期所在周是本年的哪一周
```
# 按季度统计
```sql
select to_char(a.CREATE_TIME, 'q'), count(1)
from TEST a
group by to_char(a.CREATE_TIME, 'q')
order by to_char(a.CREATE_TIME, 'q');
--这里的q跟ww同理,为本年的哪一季度
```
本文是原创文章,采用 CC BY-NC-ND 4.0 协议,完整转载请注明来自 ZFS的成长之路
评论
匿名评论
隐私政策
你无需删除空行,直接评论以获取最佳展示效果