Possible sans curseur ?

Version imprimable

Imaginons une table contenant les données suivantes (SGBD : SQL serveur) :
Code:

1 2 3 4 5 6 7 8 9 10 DATE NOM LIEU ----------------------------------------------------------- 2008-12-02 09:00:00 Pierre CUISINE 2008-12-02 09:23:00 Pierre CUISINE 2008-12-02 10:32:00 Pierre CUISINE 2008-12-02 10:50:00 Pierre CHAMBRE 2008-12-02 11:32:00 Pierre CHAMBRE 2008-12-02 14:00:00 Pierre CUISINE ...
Est-il possible d'isoler, sans utiliser de curseur, les enregistrements en rouge ? J'ai pensé au min(DATE) avec un group by par LIEU mais dans ce cas, je n'ai aucune trace de l'enregistrement de 14h.

L'objectif serait par exemple de detecter les changements de pièce de Pierre...

Est-ce possible sans curseur ? Merci !

03/12/2008, 01h36
mustapha520

curseur

tu donne plus d explication svp
note : tu veux utilise une fonction , les requetes imbriquees.....
03/12/2008, 10h30
skuatamad

Salut,
Oui regarde les foncytions analytiques
Notemment lag,lead.
03/12/2008, 11h16
Scorpi0

Citation:

Envoyé par skuatamad

Salut,
Oui regarde les foncytions analytiques
Notemment lag,lead.

Pas dans SQL Server, c'est du spécifique Oracle ça.

Sinon va voir là : http://www.developpez.net/forums/f49...ms-sql-server/
03/12/2008, 12h03
skuatamad

Citation:

Envoyé par Scorpi0

Pas dans SQL Server, c'est du spécifique Oracle ça.

Les fonctions analytiques fonctionnent avec sqlserver.
Tu veux dire que lead et lag n'existent pas ?
03/12/2008, 12h30
laurent9257

Elles existent sur la version 2008 mais je suis en 2005...

