Mengonversi fitur Teradata RESET WHEN ke Amazon Redshift SQL - AWS Prescriptive Guidance
RingkasanPrasyarat dan batasanArsitekturAlatEpikSumber daya terkait

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Mengonversi fitur Teradata RESET WHEN ke Amazon Redshift SQL

Po Hong, Amazon Web Services

Ringkasan

RESET WHEN adalah fitur Teradata yang digunakan dalam fungsi jendela analitik SQL. Ini adalah perpanjangan dari standar ANSI SQL. RESET WHEN menentukan partisi di mana fungsi jendela SQL beroperasi berdasarkan beberapa kondisi tertentu. Jika kondisi dievaluasi ke TRUE, sub-partisi dinamis baru dibuat di dalam partisi jendela yang ada. Untuk informasi selengkapnya tentang RESET KAPAN, lihat dokumentasi Teradata.

Amazon Redshift tidak mendukung RESET WHEN dalam fungsi jendela SQL. Untuk mengimplementasikan fungsionalitas ini, Anda harus mengonversi RESET WHEN ke sintaks SQL asli di Amazon Redshift, dan menggunakan beberapa fungsi bersarang. Pola ini menunjukkan bagaimana Anda dapat menggunakan fitur Teradata RESET WHEN dan bagaimana Anda dapat mengonversinya ke sintaks Amazon Redshift SQL. 

Prasyarat dan batasan

Prasyarat

  • Pengetahuan dasar gudang data Teradata dan sintaks SQL-nya

  • Pemahaman yang baik tentang Amazon Redshift dan sintaks SQL-nya

Arsitektur

Tumpukan teknologi sumber

  • Gudang data Teradata

Tumpukan teknologi target

  • Amazon Redshift

Arsitektur

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 RESET WHEN ke Amazon Redshift SQL. Anda dapat mengonversi ekstensi Teradata ini dengan mengikuti panduan di bagian berikutnya.

Alat

Kode

Untuk mengilustrasikan konsep RESET WHEN, pertimbangkan definisi tabel berikut di Teradata:

create table systest.f_account_balance                                
( account_id integer NOT NULL,
  month_id integer,
  balance integer )
unique primary index (account_id, month_id);

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

BEGIN TRANSACTION;
Insert Into systest.f_account_balance values (1,1,60);
Insert Into systest.f_account_balance values (1,2,99);
Insert Into systest.f_account_balance values (1,3,94);
Insert Into systest.f_account_balance values (1,4,90);
Insert Into systest.f_account_balance values (1,5,80);
Insert Into systest.f_account_balance values (1,6,88);
Insert Into systest.f_account_balance values (1,7,90);
Insert Into systest.f_account_balance values (1,8,92);
Insert Into systest.f_account_balance values (1,9,10);
Insert Into systest.f_account_balance values (1,10,60);
Insert Into systest.f_account_balance values (1,11,80);
Insert Into systest.f_account_balance values (1,12,10);
END TRANSACTION;

Tabel sampel memiliki data berikut:

account_id

bulan_id

keseimbangan

1

1

60

1

2

99

1

3

94

1

4

90

1

5

80

1

6

88

1

7

90

1

8

92

1

9

10

1

10

60

1

11

80

1

12

10

Untuk setiap akun, katakanlah Anda ingin menganalisis urutan kenaikan saldo bulanan berturut-turut. Ketika saldo satu bulan kurang dari, atau sama dengan, saldo bulan sebelumnya, persyaratannya adalah mengatur ulang penghitung ke nol dan memulai kembali.

Teradata RESET SAAT menggunakan kasus

Untuk menganalisis data ini, Teradata SQL menggunakan fungsi jendela dengan agregat bersarang dan frase RESET WHEN, sebagai berikut:

SELECT account_id, month_id, balance,
 ( ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY month_id
RESET WHEN balance <= SUM(balance) over (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ) -1 ) as balance_increase
FROM systest.f_account_balance
ORDER BY 1,2;

Output:

 account_id

bulan_id

keseimbangan

balance_increase

