Calcul incrément par groupement

Version imprimable

Bonjour,

Je coince sur une requête , voici un exemple d'input :

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
 
SELECT 'Paris' as ville,5 as numero, 'A' as lettre
UNION
SELECT 'Paris' as ville,6 as numero, 'A' as lettre
UNION
SELECT 'Paris' as ville,8 as numero, 'B' as lettre
UNION
SELECT 'Paris' as ville,12 as numero, 'B' as lettre
UNION
SELECT 'Paris' as ville,19 as numero, 'A' as lettre
UNION
SELECT 'Paris' as ville,20 as numero, 'A' as lettre
UNION
SELECT 'Bruxelles' as ville,5 as numero, 'C' as lettre
UNION
SELECT 'Bruxelles' as ville,7 as numero, 'C' as lettre
UNION
SELECT 'Bruxelles' as ville,8 as numero, 'B' as lettre
UNION
SELECT 'Bruxelles' as ville,13 as numero, 'B' as lettre
UNION
SELECT 'Bruxelles' as ville,21 as numero, 'C' as lettre
UNION
SELECT 'Bruxelles' as ville,22 as numero, 'C' as lettre

Output désiré :

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
 
SELECT 'Paris' as ville,5 as numero, 'A' as lettre , 1 as result
UNION
SELECT 'Paris' as ville,6 as numero, 'A' as lettre , 1 as result
UNION
SELECT 'Paris' as ville,8 as numero, 'B' as lettre , 2 as result
UNION
SELECT 'Paris' as ville,12 as numero, 'B' as lettre , 2 as result
UNION
SELECT 'Paris' as ville,19 as numero, 'A' as lettre , 3 as result
UNION
SELECT 'Paris' as ville,20 as numero, 'A' as lettre , 3 as result
UNION
SELECT 'Bruxelles' as ville,5 as numero, 'C' as lettre , 1 as result
UNION
SELECT 'Bruxelles' as ville,7 as numero, 'C' as lettre , 1 as result
UNION
SELECT 'Bruxelles' as ville,8 as numero, 'B' as lettre , 2 as result
UNION
SELECT 'Bruxelles' as ville,13 as numero, 'B' as lettre , 2 as result
UNION
SELECT 'Bruxelles' as ville,21 as numero, 'C' as lettre , 3 as result
UNION
SELECT 'Bruxelles' as ville,22 as numero, 'C' as lettre , 3 as result

Ce que je désire:
-Regrouper par ville
-Pour chaque ville , trier par numéro croissant
-A chaque numéro est associée une lettre.Si la lettre change , je doit incrémenter le résultat (même si l'on repasse à la même lettre que précédemment ).
-Le résultat est remis à 0 à chaque changement de ville
-L'objectif est d'avoir la colonne result

Une idée?
Merci d'avance ;)

20/07/2016, 20h53
SQLpro

Il n'existe par d'ordre naturel des lignes dans une base de données. Vous devez baser votre logique sur tout autre chose que la lecture itérative des lignes car jamais aucun ordre des lignes dans une table n'est garantie. Or votre demande suppose que l'ordre d'apparition des lignes ait un sens pour vous ce qui est naturellement infaisable.

Vous devez donc rajouter les informations manquantes dans votre table, par exemple un séquencement des lignes ou encore une horodatation.

A +

1 pièce(s) jointe(s)

Par exemple, avec la table suivante :

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
SELECT *
INTO   MA_TABLE
FROM 
(
SELECT 'Paris' as ville,5 as numero, 'A' as lettre, 1 as ligne
UNION
SELECT 'Paris' as ville,6 as numero, 'A' as lettre, 2 as ligne
UNION
SELECT 'Paris' as ville,8 as numero, 'B' as lettre, 3 as ligne
UNION
SELECT 'Paris' as ville,12 as numero, 'B' as lettre, 4 as ligne
UNION
SELECT 'Paris' as ville,19 as numero, 'A' as lettre, 5 as ligne
UNION
SELECT 'Paris' as ville,20 as numero, 'A' as lettre, 6 as ligne
UNION
SELECT 'Bruxelles' as ville,5 as numero, 'C' as lettre, 7 as ligne
UNION
SELECT 'Bruxelles' as ville,7 as numero, 'C' as lettre, 8 as ligne
UNION
SELECT 'Bruxelles' as ville,8 as numero, 'B' as lettre, 9 as ligne
UNION
SELECT 'Bruxelles' as ville,13 as numero, 'B' as lettre, 10 as ligne
UNION
SELECT 'Bruxelles' as ville,21 as numero, 'C' as lettre, 11 as ligne
UNION
SELECT 'Bruxelles' as ville,22 as numero, 'C' as lettre, 12 as ligne
) AS T;

Dans laquelle j'ai rejouté une colonne "ligne" avec une numérotation séquentielle des lignes, la requête pour solutionner votre problème devient :

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
WITH
T0 AS 
(
SELECT ville, numero, lettre, ROW_NUMBER() OVER(PARTITION BY ville ORDER BY numero) AS N
FROM MA_TABLE
),
T1
AS
(
SELECT *, 
       LAG(N) OVER(PARTITION BY ville ORDER BY N) AS PRED, 
       LEAD(N) OVER(PARTITION BY ville ORDER BY N) AS SUCC 
FROM T0
),
T2 AS
(
SELECT ville, numero, lettre, N, CAST(1 AS INT) AS C
FROM  T1
WHERE  PRED IS NULL
UNION ALL
SELECT T2.ville, T1.numero, T1.lettre, T1.N, CASE WHEN T1.lettre  = T2.lettre THEN T2.C ELSE T2.C + 1 END
FROM   T2
       JOIN T1  
            ON T1.PRED = T2.N AND T1.ville = T2.ville AND T2.numero < T1.numero
)
SELECT *
FROM   T2
ORDER BY ville, numero, lettre;

Et donne :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ville     numero      lettre N     C
--------- ----------- ------ ----- -----
Bruxelles 5           C      1     1
Bruxelles 7           C      2     1
Bruxelles 8           B      3     2
Bruxelles 13          B      4     2
Bruxelles 21          C      5     3
Bruxelles 22          C      6     3
Paris     5           A      1     1
Paris     6           A      2     1
Paris     8           B      3     2
Paris     12          B      4     2
Paris     19          A      5     3
Paris     20          A      6     3

Pour apprendre le langage SQL, mon livre commemon site web peut vous être utile...

Pièce jointe 215979

A +

22/07/2016, 10h58
vinch999

Merci pour la réponse,

Je n'avais pas pensé à la CTE récursive.

Par contre, je n'ai pas vraiment compris le pourquoi de votre 1er commentaire. (la table que j'ai proposée n'est qu'un exemple, je sais bien qu'il faut un id ;))
Et pour info, votre méthode fonctionne sur ma table de test également.

En tous cas ça marche sur ma véritable table , merci ! ;)