Oracle时间框架转换

本文关键字：转换 框架 时间 Oracle | 更新日期: 2023-09-27 18:24:52

我有以下配置：一个名为source（bid，valid_from，valid_to，qty）和destination（bid、jan、feb、mar、apr、…、dec）的表。我想把来源的数据插入来源，（将数量平均拆分为有效月份，剩余的拆分为时间框架中的最后一个月）如下：如果来源中的记录是

00001     01.02.2001   31.06.2001     132

这将被转换为目的地：

00001  0 26 26 26 26 28 0 0 0 0 0 0

我怎么能这么做？非常感谢。

Oracle时间框架转换

我假设valid_from/valid_to从不跨越多年；即TO_DATE(valid_from,'YYYY')总是= TO_DATE(valid_to,'YYYY')。

SQL> CREATE TABLE source (
  2      bid         VARCHAR2(5)
  3  ,   valid_from  DATE
  4  ,   valid_to    DATE
  5  ,   qty         NUMBER
  6  );
Table created.
SQL> INSERT INTO source VALUES ('00001',TO_DATE('20010201','YYYYMMDD'),TO_DATE('20010630','YYYYMMDD'),132);
1 row created.
SQL> INSERT INTO source VALUES ('00002',TO_DATE('20020301','YYYYMMDD'),TO_DATE('20021231','YYYYMMDD'),59);
1 row created.
SQL> CREATE TABLE destination (
  2      bid         VARCHAR2(5)
  3  ,   jan         NUMBER
  4  ,   feb         NUMBER
  5  ,   mar         NUMBER
  6  ,   apr         NUMBER
  7  ,   may         NUMBER
  8  ,   jun         NUMBER
  9  ,   jul         NUMBER
 10  ,   aug         NUMBER
 11  ,   sep         NUMBER
 12  ,   oct         NUMBER
 13  ,   nov         NUMBER
 14  ,   dec         NUMBER
 15  );
Table created.
SQL> COLUMN jan FORMAT 999
SQL> COLUMN feb FORMAT 999
SQL> COLUMN mar FORMAT 999
SQL> COLUMN apr FORMAT 999
SQL> COLUMN may FORMAT 999
SQL> COLUMN jun FORMAT 999
SQL> COLUMN jul FORMAT 999
SQL> COLUMN aug FORMAT 999
SQL> COLUMN sep FORMAT 999
SQL> COLUMN oct FORMAT 999
SQL> COLUMN nov FORMAT 999
SQL> COLUMN dec FORMAT 999
SQL> INSERT INTO destination
  2  SELECT bid
  3  ,      NVL(MAX(DECODE(r,01,split_qty)),0)  jan
  4  ,      NVL(MAX(DECODE(r,02,split_qty)),0)  feb
  5  ,      NVL(MAX(DECODE(r,03,split_qty)),0)  mar
  6  ,      NVL(MAX(DECODE(r,04,split_qty)),0)  apr
  7  ,      NVL(MAX(DECODE(r,05,split_qty)),0)  may
  8  ,      NVL(MAX(DECODE(r,06,split_qty)),0)  jun
  9  ,      NVL(MAX(DECODE(r,07,split_qty)),0)  jul
 10  ,      NVL(MAX(DECODE(r,08,split_qty)),0)  aug
 11  ,      NVL(MAX(DECODE(r,09,split_qty)),0)  sep
 12  ,      NVL(MAX(DECODE(r,10,split_qty)),0)  oct
 13  ,      NVL(MAX(DECODE(r,11,split_qty)),0)  nov
 14  ,      NVL(MAX(DECODE(r,12,split_qty)),0)  dec
 15  FROM
 16  (
 17  SELECT x.bid
 18  ,      x.month_abbr
 19  ,      x.r
 20  ,      x.rn
 21  ,      x.total_months
 22  ,      x.qty
 23  ,      FLOOR(x.qty / x.total_months)
 24         + DECODE(x.rn
 25                  ,      x.total_months, MOD(x.qty, x.total_months)
 26                  ,      0)                           split_qty
 27  FROM  (SELECT     s.bid
 28         ,          months.r
 29         ,          ROW_NUMBER()
 30                    OVER (PARTITION BY s.bid
 31                          ORDER BY     months.r)            rn
 32         ,          COUNT(*)
 33                    OVER (PARTITION BY s.bid)               total_months
 34         ,          s.qty
 35         FROM      (SELECT     ROWNUM   r
 36                    FROM       DUAL
 37                    CONNECT BY LEVEL <= 12)  months
 38         ,          source                   s
 39         WHERE      TO_CHAR(s.valid_from,'YYYY') = TO_CHAR(s.valid_to,'YYYY')
 40         AND        months.r BETWEEN TO_NUMBER(TO_CHAR(s.valid_from,'MM'))
 41                                 AND TO_NUMBER(TO_CHAR(s.valid_to,'MM'))) x
 42  )
 43  GROUP BY bid
 44  ;
2 rows created.
SQL> SELECT *
  2  FROM   destination
  3  ;
BID    JAN  FEB  MAR  APR  MAY  JUN  JUL  AUG  SEP  OCT  NOV  DEC
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
00001    0   26   26   26   26   28    0    0    0    0    0    0
00002    0    0    5    5    5    5    5    5    5    5    5   14
SQL>
相关文章：