Parola chiave Oracle ‘Partition By’ e ‘Row_Number’

Ho una query SQL scritta da qualcun altro e sto cercando di capire cosa fa. Qualcuno può spiegare cosa fanno qui le parole chiave Partition By e Row_Number e dare un semplice esempio di esso in azione, nonché il motivo per cui si vorrebbe usarlo?

Un esempio di partizione di:

 (SELECT cdt.*, ROW_NUMBER () OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency ORDER BY cdt.country_code, cdt.account, cdt.currency) seq_no FROM CUSTOMER_DETAILS cdt); 

Ho visto alcuni esempi online, sono un po ‘troppo profondi.

Grazie in anticipo!

PARTITION BY set separati, consente di essere in grado di lavorare (ROW_NUMBER (), COUNT (), SUM (), ecc.) Sul set correlato in modo indipendente.

Nella tua query, il set correlato comprendeva righe con codice cdt.country_docs, cdt.account, cdt.currency. Quando esegui la partizione su queste colonne e applichi ROW_NUMBER su tali colonne. Quelle altre colonne su tale combinazione / insieme riceveranno il numero sequenziale da ROW_NUMBER

Ma quella query è divertente, se la tua partizione è composta da alcuni dati univoci e tu metti un numero_riga su di essa, produrrà solo lo stesso numero. È come se tu facessi un ORDER BY su una partizione che è garantita per essere unica. Esempio, pensa al GUID come combinazione unica di cdt.country_code, cdt.account, cdt.currency

newid() produce GUID, quindi cosa ti aspetti da questa espressione?

 select hi,ho, row_number() over(partition by newid() order by hi,ho) from tbl; 

… Bene, tutto il partizionato (nessuno era partizionato, ogni riga è partizionata nella propria riga) righe ‘row_numbers sono tutti impostati su 1

Fondamentalmente, dovresti partizionare su colonne non univoche. ORDER BY on OVER aveva bisogno della PARTITION BY per avere una combinazione non univoca, altrimenti tutti i row_numbers diventeranno 1

Un esempio, questo sono i tuoi dati:

 create table tbl(hi varchar, ho varchar); insert into tbl values ('A','X'), ('A','Y'), ('A','Z'), ('B','W'), ('B','W'), ('C','L'), ('C','L'); 

Quindi questo è analogo alla tua richiesta:

 select hi,ho, row_number() over(partition by hi,ho order by hi,ho) from tbl; 

Quale sarà l’output di quello?

 HI HO COLUMN_2 AX 1 AY 1 AZ 1 BW 1 BW 2 CL 1 CL 2 

Vedi la combinazione di HI HO? Le prime tre righe hanno una combinazione unica, quindi sono impostate su 1, le righe B hanno lo stesso W, quindi ROW_NUMBERS differenti, allo stesso modo con le righe HI C.

Ora, perché è necessario l’ ORDER BY ? Se lo sviluppatore precedente desidera semplicemente inserire un numero_riga su dati simili (ad es. HI B, tutti i dati sono BW, BW), può semplicemente eseguire questa operazione:

 select hi,ho, row_number() over(partition by hi,ho) from tbl; 

Ma ahimè, Oracle (e anche Sql Server) non consente la partizione senza ORDER BY ; mentre in Postgresql, ORDER BY su PARTITION è facoltativo: http://www.sqlfiddle.com/#!1/27821/1

 select hi,ho, row_number() over(partition by hi,ho) from tbl; 

Il tuo ORDER BY sulla tua partizione sembra un po ‘ridondante, non a causa della precedente colpa dello sviluppatore, alcuni database non permettono a PARTITION senza ORDER BY , potrebbe non riuscire a trovare una buona colonna candidata su cui ordinare. Se entrambe le colonne PARTITION BY e ORDER BY sono uguali, rimuovi solo ORDER BY, ma poiché alcuni database non lo consentono, puoi semplicemente eseguire questa operazione:

 SELECT cdt.*, ROW_NUMBER () OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency ORDER BY newid()) seq_no FROM CUSTOMER_DETAILS cdt 

Non riesci a trovare una buona colonna da utilizzare per l’ordinamento di dati simili? Si potrebbe anche ordinare casualmente, i dati partizionati hanno comunque gli stessi valori . Ad esempio, è ansible utilizzare GUID (si utilizza newid() per SQL Server). In modo che abbia lo stesso output realizzato da uno sviluppatore precedente, è un peccato che alcuni database non consentano PARTITION senza ORDER BY

