SQL数据转换

Question

我在Excel中收到了数据，日期列(名为ALL_PERIODS)是字符串，具有以下格式：

JFM 14 - W/E 04/05/14

2014年1月，2月，3月，周结束

我需要将该列划分为三个新列: DATE_ (DATE)、YEAR_ (INT)、QUARTER_ (INT)，因此我编写了以下转换：

列日期：

ALTER TABLE MY_TABLE 
   ADD DATE_TEMP VARCHAR2 (128);
UPDATE MY_TABLE 
   SET DATE_TEMP = SUBSTR(ALL_PERIODS, -2, 2);
UPDATE MY_TABLE 
   SET DATE_TEMP = REPLACE(DATE_TEMP, '/',  '');
UPDATE MY_TABLE 
   SET DATE_TEMP = to_char(to_date(DATE_TEMP, 'mmddyyyy'), 'yyyy-mm-dd');
UPDATE MY_TABLE 
   SET DATE_TEMP = REPLACE(DATE_TEMP, '00',  '20');
UPDATE MY_TABLE 
   SET DATE_TEMP = TO_DATE(DATE_TEMP, 'YYYY-MM-DD');
ALTER TABLE MY_TABLE 
ADD DATE_ DATE;
UPDATE MY_TABLE 
   SET DATE_ = DATE_TEMP;
ALTER TABLE MY_TABLE 
   DROP COLUMN DATE_TEMP;

栏年：

ALTER TABLE MY_TABLE
    ADD YEAR_TEMP VARCHAR2(128 BYTE);
UPDATE MY_TABLE
    SET YEAR_TEMP =  SUBSTR(ALL_PERIODS, 8, 4);
ALTER TABLE MY_TABLE
    ADD YEAR_ NUMBER(4);
UPDATE MY_TABLE
    SET YEAR_ =  YEAR_TEMP;
UPDATE MY_TABLE
    SET YEAR_ = CONCAT('20', YEAR_);
ALTER TABLE MY_TABLE
    DROP COLUMN YEAR_TEMP;

第一栏季度：

ALTER TABLE MY_TABLE 
    ADD QUARTER NUMBER(1);
UPDATE MY_TABLE
    SET QUARTER_ = 4
        WHERE DATE_ = '30-DEC-17';
...and the same with other 3 quarters

它在某种程度上起作用了，但我想它并不有效(我在SQL中不是很高级，我还在学习)，而且更新操作太慢，所以我想转换ALL_PERIODS列，同时将数据插入到三个需要的列的新表中。

你能告诉我，插入的是什么样子的吗？谢谢你N。

Answer 1

INSERT INTO my_table (COL1, COL2, ... DATE_, QUARTER_, YEAR_)
SELECT sr.COL1, sr.COL2, .... 
TO_DATE(TO_CHAR(TO_DATE(SUBSTR(sr.ALL_PERIODS, -8), 'mm/dd/yy'), 'YYYY-MM-DD'), 'YYYY-MM-DD') DATE_,
CASE SUBSTR(sr.ALL_PERIODS, 1, 3)
WHEN 'JFM' THEN 1 
WHEN 'AMJ' THEN 2
WHEN 'JAS' THEN 3
WHEN 'OND' THEN 4
END QUARTER_,
'20'||SUBSTR(sr.ALL_PERIODS, 5,2) YEAR_,
FROM SOURCE_TABLE sr
[WHERE sr.COL99 = 'Y'];

Answer 2

DDL-语句(add/delete列)速度慢，没有必要。插入数据时不应更改表定义！

我将插入如下3列(用实际的ALL_PERIODS值替换我使用的日期字符串)：

INSERT INTO MY_TABLE (DATE_, YEAR_, QUARTER_)
TO_DATE(SUBSTR('JFM 14 - W/E 04/05/14', -8), 'dd/mm/yy'),
EXTRACT(YEAR FROM TO_DATE(SUBSTR('JFM 14 - W/E 04/05/14', -2), 'yy')),
-- OR SHORTER IF ALWAYS IN THE 2000: TO_NUMBER(SUBSTR('JFM 14 - W/E 04/05/14', -2)) + 2000
DECODE(SUBSTR('JFM 14 - W/E 04/05/14',1,3), 'JFM', 1, 'AMJ', 2, 'JAS', 3, 'OND', 4);

问候

Answer 3

如果ALL_PERIODS已经加载到表中？

而要计算的额外列已经添加到表中了吗？

那么，一次更新就足够了。

UPDATE MY_TABLE
SET 
 DATE_ = TO_DATE(SUBSTR(RTRIM(ALL_PERIODS),-8),'MM/DD/YY')
,YEAR_ = 2000+SUBSTR(ALL_PERIODS,5,2)
,QUARTER_ = DECODE(SUBSTR(ALL_PERIODS,1,3), 'JFM', 1, 'AMJ', 2, 'JAS', 3, 'OND', 4)
WHERE ALL_PERIODS IS NOT NULL 
  AND DATE_ IS NULL

db<>fiddle https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=ee4faccfa6854861cfcba4e780b75509的测试