Discussion :

[artichoux]

Bonjour à tous,

Petite précision avant de commencer : je ne suis pas informaticien de formation j'ai quelques légères connaissances mais tout de même assez limitées...

Dans le cadre de mon travail j'ai besoin de récupérer des données de bruit que je dois présenter de la manière suivante :

LAmax nombre

70 14
71 35
72 48
73 65

Le "nombre" correspondant au nombre de fois ou le niveau sonore LAmax est obtenu sur un temps donné. Pour faire ça j'ai utilisé ce 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
 
DROP TABLE #TableTemporaireNAx
CREATE TABLE #TableTemporaireNAx
(
[STATION]char(100),
[IDStation]int,
[LAmax]float,
[Nombre] float
)
 
INSERT INTO #TableTemporaireNAx(STATION, IDStation, LAmax, Nombre) 
SELECT
		dbo.station.nom_station,
		fab.couplage.IDSTATION, 
		round(fab.Couplage.Max_Level,0), 
		count(*)
 
FROM    dbo.Evenement INNER JOIN fab.Couplage ON dbo.Evenement.IDEVENT = fab.Couplage.IDEVENT INNER JOIN fab.TrajsDEPLB ON fab.Couplage.IDTRAJDEPLB = fab.TrajsDEPLB.IDTRAJDEPLB inner join dbo.station ON dbo.Station.IDSTATION = fab.Couplage.IDSTATION
WHERE   fab.couplage.IDSTATION in (@station)
		AND fab.Couplage.MultiVols = 1 
		AND fab.Couplage.MultiEvents = 1 
		AND fab.Couplage.Start_Date_TU between @datebegin and @dateend 
		and dbo.Evenement.Validation = 0				 
group by
		dbo.station.nom_station,
		fab.couplage.IDSTATION, 
		round(fab.Couplage.Max_Level,0)
 
order by IDSTATION, LAmax asc

(c'est un peu le bazar mais les données liées au problème viennent de fab.Couplage.Max_Level et de count(*) ).

Jusque là tout va bien, j'obtiens bien un tableau comme celui voulu mais dans le cas où un niveau LAmax n'a pas été mesuré sur la période donnée aucune valeur n'est affichée et j'ai un trou dans mon tableau. Ce que j'aimerais c'est avoir une suite de LAmax avec le nombre d'apparitions mais dans le cas ou aucune valeur n'a été mesurée, j'aimerais tout de même avoir le niveau LAmax marqué mais avec 0 dans la colonne nombre. Avez vous une idée de comment faire ça ?

Merci d'avance !

[al1_24]

Comme le SGBD ne peut inventer les valeurs absentes, il faudrait créer une table qui contient toutes les valeurs possibles.
Il suffit ensuite de faire une jointure externe entre cette table et la table des valeurs relevées.

[artichoux]

J'ai crée une table avec les valeurs souhaitées à l'aide d'un while :

Code :

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

1
2
3
4
5
6
7
8
9
 
declare @increment int = 60
 
	 WHILE @increment<=100
 
		BEGIN
		SELECT @increment
		SET @increment = @increment + 1
		END

Les valeurs que je veux mettre en LAmax sont donc crées dans une table à part, ça c'est bon, mais je ne vois pas comment ajouter seulement les valeurs manquantes. A chaque fois toutes les valeurs du while sont prises et se mettent soit à la suite du tableau, soit le count donne des valeurs fausses.

[aieeeuuuuu]

Bonjour,

Ou alors, c'est la jointure interne qui pose problème (une description des table aiderait à mieux comprendre le problème), et il suffit alors de faire des jointures externes (LEFT OUTER JOIN), sans oublier de remonter les conditions de filtres dans les conditions de jointure, afin de résoudre le problème.

[artichoux]

Voilà la description de la table allégé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
 
 
DROP TABLE #TableTemporaireNAx                                                           
CREATE TABLE #TableTemporaireNAx
(
[STATION]char(100),                                                                                                   -- création de la table de base avec les 4 colonnes, Station et IDstation sont declarés plus tôt et récupérés dans le SELECT.
[IDStation]int, 
[LAmax]float,
[Nombre] float
)
 
INSERT INTO #TableTemporaireNAx(STATION, IDStation, LAmax, Nombre) 
SELECT
		dbo.station.nom_station,                                                                                         -- insertion nom de la station
		fab.couplage.IDSTATION,                                                                                          -- insertion de l'id de la station
		round(fab.Couplage.Max_Level,0),                                                                                 -- selection des LAmax
		count(*)                                                                                                          -- compte le nombre d'apparition de chaque LAmax
 
FROM    fab.Couplage  INNER JOIN dbo.station ON dbo.Station.IDSTATION = fab.Couplage.IDSTATION                                  -- permet de récupérer les valeurs LAmax et le nom de la station de mesure
WHERE   
                fab.couplage.IDSTATION in (@station)                                                                             -- selectionne la station dont on récupère les données
		AND fab.Couplage.Start_Date_TU between @datebegin and @dateend                                                   -- selectionne l'intervalle de mesure
group by
		dbo.station.nom_station,
		fab.couplage.IDSTATION, 
		round(fab.Couplage.Max_Level,0)
 
order by  LAmax asc                                                                                                                 -- range les LAmax dans l'ordre
 
 
DROP TABLE #test_increment
CREATE TABLE #test_increment                                                                                                     -- création de la table avec les valeurs LAmax souhaitées
(
[increment] int
)
 
 
declare @increment int = 60                                                                                                       -- boucle créant les donnés nécessaires
	 WHILE @increment<=100
 
		BEGIN
		Insert into #test_increment(increment)
		SELECT @increment
 
		SET @increment = @increment + 1
		END
 
 
SELECT TOP 50
[increment]
FROM #test_increment

Tout ça marche bien mais pour effectuer la jointure externe j'ai besoin d'avoir des éléments communs entre les deux tables pour qu'elles aient de quoi se rattacher, ce qui n'est pas le cas car le nom de station reste le même à chaque ligne et l'id station aussi (je sais pas si je suis très clair). J'ai également tenté de faire simplement une UNION entre les colonnes "LAmax" et "increment" mais la colonne est remplie par les valeurs LAmax puis ensuite par les valeurs increment et ne se suivent donc pas.

J'espère que ce que je dis est clair je suis novice

[elsuket]

Bonjour Artichoux,

Le problème vient du fait que COUNT(*) compte le nombre de lignes, que la colonne soit à NULL ou pas.
On est donc obligés de recourir à l'agrégat SUM() sur une expression CASE, comme suit :

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
;WITH
	NUMBER AS
	(
		SELECT	number
		FROM	master.dbo.spt_values
		WHERE	type = 'P'
		AND	number BETWEEN 60 AND 100
	)
SELECT		S.nom_station
		, C.IDSTATION
		, ROUND(C.Max_Level,0) AS LAmax
		, SUM(CASE WHEN C.Max_Level IS NULL THEN 0 ELSE 1 END) AS occurence
FROM		dbo.Evenement AS E
INNER JOIN	fab.Couplage AS C
			ON E.IDEVENT = C.IDEVENT
RIGHT JOIN	NUMBER AS N
			ON ROUND(C.Max_Level,0) = N.number
INNER JOIN	fab.TrajsDEPLB AS T
			ON fC.IDTRAJDEPLB = T.IDTRAJDEPLB
INNER JOIN	dbo.station AS S
			ON S.IDSTATION = C.IDSTATION
WHERE		C.IDSTATION IN (@station)
AND		C.MultiVols = 1 
AND		C.MultiEvents = 1 
AND		C.Start_Date_TU between @datebegin and @dateend 
AND		E.Validation = 0				 
GROUP BY	S.nom_station, C.IDSTATION, ROUND(C.Max_Level,0)
ORDER BY	IDSTATION, LAmax

On peut également remplacer l'expression de table commune (CTE) NUMBER par l'empilement de CTE suivant, un peu tordu mais qui a le mérite d'être très efficace :

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
WITH
	N AS (SELECT NULL AS v UNION ALL SELECT NULL)		-- 2 lignes générées
	, N1 AS (SELECT A.v FROM N AS A CROSS JOIN N AS B)	-- 4 lignes générées
	, N2 AS (SELECT A.v FROM N1 AS A CROSS JOIN N1 AS B)	-- 16 lignes générées
	, N3 AS (SELECT A.v FROM N2 AS A CROSS JOIN N2 AS B)	-- 256 lignes générées
	, P AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
		FROM	N3
	)
	, NUMBER AS
	(
		SELECT	n
		FROM	P
		WHERE	n BETWEEN 60 AND 100
	)
