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 !!!