利用VARRAY重叠时间序列数据

Question

我有一些数据想要重叠，我就像下面这样做。而且可能有一些更聪明的方法，因为数据非常大，请提供建议。

这是我的VArray：

CREATE OR REPLACE TYPE NUMBER_VARRAY_5 AS VARRAY (5) OF NUMBER NOT NULL

以下是原始数据(表TEST_1)

"DATE_A","TOTO3_A"
"01/01/2005","281.25"
"02/01/2005","-9999.00"
"03/01/2005","291.50"
"04/01/2005","310.50"
"05/01/2005","298.75"
"06/01/2005","300.75"

表TEST_2是日期滞后1天和固定日(5天)重叠的结果。

"DATE_START","DATE_END","O3"
"01/01/2005","05/01/2005","<Collection>"
1      281.25
2      -9999
3      291.5
4      310.5
5      298.75
"02/01/2005","06/01/2005","<Collection>"
1      -9999
2      291.5
3      310.5
4      298.75
5      300.75

这是我的代码

CREATE OR REPLACE PROCEDURE TEST
IS
   NUMBER_VARRAY   NUMBER_VARRAY_5 := NUMBER_VARRAY_5 ();
   L_VALUE         NUMBER;
   MIN_DATE        DATE;
   MAX_DATE        DATE;

BEGIN
   SELECT MIN (DATE_A), MAX (DATE_A)
     INTO MIN_DATE, MAX_DATE
     FROM TEST_1;

   FOR CUR_R IN 0 .. (MAX_DATE - MIN_DATE - 4)       
   LOOP
      L_VALUE := 0;

      FOR I IN CUR_R .. (CUR_R + 4)
      LOOP
         L_VALUE := L_VALUE + 1;
         NUMBER_VARRAY.EXTEND;

         SELECT TOTO3_A
           INTO NUMBER_VARRAY (L_VALUE)
           FROM TEST_1
          WHERE DATE_A = MIN_DATE + I;
      END LOOP;

      INSERT INTO TEST_2 (DATE_START, DATE_END, O3)
           VALUES (MIN_DATE + CUR_R, (MIN_DATE + CUR_R + 4), NUMBER_VARRAY);

      COMMIT;

      NUMBER_VARRAY.DELETE ();
   END LOOP;
END TEST;

Answer 1

我认为您可以通过使用COLLECT函数在纯SQL中这样做：

with dt as ( select max(date_a)-4 as mx from TEST_1 )
   , dr as ( select date_a as date_start
                    , date_a + 4 as date_end 
            from TEST_1 
                 cross join dt
             where date_a <= dt.mx )
select dr.*
       , cast(collect(TEST_1.toto3_a) as NUMBER_VARRAY_5) as coll03
from TEST_1
      cross join dr
where TEST_1.date_a between dr.date_start and dr.date_end  
group by dr.date_start, dr.date_end
order by dr.date_start
/

有一些错误:ORA-22814:属性值或元素值大于类型中指定的值

嗯，试试这片伏都教：

 cast(collect(cast(TEST_1.toto3_a as number)) as NUMBER_VARRAY_5)