Anche se in realtà, mi sfugge e non riesco a trovare una buona ragione per mettere un numero sulle stesse combinazioni (BW, BW nell’esempio sopra). Sta dando l’impressione che il database abbia dati ridondanti. In qualche modo mi ha ricordato questo: come ottenere un record univoco dallo stesso elenco di record della tabella? Nessun vincolo univoco nella tabella

Sembra davvero arcano vedere una PARTITION BY con la stessa combinazione di colonne con ORDER BY, non può facilmente dedurre l’intento del codice.

Test in diretta: http://www.sqlfiddle.com/#!3/27821/6


Ma come notato anche da dbaseman, è inutile partizionare e ordinare sulle stesse colonne.

Hai un set di dati come questo:

 create table tbl(hi varchar, ho varchar); insert into tbl values ('A','X'), ('A','X'), ('A','X'), ('B','Y'), ('B','Y'), ('C','Z'), ('C','Z'); 

Allora tu SEI PARTE DI ciao, ho; e poi ORDINA PER Ciao, ho. Non ha senso numerare dati simili 🙂 http://www.sqlfiddle.com/#!3/29ab8/3

 select hi,ho, row_number() over(partition by hi,ho order by hi,ho) as nr from tbl; 

Produzione:

 HI HO ROW_QUERY_A AX 1 AX 2 AX 3 BY 1 BY 2 CZ 1 CZ 2 

Vedere? Perché è necessario inserire i numeri di riga sulla stessa combinazione? Cosa analizzerai sulla tripla A, X, sul doppio B, Y, sul doppio C, Z? 🙂


Devi solo usare PARTITION su una colonna non univoca, quindi ordinare sulla colonna uniqueing delle colonne non univoche . L’esempio lo renderà più chiaro:

 create table tbl(hi varchar, ho varchar); insert into tbl values ('A','D'), ('A','E'), ('A','F'), ('B','F'), ('B','E'), ('C','E'), ('C','D'); select hi,ho, row_number() over(partition by hi order by ho) as nr from tbl; 

PARTITION BY hi opera su una colonna non univoca, quindi su ogni colonna partizionata, ordini sulla sua colonna univoca (ho), ORDER BY ho

Produzione:

 HI HO NR AD 1 AE 2 AF 3 BE 1 BF 2 CD 1 CE 2 

Quel set di dati ha più senso

Test in diretta: http://www.sqlfiddle.com/#!3/d0b44/1

E questo è simile alla tua query con le stesse colonne su PARTITION BY e ORDER BY:

 select hi,ho, row_number() over(partition by hi,ho order by hi,ho) as nr from tbl; 

E questa è l’uscita:

 HI HO NR AD 1 AE 1 AF 1 BE 1 BF 1 CD 1 CE 1 

Vedere? senza senso?

Test in diretta: http://www.sqlfiddle.com/#!3/d0b44/3


Finalmente questa potrebbe essere la domanda giusta:

 SELECT cdt.*, ROW_NUMBER () OVER (PARTITION BY cdt.country_code, cdt.account -- removed: cdt.currency ORDER BY -- removed: cdt.country_code, cdt.account, cdt.currency) -- keep seq_no FROM CUSTOMER_DETAILS cdt 

Seleziona il numero di riga per codice paese, account e valuta. Quindi, le righe con il prefisso “US”, l’account “XYZ” e la valuta “$ USD” riceveranno un numero di riga assegnato da 1-n; lo stesso vale per ogni altra combinazione di quelle colonne nel set di risultati.

Questa query è divertente, perché la clausola order by non fa assolutamente nulla. Tutte le righe in ogni partizione hanno lo stesso codice paese, account e valuta, quindi non è ansible ordinare da tali colonne. I numeri di riga finale assegnati in questa particolare query saranno quindi imprevedibili.

Spero possa aiutare…

Uso spesso row_number () come metodo rapido per eliminare record duplicati dalle mie istruzioni select. Basta aggiungere una clausola where. Qualcosa di simile a…

 select a,b,rn from (select a, b, row_number() over (partition by a,b order by a,b) as rn from table) where rn=1; 

So che questo è un thread vecchio ma PARTITION è l’equiv di GROUP BY non ORDER BY. ORDER BY in questa funzione è. . . ORDINATO DA. È solo un modo per creare unicità fuori dalla ridondanza aggiungendo un numero di sequenza. Oppure puoi eliminare gli altri record ridondanti dalla clausola WHERE quando fai riferimento alla colonna con l’alias per la funzione. Tuttavia, DISTINCT nell’istruzione SELECT probabilmente realizzerebbe la stessa cosa a tale riguardo.