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

**Bernardos** · 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

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.

**Bernardos** · 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

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 ?

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

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager