Archives pour la catégorie Interrogation

[Concours Sql Pro] un dans dix ( 3 étoiles ).

Article publié avec l’autorisation de l’auteur sql pro.

Cet article est une solution donnée à un problème de joe selko que j’avais résolu en janvier 2007.

Enoncé : ici

Si vous le souhaitez, vous pouvez vous rendre sur le site de Sql Pro, vous cherchez une solution de votre coté et vous regardez si votre résultat est différent de la solution que je propose.
Lire la suite

FONCTION DE FENETRAGE ET DE RANKING.

Syntaxe :
ROW_NUMBER() OVER([PARTITION BY value_expression ] ORDER BY value_expression)
Définition :
ROW_NUMBER()
Retourne le numéro séquentiel d’une ligne d’une partition d’un jeu de résultats, en commençant à 1 pour la première ligne de chaque partition
PARTITION BY
Divise l’ensemble de résultats en partitions. La fonction de fenêtre est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition.
toute l’information à l’adresse :
http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk

Un exemple d’utilisation :
Fournir les 3 premiers traffic d’un identificateur de cellule par semaine.

CREATE TABLE TRAFFIC_CELLULES(identificateur_de_cellule CHAR(3),traffic INT,date DATETIME);  <br />
 <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',100,'01/01/2008') <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',200,'01/01/2008') <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',225,'01/01/2008') <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',400,'01/01/2008') <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',150,'01/02/2008') <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',175,'01/02/2008') <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',200,'01/02/2008') <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('B',100,'01/01/2008') <br />
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('B',100,'01/03/2008')  <br />
 <br />
WITH selection <br />
AS <br />
( <br />
SELECT identificateur_de_cellule,traffic,datepart(ww,date) as sem,ROW_NUMBER() OVER (PARTITION BY identificateur_de_cellule,datepart(ww,date) ORDER BY traffic) as OrderRank FROM TRAFFIC_CELLULES <br />
) <br />
SELECT * FROM selection where OrderRank <= 3 <br />
 <br />
Resultat: <br />
 <br />
A      100    1    1 <br />
A      200    1    2 <br />
A      225    1    3 <br />
A      150    5    1 <br />
A      175    5    2 <br />
A      200    5    3 <br />
B      100    1    1 <br />
B      100    9    1

Expression de table courante.

Une expression de table courante ( CTE pour common Table Expression ) ressemble beaucoup à une vue non persistante.

Syntaxe :

WITH nom_CTE ( nom_colonne,… )
AS
(
requete
)
SELECT * FROM nom_CTE

Exemple de code :

WITH selection AS (
SELECT identificateur_de_cellule,traffic,datepart(ww,date) as sem,ROW_NUMBER() OVER (PARTITION BY identificateur_de_cellule,datepart(ww,date) ORDER BY traffic) as OrderRank FROM TRAFFIC_CELLULES
)
SELECT * FROM selection where OrderRank < salespersonid='sp.SalesPersonID'
UNION ALL
selectionner
)
SELECT * FROM simpleRecursive

Exemple :

– Creation d’une table avec les données sous forme d’arbre
CREATE TABLE Employee_Tree( Employee_NM nvarchar(50), employee_ID int Primary Key, reportsTo int )
INSERT INTO Employee_Tree VALUES (‘Richard’,1,NULL)
INSERT INTO Employee_Tree VALUES(‘Stephen’,2,1)
INSERT INTO Employee_Tree VALUES(‘Clemens’,3,2)
INSERT INTO Employee_Tree VALUES(‘Malek’,4,2)
INSERT INTO Employee_Tree VALUES(‘Goksin’,5,4)

–Requete recursive
WITH SimpleRecursive(Employee_NM,Employee_ID,ReportsTo) AS
( SELECT Employee_NM, Employee_ID, ReportsTo FROM Employee_Tree WHERE Employee_ID = 2
UNION ALL
SELECT p.Employee_NM, p.Employee_ID, p.reportsTo FROM Employee_Tree p INNER JOIN SimpleRecursive A ON A.Employee_ID = P.ReportsTo
)
SELECT sr.Employee_NM AS Employee, et.Employee_NM As Boss FROM SimpleRecursive sr INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID

lien : http://sqlpro.developpez.com/cours/sqlserver/cte-recursives/

Récursivité, CTE et génération de table.

Bonjour,

Samedi, en me promenant sur le site GUSS, il m’a été démontré une technique remarquable : l’utilisation des CTE pour générer une table. Notez l’option MAXRECURSION qui permet de dépasser 100 récursions par défaut. Encore une utilisation magnifique des CTE sur SQL Serveur 2005. A rapprochez de l’article sur GO [count]!

WITH CTETemps AS  
  (  
    SELECT cast('1990-01-01' AS datetime) Date  
    UNION ALL  
    SELECT Date + 1  
    FROM CTETemps  
    WHERE Date + 1 < '2031-01-01'  
  )  
SELECT top 10 * FROM CTETEMPS ORDER BY Date desc OPTION (MAXRECURSION 0);