Discussion :

[informer]

Bonjour à tous,

Ma question porte sur une sommation conditionnelle par fenêtrage et elle est la suivante: Est il possible de remplacer la sous requête conditionnelle ci-dessous par un calcul par fenêtrage.

La requête d'origine

Code SQL :

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

1
2
3
4
5
6
7
8
9
select 
	id
	col1
	,(select SUM(col1)
		from temp b
		where b.LTime>=a.LTime
		and b.id = a.id 
		and b.date = a.date) as NbrCol1
from temp a

La partie à modifier est donc la sous requête:

Code SQL :

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

1
2
3
4
5
select SUM(col1)
from temp b
where b.LTime>=a.LTime
and b.id = a.id 
and b.date = a.date) as NbrCol1

Par un code du genre

Code SQL :

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

 sum (case when LTime>=LTime then b.col1 else 0 end ) over (partition by id, date) as NbrCol1

Sachant qu'en l'état, la condition sur LTime est inopérante car les LTime qui doivent être comptabilisés sont ceux des enregistrements qui ont le même id et date que l'enregistrement considéré de temp a mais avec un LTime> au LTime de l'enregistrement considéré de temp a

J'avais pensé à un

Code SQL :

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

 inner join temp b on a.id = b.id and a.date = b.date

pour faire

Code SQL :

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

 sum (case when b.LTime>= a.LTime then b.col1 else 0 end ) over (partition by b.id, b.date) as NbrCol1

mais ça paraît pas très propre car ne respectant pas la philosophie du fenêtrage et sachant que je ne connait pas les champs qui forment la clé de la table temp a

Merci pour toute aide apportée

A+

[aieeeuuuuu]

Bonjour,

Que donne ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT 
		id
	,	col1
	,	SUM(col1) OVER(
			PARTITION BY id, Date
			ORDER BY LTime 
			RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
		) AS NbrCol1
FROM Temp

Ou même plus simplement ça :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT 
		id
	,	col1
	,	SUM(col1) OVER(
			PARTITION BY id, Date
			ORDER BY LTime DESC
		) AS NbrCol1
FROM Temp

[informer]

Bonjour aieeeuuuuu

Le code OVER PARTITON proposé permet-il de calculer pour chaque enregistrement, la somme (col1) de tous les enregistrements regroupés par id et date avec leur LTime > au LTime de l'enregistrement pour lequel est calculé la somme

Bonjour aieeeuuuuu, l'ORDER BY seul ne conserve-t-il que les enregistrement de la partition dont le LTime< au LTime de l'enregistrement de la sélection?

Si je comprend bien le RANGE BETWEEN CURRENT ROW, il commence la partition à l'enregistrement de la partition qui est celui du select et prend ensuite ceux dont le Ltime est supérieur, c'est ça? Par contre s'il existe plusieurs enregistrements de la partition avec un même LTime que le CURRENT ROW, sont ils OBLIGATOIREMENT ordonnés après le CURRENT ROW?

Exemple:

Id date col 2 LTime col1 Sum (Col1)
1 01012015 aaa 1 1 (1+2+4+6)
1 01012015 bbb 1 2 (1+2+4+6)
1 01012015 ccc 1 4 (1+2+4+6)
1 01012015 bbb 2 6 (1+2+4+6)

Avec le CURRENT ROW , aurais-je pour la ligne 2 dans la PARTITON aussi la ligne 1 i.e les 4 lignes comme ci-dessus?

Je fais le test sur les 2 propositions et reviens pour donner la conclusion!

Encore merci pour ton aide aieeeuuuuu
A bientôt

[informer]

Re salut aieeeuuuuu

Juste pour dire tout simplement efficace , sans bavure , carré et brillant

Par contre je ne comprends pas pourquoi c'est le DESC qui prend tous les enregistrements qui ont le LTime> LTime de l'enregistrement du select et non ASC

Pourquoi avec DESC on a la somme correcte comme ci-dessous et non avec ASC?

id	date	col2	LTime	col1	Sum
1	01012015	b	1	1	(1+2+6)
1	01012015	c	1	2	(1+2+6)
1	01012015	d	-1	4	4
1	01012015	e	2	6	6

car si j'ai bien compris, avec ASC, on aurait

id	date	col2	LTime	col1	Sum
1	01012015	b	1	1	(1+2+4)
1	01012015	c	1	2	(1+2+4))
1	01012015	d	-1	4	4
1	01012015	e	2	6	(1+2+4+6)