Le tout est de simuler les fonction lead et lag avec une simple auto-jointure sur un rank :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH myTable AS
(
SELECT to_date('2008-12-02 09:00:00','YYYY-MM-DD HH24:Mi:SS') AS heure,      'Pierre'  AS nom,              'CUISINE' AS piece FROM dual
union ALL SELECT to_date('2008-12-02 09:23:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'CUISINE' FROM dual
union ALL SELECT to_date('2008-12-02 10:32:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'CUISINE' FROM dual
union ALL SELECT to_date('2008-12-02 10:50:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'CHAMBRE' FROM dual
union ALL SELECT to_date('2008-12-02 11:32:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'CHAMBRE' FROM dual
union ALL SELECT to_date('2008-12-02 14:00:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'CUISINE' FROM dual
union ALL SELECT to_date('2008-12-02 15:23:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'SALON' FROM dual
union ALL SELECT to_date('2008-12-02 16:32:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'CUISINE' FROM dual
union ALL SELECT to_date('2008-12-02 17:50:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'CHAMBRE' FROM dual
union ALL SELECT to_date('2008-12-02 18:32:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'CHAMBRE' FROM dual
union ALL SELECT to_date('2008-12-02 19:00:00','YYYY-MM-DD HH24:Mi:SS')      ,'Pierre',                'SDB' FROM dual
union ALL SELECT to_date('2008-12-02 09:23:00','YYYY-MM-DD HH24:Mi:SS')      ,'Paul',                'CUISINE' FROM dual
union ALL SELECT to_date('2008-12-02 10:32:00','YYYY-MM-DD HH24:Mi:SS')      ,'Paul',                'CUISINE' FROM dual
union ALL SELECT to_date('2008-12-02 10:50:00','YYYY-MM-DD HH24:Mi:SS')      ,'Paul',                'CHAMBRE' FROM dual
union ALL SELECT to_date('2008-12-02 11:32:00','YYYY-MM-DD HH24:Mi:SS')      ,'Paul',                'CHAMBRE' FROM dual
union ALL SELECT to_date('2008-12-02 14:00:00','YYYY-MM-DD HH24:Mi:SS')      ,'Paul',                'CUISINE' FROM dual
union ALL SELECT to_date('2008-12-02 15:23:00','YYYY-MM-DD HH24:Mi:SS')      ,'Paul',                'SALON' FROM dual
union ALL SELECT to_date('2008-12-02 16:32:00','YYYY-MM-DD HH24:Mi:SS')      ,'Paul',                'CUISINE' FROM dual
union ALL SELECT to_date('2008-12-02 17:50:00','YYYY-MM-DD HH24:Mi:SS')      ,'Jack',                'CHAMBRE' FROM dual
union ALL SELECT to_date('2008-12-02 18:32:00','YYYY-MM-DD HH24:Mi:SS')      ,'Jack',                'CHAMBRE' FROM dual
union ALL SELECT to_date('2008-12-02 19:00:00','YYYY-MM-DD HH24:Mi:SS')      ,'Jack',                'SDB' FROM dual
),
myTableRank AS
(SELECT rank() over(partition BY nom ORDER BY heure) AS rank, t.* FROM myTable t)
SELECT t2.heure, t2.nom, t2.piece
FROM myTableRank t1 RIGHT OUTER JOIN myTableRank t2 
                    ON t1.rank + 1 = t2.rank 
                    AND t1.nom = t2.nom
WHERE t1.piece != t2.piece OR t1.piece IS NULL
ORDER BY t2.nom, t2.heure

Résultat :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
02/12/2008 17:50:00	Jack	CHAMBRE
02/12/2008 19:00:00	Jack	SDB
02/12/2008 09:23:00	Paul	CUISINE
02/12/2008 10:50:00	Paul	CHAMBRE
02/12/2008 14:00:00	Paul	CUISINE
02/12/2008 15:23:00	Paul	SALON
02/12/2008 16:32:00	Paul	CUISINE
02/12/2008 09:00:00	Pierre	CUISINE
02/12/2008 10:50:00	Pierre	CHAMBRE
02/12/2008 14:00:00	Pierre	CUISINE
02/12/2008 15:23:00	Pierre	SALON
02/12/2008 16:32:00	Pierre	CUISINE
02/12/2008 17:50:00	Pierre	CHAMBRE
02/12/2008 19:00:00	Pierre	SDB

Edit : avec toute la famille c'est plus complet. A priori la fonction de fenêtrage rank() est dispo sur SQL Server 2005.

Salut !

On peut faire encore plus tordu !
Bon, suppose que tu numérotes tes lignes en les groupant par nom, puis en ordonnant par date.
Ca te permet de voir les saut de numéros pour un nom et un lieu donné qui indiquent un changement de lieux
Le problème, c'est d'arriver à voir les sauts sans lire les lignes précédentes...

Consière une deuxième numérotation, où tu groupes cette fois par nom et lieu, puis tu ordonnes par date.
En fait, tu peux t'apercevoir que lorsque tu restes dans une même chambre, la différence entre tes deux numérotations reste constate.

Et donc, il suffit de renuméroter en groupant sur cette différence, et garder le premier de chaque groupe !

Allez, ça donne :
Code:

1 2 3 4 5 6 7 8 9 10 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER(PARTITION BY nom, rk - rk2 ORDER BY Ta_Date) rk3 FROM ( SELECT a.*, ROW_NUMBER() OVER(PARTITION BY nom ORDER BY ta_date) as rk , ROW_NUMBER() OVER (PARTITION BY nom, lieu ORDER BY ta_date) as rk2 FROm TaTable a ) t ) t2 WHERE rk3 = 1
Conclusion : c'est pas si mal que ça la solution du curseur :D

Citation:
Envoyé par laurent9257
Imaginons une table contenant les données suivantes (SGBD : SQL serveur) :
Code:

1 2 3 4 5 6 7 8 9 10 DATE NOM LIEU ----------------------------------------------------------- 2008-12-02 09:00:00 Pierre CUISINE 2008-12-02 09:23:00 Pierre CUISINE 2008-12-02 10:32:00 Pierre CUISINE 2008-12-02 10:50:00 Pierre CHAMBRE 2008-12-02 11:32:00 Pierre CHAMBRE 2008-12-02 14:00:00 Pierre CUISINE ...
Est-il possible d'isoler, sans utiliser de curseur, les enregistrements en rouge ? J'ai pensé au min(DATE) avec un group by par LIEU mais dans ce cas, je n'ai aucune trace de l'enregistrement de 14h.

L'objectif serait par exemple de detecter les changements de pièce de Pierre...

Est-ce possible sans curseur ? Merci !
Voilà une problématique intéressante !
Une petite précision qui a son importance : à y regarder de plus près, tu ne cherches pas que les changements de pièce de chaque personne, mais également leur première pièce visitée.

Une solution sans fonction de classement ni curseur :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 SELECT * FROM MaTable T1 -- on identifie les _changements_ de pièce de chaque personne WHERE EXISTS ( SELECT 1 FROM MaTable T2 WHERE Nom = T1.Nom AND [Date] < T1.[Date] AND Lieu <> T1.Lieu AND NOT EXISTS ( SELECT 1 FROM MaTable WHERE Nom = T1.Nom AND [Date] > T2.[Date] AND [Date] < T1.[Date] AND Lieu = T1.Lieu)) -- on identifie également la _première_ pièce visitée par chaque personne OR NOT EXISTS ( SELECT 1 FROM MaTable WHERE Nom = T1.Nom AND [Date] < T1.[Date]);
Mais que se passe-t-il si, dans la même seconde, la localisation d'une personne est indiquée plusieurs fois, comme dans l'exemple ci-dessous :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 DATE NOM LIEU ----------------------------------------------------------- 2008-12-02 08:00:00 Pierre SALON 2008-12-02 09:00:00 Pierre CUISINE 2008-12-02 09:00:00 Pierre CUISINE 2008-12-02 09:00:00 Pierre CHAMBRE 2008-12-02 09:23:00 Pierre CUISINE 2008-12-02 10:32:00 Pierre CUISINE 2008-12-02 10:50:00 Pierre CHAMBRE 2008-12-02 11:32:00 Pierre CHAMBRE 2008-12-02 14:00:00 Pierre CUISINE ...
Si ce cas est susceptible de se produire, la requête est bien sûr à affiner pour refléter la règle de gestion choisie.
En effet, à 9h00, quelle(s) ligne(s) considère-t-on comme un/des changement(s) de pièce ? Et, à 9h23, considère-t-on que Pierre a changé de pièce ?
Une solution simple pourrait être d'utiliser un id auto-incrémenté pour "départager" les lignes dont l'heure est identique.

Mais on peut aussi considérer, au contraire, que ce cas ne doit jamais se produire. Ce qui signifie qu'un index unique pourrait être appliqué sur le couple ([Date],Nom).

Solution sans fonction de fenêtrage avec une théta jointure :

Code:

1
2
3
4
5
-- la table d'essais
CREATE TABLE T_DEPLACEMENT_DPC
(DPC_DATEHEURE DATETIME,
 DPC_PERSONNE  VARCHAR(16),
 DPC_PIECE     VARCHAR(16))

Les données du jeu d'essais :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 09:00:00', 'Pierre', 'CUISINE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 09:23:00' ,'Pierre', 'CUISINE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 10:32:00' ,'Pierre', 'CUISINE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 10:50:00' ,'Pierre', 'CHAMBRE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 11:32:00' ,'Pierre', 'CHAMBRE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 14:00:00' ,'Pierre', 'CUISINE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 15:23:00' ,'Pierre', 'SALON')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 16:32:00' ,'Pierre', 'CUISINE') 
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 17:50:00' ,'Pierre', 'CHAMBRE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 18:32:00' ,'Pierre', 'CHAMBRE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 19:00:00' ,'Pierre', 'SDB')
 
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 09:23:00' ,'Paul', 'CUISINE') 
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 10:32:00' ,'Paul', 'CUISINE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 10:50:00' ,'Paul', 'CHAMBRE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 11:32:00' ,'Paul', 'CHAMBRE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 14:00:00' ,'Paul', 'CUISINE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 15:23:00' ,'Paul', 'SALON')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 16:32:00' ,'Paul', 'CUISINE') 
 
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 17:50:00' ,'Jack', 'CHAMBRE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 18:32:00' ,'Jack', 'CHAMBRE')
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 19:00:00' ,'Jack', 'SDB')

La solution classique sans fonction de fenêtrage :

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT PS1, PC2, DH2
FROM   (SELECT T1.DPC_PERSONNE AS PS1, 
               T1.DPC_PIECE AS PC1, MAX(T1.DPC_DATEHEURE) AS DH1,  
               T2.DPC_PIECE AS PC2, MIN(T2.DPC_DATEHEURE) AS DH2
        FROM   T_DEPLACEMENT_DPC AS T1
               LEFT OUTER JOIN T_DEPLACEMENT_DPC AS T2
                    ON  T1.DPC_PERSONNE = T2.DPC_PERSONNE
                        AND  T1.DPC_DATEHEURE < T2.DPC_DATEHEURE
        GROUP  BY T1.DPC_PERSONNE, T1.DPC_PIECE, T2.DPC_PIECE) AS T
WHERE  PC1 <> PC2
ORDER  BY 1, 3

A la longue, en fonction de la volumétrie des données, je parierait que cette requête est beaucoup moins gourmande avec les bons index que celle avec la fonction de fenêtrage !

Je vais la conserver dans mes futurs problèmes !

A +

Citation:

Envoyé par SQLpro

La solution classique sans fonction de fenêtrage :

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT PS1, PC2, DH2
FROM   (SELECT T1.DPC_PERSONNE AS PS1, 
               T1.DPC_PIECE AS PC1, MAX(T1.DPC_DATEHEURE) AS DH1,  
               T2.DPC_PIECE AS PC2, MIN(T2.DPC_DATEHEURE) AS DH2
        FROM   T_DEPLACEMENT_DPC AS T1
               LEFT OUTER JOIN T_DEPLACEMENT_DPC AS T2
                    ON  T1.DPC_PERSONNE = T2.DPC_PERSONNE
                        AND  T1.DPC_DATEHEURE < T2.DPC_DATEHEURE
        GROUP  BY T1.DPC_PERSONNE, T1.DPC_PIECE, T2.DPC_PIECE) AS T
WHERE  PC1 <> PC2
ORDER  BY 1, 3

Bonsoir SQLpro,

du fait même de la clause...

Code:

1
2
 
GROUP  BY T1.DPC_PERSONNE, T1.DPC_PIECE, T2.DPC_PIECE

... votre requête ne pourra pas indiquer qu'une personne est passée plusieurs fois de la pièce A à la pièce B.
Pour s'en convaincre, on emploiera le jeu de test suivant...

Code:

1
2
3
4
5
6
7
8
9
10
11
 
DELETE FROM T_DEPLACEMENT_DPC;
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 09:00:00', 'Pierre', 'CUISINE');
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 09:23:00' ,'Pierre', 'CHAMBRE');
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 10:32:00' ,'Pierre', 'CUISINE');
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 10:50:00' ,'Pierre', 'CHAMBRE');
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 11:32:00' ,'Pierre', 'CUISINE');
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 14:00:00' ,'Pierre', 'CHAMBRE');
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 15:23:00' ,'Pierre', 'CUISINE');
INSERT INTO T_DEPLACEMENT_DPC VALUES ('20081202 16:32:00' ,'Pierre', 'CHAMBRE');
-- Dans ce jeu de test, Pierre ne semble penser qu'à manger et dormir ;-)

... et on constatera que votre requête ne renvoie que 2 lignes (1 pour la cuisine, 1 pour la chambre).

Mea culpa !, voici la solution relationnelle :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT T2.*
FROM   (SELECT T.DPC_DATEHEURE, T.DPC_PERSONNE, T.DPC_PIECE, 
               MIN(TAPRES.DPC_DATEHEURE) AS DPC_DATE_HEURE_APRES 
        FROM   T_DEPLACEMENT_DPC AS T
               LEFT OUTER JOIN T_DEPLACEMENT_DPC AS TAPRES
                    ON T.DPC_PERSONNE = TAPRES.DPC_PERSONNE 
                       AND T.DPC_DATEHEURE < TAPRES.DPC_DATEHEURE
        GROUP BY T.DPC_DATEHEURE, T.DPC_PERSONNE, T.DPC_PIECE) AS T1
       INNER JOIN T_DEPLACEMENT_DPC AS T2
             ON T1.DPC_PERSONNE = T2.DPC_PERSONNE
                AND T1.DPC_DATE_HEURE_APRES = T2.DPC_DATEHEURE
WHERE T1.DPC_PIECE <> T2.DPC_PIECE   
ORDER BY DPC_PERSONNE, DPC_DATEHEURE

A +