Discussion :

[vinch999]

Hello , j'ai un soucis pour calculer une moyenne sur base de moyennes.
Mon cas concret est que j'ai des mesures(plusieurs par jour) dont je calcule chaque jour la moyenne.
Je dois prendre des décisions dans le cas où la moyenne sur 1 semaine dépasse certains seuils.
Malheureusement j'ai des problèmes de perf lorsque je tente de calculer la moyenne directement sur les données détaillées.
J'essaie donc de contourner le problème en me basant sur les moyennes par jour qui elles se calculent en un temps respectable. J'aurais la possiblité de stocker le count de mesures sur la journée.

Exemple :
Un étudiant suit 2 cours (math et info), en math il a 2 cotes de 8 et 12 sur 20. En info : 8 , 12, 16 et 20/20 . Par cours sa moyenne est donc de 10 et 14 .
(8+12)/2 et (8+12+16+20)/4 .
Hors (10+14) /2 a un résultat différent de (8+12+8+12+16+20)/6 , et c'est normal car je ne tiens pas compte du coefficient de pondération.
Je devrais faire (2/6)*10 + (4/6)*14 pour avoir un résultat correct.

Quelle query pourrais-je faire pour obtenir le bon résultat, si ma base est ceci? En tablant sur le fait que l'on ne peut aller voir les données détaillées. Que l'on a accès qu'aux moyennnes par cours. Mais que l'on connait le nombre de cotes par cours.

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
 
SELECT eleve,cours,COUNT(*) as cnt,AVG(cote) as moyenne
FROM
(
	SELECT 'a' as eleve,'math' as cours, 8 as cote
	UNION
	SELECT 'a' as eleve,'math' as cours, 12 as cote
	UNION
	SELECT 'a' as eleve,'info' as cours, 8 as cote
	UNION
	SELECT 'a' as eleve,'info' as cours, 12 as cote
	UNION
	SELECT 'a' as eleve,'info' as cours, 16 as cote
	UNION
	SELECT 'a' as eleve,'info' as cours, 20 as cote
)TAB
GROUP BY eleve,cours

Je voudrais comme résultat :
a;12,66

Merci d'avance pour l'aide

[elsuket]

Bonjour,

Il est dommage que vous n'ayez pas partagé la structure de votre base de données, donc je suis parti avec l'hypothèse suivante : un étudiant assiste à au moins un cours, et un cours peut être suivi par au moins un étudiant. Dès lors notre modèle physique comprend une table d'étudiants, une table de cours, et une dernière table représentant la relation entre un étudiant et un cours, qualifiée par la note que l'étudiant a obtenu à un cours auquel il assiste.

Ceci se traduit par le lot de requête suivant :

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
CREATE TABLE cours
(
	cours_id tinyint NOT NULL IDENTITY(0, 1)
		CONSTRAINT PK_cours PRIMARY KEY
	, nom varchar(30) NOT NULL
		CONSTRAINT UQ_cours_nom UNIQUE
	, coefficient tinyint NOT NULL
		CONSTRAINT CHK_cours_coefficient CHECK (coefficient BETWEEN 1 AND 10)
)
GO
 
INSERT INTO dbo.cours (nom, coefficient)
VALUES ('Mathematiques', 1)
	, ('Informatique', 1)
GO
 
CREATE TABLE etudiant
(
	etudiant_id int NOT NULL IDENTITY(0, 1)
		CONSTRAINT PK_etudiant PRIMARY KEY
	, nom varchar(200) NOT NULL
		CONSTRAINT UQ_etudiant_nom UNIQUE
)
GO
 
INSERT INTO dbo.etudiant (nom)
VALUES ('vinch999'), ('elsuket')
GO
 
CREATE TABLE cours_etudiant
(
	cours_etudiant_id int NOT NULL IDENTITY
		CONSTRAINT PK_cours_etudiant PRIMARY KEY
	, cours_id tinyint NOT NULL
		CONSTRAINT FK_cours_etudiant__cours_id FOREIGN KEY (cours_id) REFERENCES dbo.cours
	, etudiant_id int NOT NULL
		CONSTRAINT FK_cours_etudiant__etudiant_id FOREIGN KEY (etudiant_id) REFERENCES dbo.etudiant
	, cote tinyint NOT NULL
		CONSTRAINT CHK_cours_etudiant__cote CHECK (cote BETWEEN 0 AND 20)
)
GO
 
