[SQL2008][T-SQL] obtenir un total cumule et un pourcentage cumule

lbernard · 14/07/2011, 16h32

Bonjour,
Toujours sur ma problématqiue des Event je reviens vers vous pour un nouveau problème.

J'ai besoin à partir de ma table event d'obtenir un total cumulé et un pourcentage cumulé.
L'objectif étant de faire un graphe dans SSRS.
Je m'explique :

Voici le contenu de ma table Event (code de création et d'insertion plus bas)
Pour info le IdTypeEvent est l'identifiant du type d'evenement par exemple le 42 correspond à un evenement de type"surchaufe de la cuve", 3 correspond à "Pression trop Elevée"

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
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
111	169	2011-07-09 08:45:25.000
112	169	2011-07-09 08:45:28.000
113	169	2011-07-09 08:46:00.000
114	142	2011-07-09 08:47:12.000
115	13	2011-07-09 08:47:15.000
116	117	2011-07-09 08:48:00.000
117	198	2011-07-09 08:50:00.000
118	1122	2011-07-09 08:53:00.000
119	142	2011-07-09 08:54:27.000
120	13	2011-07-09 08:54:30.000
121	11	2011-07-09 08:55:42.000
122	114	2011-07-09 08:55:42.000
123	131	2011-07-09 08:55:42.000
124	112	2011-07-09 08:55:42.000
125	113	2011-07-09 08:55:42.000
126	142	2011-07-09 08:57:46.000
127	13	2011-07-09 08:57:49.000
128	11	2011-07-09 08:59:59.000
129	114	2011-07-09 08:59:59.000
130	131	2011-07-09 08:59:59.000
131	112	2011-07-09 08:59:59.000
132	113	2011-07-09 08:59:59.000
89	147	2011-07-10 08:45:25.000
90	147	2011-07-10 08:45:28.000
91	1121	2011-07-10 08:46:00.000
92	42	2011-07-10 08:47:12.000
93	3	2011-07-10 08:47:15.000
94	117	2011-07-10 08:48:00.000
95	198	2011-07-10 08:50:00.000
96	1122	2011-07-10 08:53:00.000
97	142	2011-07-10 08:54:27.000
98	13	2011-07-10 08:54:30.000
99	11	2011-07-10 08:55:42.000
100	114	2011-07-10 08:55:42.000
101	131	2011-07-10 08:55:42.000
102	112	2011-07-10 08:55:42.000
103	113	2011-07-10 08:55:42.000
104	142	2011-07-10 08:57:46.000
105	13	2011-07-10 08:57:49.000
106	11	2011-07-10 08:59:59.000
107	114	2011-07-10 08:59:59.000
108	131	2011-07-10 08:59:59.000
109	159	2011-07-10 08:59:59.000
110	159	2011-07-10 08:59:59.000
67	42	2011-07-11 08:45:25.000
68	3	2011-07-11 08:45:28.000
69	1121	2011-07-11 08:46:00.000
70	42	2011-07-11 08:47:12.000
71	3	2011-07-11 08:47:15.000
72	117	2011-07-11 08:48:00.000
73	198	2011-07-11 08:50:00.000
74	1122	2011-07-11 08:53:00.000
75	142	2011-07-11 08:54:27.000
76	13	2011-07-11 08:54:30.000
77	11	2011-07-11 08:55:42.000
78	114	2011-07-11 08:55:42.000
79	131	2011-07-11 08:55:42.000
80	112	2011-07-11 08:55:42.000
81	113	2011-07-11 08:55:42.000
82	142	2011-07-11 08:57:46.000
83	13	2011-07-11 08:57:49.000
84	11	2011-07-11 08:59:59.000
85	114	2011-07-11 08:59:59.000
86	131	2011-07-11 08:59:59.000
87	112	2011-07-11 08:59:59.000
88	113	2011-07-11 08:59:59.000
45	69	2011-07-12 08:45:25.000
46	69	2011-07-12 08:45:28.000
47	69	2011-07-12 08:46:00.000
48	42	2011-07-12 08:47:12.000
49	3	2011-07-12 08:47:15.000
50	17	2011-07-12 08:48:00.000
51	98	2011-07-12 08:50:00.000
52	122	2011-07-12 08:53:00.000
53	42	2011-07-12 08:54:27.000
54	3	2011-07-12 08:54:30.000
55	1	2011-07-12 08:55:42.000
56	14	2011-07-12 08:55:42.000
57	31	2011-07-12 08:55:42.000
58	12	2011-07-12 08:55:42.000
59	13	2011-07-12 08:55:42.000
60	42	2011-07-12 08:57:46.000
61	3	2011-07-12 08:57:49.000
62	1	2011-07-12 08:59:59.000
63	14	2011-07-12 08:59:59.000
64	31	2011-07-12 08:59:59.000
65	12	2011-07-12 08:59:59.000
66	13	2011-07-12 08:59:59.000
23	47	2011-07-13 08:45:25.000
24	47	2011-07-13 08:45:28.000
25	121	2011-07-13 08:46:00.000
26	42	2011-07-13 08:47:12.000
27	3	2011-07-13 08:47:15.000
28	17	2011-07-13 08:48:00.000
29	98	2011-07-13 08:50:00.000
30	122	2011-07-13 08:53:00.000
31	42	2011-07-13 08:54:27.000
32	3	2011-07-13 08:54:30.000
33	1	2011-07-13 08:55:42.000
34	14	2011-07-13 08:55:42.000
35	31	2011-07-13 08:55:42.000
36	12	2011-07-13 08:55:42.000
37	13	2011-07-13 08:55:42.000
38	42	2011-07-13 08:57:46.000
39	3	2011-07-13 08:57:49.000
40	1	2011-07-13 08:59:59.000
41	14	2011-07-13 08:59:59.000
42	31	2011-07-13 08:59:59.000
43	59	2011-07-13 08:59:59.000
44	59	2011-07-13 08:59:59.000

De cette table je dois créer un top 10 des Event qui se déclenche le plus souvent. au numéro d'event on ajoutera, le nombre de fois où il s'est déclenché, accompagné d'un total, d'un total cumulé, et d'un pourcentage cumulé. Le Résultat doit être exactement ce qui suit

Code :

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

1
2
3
4
5
6
7
8
9
10
11
IdTop10 IdTypeEvent CptEvent TotalCumule Pourcentage
1	3		13	13	  16.05
2	42		13	26	  32.10
3	13		12	38	  46.91
4	142		7	45	  55.56
5	14		6	51	  62.96
6	11		6	57	  70.37
7	1		6	63	  77.78
8	31		6	69	  85.19
9	114		6	75	  92.59
10	131		6	81	  100.00

le pourcentage étant calculé de cette manière Totalcumule/Total * 100

J'ai créé un script qui me permet d'obtenir ceci mais(car il ya un mais) il ne me convient qu'à moitié car d'une part je le trouve un peu "tordu" et d'autre part je passe par une table temporaire intermédiaire

Voici ce script :

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
SELECT TOP 10 
       ROW_NUMBER() over (ORDER BY count(*) DESC) AS IdTop10,
       IdTypeEvent,
       count(*) AS CptEvent
INTO  #Top10Event 
FROM #Event AS tbl group By IdTypeEvent order by count(*) DESC
 
 
 
/*Query 1.2*/
 
 
SELECT *, 
      (SELECT Sum(CptEvent) FROM #Top10Event TE2 WHERE TE2.IdTop10 <= TE1.IdTop10 ) as TotalCumule,
      convert(numeric(5,2),(Convert(Numeric(10,4),(SELECT Sum(CptEvent)FROM #Top10Event TE2 WHERE TE2.IdTop10 <= TE1.IdTop10)) /(SELECT Sum(CptEvent) FROM #Top10Event TE2))* 100) AS Pourcentage
 
FROM #Top10Event AS TE1
 
 
 
DROP TABLE #Event
DROP TABLE #Top10Event

Voici le script de création/insertion de la table de départ:

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
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)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 47, Convert(DATETIME,'07/13/2011 08:45:25',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 47, Convert(DATETIME,'07/13/2011 08:45:28',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 121, Convert(DATETIME,'07/13/2011 08:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/13/2011 08:47:12',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/13/2011 08:47:15',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 17, Convert(DATETIME,'07/13/2011 08:48',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 98, Convert(DATETIME,'07/13/2011 08:50',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 122, Convert(DATETIME,'07/13/2011 08:53',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/13/2011 08:54:27',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/13/2011 08:54:30',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1, Convert(DATETIME,'07/13/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 14, Convert(DATETIME,'07/13/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 31, Convert(DATETIME,'07/13/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 12, Convert(DATETIME,'07/13/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/13/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/13/2011 08:57:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/13/2011 08:57:49',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1, Convert(DATETIME,'07/13/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 14, Convert(DATETIME,'07/13/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 31, Convert(DATETIME,'07/13/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 59, Convert(DATETIME,'07/13/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 59, Convert(DATETIME,'07/13/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 69, Convert(DATETIME,'07/12/2011 08:45:25',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 69, Convert(DATETIME,'07/12/2011 08:45:28',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 69, Convert(DATETIME,'07/12/2011 08:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/12/2011 08:47:12',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/12/2011 08:47:15',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 17, Convert(DATETIME,'07/12/2011 08:48',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 98, Convert(DATETIME,'07/12/2011 08:50',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 122, Convert(DATETIME,'07/12/2011 08:53',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/12/2011 08:54:27',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/12/2011 08:54:30',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1, Convert(DATETIME,'07/12/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 14, Convert(DATETIME,'07/12/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 31, Convert(DATETIME,'07/12/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 12, Convert(DATETIME,'07/12/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/12/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/12/2011 08:57:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/12/2011 08:57:49',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1, Convert(DATETIME,'07/12/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 14, Convert(DATETIME,'07/12/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 31, Convert(DATETIME,'07/12/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 12, Convert(DATETIME,'07/12/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/12/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/11/2011 08:45:25',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/11/2011 08:45:28',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1121, Convert(DATETIME,'07/11/2011 08:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/11/2011 08:47:12',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/11/2011 08:47:15',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 117, Convert(DATETIME,'07/11/2011 08:48',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 198, Convert(DATETIME,'07/11/2011 08:50',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1122, Convert(DATETIME,'07/11/2011 08:53',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 142, Convert(DATETIME,'07/11/2011 08:54:27',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/11/2011 08:54:30',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 11, Convert(DATETIME,'07/11/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 114, Convert(DATETIME,'07/11/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 131, Convert(DATETIME,'07/11/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 112, Convert(DATETIME,'07/11/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 113, Convert(DATETIME,'07/11/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 142, Convert(DATETIME,'07/11/2011 08:57:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/11/2011 08:57:49',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 11, Convert(DATETIME,'07/11/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 114, Convert(DATETIME,'07/11/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 131, Convert(DATETIME,'07/11/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 112, Convert(DATETIME,'07/11/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 113, Convert(DATETIME,'07/11/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 147, Convert(DATETIME,'07/10/2011 08:45:25',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 147, Convert(DATETIME,'07/10/2011 08:45:28',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1121, Convert(DATETIME,'07/10/2011 08:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 42, Convert(DATETIME,'07/10/2011 08:47:12',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 3, Convert(DATETIME,'07/10/2011 08:47:15',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 117, Convert(DATETIME,'07/10/2011 08:48',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 198, Convert(DATETIME,'07/10/2011 08:50',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1122, Convert(DATETIME,'07/10/2011 08:53',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 142, Convert(DATETIME,'07/10/2011 08:54:27',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/10/2011 08:54:30',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 11, Convert(DATETIME,'07/10/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 114, Convert(DATETIME,'07/10/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 131, Convert(DATETIME,'07/10/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 112, Convert(DATETIME,'07/10/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 113, Convert(DATETIME,'07/10/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 142, Convert(DATETIME,'07/10/2011 08:57:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/10/2011 08:57:49',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 11, Convert(DATETIME,'07/10/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 114, Convert(DATETIME,'07/10/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 131, Convert(DATETIME,'07/10/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 159, Convert(DATETIME,'07/10/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 159, Convert(DATETIME,'07/10/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 169, Convert(DATETIME,'07/09/2011 08:45:25',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 169, Convert(DATETIME,'07/09/2011 08:45:28',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 169, Convert(DATETIME,'07/09/2011 08:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 142, Convert(DATETIME,'07/09/2011 08:47:12',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/09/2011 08:47:15',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 117, Convert(DATETIME,'07/09/2011 08:48',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 198, Convert(DATETIME,'07/09/2011 08:50',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 1122, Convert(DATETIME,'07/09/2011 08:53',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 142, Convert(DATETIME,'07/09/2011 08:54:27',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/09/2011 08:54:30',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 11, Convert(DATETIME,'07/09/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 114, Convert(DATETIME,'07/09/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 131, Convert(DATETIME,'07/09/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 112, Convert(DATETIME,'07/09/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 113, Convert(DATETIME,'07/09/2011 08:55:42',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 142, Convert(DATETIME,'07/09/2011 08:57:46',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 13, Convert(DATETIME,'07/09/2011 08:57:49',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 11, Convert(DATETIME,'07/09/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 114, Convert(DATETIME,'07/09/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 131, Convert(DATETIME,'07/09/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 112, Convert(DATETIME,'07/09/2011 08:59:59',121)
INSERT INTO #Event (IdTypeEvent,TimeEvent) SELECT 113, Convert(DATETIME,'07/09/2011 08:59:59',121)

Donc je fais appel aux experts pour m'aider à optimiser cette requete.

Merci d'avance

Loic

SQLpro · 15/07/2011, 08h52

Pourquoi utiliser une table temporaire ? Cela ne sert à rien qu'a grever les perf !
Pourquou faire plusieurs CONVERT alors que CAST est la norme SQL et se trouve être plus rapide ?

Voici une première 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
16
17
18
19
WITH 
T0 AS
(SELECT ROW_NUMBER() over (ORDER BY count(*) DESC) AS IdTop10,
        IdTypeEvent,
        count(*) AS CptEvent
 FROM   "Event"
 GROUP  BY IdTypeEvent)
SELECT *, 
       (SELECT Sum(CptEvent) 
        FROM   T0 AS TE2 
        WHERE  TE2.IdTop10 <= TE1.IdTop10 ) AS TotalCumule,
       CAST((((SELECT CAST(Sum(CptEvent) AS FLOAT)
             FROM   T0 AS TE3 
             WHERE  TE3.IdTop10 <= TE1.IdTop10)
             /(SELECT CAST(Sum(CptEvent) AS FLOAT) 
               FROM T0 AS TE4 
               WHERE IdTop10 <= 10)) * 100) AS DECIMAL(16,2)) AS Pourcentage        
FROM   T0 AS TE1
WHERE  IdTop10 <= 10

A +

lbernard · 15/07/2011, 14h16

Merci pour ta réponse,

Citation:

Pourquoi utiliser une table temporaire ? Cela ne sert à rien qu'a grever les perf !

oui on est bien d'accord c'est bien pour ca que je demandais de l'aide

Citation:

Pourquou faire plusieurs CONVERT alors que CAST est la norme SQL et se trouve être plus rapide

Aucune bonne raison je ne savais ni que cast était la norme, ni que cast était plus performant. mais alors Convert a t il un intéret? est ce que dans certains cas seul convert puisse convenir?

Quelles est la Différence entre Numeric et DECIMAL?
pourquoi avoir les Float et pas partout des décimal?
Pourquoi DECIMAL(16,2) et pas DECIMAL(5,2) ?

Bien à toi,

Loic

lbernard · 15/07/2011, 14h24

RE,
Je viens de me rendre compte de quelque chose de très embetant.
En fait, je travaille Pour la production d' une Enorme entrerpise. cette entreprise a des dizaines et des dizaines (voir centaines) d'unités de production répartis partout dans le monde.
Chaque unité de production a ses serveurs et ses bases de données indépendament des autres.

Le problème est que d'une unité à l'autre la version de MS SQL SERVER n'est pas la meme et que ROW_NUMBER en SQL 2000 ca existe pas

Que pourrais je avoir comme autre solution que de passer par une table intermédiaire pour remplacer ce ROW_NUMBER?

Merci d'avance
Bien à toi,

Loic

Jinroh77 · 22/07/2011, 17h03

Ce n'est plus en T-SQL, mais SSRS à la possibilité de faire des calculs de sommes cumulées.

Il faut regarder d'une expression dans ce genre :

Code :

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

RunningValue(FIELDS!valeur.Value, Sum, "MonGroupement")

SQLpro · 23/07/2011, 10h10

Pour palier à l'absence de ROW_NUMBER il faut faire une non équijointure avec comptage.

En gros :

Code :

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

1
2
3
4
SELECT T1.???, COUNT(*) AS NUM
FROM   TableX AS T1
       INNER JOIN TableX AS T2
             ON T1.Clef <= T2.Clef -- ou l'inverse

A +

hmira · 24/07/2011, 10h59

Citation:

Envoyé par lbernard

...
Quelles est la Différence entre Numeric et DECIMAL?
pourquoi avoir les Float et pas partout des décimal?
Pourquoi DECIMAL(16,2) et pas DECIMAL(5,2) ?

1 - Quelles est la Différence entre Numeric et Decimal ?
Extrait du BOL :
decimal[ (p[ ,s] )] et numeric[ (p[ ,s] )]
Valeurs de précision et d'échelle fixes. Lorsque la précision maximale est utilisée, les valeurs valides sont comprises entre - 10^38 +1 et 10^38 - 1. Les synonymes ISO de decimal sont dec et dec(p, s). numeric est fonctionnellement équivalent à decimal.
Conclusion :
Sous SQL Server decimal(p,s) et numeric(p,s) sont des synonymes, et il n'y a aucune différence entre les 2 types.
Certains vont même jusqu'à dire que le terme numeric est déprécié et qu'il vaut mieux utiliser uniquement le terme decimal.. Je n'ai trouvé aucune trace, dans le BOL, de cette affirmation (?),
Personnellement, sous SQL Server, je n'utilise jamais, le terme numeric(p,s), j'utilise uniquement le terme decimal(p,s).

2 - Pourquoi avoir les Float et pas partout des décimal ?
Sous SQL Server, lorsqu'un opérateur (exemple +, - * / etc..) combine 2 valeurs (ou expressions) de type différents (exemple, int et float, .), il y a une conversion implicite du type ayant la priorité la plus faible vers le type ayant la priorité la plus forte (Voir en fin de ce poste, les différents niveaux de priorités) .

Exemple:

Code SQL :

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

1
2
3
4
5
DECLARE @i1 INT, 
             @f1 FLOAT; 
SET @i1 = 2;  
SET @f1 = 1.5E-14
SELECT @i1 + @f1 AS Resultat

Code SQL :

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

1
2
Resultat
2,00000000000002     -- Le résultat est de type float. Le float ayant un niveau de priorité plus élevé que le niveau int.

Corollaire du constat précédent :
Lorsque les 2 valeurs (ou expressions) ont le même type de données, le résultat de l'opération a également ce type de données.
Exemple :

Code SQL :

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

1
2
3
4
5
DECLARE @i1 INT, 
        @i2 INT; 
SET @i1 = 2;  
SET @i2 = 5 
SELECT @i1 + @i2 AS Resultat

Code SQL :

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

1
2
Resultat
7     -- le résultat est de type INT

Cependant, le fait que le résultat soit, par essence, exprimé dans le type de données ayant la priorité la plus forte, ne garanti pas que ce dernier reste dans le domaine du type ayant la priorité la plus forte.
Exemple:

Code SQL :

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

1
2
3
4
5
DECLARE @i1 INT, 
        @i2 INT; 
SET @i1 = 2147483647;  
SET @i2 = 1;  
SELECT @i1 + @i2 AS Resultat

Resultat :
Msg*8115, Niveau*16, État*2, Ligne*5
Une erreur de dépassement arithmétique s'est produite lors de la conversion de expression en type de données int.

Remarquez que l'erreur se produit à la ligne 5, c.à.d SELECT @i1 + @i2 AS Resultat

Pour revenir à ta question, le CAST en float s'avère nécessaire pour calculer le pourcentage. En effet, sans le CAST en float, le résultat serait faux, comme le montre l'exemple ci-dessous.

Code SQL :

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

1
2
3
4
5
6
7
DECLARE 
   @i1 INT, 
   @i2 INT;   
   SET @i1 = 2 
   SET @i2 = 100  
   SELECT @i1/@i2 * 100 AS Pourcentage1, 
         CAST(@i1 AS FLOAT) / CAST(@i2 AS FLOAT) * 100 AS Pourcentage2

Code SQL :

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

1
2
3
Résultat :          
Pourcentage1	Pourcentage2
0	2

Donc on voit bien que sans le transtypage en float , le résultat obtenu dans la colonne Pourcentage1 est faux (ou plutôt ne correspond pas au résultat auquel on s'attendait !)

Voilà ce qui explique l'utilisation des transtypage en float dans la CTE de SQLPro.

Code SQL :

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

SELECT CAST(Sum(CptEvent) AS FLOAT ....

Un question subsidiaire peut se poser "Ok, mais alors pourquoi pas décimal(p,s) ?", Oui pourquoi pas ?, mais encore faut-il connaître à l'avance la précision (p) et l’échelle (s) du résultat final, ce qui n'est pas toujours évident. L'utilisation du float dans ce cas est plus approprié et limite les risques potentiels d'erreur de dépassement de capacité.

3 - Pourquoi DECIMAL(16,2) et pas DECIMAL(5,2) ?
Effectivement dans ce cas très précis on aurait pu utiliser decimal(5,2) au lieu de decimal(16,2) à condition de démontrer mathématiquement que quelque soit le contexte, l'expression pourcentage définie dans la CTE, varie entre 0 et 100. Ce qui semble être est le cas (encore faut-il le démontrer !) Le choix decimal(16,2) était surement une précaution prise par SQLPro pour exprimer le résultat tout en minimisant au maximum les risque de dépassement de capacité. Ce qui est, d'après moi, un choix raisonnable. La prudence dans son paroxysme, pourrait même nous inciter à utiliser decimal(38,2).

De même (ou dans le même ordre), si d'aventure, le nombre d'enregistrement de ta table Event dépasse 2 147 483 647 (!) , il te faudra dans ce cas :
- Utiliser la fonction d'agrégation COUNT_BIG(*) au lieu de COUNT(*)
- Et, utiliser Sum(CAST(CptEvent AS BIGINT)) au lieu de Sum(CptEvent)

-----------------------------------------------------------------
Liste des niveaux de priorités (le niveau 1 étant le plus haut niveau de priorité) :

Code X :

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
1. types de données définis par l'utilisateur (plus haut niveau de priorité)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (y compris nvarchar(max))
26. nchar
27. varchar (y compris varchar(max))
28. char
29. varbinary (y compris varbinary(max))
30. binary (plus bas niveau de priorité)

A+

22/07/2011, 17h03	#5
Jinroh77 Modérateur Alexandre Chemla Consultant en Business Intelligence Inscription : février 2006 Messages : 1 773 Détails du profil Informations personnelles : Nom : Alexandre Chemla Âge : 28 Localisation : France, Seine et Marne (Île de France) Informations professionnelles : Activité : Consultant en Business Intelligence Informations forums : Inscription : février 2006 Messages : 1 773 Points : 1 837 Points : 1 837	Ce n'est plus en T-SQL, mais SSRS à la possibilité de faire des calculs de sommes cumulées. Il faut regarder d'une expression dans ce genre : Code : Sélectionner tout - Visualiser dans une fenêtre à part RunningValue(FIELDS!valeur.Value, Sum, "MonGroupement") __________________ Alexandre Chemla - Consultant MS BI chez Masao
	00

15/07/2011, 14h24	#4
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	RE, Je viens de me rendre compte de quelque chose de très embetant. En fait, je travaille Pour la production d' une Enorme entrerpise. cette entreprise a des dizaines et des dizaines (voir centaines) d'unités de production répartis partout dans le monde. Chaque unité de production a ses serveurs et ses bases de données indépendament des autres. Le problème est que d'une unité à l'autre la version de MS SQL SERVER n'est pas la meme et que ROW_NUMBER en SQL 2000 ca existe pas Que pourrais je avoir comme autre solution que de passer par une table intermédiaire pour remplacer ce ROW_NUMBER? Merci d'avance Bien à toi, Loic
	00

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