GROUP BY sur lignes contiguës

[lipsum]

Bonjour,

Est-il possible de faire "avec un ordre SQL simple", un GROUP BY uniquement sur des lignes contiguës ?

Je m'explique. Soit les données suivantes:

COL.A COL.B COL.C
--------------------
V1 V2 2
V1 V2 4
V1 V2 1
V1 V3 1
V1 V3 5
V1 V3 6
V1 V2 7
V1 V2 2
V1 V2 9

J'aimerai obtenir:

COL.A COL.B MIN(COL.C) MAX(COL.C)
--------------------------------------
V1 V2 1 4
V1 V3 1 6
V1 V2 2 9

Merci par avance

[Ptit_Dje]

Je ne comprend pas trop bien ce que tu cherches à faire.
Qu'est ce qu'une ligne contigue pour toi ?
Peux tu nous laisser la definition de ta table ainsi qu'un script nous permettant de la remplir aisement afin de tester ?

Au cas ou...

Code :

1
2
3
SELECT col.a, col.b,min(col.c), max(col.c)
FROM test_group_by
GROUP BY col.a, col.b

[lipsum]

C'est vrai que mon exemple n'est pas top.

Je prends la table PRIME suivante:

DATE | PRIME
------------
01/01/2008 ; 10,00
02/01/2008 ; 10,00
03/01/2008 ; 10,00
04/01/2008 ; 15,00
05/01/2008 ; 15,00
06/01/2008 ; 15,00
07/01/2008 ; 10,00
08/01/2008 ; 10,00

Je souhaiterai obtenir le résultat suivant:

DEBUT ; FIN ; PRIME
----------------------------
01/01/2008 ; 03/01/2008 ; 10,00
04/01/2008 ; 06/01/2008 ; 15,00
07/01/2008 ; 08/01/2008 ; 10,00

Si je passe par le SELECT suivant:

Code :

1
2
3
4
 
SELECT MIN(DATE) AS DEBUT, MAX(DATE) AS FIN, PRIME
  FROM PRIME
 GROUP BY PRIME

j'obtiendrai

DEBUT ; FIN ; PRIME
----------------------------
01/01/2008 ; 08/01/2008 ; 10,00
04/01/2008 ; 06/01/2008 ; 15,00

et c'est ce que je ne veux pas.

[Ptit_Dje]

Comment définis tu la date de début et la date de fin ?
Est ce arbitrairement fixé ?
Est ce une contrainte business ?
Est ce sur la variation de la valeur de la prime ?

[lipsum]

La date de début et de fin sont le min et la max du champ DATE pour toutes les lignes contiguës avec la même PRIME

[SQLpro]

Ce que vous voulez faire c'est de l'agrégation de période. Les requêtes pour faire cela sont EXTREMEMENT compliquées. Soit le nombre de tranche est peu élevé et je vous conseille de faire cela dans un curseur, soit le nombre de ligne est élevé et dans ce cas il y a du travail.

Ayant déjà, fait cela pour un de mes clients, je peut vous dire que c'est le genre de requête qui m'a demandé 2 jours de boulots facturé 1600 euros...

A +

[azur668]

Initialisation des tables :

Code :

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
 
CREATE TABLE Primes (id int identity, jour date, Montant money)
--Jeu d'essai sans trous ni doublons:
INSERT INTO Primes (jour, Montant)
VALUES 
('20080101','10'),
('20080102','10'),
('20080103','10'),
('20080104','15'),
('20080105','15'),
('20080106','15'),
('20080107','10'),
('20080108','10'),
('20080109','10'),
('20080110','10')
--Jeu d'essai avec trous et doublons:
INSERT INTO Primes (jour, Montant)
VALUES 
('20080102','10'),
('20080102','10'),
('20080103','10'),
('20080104','15'),
('20080105','15'),
('20080107','15'),
('20080117','10'),
('20080118','10'),
('20080118','10'),
('20080118','10')

Solution avec CTE :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
WITH PrimesTrie AS (
    SELECT *, tri = ROW_NUMBER() over (ORDER  BY Jour)  FROM Primes
),
cte AS (
    SELECT ID, ID AS Debut, tri, Jour,Montant FROM PrimesTrie AS P1 WHERE NOT EXISTS(SELECT * FROM PrimesTrie AS P3 WHERE P3.Montant  = P1.Montant AND ( P3.tri = P1.tri - 1))
    union ALL
    SELECT P2.Id, cte.Debut,P2.tri, p2.Jour, P2.Montant  FROM PrimesTrie AS P2
    INNER JOIN cte ON P2.Montant = cte.Montant AND P2.tri = cte.tri + 1      
    )