SELECT		S.nom_station
		, C.IDSTATION
		, ROUND(C.Max_Level,0) AS LAmax
		, SUM(CASE WHEN C.Max_Level IS NULL THEN 0 ELSE 1 END) AS occurence
FROM		dbo.Evenement AS E
INNER JOIN	fab.Couplage AS C
			ON E.IDEVENT = C.IDEVENT
RIGHT JOIN	NUMBER AS N
			ON ROUND(C.Max_Level,0) = N.number
INNER JOIN	fab.TrajsDEPLB AS T
			ON fC.IDTRAJDEPLB = T.IDTRAJDEPLB
INNER JOIN	dbo.station AS S
			ON S.IDSTATION = C.IDSTATION
WHERE		C.IDSTATION IN (@station)
AND		C.MultiVols = 1 
AND		C.MultiEvents = 1 
AND		C.Start_Date_TU between @datebegin and @dateend 
AND		E.Validation = 0				 
GROUP BY	S.nom_station, C.IDSTATION, ROUND(C.Max_Level,0)
ORDER BY	IDSTATION, LAmax

Essayez et dites-nous

En ce qui concerne la table temporaire, est-ce que vous la ré-utilisez ensuite dans le lot de requêtes ?

Pour un bon approfondissement de vos connaissances à votre rythme, je vous renvoie vers le livre référencé dans ma signature, qui contient, entre autres aspects, un cours compleet sur le langage SQL et son dialecte Microsoft T-SQL.

@++

[artichoux]

Merci pour tout ce code elsuket

Malheureusement la première solution ne marche pas les résultats obtenus sont les même et pas de 0 en vue...

Quant à la deuxième je ne comprends pas ce que vous avez fait et sql server met en erreur de syntaxe les virgules et les différents N, N1 et N2 et A.v... Je ne sais pas exactement quel était l'objectif de cette partie ni comment elle fonctionne et je ne sais donc pas comment la corriger...

Pour la table temporaire je peux m'en passer j'ai simplement pris cette habitude suite à des calculs se réalisant entre plusieurs tables.

Merci pour le conseil je regarderais ça