Discussion :

[laurent9257]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !

[mustapha520]

tu donne plus d explication svp
note : tu veux utilise une fonction , les requetes imbriquees.....

[skuatamad]

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

[Scorpi0]

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/

[skuatamad]

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 ?

[laurent9257]

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

[Scorpi0]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[pacmann]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[J1]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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).

[SQLpro]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[J1]

La solution classique sans fonction de fenêtrage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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).

[SQLpro]

Mea culpa !, voici la solution relationnelle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +