[SQL2008] Obtenir table2 avec chaque ligne contenant cinq valeurs consécutives d'un champ dans Tab1 [Résolu]

lbernard · 07/07/2011, 16h18

Bon si vous trouvez mieux comme titre je veux bien changer (celui-ci ne m'inspire pas trop)
J'explique mon problème.
J'ai une table Event dont voici le code

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
 
CREATE TABLE Event (IDEvent Int Identity(1,1), 
                    IdTypeEvent Int,
                    TimeEvent DateTime,
                    )
ALTER TABLE Event ADD  CONSTRAINT [IDEvent] PRIMARY KEY CLUSTERED 
(
	[IDEvent] ASC
)                    
 
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/08/2011 08:45:25',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/08/2011 08:45:28',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 121, Convert(DATETIME,'07/08/2011 08:46',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/08/2011 08:47:12',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/08/2011 08:47:15',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 17, Convert(DATETIME,'07/08/2011 08:48',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 98, Convert(DATETIME,'07/08/2011 08:50',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 122, Convert(DATETIME,'07/08/2011 08:53',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/08/2011 08:54:27',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/08/2011 08:54:30',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 1, Convert(DATETIME,'07/08/2011 08:55:42',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 14, Convert(DATETIME,'07/08/2011 08:55:42',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 31, Convert(DATETIME,'07/08/2011 08:55:42',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 12, Convert(DATETIME,'07/08/2011 08:55:42',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/08/2011 08:55:42',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/08/2011 08:57:46',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/08/2011 08:57:49',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 1, Convert(DATETIME,'07/08/2011 08:59:59',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 14, Convert(DATETIME,'07/08/2011 08:59:59',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 31, Convert(DATETIME,'07/08/2011 08:59:59',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 12, Convert(DATETIME,'07/08/2011 08:59:59',121)
INSERT INTO Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/08/2011 08:59:59',121)

la table est triée sur TimeEvent. L'objectif est de mettre en évidence des séquences d'évenements qui se répéteraient. genre après l'evenement 5 on a toujours le 7 puis le 2.

Donc l'idée c'est de recréer une table EventSequence qui reprendrait 5 Evenements consecutifs(par exemple) avec le time du 1 et du 5 ce qui donnerait

Code :

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

1
2
3
4
5
6
 
Event1 Event2 Event3 Event4 Event5 FirstTime           LastTime
  42     3     121    42      3    01/04/2011 08:21:27   01/04/2011 08:29:14
  3      121   42     3       72   01/04/2011 hh:mm:ss   01/04/2011 hh:mm:ss 
  121    42    3      72      10   01/04/2011 hh:mm:ss   01/04/2011 hh:mm:ss 
 etc...

Bon j'ai plusieurs pistes de réflexion mais ne suis convaincu par aucune.
Utiliser PIVOT(Mais comment?), parcourir avec 2 curseurs la table(là aussi le comment m'échappe encore).

En gros pour résumé la vraie question est : comment faire ceci le plus simplement(et efficacement) possible?

Bien à vous,

Loic

SQLpro · 07/07/2011, 16h33

il faut faire une CTE avec un ROW_NUMBER sur le critère temps, puis, sur cette CTE, 5 jointures pour chacune des 5 colonnes sur le RW_NUMBER - 1.

Sans le DDL de vos tables, difficile d'en dire plus.

Merci de respectez la charte de postage : http://www.developpez.net/forums/d96...vement-poster/

A +

iberserk · 08/07/2011, 08h45

Bonjour,
Quelle est la finalité de la requête? si c'est pour de l'affichage chargez vous données classiquement et faite votre présentation dans votre code client (via instanciation d'un objet par exemple...).

Qu'arrivera t'il le jour ou vous aurez un sixième event?

Waldar · 08/07/2011, 10h25

Citation:

Envoyé par SQLpro

il faut faire une CTE avec un ROW_NUMBER sur le critère temps, puis, sur cette CTE, 5 jointures pour chacune des 5 colonnes sur le RW_NUMBER - 1.

Ou plus simple, un seul group by !

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
; WITH Evenement (IDEvent, IdTypeEvent, Time) AS
(
SELECT 1,  42, cast('01/04/2011 08:21:27' AS datetime) union ALL
SELECT 2,   3, cast('01/04/2011 08:21:29' AS datetime) union ALL
SELECT 3, 121, cast('01/04/2011 08:25:35' AS datetime) union ALL
SELECT 4,  42, cast('01/04/2011 08:29:12' AS datetime) union ALL
SELECT 5,   3, cast('01/04/2011 08:29:14' AS datetime)
)
  SELECT max(case IDEvent%5 when 1 then IdTypeEvent end) AS Event1
       , max(case IDEvent%5 when 2 then IdTypeEvent end) AS Event2
       , max(case IDEvent%5 when 3 then IdTypeEvent end) AS Event3
       , max(case IDEvent%5 when 4 then IdTypeEvent end) AS Event4
       , max(case IDEvent%5 when 0 then IdTypeEvent end) AS Event5
       , min(Time)                                       AS FirstTime
       , max(Time)                                       AS LastTime
    FROM Evenement
GROUP BY floor((IDEvent - 1) / 5)
 
 
 
Event1      Event2      Event3      Event4      Event5      FirstTime               LastTime
----------- ----------- ----------- ----------- ----------- ----------------------- -----------------------
42          3           121         42          3           2011-01-04 08:21:27.000 2011-01-04 08:29:14.000

Il manque peut-être quelques données pour affiner la requête.
Au passage, Time est un mot réservé que vous ne devriez pas utiliser en tant que nom de colonne.

lbernard · 08/07/2011, 16h07

Tout d'abord merci de vos réponses rapides.

Voci ma solution

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
SELECT  E1.IdTypeEvent AS Event1,
            E2.IdTypeEvent AS Event2,	
            E3.IdTypeEvent AS Event3,
            E4.IdTypeEvent AS Event4,
            E5.IdTypeEvent AS Event5,
            E1.TimeEvent AS FirstTimeEvent,
            E5.TimeEvent AS FirstTimeEvent,
            DateDiff(millisecond,E1.TimeEvent, E5.TimeEvent) AS TimeInterval  
 
FROM Event  AS E1 FULL OUTER JOIN 
        Event  AS E2 ON E1.IdEvent = E2.IdEvent-1 FULL OUTER JOIN
        Event  AS E3 ON E2.IdEvent = E3.IdEvent-1 FULL OUTER JOIN
        Event  AS E4 ON E3.IdEvent = E4.IdEvent-1 FULL OUTER JOIN 
        Event  AS E5 ON E4.IdEvent = E5.IdEvent-1
WHERE E1.IdEvent IS NOT NULL

et le 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
15
16
17
18
19
20
21
22
42	3	121	42	3	2011-07-08 08:45:25.000	2011-07-08 08:47:15.000	110000
3	121	42	3	17	2011-07-08 08:45:28.000	2011-07-08 08:48:00.000	152000
121	42	3	17	98	2011-07-08 08:46:00.000	2011-07-08 08:50:00.000	240000
42	3	17	98	122	2011-07-08 08:47:12.000	2011-07-08 08:53:00.000	348000
3	17	98	122	42	2011-07-08 08:47:15.000	2011-07-08 08:54:27.000	432000
17	98	122	42	3	2011-07-08 08:48:00.000	2011-07-08 08:54:30.000	390000
98	122	42	3	1	2011-07-08 08:50:00.000	2011-07-08 08:55:42.000	342000
122	42	3	1	14	2011-07-08 08:53:00.000	2011-07-08 08:55:42.000	162000
42	3	1	14	31	2011-07-08 08:54:27.000	2011-07-08 08:55:42.000	75000
3	1	14	31	12	2011-07-08 08:54:30.000	2011-07-08 08:55:42.000	72000
1	14	31	12	13	2011-07-08 08:55:42.000	2011-07-08 08:55:42.000	0
14	31	12	13	42	2011-07-08 08:55:42.000	2011-07-08 08:57:46.000	124000
31	12	13	42	3	2011-07-08 08:55:42.000	2011-07-08 08:57:49.000	127000
12	13	42	3	1	2011-07-08 08:55:42.000	2011-07-08 08:59:59.000	257000
13	42	3	1	14	2011-07-08 08:55:42.000	2011-07-08 08:59:59.000	257000
42	3	1	14	31	2011-07-08 08:57:46.000	2011-07-08 08:59:59.000	133000
3	1	14	31	12	2011-07-08 08:57:49.000	2011-07-08 08:59:59.000	130000
1	14	31	12	13	2011-07-08 08:59:59.000	2011-07-08 08:59:59.000	0
14	31	12	13	NULL	2011-07-08 08:59:59.000	NULL	NULL
31	12	13	NULL	NULL	2011-07-08 08:59:59.000	NULL	NULL
12	13	NULL	NULL	NULL	2011-07-08 08:59:59.000	NULL	NULL
13	NULL	NULL	NULL	NULL	2011-07-08 08:59:59.000	NULL	NULL

Je n'ai pas eu besoin du RowNumber vu qu'il aurait donné exactement la meme chose que le IdEvent.

Merci pour votre aide

Loic

iberserk · 08/07/2011, 16h09

Quid des performances?
Pouvez vous fournir les statistics IO et TIME?

aieeeuuuuu · 08/07/2011, 16h23

Bonjour

Citation:

IDEvent Int Identity(1,1),

Vous pouvez donc avoir des "trous" dans les séquence d'id (violation de contrainte lors d'une insertion par exemple), et votre requête ne fonctionnera plus !

Avez vous testé (et surtout comparé les perfs) la requête de Waldar ?

lbernard · 08/07/2011, 17h07

@Iberseek : Voici le résultat de ta requete

Code :

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

1
2
3
4
5
42	3	121	42	3	2011-07-08 08:45:25.000	2011-07-08 08:47:15.000
17	98	122	42	3	2011-07-08 08:48:00.000	2011-07-08 08:54:30.000
1	14	31	12	13	2011-07-08 08:55:42.000	2011-07-08 08:55:42.000
42	3	1	14	31	2011-07-08 08:57:46.000	2011-07-08 08:59:59.000
12	13	NULL	NULL	NULL	2011-07-08 08:59:59.000	2011-07-08 08:59:59.000

ce qui n'est pas du tout le résultat attendu

Citation:

Citation:
IDEvent Int Identity(1,1),

Vous pouvez donc avoir des "trous" dans les séquence d'id (violation de contrainte lors d'une insertion par exemple), et votre requête ne fonctionnera plus !

Je ne peux pas expliquer en quelques mots le contexte compliqué dans lequel je travaille chez mon client actuel mais je peux te garantir que L'id est fiable car il s'agit d'une table intermédiaire générée par mes soins juste avant d'y appliquer la requete

Code :

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

Quelle est la finalité de la requête?

Un ou plusieurs rapports SSRS.

Code :

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

chargez vous données classiquement et faite votre présentation dans votre code client (via instanciation d'un objet par exemple...).

Impossible en SSRS à ma connaissance

Code :

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

Qu'arrivera t'il le jour ou vous aurez un sixième event?

ca n'arrivera pas

plus exactement, je suis en train de rédiger une analyse fonctionelle sur différents rapports qui pourraient etre implémentés afin d'améliorer le réglage de capteurs sur des machines de production. Pour l'instant je suis seul décideur et je décide 5. Mais il est vrai que les demandeurs souhaiteront soit plus soit moins (de 3 à 10 à mon avis). j'adapterai ma requete mais une fois la solution en place ca ne bougera plus car ce facteur sera renseigné dans les spécifications comme non modifiable et ici on respecte à la lettre ce genre de document.

Bien à vous ,

Loïc

iberserk · 09/07/2011, 10h15

Citation:

Iberseek : Voici le résultat de ta requete

Je n'ai fourni aucune requête?

lbernard · 09/07/2011, 10h56

@iberserk : oui j'étais fatigué

c'est la requete de Waldar qui ne donne pas le bon résultat . désolé

Waldar · 09/07/2011, 20h30

Je ne vois pas pourquoi la requête que je vous ai proposé ne fonctionne pas si vous garantissez la continuité des Id.
Pouvez-vous fournir un peu plus de données histoire que je regarde ?

lbernard · 11/07/2011, 09h40

Bonjour Waldar,
Voici le contenu de ma table de départ (à partir de la requete de création/insertion ci-dessus)

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
IDEvent	IdTypeEvent	TimeEvent
1	42	2011-07-08 08:45:25.000
2	3	2011-07-08 08:45:28.000
3	121	2011-07-08 08:46:00.000
4	42	2011-07-08 08:47:12.000
5	3	2011-07-08 08:47:15.000
6	17	2011-07-08 08:48:00.000
7	98	2011-07-08 08:50:00.000
8	122	2011-07-08 08:53:00.000
9	42	2011-07-08 08:54:27.000
10	3	2011-07-08 08:54:30.000
11	1	2011-07-08 08:55:42.000
12	14	2011-07-08 08:55:42.000
13	31	2011-07-08 08:55:42.000
14	12	2011-07-08 08:55:42.000
15	13	2011-07-08 08:55:42.000
16	42	2011-07-08 08:57:46.000
17	3	2011-07-08 08:57:49.000
18	1	2011-07-08 08:59:59.000
19	14	2011-07-08 08:59:59.000
20	31	2011-07-08 08:59:59.000
21	12	2011-07-08 08:59:59.000
22	13	2011-07-08 08:59:59.000

Voici le résultat complet de ma requete (et c'est effectivement ce que je veux comme 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
15
16
17
18
19
20
21
22
23
Event1	Event2	Event3	Event4	Event5	FirstTimeEvent	FirstTimeEvent	TimeInterval
42	3	121	42	3	2011-07-08 08:45:25.000	2011-07-08 08:47:15.000	110000
3	121	42	3	17	2011-07-08 08:45:28.000	2011-07-08 08:48:00.000	152000
121	42	3	17	98	2011-07-08 08:46:00.000	2011-07-08 08:50:00.000	240000
42	3	17	98	122	2011-07-08 08:47:12.000	2011-07-08 08:53:00.000	348000
3	17	98	122	42	2011-07-08 08:47:15.000	2011-07-08 08:54:27.000	432000
17	98	122	42	3	2011-07-08 08:48:00.000	2011-07-08 08:54:30.000	390000
98	122	42	3	1	2011-07-08 08:50:00.000	2011-07-08 08:55:42.000	342000
122	42	3	1	14	2011-07-08 08:53:00.000	2011-07-08 08:55:42.000	162000
42	3	1	14	31	2011-07-08 08:54:27.000	2011-07-08 08:55:42.000	75000
3	1	14	31	12	2011-07-08 08:54:30.000	2011-07-08 08:55:42.000	72000
1	14	31	12	13	2011-07-08 08:55:42.000	2011-07-08 08:55:42.000	0
14	31	12	13	42	2011-07-08 08:55:42.000	2011-07-08 08:57:46.000	124000
31	12	13	42	3	2011-07-08 08:55:42.000	2011-07-08 08:57:49.000	127000
12	13	42	3	1	2011-07-08 08:55:42.000	2011-07-08 08:59:59.000	257000
13	42	3	1	14	2011-07-08 08:55:42.000	2011-07-08 08:59:59.000	257000
42	3	1	14	31	2011-07-08 08:57:46.000	2011-07-08 08:59:59.000	133000
3	1	14	31	12	2011-07-08 08:57:49.000	2011-07-08 08:59:59.000	130000
1	14	31	12	13	2011-07-08 08:59:59.000	2011-07-08 08:59:59.000	0
14	31	12	13	NULL	2011-07-08 08:59:59.000	NULL	NULL
31	12	13	NULL	NULL	2011-07-08 08:59:59.000	NULL	NULL
12	13	NULL	NULL	NULL	2011-07-08 08:59:59.000	NULL	NULL
13	NULL	NULL	NULL	NULL	2011-07-08 08:59:59.000	NULL	NULL

et le résultat complet de la tienne

Code :

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

1
2
3
4
5
6
Event1	Event2	Event3	Event4	Event5	FirstTime	LastTime
42	3	121	42	3	2011-07-08 08:45:25.000	2011-07-08 08:47:15.000
17	98	122	42	3	2011-07-08 08:48:00.000	2011-07-08 08:54:30.000
1	14	31	12	13	2011-07-08 08:55:42.000	2011-07-08 08:55:42.000
42	3	1	14	31	2011-07-08 08:57:46.000	2011-07-08 08:59:59.000
12	13	NULL	NULL	NULL	2011-07-08 08:59:59.000	2011-07-08 08:59:59.000

elle ne donne que 1 ligne sur 5

Bien à toi

Loic

Waldar · 11/07/2011, 10h52

Merci pour les compléments d'information.
Je n'avais pas bien compris votre besoin en fait, j'avais raté le décalage systématique de 1.

En m'appuyant sur la même syntaxe que précédemment, je crée un simili produit cartésien qui je pense sera moins coûteux que toutes les jointures en FULL OUTER JOIN. Cela reste néanmoins à tester sur une plus grande volumétrie.
Ici je m'appuie sur une CTE pour que la solution fonctionne partout, mais si vous avez une table d'entiers c'est encore mieux :

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
; WITH NB_Query (nb) AS
(
SELECT 1 union ALL SELECT 2 union ALL SELECT 3 union ALL SELECT 4 union ALL SELECT 5
)
  SELECT max(case Evt.IDEvent%5 when (Nq.nb+0)%5 then Evt.IdTypeEvent end) AS Event1
       , max(case Evt.IDEvent%5 when (Nq.nb+1)%5 then Evt.IdTypeEvent end) AS Event2
       , max(case Evt.IDEvent%5 when (Nq.nb+2)%5 then Evt.IdTypeEvent end) AS Event3
       , max(case Evt.IDEvent%5 when (Nq.nb+3)%5 then Evt.IdTypeEvent end) AS Event4
       , max(case Evt.IDEvent%5 when (Nq.nb+4)%5 then Evt.IdTypeEvent end) AS Event5
       , min(Evt.TimeEvent)                                                AS FirstTime
       , max(Evt.TimeEvent)                                                AS LastTime
       , DateDiff(millisecond,min(Evt.TimeEvent), max(Evt.TimeEvent))      AS TimeInterval  
    FROM Event AS Evt
         INNER JOIN NB_Query AS Nq
           ON Nq.nb < Evt.IDEvent + 1
GROUP BY (Evt.IDEvent - Nq.nb) / 5
       , Nq.nb
ORDER BY FirstTime ASC
       , (Evt.IDEvent - Nq.nb) / 5 ASC
       , Nq.nb ASC
 
 
Event1      Event2      Event3      Event4      Event5      FirstTime               LastTime                TimeInterval
----------- ----------- ----------- ----------- ----------- ----------------------- ----------------------- ------------
42          3           121         42          3           2011-07-08 08:45:25.000 2011-07-08 08:47:15.000 110000
3           121         42          3           17          2011-07-08 08:45:28.000 2011-07-08 08:48:00.000 152000
121         42          3           17          98          2011-07-08 08:46:00.000 2011-07-08 08:50:00.000 240000
42          3           17          98          122         2011-07-08 08:47:12.000 2011-07-08 08:53:00.000 348000
3           17          98          122         42          2011-07-08 08:47:15.000 2011-07-08 08:54:27.000 432000
17          98          122         42          3           2011-07-08 08:48:00.000 2011-07-08 08:54:30.000 390000
98          122         42          3           1           2011-07-08 08:50:00.000 2011-07-08 08:55:42.000 342000
122         42          3           1           14          2011-07-08 08:53:00.000 2011-07-08 08:55:42.000 162000
42          3           1           14          31          2011-07-08 08:54:27.000 2011-07-08 08:55:42.000 75000
3           1           14          31          12          2011-07-08 08:54:30.000 2011-07-08 08:55:42.000 72000
1           14          31          12          13          2011-07-08 08:55:42.000 2011-07-08 08:55:42.000 0
14          31          12          13          42          2011-07-08 08:55:42.000 2011-07-08 08:57:46.000 124000
31          12          13          42          3           2011-07-08 08:55:42.000 2011-07-08 08:57:49.000 127000
12          13          42          3           1           2011-07-08 08:55:42.000 2011-07-08 08:59:59.000 257000
13          42          3           1           14          2011-07-08 08:55:42.000 2011-07-08 08:59:59.000 257000
42          3           1           14          31          2011-07-08 08:57:46.000 2011-07-08 08:59:59.000 133000
3           1           14          31          12          2011-07-08 08:57:49.000 2011-07-08 08:59:59.000 130000
1           14          31          12          13          2011-07-08 08:59:59.000 2011-07-08 08:59:59.000 0
14          31          12          13          NULL        2011-07-08 08:59:59.000 2011-07-08 08:59:59.000 0
31          12          13          NULL        NULL        2011-07-08 08:59:59.000 2011-07-08 08:59:59.000 0
12          13          NULL        NULL        NULL        2011-07-08 08:59:59.000 2011-07-08 08:59:59.000 0
13          NULL        NULL        NULL        NULL        2011-07-08 08:59:59.000 2011-07-08 08:59:59.000 0

Il existe néanmoins une différence entre les deux requêtes, sur la valeur LastTime lorsque des données commencent à être nulles (cf. fin de résultat).
C'est facilement changeable d'une requête à l'autre, ça dépend de ce que vous voulez faire.

lbernard · 11/07/2011, 14h31

ah oui de fait les 2 requetes ne sont pas du meme monde

j'ai fait le test sur une table contenant un peu plus de 20000 lignes
Voici pour la mienne.

Code :

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

1
2
SQL Server Execution Times:
   CPU time = 507171 ms,  elapsed time = 574423 ms.

Voici pour la tienne

Code :

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

1
2
SQL Server Execution Times:
   CPU time = 421 ms,  elapsed time = 807 ms.

Pourrais-tu un peu expliquer ta requete?

Merci d'avance bien à toi,

Loïc

Waldar · 11/07/2011, 15h23

Le point de départ de ma requête c'est de faire un regroupement tous les cinq Id (je reprends celui de ma première requête), et de jouer sur le modulo 5 pour afficher les colonnes.

Voilà le résultat de votre table en répartissant les IdTypeEvent sur les cinq colonnes :

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
33
SELECT Evt.IDEvent
     , case Evt.IDEvent%5 when 1 then Evt.IdTypeEvent end AS Event1
     , case Evt.IDEvent%5 when 2 then Evt.IdTypeEvent end AS Event2
     , case Evt.IDEvent%5 when 3 then Evt.IdTypeEvent end AS Event3
     , case Evt.IDEvent%5 when 4 then Evt.IdTypeEvent end AS Event4
     , case Evt.IDEvent%5 when 0 then Evt.IdTypeEvent end AS Event5
     , (Evt.IDEvent - 1) / 5                              AS GrpId 
  FROM Event AS Evt
 
IDEvent     Event1      Event2      Event3      Event4      Event5      GrpId
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           42          NULL        NULL        NULL        NULL        0
2           NULL        3           NULL        NULL        NULL        0
3           NULL        NULL        121         NULL        NULL        0
4           NULL        NULL        NULL        42          NULL        0
5           NULL        NULL        NULL        NULL        3           0
6           17          NULL        NULL        NULL        NULL        1
7           NULL        98          NULL        NULL        NULL        1
8           NULL        NULL        122         NULL        NULL        1
9           NULL        NULL        NULL        42          NULL        1
10          NULL        NULL        NULL        NULL        3           1
11          1           NULL        NULL        NULL        NULL        2
12          NULL        14          NULL        NULL        NULL        2
13          NULL        NULL        31          NULL        NULL        2
14          NULL        NULL        NULL        12          NULL        2
15          NULL        NULL        NULL        NULL        13          2
16          42          NULL        NULL        NULL        NULL        3
17          NULL        3           NULL        NULL        NULL        3
18          NULL        NULL        1           NULL        NULL        3
19          NULL        NULL        NULL        14          NULL        3
20          NULL        NULL        NULL        NULL        31          3
21          12          NULL        NULL        NULL        NULL        4
22          NULL        13          NULL        NULL        NULL        4

Vous remarquerez la dernière colonne "(Evt.IDEvent - 1) / 5", c'est une simple division par 5, ça permet donc de définir des tranches de 5.

Comme il y a beaucoup de trous, je réalise un agrégat (on appelle cela un pivot) pour tout redéfinir par ligne :

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
  SELECT max(case Evt.IDEvent%5 when 1 then Evt.IdTypeEvent end) AS Event1
       , max(case Evt.IDEvent%5 when 2 then Evt.IdTypeEvent end) AS Event2
       , max(case Evt.IDEvent%5 when 3 then Evt.IdTypeEvent end) AS Event3
       , max(case Evt.IDEvent%5 when 4 then Evt.IdTypeEvent end) AS Event4
       , max(case Evt.IDEvent%5 when 0 then Evt.IdTypeEvent end) AS Event5
       , (Evt.IDEvent - 1) / 5                                   AS GrpId
    FROM Event AS Evt
GROUP BY (Evt.IDEvent - 1) / 5
 
Event1      Event2      Event3      Event4      Event5      GrpId
----------- ----------- ----------- ----------- ----------- -----------
42          3           121         42          3           0
17          98          122         42          3           1
1           14          31          12          13          2
42          3           1           14          31          3
12          13          NULL        NULL        NULL        4

Aux dates près, c'était la première requête que je vous avais proposé.

J'ai compris ce matin qu'il fallait créer un décalage de toutes les données une par une.

Pour ce faire, il faut donc que je parte de l'IdEvent 2, et que celui ci prenne place dans la première colonne.

Je repars de cette dernière requête, juste pour gérer ces nouvelles lignes :

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
  SELECT max(case Evt.IDEvent%5 when 2 then Evt.IdTypeEvent end) AS Event1
       , max(case Evt.IDEvent%5 when 3 then Evt.IdTypeEvent end) AS Event2
       , max(case Evt.IDEvent%5 when 4 then Evt.IdTypeEvent end) AS Event3
       , max(case Evt.IDEvent%5 when 0 then Evt.IdTypeEvent end) AS Event4
       , max(case Evt.IDEvent%5 when 1 then Evt.IdTypeEvent end) AS Event5
       , (Evt.IDEvent - 2) / 5                                   AS GrpId
    FROM Event AS Evt
   WHERE Evt.IDEvent > 1
GROUP BY (Evt.IDEvent - 2) / 5
 
 
Event1      Event2      Event3      Event4      Event5      GrpId
----------- ----------- ----------- ----------- ----------- -----------
3           121         42          3           17          0
98          122         42          3           1           1
14          31          12          13          42          2
3           1           14          31          12          3
13          NULL        NULL        NULL        NULL        4

On remarque que pour avoir les données dans les bonnes colonnes j'ai du modifier les valeurs de comparaison dans les cases, la valeur du moins dans le GrpId en plus du filtre sur l'IdEvent.

Donc à partir de ces constats, soit je faisais cinq requêtes distinctes toutes en UNION ALL, soit je créais un produit cartésien presque complet pour créer les nouvelles valeurs. J'ai choisi cette solution.

Le produit cartésien est simple à faire :

Code :

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

1
2
3
4
5
6
7
; WITH NB_Query (nb) AS
(
SELECT 1 union ALL SELECT 2 union ALL SELECT 3 union ALL SELECT 4 union ALL SELECT 5
)
SELECT *
  FROM Event AS Evt
       CROSS JOIN NB_Query AS Nq

Ensuite j'ai joué sur la valeur du Nb pour gérer les groupes et les décalages pour aboutir à la requête finale.

L'avantage de cette requête par rapport à la votre, c'est qu'elle ne lit qu'une seule fois les données de la table Event, la votre devait le faire cinq fois.
Il faut les statistiques IO avec le nombres de lectures pour s'en rendre compte.

07/07/2011, 16h33	#2
SQLpro Rédacteur/Modérateur Frédéric BROUARD Expert SGBDR & SQL Inscription : mai 2002 Messages : 10 954 Détails du profil Informations personnelles : Nom : Frédéric BROUARD Localisation : France Informations professionnelles : Activité : Expert SGBDR & SQL Secteur : Conseil Informations forums : Inscription : mai 2002 Messages : 10 954 Points : 17 774 Points : 17 774	il faut faire une CTE avec un ROW_NUMBER sur le critère temps, puis, sur cette CTE, 5 jointures pour chacune des 5 colonnes sur le RW_NUMBER - 1. Sans le DDL de vos tables, difficile d'en dire plus. Merci de respectez la charte de postage : http://www.developpez.net/forums/d96...vement-poster/ A + __________________ Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/ *Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.* Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
	00

08/07/2011, 08h45	#3
iberserk Membre Expert Bruno IGNACE Architecte de base de données Inscription : novembre 2004 Messages : 1 299 Détails du profil Informations personnelles : Nom : Bruno IGNACE Âge : 30 Localisation : France, Gironde (Aquitaine) Informations professionnelles : Activité : Architecte de base de données Secteur : High Tech - Éditeur de logiciels Informations forums : Inscription : novembre 2004 Messages : 1 299 Points : 2 282 Points : 2 282	Bonjour, Quelle est la finalité de la requête? si c'est pour de l'affichage chargez vous données classiquement et faite votre présentation dans votre code client (via instanciation d'un objet par exemple...). Qu'arrivera t'il le jour ou vous aurez un sixième event? __________________ Prendre conscience, c'est transformer le voile qui recouvre la lumière en miroir.
	00

08/07/2011, 16h09	#6
iberserk Membre Expert Bruno IGNACE Architecte de base de données Inscription : novembre 2004 Messages : 1 299 Détails du profil Informations personnelles : Nom : Bruno IGNACE Âge : 30 Localisation : France, Gironde (Aquitaine) Informations professionnelles : Activité : Architecte de base de données Secteur : High Tech - Éditeur de logiciels Informations forums : Inscription : novembre 2004 Messages : 1 299 Points : 2 282 Points : 2 282	Quid des performances? Pouvez vous fournir les statistics IO et TIME? __________________ Prendre conscience, c'est transformer le voile qui recouvre la lumière en miroir.
	00

09/07/2011, 20h30	#11
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 5 684 Détails du profil Informations personnelles : Nom : Fabien Âge : 34 Localisation : France, Yvelines (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : Arts - Culture Informations forums : Inscription : septembre 2008 Messages : 5 684 Points : 10 433 Points : 10 433	Je ne vois pas pourquoi la requête que je vous ai proposé ne fonctionne pas si vous garantissez la continuité des Id. Pouvez-vous fournir un peu plus de données histoire que je regarde ? __________________ Email : http://scr.im/waldar
	00

09/07/2011, 10h56	#10
lbernard Nouveau Membre du Club Loïc Bernard Consultant en Business Intelligence Inscription : avril 2008 Messages : 44 Détails du profil Informations personnelles : Nom : Loïc Bernard Âge : 34 Localisation : Belgique Informations professionnelles : Activité : Consultant en Business Intelligence Secteur : Conseil Informations forums : Inscription : avril 2008 Messages : 44 Points : 32 Points : 32	@iberserk : oui j'étais fatigué c'est la requete de Waldar qui ne donne pas le bon résultat . désolé
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email