Denali CTP3 – Clause OVER étendue

Depuis plusieurs versions de SQL Server nous disposons de la clause OVER qui permet de définir une partition et un ordre dans un jeu de résultat et d’appliquer une fonction à ce jeu de résultat.

Les fonctions existantes

Associé avec la fonction ROW_NUMBER, cette fonction permet d’ajouter une colonne permettant d’identifier le numéro de ligne de l’enregistrement. SSMS proposait dans l’interface graphique le numéro de ligne, avec cette instruction, on l’ajoute dans le recordset en sortie.

USE AdventureWorksDenali
GO

-- liste des commande savec numéro de ligne
-- triées par date de commande
SELECT SalesOrderID,CustomerID,TotalDue,
	ROW_NUMBER() OVER(ORDER BY OrderDate) as rid
FROM Sales.SalesOrderHeader

image

Le jeu de données est trié en fonction du ORDER BY de la clause OVER().

Il est aussi possible d’utiliser les fonctions de ranking RANK et DENSE_RANK

-- liste des produits en stock
-- triés par quantité descendante
SELECT ProductID,Quantity,
	RANK() OVER(ORDER BY Quantity desc) as [Rank]
FROM Production.ProductInventory

image

On obtient donc un rang en fonction de la quantité commandée. Notez que si vous appliquer un TOP (N) à cette requête (exemple un TOP (12)), je vous suggère d’utiliser le mot clé WITH TIES qui permet d’afficher tous les enregistrements de rang 11 et pas seulement les enregistrements 483 et 389 …

Le problème de la discontinuité du rang peut aussi se poser. Doit on considérer que l’enregistrement dur la ligne 15 (ProductID 367) et il au rang 15 ou bien au rang 12 … Est-ce que mon application veut des valeurs continues pour le rang. C’est votre application qui décide. D’où l’existence de la fonction DENSE_RANK qui permet d’obtenir des valeurs continues.

-- liste des produits en stock avec rang (dense)
-- triés par quantité descendante
SELECT ProductID,Quantity,
	DENSE_RANK() OVER(ORDER BY Quantity desc) as [Rank]
FROM Production.ProductInventory

 

image

Il est possible de créer des partitions au sein du jet de données pour la clause OVER.

-- liste des produits en stock avec rang
-- groupé par entrepot
-- triés par quantité descendante
SELECT ProductID,LocationID,Quantity,
	RANK() OVER(PARTITION BY LocationID ORDER BY Quantity desc) as [Rank]
FROM Production.ProductInventory

image

A chaque changement de partition (d’entrepôt), le rang est réinitialisé à 1.

Il existe la possibilité de “diviser” le jeu de données en plusieurs groupes de “volumétrie” identique”. C’est la fonction NTILE. L’exemple ci dessous montre une répartition en 3 groupes.

SELECT ProductID,Quantity,
	NTILE(3) OVER(ORDER BY Quantity desc) as [Groupe]
FROM Production.ProductInventory

image

 

Voyons à présent quelles sont els nouveautés offertes par Denali.

Les fonctions FIRST_VALUE et LAST_VALUE

La fonction FIRST_VALUE() permet de sélectionner la première valeur de la fenêtre d’enregistrements, en fonction de la clause Order By qui définit l’ordre, et donc la première valeur.

-- client par client, la liste des commandes
-- avec la date de dommande et le numéro de la première commande passée
SELECT CustomerID,SalesOrderID,OrderDate,
       FIRST_VALUE(SalesOrderID) OVER (PARTITION BY CustomerID 
                               ORDER BY OrderDate) as FirstOne
FROM Sales.SalesOrderHeader

image

Le résultat obtenu nous montre bien pour le client 11000, ses 3 commandes ainsi que la colonne contenant le numéro de la première commande.

Je m’attendais à retrouver un comportement identique avec LAST_VALUE(). La fonction devant renvoyer la dernière valeur de la fenêtre d’enregistrements.