INSERT INTO dbo.cours_etudiant (cours_id, etudiant_id, cote)
VALUES (0, 0, 8), (0, 0, 12), (1, 0, 8), (1, 0, 12), (1, 0, 16), (1, 0, 20)
	, (0, 1, 8), (0, 1, 15), (1, 1, 8), (1, 1, 12), (1, 1, 14), (1, 1, 18)
GO

Notons que je n'ai mis que des coefficients à 1 pour toutes les matières, puisque vous ne les avez pas donnés.
Pour les changer, il suffit d'exécuter un UPDATE sur la colonne coefficient de la table dbo.cours.
Si l'on souhaite simplement lister tous les résultats pour tous les cours auxquels les étudiants ont assisté, on peut exécuter :

Code :

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

1
2
3
4
5
6
7
8
9
10
-- Liste des résultats par étudiant
SELECT		E.nom AS nom_etudiant
		, C.nom AS cours
		, C.coefficient
		, CE.cote
FROM		dbo.etudiant AS E
INNER JOIN	dbo.cours_etudiant AS CE
			ON E.etudiant_id = CE.etudiant_id
INNER JOIN	dbo.cours AS C
			ON C.cours_id = CE.cours_id

Si l'on souhaite obtenir la moyenne par cours pour chaque étudiant, on peut exécuter :

Code :

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

1
2
3
4
5
6
7
8
9
10
-- Moyenne par cours pour chaque étudiant
SELECT		E.nom AS nom_etudiant
		, C.nom AS cours
		, AVG(CE.cote * CAST(C.coefficient AS float)) AS moyenne_par_cours
FROM		dbo.etudiant AS E
INNER JOIN	dbo.cours_etudiant AS CE
			ON E.etudiant_id = CE.etudiant_id
INNER JOIN	dbo.cours AS C
			ON C.cours_id = CE.cours_id
GROUP BY	E.nom, C.nom

Enfin, si l'on souhaite calculer la moyenne générale de tous les élèves, on peut réutiliser la requête précédente pour exécuter :

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
-- Moyenne générale pour chaque étudiant
;WITH
	CTE AS
	(
		SELECT		E.nom AS nom_etudiant
				, C.nom AS cours
				, AVG(CE.cote * CAST(C.coefficient AS float)) AS moyenne_par_cours
		FROM		dbo.etudiant AS E
		INNER JOIN	dbo.cours_etudiant AS CE
					ON E.etudiant_id = CE.etudiant_id
		INNER JOIN	dbo.cours AS C
					ON C.cours_id = CE.cours_id
		GROUP BY	E.nom, C.nom
	)
SELECT	nom_etudiant
	, AVG(moyenne_par_cours) AS moyenne_generale
FROM	CTE
GROUP	BY nom_etudiant

En ce qui concerne les performances, il faut revoir le plan de requête avec la sortie de SET STATISTICS TIME, IO ON pour comprendre précisément où se situe le problème. Dans ce cas-ci, un jeu d'index judicieusement posés vous permettra d'obtenir un temps de réponse normal. Si ce n'est pas suffisant, une vue indexée vous aidera, mais je doute que ce sera nécessaire.

@++

[vinch999]

Hello,
Merci beaucoup d'avoir pris le temps de faire cet exemple mais ce n'est pas ce dont j'ai besoin.
Mon problème n'est en fait pas celui là, j'ai essayé de vulgariser. Voici le véritable :

