我按月份对表格进行分组
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
但记录并没有涵盖所有月份。
我希望输出包含所有月份,并在输出中显示缺失的月份并使用默认值:
2017-01 ...
2017-02 ...
2017-03 ZERO
2017-04 ZERO
2017-05 ...
甲骨文有一系列很好的日期操作函数 https://docs.oracle.com/database/122/SQLRF/Single-Row-Functions.htm#GUID-5652DBC2-41C7-4F07-BEDD-DAF620E35F3C。这个问题的两个相关问题是
-
MONTHS_BETWEEN()
计算两个日期之间的月数
-
ADD_MONTHS()
将日期增加给定的月数
我们可以组合这些函数来生成表记录所涵盖的所有月份的表。然后,我们使用外部联接有条件地将记录从 USER_INFO 联接到该日历。当没有记录匹配时count(id)
将为零。
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
/
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)