Mengonversi fitur temporal Teradata NORMALIZE ke Amazon Redshift SQL

Po Hong, Amazon Web Services

Ringkasan

NORMALIZE adalah ekstensi Teradata ke standar ANSI SQL. Ketika tabel SQL menyertakan kolom yang memiliki tipe data PERIODE, NORMALIZE menggabungkan nilai yang bertemu atau tumpang tindih di kolom itu, untuk membentuk satu periode yang mengkonsolidasikan beberapa nilai periode individual. Untuk menggunakan NORMALIZE, setidaknya satu kolom dalam daftar SQL SELECT harus dari tipe data PERIODE temporal Teradata. Untuk informasi selengkapnya tentang NORMALIZE, lihat dokumentasi Teradata. 

Amazon Redshift tidak mendukung NORMALIZE, tetapi Anda dapat menerapkan fungsi ini dengan menggunakan sintaks SQL asli dan fungsi jendela LAG di Amazon Redshift. Pola ini berfokus pada penggunaan ekstensi Teradata NORMALIZE dengan kondisi ON MEETS OR OVERLAPS, yang merupakan format paling populer. Ini menjelaskan bagaimana fitur ini bekerja di Teradata dan bagaimana hal itu dapat diubah menjadi sintaks SQL asli Amazon Redshift.

Prasyarat dan batasan

Prasyarat

Arsitektur

Tumpukan teknologi sumber

Tumpukan teknologi target

Arsitektur target

Untuk arsitektur tingkat tinggi untuk memigrasikan database Teradata ke Amazon Redshift, lihat pola Memigrasikan database Teradata ke Amazon Redshift menggunakan agen ekstraksi data AWS SCT. Migrasi tidak secara otomatis mengonversi frasa Teradata NORMALIZE ke Amazon Redshift SQL. Anda dapat mengonversi ekstensi Teradata ini dengan mengikuti pedoman dalam pola ini.

Alat

Kode

Untuk mengilustrasikan konsep dan fungsionalitas NORMALIZE, pertimbangkan definisi tabel berikut di Teradata:

CREATE TABLE systest.project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          duration      PERIOD(DATE)
     );

Jalankan kode SQL berikut untuk memasukkan data sampel ke dalam tabel:

BEGIN TRANSACTION;

INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  PERIOD(DATE '2010-01-10', DATE '2010-03-20') );
INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  PERIOD(DATE '2010-03-20', DATE '2010-07-15') );

INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  PERIOD(DATE '2010-06-15', DATE '2010-08-18') );
INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  PERIOD(DATE '2010-03-10', DATE '2010-07-20') );

INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  PERIOD(DATE '2020-05-10', DATE '2020-09-20') );

END TRANSACTION;

Hasil:

select * from systest.project order by 1,2,3;
 
 *** Query completed. 4 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.
 
     emp_id  project_name              dept_id  duration
-----------  --------------------  -----------  ------------------------
         10  First Phase                  1000  ('10/01/10', '10/03/20')        
         10  First Phase                  2000  ('10/03/20', '10/07/15')
         10  Second Phase                 2000  ('10/06/15', '10/08/18')
         20  First Phase                  2000  ('10/03/10', '10/07/20')
         20  Second Phase                 1000  ('20/05/10', '20/09/20')

Kasus penggunaan Teradata NORMALIZE

Sekarang tambahkan klausa Teradata NORMALIZE SQL ke pernyataan SELECT:

SELECT NORMALIZE ON MEETS OR OVERLAPS emp_id, duration 
FROM systest.project 
ORDER BY 1,2;

Operasi NORMALIZE ini dilakukan pada satu kolom (emp_id). Untuk emp_id=10, tiga nilai periode yang tumpang tindih dalam durasi menyatu menjadi nilai periode tunggal, sebagai berikut:  

     emp_id  duration
-----------  ------------------------
         10  ('10/01/10', '10/08/18')
         20  ('10/03/10', '10/07/20')
         20  ('20/05/10', '20/09/20')

Pernyataan SELECT berikut melakukan operasi NORMALIZE pada project_name dan dept_id. Perhatikan bahwa daftar SELECT hanya berisi satu kolom PERIODE, durasi.

SELECT NORMALIZE project_name, dept_id, duration 
FROM systest.project;

Output:

project_name              dept_id  duration
--------------------  -----------  ------------------------
First Phase                  1000  ('10/01/10', '10/03/20')
Second Phase                 1000  ('20/05/10', '20/09/20')
First Phase                  2000  ('10/03/10', '10/07/20')
Second Phase                 2000  ('10/06/15', '10/08/18')

SQL setara Amazon Redshift

Amazon Redshift saat ini tidak mendukung tipe data PERIODE dalam tabel. Sebagai gantinya, Anda perlu membagi bidang data Periode Teradata menjadi dua bagian: start_date, end_date, sebagai berikut:  

CREATE TABLE systest.project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          start_date  DATE,
          end_date    DATE
     );

Masukkan data sampel ke dalam tabel:

BEGIN TRANSACTION;
 
INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  DATE '2010-01-10', DATE '2010-03-20' );
INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  DATE '2010-03-20', DATE '2010-07-15');
 
INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  DATE '2010-06-15', DATE '2010-08-18' );
INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  DATE '2010-03-10', DATE '2010-07-20' );
 
INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  DATE '2020-05-10', DATE '2020-09-20' );
 
END TRANSACTION;

Output:

 emp_id | project_name | dept_id | start_date |  end_date
--------+--------------+---------+------------+------------
     10 | First Phase  |    1000 | 2010-01-10 | 2010-03-20
     10 | First Phase  |    2000 | 2010-03-20 | 2010-07-15
     10 | Second Phase |    2000 | 2010-06-15 | 2010-08-18
     20 | First Phase  |    2000 | 2010-03-10 | 2010-07-20
     20 | Second Phase |    1000 | 2020-05-10 | 2020-09-20
(5 rows)

Untuk menulis ulang klausa NORMALIZE Teradata, Anda dapat menggunakan fungsi jendela LAG di Amazon Redshift. Fungsi ini mengembalikan nilai-nilai untuk baris pada offset tertentu di atas (sebelum) baris saat ini di partisi.

Anda dapat menggunakan fungsi LAG untuk mengidentifikasi setiap baris yang memulai periode baru dengan menentukan apakah suatu periode bertemu atau tumpang tindih dengan periode sebelumnya (0 jika ya dan 1 jika tidak). Ketika bendera ini diringkas secara kumulatif, ia menyediakan pengidentifikasi grup yang dapat digunakan di klausa Grup By luar untuk sampai pada hasil yang diinginkan di Amazon Redshift.  

Berikut contoh pernyataan Amazon Redshift SQL yang menggunakan LAG ():

SELECT emp_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project 
ORDER BY 1,2;

Output:

 emp_id | start_date |  end_date  | groupstartflag
--------+------------+------------+----------------
     10 | 2010-01-10 | 2010-03-20 |              1
     10 | 2010-03-20 | 2010-07-15 |              0
     10 | 2010-06-15 | 2010-08-18 |              0
     20 | 2010-03-10 | 2010-07-20 |              1
     20 | 2020-05-10 | 2020-09-20 |              1
(5 rows)

Pernyataan Amazon Redshift SQL berikut hanya dinormalisasi pada kolom emp_id:

SELECT T2.emp_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date
FROM 
( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY emp_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID
FROM ( SELECT emp_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project ) T1
) T2
GROUP BY T2.emp_id, T2.GroupID
ORDER BY 1,2;

Output:  

 emp_id | new_start_date | new_end_date
--------+----------------+------------------------------------
     10 | 2010-01-10     | 2010-08-18
     20 | 2010-03-10     | 2010-07-20
     20 | 2020-05-10     | 2020-09-20
(3 rows)

Pernyataan Amazon Redshift SQL berikut dinormalisasi pada kolom project_name dan dept_id:

SELECT T2.project_name, T2.dept_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date
FROM 
( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY project_name, dept_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID
FROM ( SELECT project_name, dept_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY project_name, dept_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project ) T1
) T2
GROUP BY T2.project_name, T2.dept_id, T2.GroupID
ORDER BY 1,2,3;

Output:

 project_name | dept_id | new_start_date | new_end_date
--------------+---------+----------------+--------------
 First Phase  |    1000 | 2010-01-10     | 2010-03-20
 First Phase  |    2000 | 2010-03-10     | 2010-07-20
 Second Phase |    1000 | 2020-05-10     | 2020-09-20
 Second Phase |    2000 | 2010-06-15     | 2010-08-18
(4 rows)

Epik

Sumber daya terkait

Referensi

Alat

Mitra