Creation de la table :

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
CREATE TABLE [dbo].[TEST_data](
	[DateKey] [int] NOT NULL,
	[Time] [time](0) NOT NULL,
	[MACAdress] [varchar](12) NOT NULL,
	[measure_1] [float] NOT NULL,
	[measure_2] [float] NOT NULL,
	[measure_3] [float] NOT NULL,
 CONSTRAINT [PK_data] PRIMARY KEY CLUSTERED 
(
	[DateKey] ASC,
	[Time] ASC,
	[MACAdress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
 
 
INSERT INTO [dbo].[TEST_data] 
VALUES 
	(20150822,'22:00:00','AABB11223344',17,3,4),(20150823,'22:10:00','AABB11223344',5,2,45),(20150819,'22:07:00','AABB11223344',3,22,5),
	(20150822,'21:00:00','55BB11223344',13,3,12),(20150823,'22:05:00','55BB11223344',5,2,43),(20150819,'22:10:00','55BB11223344',1,2,1),
	(20150822,'21:00:00','22BB11223344',1,5,4),(20150823,'22:04:00','22BB11223344',53,2,4),(20150819,'22:20:00','22BB11223344',6,23,2);
 
SELECT * FROM [dbo].[TEST_data];

Je veux comme résultat la moyenne des mesures , par mac adress sur les 7 derniers jours pour les mesures entre 20h et 23h00, dont les macadress sont dans une liste définie:

Code :

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

1
2
3
4
5
6
7
SELECT MACAdress,AVG(measure_1),AVG(measure_2),AVG(measure_3) 
FROM [dbo].[TEST_data]
WHERE DateKey BETWEEN 20150817 AND 20150823
AND Time BETWEEN '21:00:00' AND '23:00:00'
AND MACAdress IN ('AABB11223344','55BB11223344','22BB11223344')
GROUP BY MACAdress 
ORDER BY MACAdress

Cette table peut faire 500Go, et donc le temps de calcul est très important.
J'aurais aimé pour agréger directement mais ça me semble compromis.

J'ai essayé d'améliorer le temps en faisant comme suit :
-Prendre un subset de données chaque jour

Code :

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

1
2
3
4
5
SELECT MACAdress,measure_1,measure_2,measure_3
INTO #temp_data
FROM [dbo].[TEST_data]
WHERE DateKey = 20150822
AND Time BETWEEN '21:00:00' AND '23:00:00'

Créer un index sur cette table temporaire sur le champ MAC, afin de pouvoir retrouver rapidement les mac à calculer( en gros,je dois calculer 1000 mac sur 1000000 d'existantes, donc pas besoin de tout agréger)

Code :

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

CREATE INDEX IX_MAC ON #temp_data(MACAdress)

Filtrer et agréger sur base de cette table via la liste des mac (table) à calculer
J'obtiens donc la moyenne journalière pour chacune des macadress.
Et au début de la semaine suivante je voudrais faire la moyenne des 7 jours de données dont j'ai calculé la moyenne journalière.

[vinch999]

J'ai fini par trouver une solution.
-Je calcule la moyenne par jour et stock le count
-chaque semaine, je fais la moyenne des moyennes en faisant la somme des count et en faisant : count/sum(count) pour avoir le bon ratio. :SUM((CAST(cnt as float)/CAST(cnt_total as float))*[moyenne])

Ça à l'air d'être ok au niveau des résultats et des perfs.

[SQLpro]

Vos problèmes de perf sont normaux pour deux raisons :
1) la structure de votre table est contre performante au niveau de sa PK. Un PK multicolonne est toujours problématique. De plus il est stupide d'avoir séparé en deux la date et l'heure alors que l'un est une précision de l'autres (vous avez violé la 1FN à l'envers !!!). En sus avec un littéral derrière...
Résumez votre table avec une PK de type auto incrément.
2) bien qu'une adresse IP se présente sous forme de 4 entiers, c'est un seul entier de type INT et il aurait mieux valu mettre un INT et faire une vue de décomposition.

Au passages oit vous travaillez avec des noms anglais, soit avec des noms français, mais le franglais c'est pas top... Donc MacAddress avec 2 D !

3) La solution de votre problème passe par de multiples vues indexées...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE VIEW dbo.V_data
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) AS N, MACAdress, [DateKey], 
       COUNT_BIG(measure_1) AS NOMBRE_MESURE_1, 
       SUM(measure_1) AS CUMUL_MESURE_1,
       COUNT_BIG(measure_2) AS NOMBRE_MESURE_2, 
       SUM(measure_2) AS CUMUL_MESURE_2,
       COUNT_BIG(measure_3) AS NOMBRE_MESURE_3, 
       SUM(measure_3) AS CUMUL_MESURE_3
FROM   [dbo].[TEST_data] 
GROUP  BY DateKey, Time, MACAdress;
GO

Code :

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

1
2
CREATE UNIQUE CLUSTERED INDEX XV ON dbo.V_data ([DateKey], MACAdress);
GO

Code :

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

1
2
3
4
5
6
7
CREATE VIEW dbo.V_data_mean
AS
SELECT [DateKey],  MACAdress, 
       CUMUL_MESURE_1 / NOMBRE_MESURE_1 AS MOYENNE_1, 
       CUMUL_MESURE_2 / NOMBRE_MESURE_2 AS MOYENNE_2,
       CUMUL_MESURE_3 / NOMBRE_MESURE_3 AS MOYENNE_3
FROM   dbo.V_data --WITH(NOEXPAND)

Vous faites ceci à la granularité heure.

Puis une procédure qui va récupérer les données de toutes ces vues indexées en prenant la queue et la tete dans la table détail, les minutes de queue et de tête dans la vue des minutes, les heures de queue et de tête dans la vue des heures et le reste dans la vue des jours.

A +