oracle – 按小时或按日分组记录并用零或空填充缺口
发布时间:2021-03-19 09:32:49 所属栏目:站长百科 来源:网络整理
导读:我写了一个按小时统计记录的查询: select TO_CHAR(copied_timestamp,'YYYY-MM-DD HH24'),count(*) from req group byTO_CHAR(copied_timestamp,'YYYY-MM-DD HH24'); 结果是: 2012-02-22 13 22802012-02-22 15 12502012-02-22 16 12452012-02-22 19 1258 但
我写了一个按小时统计记录的查询: select TO_CHAR(copied_timestamp,'YYYY-MM-DD HH24'),count(*) from req group by TO_CHAR(copied_timestamp,'YYYY-MM-DD HH24'); 结果是: 2012-02-22 13 2280 2012-02-22 15 1250 2012-02-22 16 1245 2012-02-22 19 1258 但我需要这样的结果: 2012-02-22 13 2280 2012-02-22 14 0 2012-02-22 15 1250 2012-02-22 16 1245 2012-02-22 17 0 2012-02-22 18 0 2012-02-22 19 1258 此外,我也有按日和月分组的查询! select TO_CHAR(copied_timestamp,'YYYY-MM-DD'),count(*) from req group by TO_CHAR(copied_timestamp,'YYYY-MM-DD'); select TO_CHAR(copied_timestamp,'YYYY-MM'),'YYYY-MM'); 我需要将它们的间隙填充为零或零. 解决方法尝试:第一次查询(按小时): with t as ( select mnd + ((level-1)/24) ddd from (select trunc(min(copied_timestamp),'hh') mnd,trunc(max(copied_timestamp),'hh') mxd from req) v connect by mnd + ((level-1)/24) <= mxd ) select to_char(trunc(d1,'hh'),'yyyy-mm-dd hh24'),count(d2) from (select nvl(copied_timestamp,ddd) d1,copied_timestamp d2 from req right outer join ( select ddd from t) ad on ddd = trunc(copied_timestamp,'hh')) group by trunc(d1,'hh'); 第二个查询(按天): with t as ( select mnd + level-1 ddd from (select trunc(min(copied_timestamp),'dd') mnd,'dd') mxd from req) v connect by mnd + level-1 <= mxd ) select to_char(trunc(d1,'dd'),'yyyy-mm-dd'),count(d2) from (select nvl(copied_timestamp,copied_timestamp d2 from req right outer join ( select ddd from t) ad on ddd = trunc(copied_timestamp,'dd')) group by trunc(d1,'dd'); 第三个查询(按月): with t as ( select add_months(mnd,level-1) ddd from (select trunc(min(copied_timestamp),'mm') mnd,'mm') mxd from req) v connect by add_months(mnd,level-1) <= mxd ) select to_char(trunc(d1,'mm'),'yyyy-mm'),'mm')) group by trunc(d1,'mm'); (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |