SQL Server 2005 a introduit les fonctions de classement (ROW_NUMBER, RANK, DENSERANK et NTILE). Ces fonctions de classement s\u2019appuient notamment sur la clause OVER, utilis\u00e9e pour la notion de \u00ab fen\u00eatrage \u00bb. Mais saviez-vous que cette clause est aussi utilisable avec les simples agr\u00e9gats ?
\nTout d\u2019abord, un petit tour des fonctions de classement.
\nROW_NUMBER permet de num\u00e9roter les enregistrements, sans notion d\u2019ex-\u00e6quo. Par exemple, pour classer les livres des diff\u00e9rentes biblioth\u00e8ques d\u2019une ville par ordre d\u2019entr\u00e9e au catalogue :<\/p>\n
SELECT\r\n\tTitre,\r\n\tAuteur,\r\n\tROW_NUMBER() OVER (PARTITION BY IdBibliotheque ORDER BY DateHeureEnregistrement)\r\nFROM Livres<\/pre>\nRANK permet de classer des enregistrements en tenant compte de la notion d\u2019ex-\u00e6quo, avec donc potentiellement des trous dans la num\u00e9rotation. Par exemple, pour les meilleurs joueurs d\u2019un tournoi, class\u00e9s par cat\u00e9gorie :<\/p>\n
SELECT\r\n\tNom,\r\n\tPrenom,\r\n\tRANK() OVER (PARTITION BY Categorie ORDER BY Score)\r\nFROM Participants<\/pre>\nDENSE_RANK, tout comme RANK, classe en tenant compte de la notion d\u2019ex-\u00e6quo, mais cette fois-ci sans trou dans la num\u00e9rotation.
\nEnfin, NTILE permet de d\u00e9couper un groupe en N parties \u00e9gales (autant que faire se peut). Par exemple, pour d\u00e9terminer quatre groupes de niveau pour les filles et autant pour les gar\u00e7ons pour une \u00e9preuve, on peut utiliser :<\/p>\nSELECT\r\n\tNom,\r\n\tPrenom,\r\n\tNTILE(4) OVER (PARTITION BY Sexe ORDER BY Score)\r\nFROM Participants<\/pre>\nL\u2019aide en ligne (http:\/\/msdn.microsoft.com\/fr-fr\/library\/ms189798.aspx<\/a>) vous apportera toutes les informations que vous d\u00e9sirez sur ce sujet.
\nInt\u00e9ressons-nous maintenant de plus pr\u00e8s \u00e0 cette clause ORDER. Nous l\u2019avons vue avec les fonctions de classement utilis\u00e9e (ou en tout cas utilisable) sous sa forme compl\u00e8te, avec PARTITION BY facultatif et ORDER BY obligatoire. Mais il existe une autre syntaxe, dans laquelle ORDER BY n\u2019est tout simplement pas pr\u00e9sent, et PARTITION BY est au contraire sinon obligatoire du point de vue de la syntaxe pure, du moins vivement recommand\u00e9 ; il s\u2019agit des agr\u00e9gats.
\nL\u2019id\u00e9e est de pouvoir appliquer l\u2019agr\u00e9gat sur des sous parties de l\u2019ensemble des enregistrements, tout comme cela se ferait avec la clause GROUP BY. La diff\u00e9rence avec GROUP BY r\u00e9side dans le fait que cette derni\u00e8re oblige chaque colonne retourn\u00e9e soit \u00e0 faire partie de la cl\u00e9 de regroupement, soit \u00e0 \u00eatre le r\u00e9sultat d\u2019une fonction d\u2019agr\u00e9gation.
\nPar exemple, pour obtenir les clients de diff\u00e9rentes agences bancaires avec la part que repr\u00e9sente le solde de leur compte par rapport \u00e0 l\u2019ensemble des clients de l\u2019agence, on peut dans un premier temps calculer la somme des soldes pour chaque agence, puis diviser chaque solde de client par la somme correspondant \u00e0 son agence :<\/p>\nWITH SommeAgence (IdAgence, Somme) AS\r\n\t(SELECT IdAgence, SUM(Solde)\r\n\t\tFROM Clients\r\n\t\tGROUP BY IdAgence)\r\nSELECT\r\n\tc.Nom,\r\n\tc.Prenom,\r\n\tc.Solde,\r\n\t100.*c.Solde\/sa.Somme\r\nFROM Clients c\r\n\tjoin SommeAgence sa on sa.IdAgence=c.IdAgence<\/pre>\nMais gr\u00e2ce \u00e0 la clause OVER qui peut \u00eatre utilis\u00e9e avec les fonctions d\u2019agr\u00e9gations, la syntaxe est nettement r\u00e9duite, et surtout la requ\u00eate est optimis\u00e9e.<\/p>\n
SELECT\r\n\tc.Nom,\r\n\tc.Prenom,\r\n\tc.Solde,\r\n\t100.*c.Solde\/SUM(c.Solde) OVER (PARTITION BY IdAgence)\r\nFROM Clients c<\/pre>\nPersonnellement, j\u2019avoue que j\u2019appr\u00e9cie cette syntaxe. Libre \u00e0 chacun d\u2019en penser ce qu\u2019il souhaite et de l\u2019adapter \u00e0 ses propres cas particuliers\u2026<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server 2005 a introduit les fonctions de classement (ROW_NUMBER, RANK, DENSERANK et NTILE). Ces fonctions de classement s\u2019appuient notamment sur la clause OVER, utilis\u00e9e pour la notion de \u00ab fen\u00eatrage \u00bb. Mais saviez-vous que cette clause est aussi utilisable … Continuer la lecture