Oracle SQL查询-在两个日期之间生成记录 [英] Oracle SQL query - generate records between two dates

问题描述

按表中的数据按生效日期存储.您能帮我一个ORACLE SQL语句吗，该语句将8/1数据复制到8/2、8/3、8/4并在之后重复8/5值?

The data in by table is stored by effective date. Can you please help me with an ORACLE SQL statement, that replicates the 8/1 data onto 8/2, 8/3,8/4 and repeat the 8/5 value after?

DATE             VALUE
8/1/2017           x
8/5/2017           b
8/7/2017           a

所需的输出:

DATE             VALUE
8/1/2017           x
8/2/2017           x
8/3/2017           x
8/4/2017           x
8/5/2017           b
8/6/2017           b

推荐答案

这里是执行此操作的一种方法.它假定日期都是纯日期(没有时间部分-实际上意味着一天中的时间都是00:00:00).它还假定您希望输出在输入中包括第一个日期和最后一个日期之间的所有日期.

Here is one way to do this. It assumes the dates are all pure dates (no time of day component - which in fact means the time of day is 00:00:00 everywhere). It also assumes you want the output to include all the dates between the first and the last date in the inputs.

第一个和最后一个日期在最里面的查询中计算.然后，使用分层查询(连接依据)创建它们之间的所有日期，并将结果左联接到原始数据.然后，将解析的last_value()函数与ignore nulls选项一起使用即可获得输出.

The first and last dates are computed in the innermost query. Then all the dates between them are created with a hierarchical (connect by) query, and the result is left-joined to the original data. The output is then obtained by using the analytical last_value() function with the ignore nulls option.

with
     inputs ( dt, value ) as (
       select to_date('8/1/2017', 'mm/dd/yyyy'), 'x' from dual union all
       select to_date('8/5/2017', 'mm/dd/yyyy'), 'b' from dual union all
       select to_date('8/7/2017', 'mm/dd/yyyy'), 'a' from dual
     )
-- End of simulated input data (for testing purposes only, not part of the solution).
-- Use your actual table and column names in the SQL query that begins below this line.
select dt, last_value(value ignore nulls) over (order by dt) as value
from   ( select f.dt, i.value
         from   ( select min_dt + level - 1 as dt
                  from   ( select max(dt) as max_dt, min(dt) as min_dt
                           from   inputs
                         )
                  connect by level <= max_dt - min_dt + 1
                ) f
                left outer join inputs i on f.dt = i.dt
       )
;

DT          VALUE
----------  -----
2017-08-01  x
2017-08-02  x
2017-08-03  x
2017-08-04  x
2017-08-05  b
2017-08-06  b
2017-08-07  a

这篇关于Oracle SQL查询-在两个日期之间生成记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！