如何按月分组,包括所有月份? [英] How to group by month including all months?
本文介绍了如何按月分组,包括所有月份?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我按月对桌子分组
SELECT TO_CHAR (created, 'YYYY-MM') AS operation, COUNT (id)
FROM user_info
WHERE created IS NOT NULL
GROUP BY ROLLUP (TO_CHAR (created, 'YYYY-MM'))
2015-04 1
2015-06 10
2015-08 22
2015-09 8
2015-10 13
2015-12 5
2016-01 25
2016-02 37
2016-03 24
2016-04 1
2016-05 1
2016-06 2
2016-08 2
2016-09 7
2016-10 103
2016-11 5
2016-12 2
2017-04 14
2017-05 2
284
但是记录不能涵盖所有月份.
我希望输出包含所有月份,而缺失的月份将显示为默认值:
But the records don't cover all the months.
I would like the output to include all the months, with the missing ones displayed in the output with a default value:
2017-01 ...
2017-02 ...
2017-03 ZERO
2017-04 ZERO
2017-05 ...
推荐答案
Oracle has a good array of date manipulation functions. The two pertinent ones for this problem are
-
MONTHS_BETWEEN()
计算两个日期之间的月数 -
ADD_MONTHS()
将日期增加给定的月数
MONTHS_BETWEEN()
which calculates the number of months between two datesADD_MONTHS()
which increments a date by the given number of months
我们可以结合使用这些功能来生成一张表格,其中包含您的表格记录所涵盖的所有月份.然后,我们使用外部联接将有条件的记录从USER_INFO联接到该日历.当没有记录匹配时, count(id)
将为零.
We can combine these functions to generate a table of all the months spanned by your table's records. Then we use an outer join to conditionally join records from USER_INFO to that calendar. When no records match count(id)
will be zero.
with cte as (
select max(trunc(created, 'MM')) as max_dt
, min(trunc(created, 'MM')) as min_dt
from user_info
)
, cal as (
select add_months(min_dt, (level-1)) as mth
from cte
connect by level <= months_between(max_dt, min_dt) + 1
)
select to_char(cal.mth, 'YYYY-MM') as operation
, count(id)
from cal
left outer join user_info
on trunc(user_info.created, 'mm') = cal.mth
group by rollup (cal.mth)
order by 1
/
这篇关于如何按月分组,包括所有月份?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文