Merci de tes lumières pour m'expliquer ce que fait ASC et DESC

A bientôt

[aieeeuuuuu]

Le SUM() OVER(... ORDER BY x) fait la somme cumulée.
C'est à dire la somme des valeurs précédentes dans la partition, où précédente s'entend selon l'ORDER BY.
En classant en décroissant, les valeurs précédentes sont donc celles qui sont égales ou supérieures à la ligne courante, c'est à dire ce que vous voulez.

[informer]

Merci encore aieeeuuuuu

Mais c'est la notion de précédent qui coince!

Je conçois l'ORDER BY LTime ASC au sens du SELECT qui fait que si je le transpose à la PARTITION, je devrais prendre dans la PARTITION, si j'utilise ASC, tous les enregistrements qui ont leur LTime>= LTime de l'enregistrement du select

Et inversement avec DESC i.e. les enregistrements qui ont leur LTime< LTime de l'enregistrement du select


Pour moi, avec un OVER PARTITON id, date ORDER BY LTIME ASC, je devrais avoir pour la 1ère ligne

Id	date	col 2	LTime	col1
1	01012015	b	1	1

Tous les enregistrement qui ont un LTime >= à la 1ère ligne i.e donc les lignes 1,2,4 alors que c'est le contraire, la PARTITION va être peuplée par les lignes 1,2,3 !!!

[aieeeuuuuu]

Non, c'est justement l'inverse ! si on classe une suite par ordre décroissant :
9;7;6;5;3;2

Les chiffres qui précédent 5, sont bien qui sont plus grands : 9, 7 et 6

[informer]

J'ai trouvé cette définition pour ORDER BY

OVER(ORDER BY): tous les tuples entre le début de l'ordre et le tuple courant

Mais voilà je ne comprends pas à quoi correspond le début de l'ordre avec un ASC ou DESC !!

[informer]

id	date	col2	LTime	col1
1	01012015	a	1	1
1	01012015	b	1	2
1	01012015	c	-1	4
1	01012015	d	2	6

L'ORDER BY LTime DESC ==> dans la partition

id	date	col2	LTime	col1
1	01012015	d	2	6
1	01012015	b	1	2
1	01012015	a	1	1
1	01012015	c	-1	4

Donc si prend le cas de la ligne 1 qui est toujours la dernière même si existe d'autres tuples avec (1, 01012015)

1

01012015

a

1

1

Je prend bien les valeurs supérieures

Encore merci aieeeuuuuu

[aieeeuuuuu]

En effet, SUM() OVER() utilisé avec ORDER BY permet de faire des sommes cumulées croissantes.

L'usage typique : obtenir le montant total des ventes depuis le début de l'année, mois par mois :

Code :

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

1
2
3
4
5
6
 
SELECT 
    Annee
    , Mois
    , SUM(montant) OVER(PARTITION BY annee ORDER BY Mois) AS totalDebutAnnee
FROM LaTableDesVentes

ainsi, pour le mois de mars 2014, vous aurez le cumul des ventes de janvier à mars 2014.

PARTITION BY permet de définir la fenêtre (dans l'exemple, l'année complète)
ORDER BYpermet d'ordonner les données au sein de la fenêtre définie (dans l'exemple, selon le mois de l'année)
En appliquant un SUM à tout ça, on obtient la somme des valeurs entre la première ligne de la fenêtre (janvier) et la ligne en cours.

Dans votre cas, vous voulez la somme des valeurs plus grandes(il vous faudrait alors les valeurs entre le ligne courante, et la dernière ligne de la fenêtre comme indiqué dans me première requête). classer par ordre décroissant permet de contourner le problème : c'est bien du coup les lignes précédentes qui vous intéressent...

Je ne sais pas si je suis clair, mais je ne vois pas comment l'être plus.
Faites des tests avec des petites tables de quelques lignes, vous comprendrez mieux le fonctionnement de SUM + OVER + ORDER BY

[informer]

Bonsoir aieeeuuuuu

C'est maintenant très clair une fois comprise la notion de début de l'ordre en fonction de ASC ou DESC !

Encore merci beaucoup pour ton aide qui non seulement m'a permis de résoudre mon problème mais m'a fait comprendre une notion de base essentielle pour utiliser le fenêtrage

Bonne soirée