Convertire la funzionalità Teradata RESET WHEN in Amazon Redshift SQL - Prontuario AWS
RiepilogoPrerequisiti e limitazioniArchitetturaStrumentiEpicheRisorse correlate

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Convertire la funzionalità Teradata RESET WHEN in Amazon Redshift SQL

Fonte: data warehouse Teradata

Obiettivo: Amazon Redshift

Tipo R: Re-architect

Ambiente: produzione

Tecnologie: analisi; database; migrazione

Carico di lavoro: tutti gli altri carichi di lavoro

Servizi AWS: Amazon Redshift

Riepilogo

RESET WHEN è una funzionalità di Teradata utilizzata nelle funzioni analitiche delle finestre SQL. È un'estensione dello standard ANSI SQL. RESET WHEN determina la partizione su cui opera una funzione di finestra SQL in base a una condizione specificata. Se la condizione restituisce TRUE, viene creata una nuova sottopartizione dinamica all'interno della partizione di finestra esistente. Per ulteriori informazioni su RESET WHEN, consultate la documentazione di Teradata.

Amazon Redshift non supporta RESET WHEN nelle funzioni delle finestre SQL. Per implementare questa funzionalità, devi convertire RESET WHEN nella sintassi SQL nativa in Amazon Redshift e utilizzare più funzioni annidate. Questo modello dimostra come utilizzare la funzionalità Teradata RESET WHEN e come convertirla nella sintassi SQL di Amazon Redshift. 

Prerequisiti e limitazioni

Prerequisiti

  • Conoscenza di base del data warehouse Teradata e della sua sintassi SQL

  • Buona conoscenza di Amazon Redshift e della sua sintassi SQL

Architettura

Stack tecnologico di origine

  • Data warehouse Teradata

Stack tecnologico Target

  • Amazon Redshift

Architettura

Per un'architettura di alto livello per la migrazione di un database Teradata ad Amazon Redshift, consulta lo schema Migrare un database Teradata su Amazon Redshift utilizzando gli agenti di estrazione dati AWS SCT. La migrazione non converte automaticamente la frase Teradata RESET WHEN in Amazon Redshift SQL. Puoi convertire questa estensione Teradata seguendo le linee guida nella sezione successiva.

Strumenti

Codice

Per illustrare il concetto di RESET WHEN, si consideri la seguente definizione di tabella in Teradata:

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

Esegui il seguente codice SQL per inserire dati di esempio nella tabella:

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;

La tabella di esempio contiene i seguenti dati:

account_id

month_id

equilibrio

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

Per ogni account, supponiamo che tu voglia analizzare la sequenza di aumenti mensili consecutivi del saldo. Quando il saldo di un mese è inferiore o uguale al saldo del mese precedente, è necessario azzerare il contatore e riavviare il sistema.

Caso d'uso Teradata RESET WHEN

Per analizzare questi dati, Teradata SQL utilizza una funzione finestra con un aggregato annidato e una frase RESET WHEN, come segue:

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

id_mese

equilibrio

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

La query viene elaborata come segue in Teradata:

  1. La funzione di aggregazione SUM (saldo) calcola la somma di tutti i saldi di un determinato conto in un determinato mese.

  2. Controlliamo se il saldo in un determinato mese (per un determinato account) è maggiore del saldo del mese precedente.

  3. Se il saldo aumenta, tracciamo un valore di conteggio cumulativo. Se la condizione RESET WHEN risulta falsa, il che significa che il saldo è aumentato nei mesi successivi, continuiamo ad aumentare il conteggio.

  4. La funzione analitica ordinata ROW_NUMBER () calcola il valore del conteggio. Quando raggiungiamo un mese il cui saldo è inferiore o uguale al saldo del mese precedente, la condizione RESET WHEN risulta vera. In tal caso, iniziamo una nuova partizione e ROW_NUMBER () riavvia il conteggio da 1. Utilizziamo ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING per accedere al valore della riga precedente.

  5. Sottraiamo 1 per assicurarci che il valore del conteggio inizi con 0.

SQL equivalente ad Amazon Redshift

Amazon Redshift non supporta la frase RESET WHEN in una funzione di finestra analitica SQL.  Per ottenere lo stesso risultato, è necessario riscrivere Teradata SQL utilizzando la sintassi SQL nativa di Amazon Redshift e sottoquery annidate, come segue: 

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;

Poiché Amazon Redshift non supporta le funzioni di finestra annidata nella clausola SELECT di una singola istruzione SQL, è necessario utilizzare due sottoquery annidate.

  • Nella sottoquery interna (alias A), viene creato e popolato un indicatore di partizione dinamica (dynamic_part). dynamic_part è impostato su 1 se il saldo di un mese è inferiore o uguale al saldo del mese precedente; in caso contrario, è impostato su 0. 

  • Nel livello successivo (alias B), viene generato un attributo new_dynamic_part come risultato di una funzione della finestra SUM. 

  • Infine, aggiungete new_dynamic_part come nuovo attributo di partizione (partizione dinamica) all'attributo di partizione esistente (account_id) e applicate la stessa funzione di finestra ROW_NUMBER () di Teradata (e meno una). 

Dopo queste modifiche, Amazon Redshift SQL genera lo stesso output di Teradata.

Epiche

AttivitàDescrizioneCompetenze richieste
Crea la tua funzione di finestra Teradata.

Usa gli aggregati annidati e la frase RESET WHEN in base alle tue esigenze.

SQL Developer
Converti il codice in Amazon Redshift SQL.

Per convertire il codice, segui le linee guida nella sezione «Strumenti» di questo modello.

SQL Developer
Esegui il codice in Amazon Redshift.

Crea la tua tabella, carica i dati nella tabella ed esegui il codice in Amazon Redshift.

SQL Developer

Riferimenti

Strumenti

Partner