-- client par client, la liste des commandes
-- avec la date de dommande et le numéro de la dernière commande passée
SELECT CustomerID,SalesOrderID,OrderDate,
          LAST_VALUE(SalesOrderID) OVER (PARTITION BY CustomerID 
                                    ORDER BY OrderDate) as LastOne
FROM Sales.SalesOrderHeader

image

En fait, la fenêtre de données semble changer à chaque nouvel enregistrement… Le fonctionnement n’est pas tout a fait similaire à FIRST_VALUE. Je ne pense pas que ce soit un Bug. C’est seulement la compréhension de ce que fait la fonction qui ne doit pas être correcte, en tout cas, je ne dois pas avoir compris ce qu’il faut. Mais je vais essayer de me soigner et de creuser ce point. Je ferais un update du post à ce moment là.

Il suffit donc de transformer la requête de la manière suivante pour obtenir le résultat escompté :

SELECT CustomerID,SalesOrderID,OrderDate,
          FIRST_VALUE(SalesOrderID) OVER (PARTITION BY CustomerID 
                                      ORDER BY OrderDate DESC) as LastOne
FROM Sales.SalesOrderHeader

image

Il est tout a fait possible dans la même requête de cumuler l’appel des deux fonctions.

Les fonctions LEAD() et LAG()

Ces fonctions permettent de se déplacer de la valeur du paramètre offset dans la fenêtre des données de la clause OVER. Pr exemple, un offset de 1 permet de renvoyer la valeur du prochain enregistrement (LEAD) ou de l’enregistrement précédent (LAG) en fonction du Order By. L’exemple suivant permet de calculer le nombre de jours séparant deux commandes d’un même client. Le but, bien sur, étant de vérifier si cet intervalle augmente ou diminue, si les clients sont fidèles …

-- Calculer l'intervalle entre les commandes d'un client
SELECT CustomerID,SalesOrderID,OrderDate,
	LEAD(OrderDate,1) OVER(PARTITION BY CustomerID ORDER BY OrderDate) as [NextOrderDate],
	DateDiff(dd,OrderDate,
                         LEAD(OrderDate,1) OVER(PARTITION BY CustomerID ORDER BY OrderDate) ) 
    as [Interval(days)]
FROM Sales.SalesOrderHeader
where CustomerID between 29800 and 29900

 

image

 

Les fonctions analytiques

Denali apporte aussi son lot de nouvelles fonctions analytiques. Vu mon niveau dans ce domaine, je ne m’aventurerais pas à faire une analyse détaillée sur le sujet. Autant la démo que les commentaires risqueraient d’être erronés. Les spécialistes en mathématiques apprécieront probablement ces nouvelles fonctions :

 

Pour ceux qui voudraient aller encore plus loin dans l’exploration de la clause OVER, sachez qu’il et possible de définir plus précisément la fenêtre de données. A la suite du ORDER BY de la clause OVER, il suffit de rajouter les mots clé ROWS BETWEEN x PRECEDING AND y FOLLOWING. Cela permet de sélectionner uniquement les x lignes et y lignes qui précèdent et suivent l’enregistrement courant. x et y sont des valeurs numériques et peuvent être remplacées par UNBOUNDED pour “tout” sélectionner. il et aussi possible d’utiliser le mot clé CURRENT ROW.

La clause OVER est puissante et offre bon nombre de possibilité. Bonnes requêtes …

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué , . Ajoutez ce permalien à vos favoris.

4 commentaires pour Denali CTP3 – Clause OVER étendue

  1. Ping : SQL Server Denali CTP3 – Funções Analíticas: FIRST_VALUE e LAST_VALUE « SQL From Hell.com

  2. Ping : SQL Server Denali CTP 3 – Resumo « SQL From Hell.com

  3. Ping : SQL Server Denali CTP 3 – Resumo « SQL From Hell.com

  4. Ping : SQL Server 2012–vos attentes, mes démos | Christophe LAPORTE – Consultant SQL Server

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