利用sql求时间序列(含天、小时、分、秒) (Oracle)
来源:互联网 发布:js print setup 下载 编辑:程序博客网 时间:2024/05/01 21:45
事情的起因是要改善代码~~~
原先是在一个for循环里面执行sql查询语句,情景大概是每次循环都要用当前循环的值(时间)作为条件去sql里查,当时是每次循环都去执行sql语句。虽然能实现,But,执行的日志发现,每次都会去连接池里面拿一个连接,查完后再归还连接……如此循环,效率和性能都不太科学。。
所以发现为啥之前不在sql里改善??原来是需要每一分钟都要数据,如果查不到要将数值自动补0。所以当初用for循环是为了判断——如果查不到数据就补0,但这样始终还是忒慢!!
于是有同事提出用right join一个结果集,这个结果集用于从指定的时间往前推n分钟,并且是一个序列的形式。因为是right join,所以从数据表(左表)里查不到的记录会自动补为null,完了再用case when将Null转成0即可。——因为sql执行顺序是from->left(right) join->select
现假设我有一张t_data表:
其中time字段是时间字符串, 而total字段是数值型的字段。
现在的需求是查出前若干分钟的total值(注意是要求每一分钟都有时间,如果原表没有时间,就把时间加上并把total补0)
现假设t_data中现有的数据如下:
select * from t_data
我现在想要查出前若干分钟的结果集,达到上述要求。
第一步:首先想办法得出指定时间的前若干分钟的序列,这里我用20分钟为例。重点来了,这句sql很神奇!!
select to_char(to_date('201511101014', 'YYYYMMDDHH24MI') - (level - 1) / 1440, 'yyyymmddhh24mi') as minute_sequence from dualconnect by level <= 20
仔细检查到201511101000这条记录后的一条记录是201511100959,说明达到了正确利用sql语句求解时间序列的方法。
同理:(level - 1) / 1440你除以24得到的会是小时序列,除以1得到天的序列,而除以86400则会得掉秒级的序列。
把-(level-1)改成+(level-1)则是递增。
第二步:再用right join第一步查到的结果集:
select b.minute_sequence time, case when total is null then 0 else total end total from t_data a right join (select to_char(to_date('201511101014', 'YYYYMMDDHH24MI') - (level - 1) / 1440, 'yyyymmddhh24mi') as minute_sequence from dual connect by level <= 20) b on a.time = b.minute_sequence order by b.minute_sequence desc
查看结果:
可以看出,时间也补上了,而原本t_data里面没有total字段的记录也补上0了。达到了最初的目的。。
注:这里特别注意case when then (when then...) else end的语法!
其实还蛮复杂的,sql求时间序列于我是件蛮新颖、有趣的事,而巩固巩固下right join和case when then else end的语法也蛮不错的。