oracle 中按照时间分组统计

最新推荐文章于 2023-07-25 10:28:10 发布

最新推荐文章于 2023-07-25 10:28:10 发布 · 408 阅读

文章标签：

#oracle

学习笔记专栏收录该内容

2 篇文章

订阅专栏

本文通过SQL查询展示了2012年的ATM使用情况统计数据，包括按年、月、星期和季度的不同维度进行分组汇总的数据。这些数据有助于了解ATM使用的季节性和周期性趋势。

ASK_TIME ATM_NUMBER
2012-01-01 2
2012-02-01 4
2012-05-01 6
2012-08-01 8
1.按年份分组

SELECT (to_char(ASK_TIME,'yyyy')) as 年份, (sum(ATM_NUMBER)) as ATM数量
FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy')
--------------------------------------------------------------------
年份 ATM数量
2012 20
2.按月份分组

SELECT (to_char(ASK_TIME,'yyyy-mm')) as 月份, (sum(ATM_NUMBER)) as ATM数量
FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy-mm')
-----------------------------------------------------------------------
月份 ATM数量
2012-01 2
2012-02 4
2012-05 6
2012-08 8
3.按星期分组

SELECT (to_char(ASK_TIME,'yyyy-IW')) as 星期, (sum(ATM_NUMBER)) as ATM数量
FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy-IW')

4.按季度分组

SELECT (to_char(ASK_TIME,'yyyy-Q')) as 季度, (sum(ATM_NUMBER)) as ATM数量
FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy-Q')
-----------------------------------------------------------------
季度 ATM数量
2012-1 6
2012-2 6
2012-3 8
或者

SELECT (to_char(ASK_TIME,'yyyy')) as 年份 ,
sum(decode(to_char(ASK_TIME,'mm'),'01',ATM_NUMBER,'02',ATM_NUMBER,'03',ATM_NUMBER,'0')) as 第一季,
sum(decode(to_char(ASK_TIME,'mm'),'04',ATM_NUMBER,'05',ATM_NUMBER,'06',ATM_NUMBER,'0')) as 第二季,
sum(decode(to_char(ASK_TIME,'mm'),'07',ATM_NUMBER,'08',ATM_NUMBER,'09',ATM_NUMBER,'0')) as 第三季,
sum(decode(to_char(ASK_TIME,'mm'),'10',ATM_NUMBER,'11',ATM_NUMBER,'12',ATM_NUMBER,'0')) as 第四季
FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy')
------------------------------------------------------------
年份第一季第二季第三季第四季
2012 6 6 8 0