1

1

60

0

1

2

99

1

1

3

94

0

1

4

90

0

1

5

80

0

1

6

88

1

1

7

90

2

1

8

92

3

1

9

10

0

1

10

60

1

1

11

80

2

1

12

10

0

Kueri diproses sebagai berikut di Teradata:

  1. Fungsi agregat SUM (saldo) menghitung jumlah semua saldo untuk akun tertentu dalam bulan tertentu.

  2. Kami memeriksa untuk melihat apakah saldo dalam bulan tertentu (untuk akun tertentu) lebih besar dari saldo bulan sebelumnya.

  3. Jika saldo meningkat, kami melacak nilai hitungan kumulatif. Jika kondisi RESET WHEN dievaluasi menjadi false, yang berarti saldo telah meningkat selama beberapa bulan berturut-turut, kami terus meningkatkan hitungan.

  4. Fungsi analitis berurutan ROW_NUMBER () menghitung nilai hitungan. Ketika kita mencapai bulan yang saldo kurang dari, atau sama dengan, saldo bulan sebelumnya, kondisi RESET WHEN dievaluasi menjadi benar. Jika demikian, kita memulai partisi baru dan ROW_NUMBER () memulai ulang hitungan dari 1. Kami menggunakan ROWS BETWEEN 1 PRECEDING DAN 1 PRECEDING untuk mengakses nilai baris sebelumnya.

  5. Kami mengurangi 1 untuk memastikan bahwa nilai hitungan dimulai dengan 0.

SQL setara Amazon Redshift

Amazon Redshift tidak mendukung frase RESET WHEN dalam fungsi jendela analitik SQL.  Untuk menghasilkan hasil yang sama, Anda harus menulis ulang Teradata SQL menggunakan sintaks SQL asli Amazon Redshift dan sub-kueri bersarang, sebagai berikut: 

SELECT account_id, month_id, balance,
   (ROW_NUMBER() OVER(PARTITION BY account_id, new_dynamic_part ORDER BY month_id) -1) as balance_increase
FROM
( SELECT account_id, month_id, balance, prev_balance,
SUM(dynamic_part) OVER (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As new_dynamic_part
FROM ( SELECT account_id, month_id, balance,
SUM(balance) over (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as prev_balance,
(CASE When balance <= prev_balance Then 1 Else 0 END) as dynamic_part
FROM systest.f_account_balance ) A
) B
ORDER BY 1,2;

Karena Amazon Redshift tidak mendukung fungsi jendela bersarang di klausa SELECT dari satu pernyataan SQL, Anda harus menggunakan dua sub-kueri bersarang.

  • Dalam sub-query bagian dalam (alias A), indikator partisi dinamis (dynamic_part) dibuat dan diisi. dynamic_part diatur ke 1 jika saldo satu bulan kurang dari atau sama dengan saldo bulan sebelumnya; jika tidak, itu diatur ke 0. 

  • Di lapisan berikutnya (alias B), atribut new_dynamic_part dihasilkan sebagai hasil dari fungsi jendela SUM. 

  • Akhirnya, Anda menambahkan new_dynamic_part sebagai atribut partisi baru (partisi dinamis) ke atribut partisi yang ada (account_id) dan menerapkan fungsi jendela ROW_NUMBER () yang sama seperti di Teradata (dan minus satu). 

Setelah perubahan ini, Amazon Redshift SQL menghasilkan output yang sama dengan Teradata.

Epik

TugasDeskripsiKeterampilan yang dibutuhkan
Buat fungsi jendela Teradata Anda.

Gunakan agregat bersarang dan frase RESET WHEN sesuai dengan kebutuhan Anda.

Developer SQL
Konversi kode ke Amazon Redshift SQL.

Untuk mengonversi kode Anda, ikuti panduan di bagian “Alat” dari pola ini.

Developer SQL
Jalankan kode di Amazon Redshift.

Buat tabel Anda, muat data ke dalam tabel, dan jalankan kode Anda di Amazon Redshift.

Developer SQL

Referensi

Alat

Mitra