oracle统计时间区间
1、按天统计数据,没有补0
SELECT
b.DAY_TIME as abscissa_name,
nvl( a.ct, 0 ) as total
FROM
(
SELECT
to_char( t.REQUEST_TIME, 'yyyy-MM-dd' ) birthday,
count( 1 ) ct
FROM
ESB_LOG t
GROUP BY
to_char( t.REQUEST_TIME, 'yyyy-MM-dd' )
ORDER BY
to_char( t.REQUEST_TIME, 'yyyy-MM-dd' ) ASC
) a right join (
SELECT
TO_CHAR( TO_DATE( '2020-08-01', 'YYYY-MM-DD' ) + ROWNUM - 1, 'YYYY-MM-DD' ) DAY_TIME
FROM
DUAL CONNECT BY ROWNUM-1 <= TO_DATE( '2020-08-21', 'YYYY-MM-DD' ) - TO_DATE( '2020-08-01', 'YYYY-MM-DD' )
) b ON a.birthday = b.DAY_TIME
ORDER BY
b.DAY_TIME
2、按月统计,没有补0
SELECT
TO_CHAR( ADD_MONTHS( TO_DATE( '2020-08-20', 'yyyy-MM-dd' ), ROWNUM - 1 ), 'yyyy-MM' ) AS abscissa_name
FROM
DUAL CONNECT BY ROWNUM <= months_between(
to_date( '2021-11-10', 'yyyy-MM-dd' ),
to_date( '2020-08-20', 'yyyy-MM-dd' )) + 2;
SELECT
to_char ( REQUEST_TIME, 'yyyy-mm' ) abscissa_name,
count( * ) AS total
FROM
ESB_LOG
WHERE
to_char ( REQUEST_TIME, 'yyyy-mm-dd' ) BETWEEN '2020-08-20'
AND '2021-11-10'
GROUP BY
to_char ( REQUEST_TIME, 'yyyy-mm' );
SELECT
b.abscissa_name,
nvl ( a.total, 0 ) as total
FROM
(
SELECT
to_char ( REQUEST_TIME, 'yyyy-mm' ) abscissa_name,
count( * ) AS total
FROM
ESB_LOG
WHERE
to_char ( REQUEST_TIME, 'yyyy-mm-dd' ) BETWEEN '2020-08-20'
AND '2021-11-10'
GROUP BY
to_char ( REQUEST_TIME, 'yyyy-mm' )
) a
RIGHT JOIN (
SELECT
TO_CHAR ( ADD_MONTHS ( TO_DATE ( '2020-08-20', 'yyyy-MM-dd' ), ROWNUM - 1 ), 'yyyy-MM' ) AS abscissa_name
FROM
DUAL CONNECT BY ROWNUM <= months_between ( to_date ( '2021-11-10', 'yyyy-MM-dd' ), to_date ( '2020-08-20', 'yyyy-MM-dd' ) ) + 2
) b ON b.abscissa_name = a.abscissa_name
ORDER BY
b.abscissa_name
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战