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
但我需要这样的结果:
2012-02-22 13 22802012-02-22 14 02012-02-22 15 12502012-02-22 16 12452012-02-22 17 02012-02-22 18 02012-02-22 19 1258
此外,我也有按日和月分组的查询!
select TO_CHAR(copIEd_timestamp,'YYYY-MM-DD'),count(*) from reqgroup 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');总结
以上是内存溢出为你收集整理的oracle – 按小时或按日分组记录并用零或空填充缺口全部内容,希望文章能够帮你解决oracle – 按小时或按日分组记录并用零或空填充缺口所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)