SELECT  du=MIN(Jour), au=MAX(Jour),Montant, nb = COUNT(*)  FROM cte GROUP BY Debut, Montant ORDER BY debut OPTION (MAXRECURSION 0)

L'ennui avec CTE c'est que chaque écart de période consomme un niveau de récursivité.
On est obligé de mettre MAXRECURSION 0 pour fonctionner avec des zones contigües de plus de 100 jours.
Je ne sait pas ce que ça vaut niveau perfs.

Solution sans CTE :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH PrimesTrie AS (
    SELECT *, tri = ROW_NUMBER() over (ORDER  BY Jour) FROM Primes
),
Debuts AS (
    SELECT NumPeriode= row_number()  over (ORDER  BY tri), ID AS Debut, tri, Jour,Montant FROM PrimesTrie AS P1 WHERE NOT EXISTS(SELECT * FROM PrimesTrie AS P3 WHERE P3.Montant  = P1.Montant AND P3.tri = P1.tri - 1)
    ),
Fins AS (
    SELECT  NumPeriode= row_number()  over (ORDER  BY tri) , ID AS Fin, tri, Jour,Montant FROM PrimesTrie AS P1 WHERE NOT EXISTS(SELECT * FROM PrimesTrie AS P3 WHERE P3.Montant  = P1.Montant AND P3.tri = P1.tri + 1)
    ),
periodes AS (
    SELECT Debuts.NumPeriode,Debut, fin FROM Debuts INNER JOIN Fins ON debuts.NumPeriode = fins.NumPeriode
    ),
Groupes AS (
    SELECT NumPeriode, Id, tri, Jour, Montant  FROM PrimesTrie AS P1
    INNER JOIN periodes ON P1.tri BETWEEN Debut AND Fin
    )
SELECT NumPeriode,  du=MIN(Jour), au=MAX(Jour), Montant  FROM Groupes GROUP BY NumPeriode,Montant ORDER BY NumPeriode

La rupture se fait uniquement sur le Montant, avec tri primaire sur la date. Les jours en double et les jours espacés sont considérés comme contigus.

On peut un peu modifier les critères pour ne pas considérer comme contigus les jours espacés ou les jours en double.

Si les tarifs n'ont pas bougés depuis 2008, je voudrais bien monnayer mes CTE à 1600 euros

[michaud_fr]

Une question, est ce pour une utilisation unique? ou pour un lancement régulier d'une requête?

Pour une utilisation unique, avec un cursor, cela est le plus simple. On parcourt la table et on la met à jour.

Pour une utilisation régulière à mon avis il manque fonctionnellement des datas.

Se baser sur le changement d'une valeur pour calculer des périodes, c'est pas top.

[azur668]

Envoyé par michaud_fr

On parcourt la table et on la met à jour.

Qui parle de mise à jour ici ?

Envoyé par michaud_fr

Pour une utilisation régulière à mon avis il manque fonctionnellement des datas..

Quelles data manque-t-il ?
Quel rapport avec l'utilisation régulière ou unique ?

Envoyé par michaud_fr

Se baser sur le changement d'une valeur pour calculer des périodes, c'est pas top.

Pourquoi pas ?
si ce besoin est exprimé, c'est qu'il correspond à quelque chose, non ?

[SQLpro]

J'ai publié différentes solution sur mon blog, il y a un certain temps :
http://blog.developpez.com/sqlpro/p7...valles-en-sql/
http://blog.developpez.com/sqlpro/p9...lles-en-sql-1/

A +

[patic]

Bonjour SqlPro,

Existe t il des sortes de "patterns" en sql comme il en existe en développement ?
Bien souvent on peut trouver différentes solutions pour résoudre un problème, hors à moins de prendre beaucoup de temps pour les chercher toutes, puis les tester pour en désigner la ou les meilleures, on prend la première idée qui fonctionne.
Des livres intéressants peut être ?

Merci

[aieeeuuuuu]

Bonjour,

Voici une autre solution sans curseur ni récursivité. Il lui faudra cependant un index sur (jour, montant)

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
 
SELECT 
	MIN(p1.jour) AS Debut, 
	MAX(p1.jour) AS Fin, 
	MAX(montant) AS Montant
FROM primes p1
OUTER APPLY (
	SELECT TOP(1) jour
	FROM primes p2
	WHERE p2.montant <> p1.montant
		AND p2.jour > p1.jour
	ORDER BY Jour
	) T
GROUP BY T.jour
ORDER BY Debut

[azur668]

Très belle solution !
Surement bien plus efficace que la mienne avec CTE, sous-requêtes et fonctions de fenêtrage !

Envoyé par aieeeuuuuu

Il lui faudra cependant un index sur (jour, montant)

Pas plus que avec les autres solutions.
Et puis c'est juste pour les perfs, si il y a que 1000 lignes l'index est inutile