源表:
biz_dt secu_code matched_num
20100901 10001 100
20100903 10001 101
20100906 10001 104
20100901 10002 107
20100902 10002 103
20100906 10002 117
需要得到下面的目标表:
biz_dt secu_code matched_num
20100901 10001 100
20100902 10001 100
20100903 10001 101
20100904 10001 101
20100905 10001 101
20100906 10001 104
20100901 10002 107
20100902 10002 103
20100903 10002 103
20100904 10002 103
20100905 10002 103
20100906 10002 117没有某日期的数据用上一天的数据来填充
biz_dt secu_code matched_num
20100901 10001 100
20100903 10001 101
20100906 10001 104
20100901 10002 107
20100902 10002 103
20100906 10002 117
需要得到下面的目标表:
biz_dt secu_code matched_num
20100901 10001 100
20100902 10001 100
20100903 10001 101
20100904 10001 101
20100905 10001 101
20100906 10001 104
20100901 10002 107
20100902 10002 103
20100903 10002 103
20100904 10002 103
20100905 10002 103
20100906 10002 117没有某日期的数据用上一天的数据来填充
则日期列最后为贴子上的那样。然后 后一列就是罗列secu_code的值,后面的matched_num字段就是对应这前面两个字段的值,如果其中一个日期没有值,如20100902 10001 则该日该代码的值就沿用上一日期的值
biz_dt varchar2(10),
secu_code number(18,0),
matched_num number(18,0)
);INSERT INTO tb(biz_dt, secu_code, matched_num)
VALUES('20100901', 10001, 100);
INSERT INTO tb(biz_dt, secu_code, matched_num)
VALUES('20100902', 10001, 100);
INSERT INTO tb(biz_dt, secu_code, matched_num)
VALUES('20100903', 10001, 101);
INSERT INTO tb(biz_dt, secu_code, matched_num)
VALUES('20100904', 10001, 101);
INSERT INTO tb(biz_dt, secu_code, matched_num)
VALUES('20100908', 10001, 101);
INSERT INTO tb(biz_dt, secu_code, matched_num)
VALUES('20100912', 10001, 104);
INSERT INTO tb(biz_dt, secu_code, matched_num)
VALUES('20100913', 10002, 107);
INSERT INTO tb(biz_dt, secu_code, matched_num)
VALUES('20100919', 10002, 103);select * from tb;DECLARE
v_cdate VARCHAR2(10);
v_mincdate VARCHAR2(10);
v_maxcdate VARCHAR2(10);
v_cnt NUMBER(18,0);
BEGIN
SELECT min(biz_dt) as min_biz_dt, max(biz_dt) as max_biz_dt into v_mincdate, v_maxcdate FROM tb;
v_cdate := v_mincdate; v_mincdate := to_char(to_date(v_mincdate,'yyyymmdd')+1,'yyyymmdd');
WHILE v_mincdate<v_maxcdate LOOP
SELECT COUNT(biz_dt) INTO v_cnt FROM tb WHERE biz_dt=v_mincdate;
IF v_cnt=0 THEN -- 如果没有找到当天的记录,插入当天的记录;
INSERT INTO tb(biz_dt, secu_code, matched_num)
SELECT v_mincdate, secu_code, matched_num FROM tb WHERE biz_dt=v_cdate;
v_cdate := v_mincdate;
ELSE
v_cdate := v_mincdate;
END IF;
v_mincdate := to_char(to_date(v_mincdate,'yyyymmdd')+1,'yyyymmdd');
END LOOP;
COMMIT;
END;
/select * from tb order by biz_dt;
2 biz_dt varchar2(10),
3 secu_code number(18,0),
4 matched_num number(18,0)
5 );表已创建。scott@SZTYORA>
scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
2 VALUES('20100901', 10001, 100);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
2 VALUES('20100902', 10001, 100);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
2 VALUES('20100903', 10001, 101);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
2 VALUES('20100904', 10001, 101);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
2 VALUES('20100908', 10001, 101);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
2 VALUES('20100912', 10001, 104);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
2 VALUES('20100913', 10002, 107);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
2 VALUES('20100919', 10002, 103);已创建 1 行。scott@SZTYORA>
scott@SZTYORA> select * from tb;BIZ_DT SECU_CODE MATCHED_NUM
-------------------- ---------- -----------
20100901 10001 100
20100902 10001 100
20100903 10001 101
20100904 10001 101
20100908 10001 101
20100912 10001 104
20100913 10002 107
20100919 10002 103已选择8行。scott@SZTYORA> DECLARE
2 v_cdate VARCHAR2(10);
3 v_mincdate VARCHAR2(10);
4 v_maxcdate VARCHAR2(10);
5 v_cnt NUMBER(18,0);
6 BEGIN
7 SELECT min(biz_dt) as min_biz_dt, max(biz_dt) as max_biz_dt into v_mincdate, v_maxcdate FROM tb;
8 v_cdate := v_mincdate;
9
10 v_mincdate := to_char(to_date(v_mincdate,'yyyymmdd')+1,'yyyymmdd');
11 WHILE v_mincdate<v_maxcdate LOOP
12 SELECT COUNT(biz_dt) INTO v_cnt FROM tb WHERE biz_dt=v_mincdate;
13 IF v_cnt=0 THEN -- 如果没有找到当天的记录,插入当天的记录;
14 INSERT INTO tb(biz_dt, secu_code, matched_num)
15 SELECT v_mincdate, secu_code, matched_num FROM tb WHERE biz_dt=v_cdate;
16 v_cdate := v_mincdate;
17 ELSE
18 v_cdate := v_mincdate;
19 END IF;
20 v_mincdate := to_char(to_date(v_mincdate,'yyyymmdd')+1,'yyyymmdd');
21 END LOOP;
22 COMMIT;
23 END;
24 /PL/SQL 过程已成功完成。scott@SZTYORA> select * from tb order by biz_dt;BIZ_DT SECU_CODE MATCHED_NUM
-------------------- ---------- -----------
20100901 10001 100
20100902 10001 100
20100903 10001 101
20100904 10001 101
20100905 10001 101
20100906 10001 101
20100907 10001 101
20100908 10001 101
20100909 10001 101
20100910 10001 101
20100911 10001 101
20100912 10001 104
20100913 10002 107
20100914 10002 107
20100915 10002 107
20100916 10002 107
20100917 10002 107
20100918 10002 107
20100919 10002 103已选择19行。scott@SZTYORA>
------修改了下
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
Connected as billing
SQL>
SQL> with tablea as(
2 select '20100901' biz_dt, '10001' secu_code, 100 matched_num
3 from dual
4 union all
5 select '20100903' biz_dt, '10001' secu_code, 101 matched_num
6 from dual
7 union all
8 select '20100906' biz_dt, '10001' secu_code, 104 matched_num
9 from dual
10 union all
11 select '20100901' biz_dt, '10002' secu_code, 107 matched_num
12 from dual
13 union all
14 select '20100902' biz_dt, '10002' secu_code, 103 matched_num
15 from dual
16 union all
17 select '20100906' biz_dt, '10002' secu_code, 117 matched_num
18 from dual
19 union all
20 select '20100909' biz_dt, '10002' secu_code, 118 matched_num from dual
21 )
22 select a biz_dt,b secu_code,c matched_num
23 from (select distinct a + level - 1 a, b, c
24 from (select biz_dt a,
25 t.secu_code b,
26 t.matched_num c,
27 nvl(lead(biz_dt, 1)
28 over(partition by secu_code order by biz_dt),
29 biz_dt) d
30 from tableA t)
31 connect by level < d - a + 1)
32 order by b, a
33 / BIZ_DT SECU_CODE MATCHED_NUM
---------- --------- -----------
20100901 10001 100
20100902 10001 100
20100903 10001 101
20100904 10001 101
20100905 10001 101
20100906 10001 104
20100901 10002 107
20100902 10002 103
20100903 10002 103
20100904 10002 103
20100905 10002 103
20100906 10002 117
20100907 10002 117
20100908 10002 117
20100909 10002 11815 rows selectedSQL>
select '20100901' biz_dt, '10001' secu_code, 100 matched_num from dual
union all
select '20100903' biz_dt, '10001' secu_code, 101 matched_num from dual
union all
select '20100906' biz_dt, '10001' secu_code, 104 matched_num from dual
union all
select '20100901' biz_dt, '10002' secu_code, 107 matched_num from dual
union all
select '20100902' biz_dt, '10002' secu_code, 103 matched_num from dual
union all
select '20100906' biz_dt, '10002' secu_code, 117 matched_num from dual
union all
select '20100909' biz_dt, '10002' secu_code, 118 matched_num from dual
)
select a biz_dt,b secu_code,c matched_num
from (select distinct a + level - 1 a, b, c
from (select biz_dt a,
t.secu_code b,
t.matched_num c,
nvl(lead(biz_dt, 1)
over(partition by secu_code order by biz_dt),
biz_dt) d
from tableA t)
connect by level < d - a + 1)
order by b, a
(小菜鸟)
-----------------
well done