用lag补满数据

http://blog.youkuaiyun.com/jgmydsai

现在如下数据

SQL> select * from test;
DATES              KEY
----------- ----------
2014-01-01           3
2015-05-01           5
2015-01-01           4
1
2
3
4
5
6

要求把中间缺失的月份补满，结果如下：

DATES              KEY
----------- ----------
2014-01-01           3
2014-02-01           3
2014-03-01           3
2014-04-01           3
2014-05-01           3
2014-06-01           3
2014-07-01           3
2014-08-01           3
2014-09-01           3
2014-10-01           3
2014-11-01           3
2014-12-01           3
2015-01-01           4
2015-02-01           4
2015-03-01           4
2015-04-01           4
2015-05-01           5
17 rows selected
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

这种使用分析函数，可以很容易的达到目的

WITH a AS
 (SELECT nd, md, months_between(md, nd) diff
    FROM (SELECT MIN(dates) nd, MAX(dates) md FROM test))
SELECT a1.dates,
       nvl(s.key, lag(key ignore NULLS) over(ORDER BY a1.dates)) AS newkey
  FROM (SELECT add_months(nd, (LEVEL - 1)) AS dates
          FROM a
        CONNECT BY LEVEL <= diff + 1) a1
  LEFT JOIN test s
    ON a1.dates = s.dates
 ORDER BY 1;