[SQL] Une clause méconnue: OVER…PARTITION

Imaginons que je possède deux tables: une table de factures et une table de lignes de facture, liees par une FK qui va bien.
Chacune de ces tables est identifiée par un ID auto-incrémenté de type IDENTITY.

Je cherche a donner a chaque ligne de facture un numéro propre a sa position dans la facture: pour chaque facture j’aurai alors une ligne 1,2,3 etc… en lieu et place des IDs de lignes qui n’ont aucune signification métier.

Nul besoin d’écrire du code type PS ou un affreux curseur! Il existe une instruction pour ce faire, la clause OVER PARTITION, qui permet de définir une sorte de scope de GROUP BY local à une fonction. En l’appliquant sur le ROW_NUMBER() on aura ce que l on souhaite:

SELECT I.INVOICE_ID,ROW_NUMBER()
OVER
(

PARTITION BY I.INVOICE_ID, L.INVOICE_LINE_ID
ORDER BY I.INVOICE_ID, L.INVOICE_LINE_ID
) INVOICE_LINE_NR

FROM INVOICES I
INNER JOIN INVOICE_LINES L

ON I.INVOICE_ID = L.INVOICE_ID

Le résultat est éloquent:

INVOICE_ID INVOICE_LINE_NR
1
1
1
2
2
2
2
3
4
4
1
2
3
1
2
3
4
1
1
2

Dans d’autres cas, cela permet aussi d’appliquer des aggrégations à des scopes différents dans une même unique requête.

A bientot!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s