利用sql求时间序列（含天、小时、分、秒） (Oracle)

事情的起因是要改善代码~~~

原先是在一个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 dual
connect 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的语法也蛮不错的。