Discussion :

[SylvainM]

Bonjour,
J'ai bien peur que ce genre de question ait déjà été posé ici, et en tout cas, je m'excuse d'avance si c'est le cas (pris par le temps, je n'ai pas écumé les forums... )
Sur une table que l'on peut fictivement résumer/illustrer ainsi :

ID	Date	Source
1	01/02/2010	A
2	05/01/2015	B
3	05/01/2015	A
4	05/01/2016	B

Je souhaite récupérer un tableau croisé avec le cumul de données (count(ID)) par année, pour chaque source.
Voici ce que ça donnerait ici dans l'exemple fictif :

Année	A	B	Total
2010	1	0	1
2015	2	1	3
2016	2	2	4

Cela est-il possible ?
(j'insiste sur le fait que la donnée doit être cumulative : 2016=2010+2011+...+2016. Sinon, ce serait un "simple" tableau croisé !)
Et si oui, je dois reconnaitre que je veux bien un peu d'aide sur cette requête !

Merci à vous !

Sylvain M.

[Sebwar]

Hello !

Il commence à être tard pour réfléchir mais je me lance

Code :

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

1
2
3
4
5
SELECT TA_DATE,
	(SELECT COUNT(*) FROM TABLE T2 WHERE T1.TA_DATE <= T2.TA_COLONNE_DATE AND SOURCE = 'A') AS A,
	(SELECT COUNT(*) FROM TABLE T3 WHERE T1.TA_DATE <= T3.TA_COLONNE_DATE AND SOURCE = 'B') AS B,
	(SELECT COUNT(*) FROM TABLE T4 WHERE T1.TA_DATE <= T4.TA_COLONNE_DATE) AS TOTAL
FROM (SELECT DISTINCT EXTRACT(YEAR FROM TIMESTAMP TA_COLONNE_DATE) AS TA_DATE FROM TABLE) T1

[SylvainM]

Super ! Merci Sebwar pour cette proposition.

Avant de l'adapter à mes champs et données réelles, j'essaie de comprendre.
Et il y a un point qui m'interpelle : il y a bien une définition pour la table "T1", mais je ne comprends pas comment sont définies les tables "T2", "T3" et "T4".

Sinon, j'ai oublié de préciser que ma table contiennent plus de 120000 données, et que les dates vont des années 1900 à aujourd'hui.
Du coup (mais peut-être cela n'a pas d'importance), je suppose qu'il faut optimiser au max la requête pour ne pas perdre en efficacité

Je me plonge dans l'adaptation, mais si tu peux m'expliquer la définition de T2 T3 T4, ce sera plus clair pour moi !
(et désolé si la question est idiote : j'assume )

Sylvain M.

[Sebwar]

T2 est l'alias de ta TABLE dans la sous requete suivante :

Code :

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

SELECT COUNT(*) FROM TABLE T2 WHERE T1.TA_DATE <= T2.TA_COLONNE_DATE AND SOURCE = 'A'

idem pour T3 et T4 avec les 2 autres sous requetes

un index sur ton l'année de ton champ date serait une bonne optimisation

[Sebwar]

tiens d'ailleur j'ai une erreur j'ai join l'année de T1 avec la date de T2, T3 et T4

c'est meiux comme ca (joindre l'année avec l'année ):

Code :

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

1
2
3
4
5
SELECT TON_ANNEE,
	(SELECT COUNT(*) FROM TABLE T2 WHERE T1.TON_ANNEE <= EXTRACT(YEAR FROM TIMESTAMP T2.TA_COLONNE_DATE) AND SOURCE = 'A') AS A,
	(SELECT COUNT(*) FROM TABLE T3 WHERE T1.TON_ANNEE <= EXTRACT(YEAR FROM TIMESTAMP T3.TA_COLONNE_DATE) AND SOURCE = 'B') AS B,
	(SELECT COUNT(*) FROM TABLE T4 WHERE T1.TON_ANNEE <= EXTRACT(YEAR FROM TIMESTAMP T4.TA_COLONNE_DATE) AS TOTAL
FROM (SELECT DISTINCT EXTRACT(YEAR FROM TIMESTAMP TA_COLONNE_DATE) AS TON_ANNEE FROM TABLE) T1

[SylvainM]

J'étais justement en train de m'en rendre compte !
Mais en fait, mon champ date n'est pas réellement un champ date : c'est un texte au format AAAAMMJJ, qui peut d'ailleurs prendre uniquement la forme AAAA si c'est une précision annuelle.
Du coup, je m'en sors avec left(DATE,4) AS ANNEE
Je poursuis l'adaptation !
Merci !

[Sebwar]

une autre solution :

Code :

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

1
2
3
4
5
6
7
SELECT TON_ANNEE,
	SUM(CASE WHEN SOURCE = 'A' THEN 1 ELSE 0 END) AS A,
	SUM(CASE WHEN SOURCE = 'B' THEN 1 ELSE 0 END) AS B,
	COUNT(*) AS TOTAL
FROM (SELECT DISTINCT EXTRACT(YEAR FROM TIMESTAMP TA_COLONNE_DATE) AS TON_ANNEE FROM TABLE) T1
	LEFT JOIN TABLE T2 ON T1.TON_ANNEE <= EXTRACT(YEAR FROM TIMESTAMP T2.TA_COLONNE_DATE)
GROUP BY TON_ANNE

[SylvainM]

Re,
Je n'avais pas vu ta 2e proposition, mais je viens d'avoir les résultats de la 1ère.
Positif : j'ai des résultats
Négatif : ces résultats sont incohérents
Je pense que le problème vient du fait qu'il n'y a pas de "ORDER BY".
Ou alors (et là, ça s'annonce encore plus dur pour ma compréhension), peut-être même faudrait-il utiliser les "fonctions de fenêtrage" ?
Là, pour l'instant, les années ne sont pas du tout triées, et les données cumullées annuelles sont fausses.
Merci de te pencher sur cette question !!

Sylvain M.

[Sebwar]

haaa ! Je savais qu'il était trop tard pour réfléchir hier soir ! l'erreur vient des jointures, j'ai mis <= au lieu de >=

requête corrigé:

Code :

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

1
2
3
4
5
SELECT TON_ANNEE,
	(SELECT COUNT(*) FROM TABLE T2 WHERE T1.TON_ANNEE >= EXTRACT(YEAR FROM TIMESTAMP T2.TA_COLONNE_DATE) AND SOURCE = 'A') AS A,
	(SELECT COUNT(*) FROM TABLE T3 WHERE T1.TON_ANNEE >= EXTRACT(YEAR FROM TIMESTAMP T3.TA_COLONNE_DATE) AND SOURCE = 'B') AS B,
	(SELECT COUNT(*) FROM TABLE T4 WHERE T1.TON_ANNEE >= EXTRACT(YEAR FROM TIMESTAMP T4.TA_COLONNE_DATE) AS TOTAL
FROM (SELECT DISTINCT EXTRACT(YEAR FROM TIMESTAMP TA_COLONNE_DATE) AS TON_ANNEE FROM TABLE) T1

idem dans la 2eme solution :

Code :

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

1
2
3
4
5
6
7
SELECT TON_ANNEE,
	SUM(CASE WHEN SOURCE = 'A' THEN 1 ELSE 0 END) AS A,
	SUM(CASE WHEN SOURCE = 'B' THEN 1 ELSE 0 END) AS B,
	COUNT(*) AS TOTAL
FROM (SELECT DISTINCT EXTRACT(YEAR FROM TIMESTAMP TA_COLONNE_DATE) AS TON_ANNEE FROM TABLE) T1
	LEFT JOIN TABLE T2 ON T1.TON_ANNEE >= EXTRACT(YEAR FROM TIMESTAMP T2.TA_COLONNE_DATE)
GROUP BY TON_ANNE

[SylvainM]

Excuse moi de ne pas t'avoir (re)remercié plus tôt, mais j'ai été aspiré par d'autres urgences !
SUPER : la requête (version1) a très bien fonctionné !
Et sinon, j'ai compris mon incompréhension (ça se dit pas, je sais ) pour la définition des tables : dans ton exemple, tu écris "FROM TABLE T3" et je prenais "FROM TABLE" comme une expression.
J'ai compris après que tu aurais pu ecrire "FROM TATABLE T3", soit "FROM TATABLE as T3".
Tout est clair pour moi maintenant !
(il comprend vite le gars, mais faut lui expliquer longtemps )

ENCORE UN GRAND MERCI !

A+

Sylvain M.

[aieeeuuuuu]

Bonjour,

Re,
Ou alors (et là, ça s'annonce encore plus dur pour ma compréhension), peut-être même faudrait-il utiliser les "fonctions de fenêtrage" ?

Oui, c'est une bonne idée qui vous évitera une jointure :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT  
	date_trunc( 'year', dte)
	, SUM(SUM(CASE source WHEN 'A' THEN 1 ELSE 0 END)) OVER(ORDER BY date_trunc( 'year', dte)) AS A
	, SUM(SUM(CASE source WHEN 'B' THEN 1 ELSE 0 END)) OVER(ORDER BY date_trunc( 'year', dte)) AS B
	, SUM(COUNT(*)) OVER(ORDER BY date_trunc( 'year', dte))
FROM LaTable
GROUP BY date_trunc('year', dte)

notez que vous pourriez mettre la transformation de la date en année (avec le date_trunc dans me requete, EXTRACT dans les votre) dans une CTE afin de factoriser le code.

[SylvainM]

Alors là, chapeau !!!
D'un peu plus d'une minute d’exécution, je suis descendu à moins de 3 secondes !!!
Juste les "CASE", tu as dû te tromper dans l'écriture ? Tu as mis :

Code :

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

CASE source WHEN 'A' THEN 1 ELSE 0 END

Mais j'ai corrigé en

Code :

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

CASE WHEN source = 'A' THEN 1 ELSE 0 END

Bravo !

Sylvain

[al1_24]

Les deux formes sont équivalentes et toutes deux normalisées.

[SylvainM]

Ah, je ne connaissais pas cette syntaxe.
Dans mon cas, en fait, c'était pas source = 'A', mais source in ('X','Y').
Dans ce genre de syntaxe, on aurait écrit comment alors ?
Merci pour la précision.

A+

Sylvain M.

[aieeeuuuuu]

La syntaxe que j'ai utilisée ne permet que des test d'égalité d'une expression par rapport aux expression du CASE.

Dans votre cas, il est donc en effet préférable d'utiliser la notation suivante :

Code :

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

1
2
 
CASE WHEN source IN ('X', 'Y') THEN ...

[SylvainM]

Ok, c'est bien ce que j'avais fait.
Merci en tout cas pour m'avoir fait découvrir une autre syntaxe possible.
A+

Sylvain M.