Discussion :

[Atm2263]

Bonjour à tous,

Je travaille actuellement sur une petite base de données traitant de démographie.
Ma base est composée de micro données (chaque ligne représente un individu échantillonné).

Chaque ligne représente un individu dont l'age et le sexe sont connus.
J'aimerais réaliser une requête me permettant d'extraire la structure par age (c'est à dire le nombre de personnes par sexe et age).

Voici la forme actuelle de ma table :

CREATE TABLE MAROC (YEAR TEXT (4), PROVMA TEXT (5),WTPER REAL (8), AGE INTEGER (3), SEX TEXT (1))

YEAR PROVMA WTPER SEX AGE
1982 1391 20.0 1 32
1982 1391 20.0 2 59
1982 1391 20.0 2 32
1982 1391 20.0 2 34
1982 1391 20.0 1 57
1982 1391 20.0 2 39
1982 1391 20.0 1 3
1982 1391 20.0 1 15
1982 1391 20.0 2 8
1982 1391 20.0 2 35

(Où Year est l'année de recensement, PROVMA le code de province, WTPER la population, SEX le sexe, et AGE, l'age.)

Et voici la table que j'aimerais obtenir :

YEAR PROVMA SUM(WTPER) H0 F0 I0 H1 F1 I1 H2 F2 ...
1982 1391
1999 1391
2004 1391
1982 1392
1999 1392
2004 1392
...

C'est à dire une table agrégée, par année et province indiquant la population par an et par province, le nombre d'hommes d'age 0 le nombre de femmes d'age 0 le nombre d'inconnus d'age 0 et ce pour tous les ages jusqu’à 100.

Auriez vous une idée de la façon de structurer une telle requête ?
Merci d'avance.

[Waldar]

Faites une recherche sur le mot-clef PIVOT.

[aieeeuuuuu]

Bonjour,

Soit un résultat avec plus de 300 colonnes !

Même si cela est possible en SQL (mais avec 300 colonnes, la requête va être monstrueuse à écrire, maintenir et exécuter), il serait beaucoup plus simple que la requête renvoi les données avec une ligne par (année, province, age, sexe), ne fasse que la somme SUM(WTPER) et que vous pivotiez le résultat dans votre application pour obtenir la présentation que vous voulez.
Si la requête effectue le trie sur les colonne PROVMA, YEAR, age, sexe, le code coté application sera assez simple. (attention cependant à gérer une eventuelle absence de données, pour une années, ou un age...)

[Atm2263]

Bonjour,

Soit un résultat avec plus de 300 colonnes !

Même si cela est possible en SQL (mais avec 300 colonnes, la requête va être monstrueuse à écrire, maintenir et exécuter), il serait beaucoup plus simple que la requête renvoi les données avec une ligne par (année, province, age, sexe), ne fasse que la somme SUM(WTPER) et que vous pivotiez le résultat dans votre application pour obtenir la présentation que vous voulez[…]

Je n'avais pas vu les choses comme ça, c'est vrai que la requête et beaucoup plus simple à écrire, sans que ça ne pose de soucis pour le traitement.

Par contre pour l'opérateur PIVOT est-il inclus dans la norme SQL ?
Je pensai qu'il était propre à SQL serveur

[aieeeuuuuu]

Bonjour,

à ma connaissance, PIVOT ne fait effectivement pas partie de la norme SQL, mais est implémenté dans SQL Server, ainsi qu'Oracle, (sous postgres également, avec crosstab)

Il est cependant possible d'obtenir le même résultat en SQL normatif, avec une requête dans ce style :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT
    YEAR ,
    PROVMA ,
    SUM( WTPER ) AS SommeWTPER
    , SUM(CASE WHEN Sexe = 1 AND Age = 0 THEN  WTPER END) AS H0
    , SUM(CASE WHEN Sexe = 2 AND Age = 0 THEN  WTPER END) AS F0
    , SUM(CASE WHEN Sexe = 1 AND Age = 1 THEN  WTPER END) AS H1
    ,...
FROM MAROC 
GROUP BY 
    YEAR ,
    PROVMA

Je vous laisse écrire les... 297 autres colonnes

[islamov2000]

Sous quel SGBD travailles tu mon frère?

[Atm2263]

Sous quel SGBD travailles tu mon frère?

J'utilise SQLite.

[Atm2263]

Je vous laisse écrire les... 297 autres colonnes

En général, quand je dois réaliser des longues requêtes, je les bricole sous un tableur, avec l'incrémentation automatique et la concaténation, c'est assez rapide

[aieeeuuuuu]

En général quand je doit réaliser des longues requêtes je les bricole sous un tableur, avec l'incrémentation automatique et la concaténation c'est assez rapide

ou, si on n'a pas de tableur sous la main, ou la flemme de l'ouvrir, directement en SQL :

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 N AS (
	SELECT TOP(101)
			ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1  AS i
			,CAST(
				ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1 
				AS VARCHAR(8)
			) AS vc
	FROM INFORMATION_SCHEMA.COLUMNS AS A
		, INFORMATION_SCHEMA.COLUMNS AS B
		, INFORMATION_SCHEMA.COLUMNS AS C
)
SELECT ', SUM(CASE WHEN Sexe = ' + Sexe.vc  + ' AND Age = ' + Age.vc + ' THEN  WTPER END) AS ' + CASE Sexe.vc WHEN 1 THEN 'H' ELSE 'F'   END + Age.vc
FROM N Age
CROSS JOIN N Sexe
WHERE Age.i BETWEEN 0 AND 100
AND Sexe.i BETWEEN 1 AND 2
ORDER BY Age.i, Sexe.i

, SUM(CASE WHEN Sexe = 1 AND Age = 0 THEN WTPER END) AS H0
, SUM(CASE WHEN Sexe = 2 AND Age = 0 THEN WTPER END) AS F0
, SUM(CASE WHEN Sexe = 1 AND Age = 1 THEN WTPER END) AS H1
, SUM(CASE WHEN Sexe = 2 AND Age = 1 THEN WTPER END) AS F1
, SUM(CASE WHEN Sexe = 1 AND Age = 2 THEN WTPER END) AS H2
, SUM(CASE WHEN Sexe = 2 AND Age = 2 THEN WTPER END) AS F2
, SUM(CASE WHEN Sexe = 1 AND Age = 3 THEN WTPER END) AS H3
, SUM(CASE WHEN Sexe = 2 AND Age = 3 THEN WTPER END) AS F3
, SUM(CASE WHEN Sexe = 1 AND Age = 4 THEN WTPER END) AS H4
, SUM(CASE WHEN Sexe = 2 AND Age = 4 THEN WTPER END) AS F4
...