Mot clé Oracle ‘Partition By’ et ‘Row_Number’

PARTITION BY jeux de ségrégations, cela vous permet de travailler (ROW_NUMBER (), COUNT (), SUM (), etc.) indépendamment sur l’ensemble associé.

Dans votre requête, l’ensemble associé était constitué de lignes avec un code cdt.country_code, cdt.account, cdt.currency similaire. Lorsque vous partitionnez ces colonnes et que vous leur appliquez ROW_NUMBER. Les autres colonnes de ces combinaisons / ensembles recevront un numéro séquentiel de ROW_NUMBER.

Mais cette requête est drôle, si votre partition contient des données uniques et que vous y mettez un numéro de ligne, cela produira simplement le même numéro. C’est comme si vous faisiez un ORDER BY sur une partition garantie unique. Exemple, pensez à GUID comme une combinaison unique de cdt.country_code, cdt.account, cdt.currency

newid() produit le GUID, alors que voulez-vous attendre de cette expression?

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

… À droite, toutes les partitions (aucune partitionnée, chaque ligne est partitionnée dans leur propre ligne) Les lignes row_numbers sont toutes définies sur 1

Fondamentalement, vous devez partitionner sur des colonnes non uniques. ORDER BY sur OVER nécessitait que PARTITION BY ait une combinaison non unique, sinon tous les row_numbers deviendraient 1

Un exemple, ce sont vos données:

 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'); 

Alors ceci est analogue à votre requête:

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

Quel sera le résultat de cela?

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

Vous voyez la combinaison de HI HO? Les trois premières lignes ont une combinaison unique, elles sont donc définies sur 1, les lignes B ont le même W, donc différentes ROW_NUMBERS, de même que les lignes HI C.

Maintenant, pourquoi l’ ORDER BY il nécessaire là-bas? Si le développeur précédent veut simplement placer row_number sur des données similaires (par exemple HI B, toutes les données sont BW, BW), il peut simplement faire ceci:

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

Mais hélas, Oracle (et Sql Server aussi) ne permet pas la partition sans ORDER BY ; alors que dans Postgresql, ORDER BY sur PARTITION est facultatif: http://www.sqlfiddle.com/#!1/27821/1

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

Votre ORDER BY sur votre partition semble un peu redondant, pas à cause de la faute du développeur précédent, certaines bases de données ne permettent tout simplement pas la PARTITION sans ORDER BY , il pourrait ne pas trouver une bonne colonne candidate à sortinger. Si les deux colonnes PARTITION BY et les colonnes ORDER BY sont identiques, supprimez simplement ORDER BY, mais comme certaines bases de données ne le permettent pas, vous pouvez simplement faire ceci:

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

Vous ne trouvez pas une bonne colonne à utiliser pour sortinger des données similaires? Vous pourriez tout aussi bien sortinger au hasard, les données partitionnées ayant les mêmes valeurs de toute façon. Vous pouvez utiliser GUID par exemple (vous utilisez newid() pour SQL Server). Ainsi, la même sortie étant faite par le développeur précédent, il est dommage que certaines bases de données ne permettent pas la PARTITION sans ORDER BY

Bien que vraiment, cela m’échappe et je ne peux pas trouver une bonne raison de mettre un nombre sur les mêmes combinaisons (BW, BW dans l’exemple ci-dessus). Cela donne l’impression d’une firebase database avec des données redondantes. Cela m’a rappelé ceci: comment obtenir un enregistrement unique dans la même liste d’enregistrements de la table? Aucune contrainte unique dans la table

Il semble vraiment difficile de voir une PARTITION BY avec la même combinaison de colonnes avec ORDER BY, ne peut pas facilement déduire l’intention du code.

Test en direct: http://www.sqlfiddle.com/#!3/27821/6

---

Mais comme dbaseman l’a également remarqué, il est inutile de partitionner et de commander sur les mêmes colonnes.

Vous avez un dataset comme ceci:

 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'); 

Puis vous PARTITION PAR hi, ho; et puis vous COMMANDEZ salut, ho. Il n’y a pas de sens à numéroter des données similaires 🙂 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; 

Sortie:

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

Voir? Pourquoi faut-il mettre des numéros de ligne sur la même combinaison? Qu’est-ce que vous allez parsingr sur sortingple A, X, double B, Y, double C, Z? 🙂

---

Il vous suffit d’utiliser PARTITION sur une colonne non unique, puis de sortinger la colonne unique de la ou des colonnes non uniques. L’exemple le rendra plus clair:

 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 opère sur une colonne non unique, puis sur chaque colonne partitionnée, vous commandez sur sa colonne unique (ho), ORDER BY ho

Sortie:

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

Cet dataset a plus de sens

Test en direct: http://www.sqlfiddle.com/#!3/d0b44/1

Et ceci est similaire à votre requête avec les mêmes colonnes sur PARTITION BY et ORDER BY:

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

Et c’est la sortie:

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

Voir? pas de sens?

Test en direct: http://www.sqlfiddle.com/#!3/d0b44/3

---

Enfin, cela pourrait être la bonne requête:

 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 

Cela sélectionne le numéro de ligne par code de pays, compte et devise. Ainsi, les lignes avec le code de pays “US”, le compte “XYZ” et la devise “$ USD” recevront chacune un numéro de ligne atsortingbué de 1-n; Il en va de même pour toutes les autres combinaisons de ces colonnes dans le jeu de résultats.

Cette requête est plutôt drôle, car la clause order by ne fait absolument rien. Toutes les lignes de chaque partition ont le même code de pays, le même compte et la même devise. Il n’y a donc pas de classement de points par ces colonnes. Les numéros de ligne ultimes atsortingbués dans cette requête particulière seront donc imprévisibles.

J’espère que cela pourra aider…

J’utilise souvent row_number () comme moyen rapide de supprimer les enregistrements en double de mes instructions select. Ajoutez simplement une clause where. Quelque chose comme…

 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; 

Je sais que c’est un vieux fil mais PARTITION est l’équivalent de GROUP BY et non ORDER BY. ORDER BY dans cette fonction est. . . COMMANDÉ PAR. C’est juste un moyen de créer un caractère unique par redondance en ajoutant un numéro de séquence. Ou vous pouvez éliminer les autres enregistrements redondants par la clause WHERE lors du référencement de la colonne aliasée de la fonction. Cependant, DISTINCT dans l’instruction SELECT accomplirait probablement la même chose à cet égard.