Manipulation des générateurs et séquences

Version imprimable

Bonjour à toutes et à tous.

Bonne année 2016. Bonne santé. Mes meilleurs vœux !

Suite à une conversation dans ce sujet : http://www.developpez.net/forums/d15...e/#post8489072
j'ai demandé comment on pouvait résoudre le problème posé par "Le Néophyte", autrement que par une procédure stockée.
Comme sa demande est faite pour MySql, je pense, sans trop me tromper, que c'est la seule solution possible dans cet environnement.

Suite à ma demande, SqlPro donne une solution faisable sous 'Microsoft Sql Server', dont je ne possède pas le SGBD sur mon ordinateur.
De plus, je ne connais pas du tout cette fonctionnalité qu'est le 'DENSE_RANK()'.

J'ai l'impression que sous FireBird, on peut résoudre ceci, en utilisant le "generator".
Je suis arrivé à le faire fonctionné mais je ne suis pas arrivé à remettre les deux générateurs de séquence à zéro sur une rupture de séquence sur le couple (course ; clagen).
('clagen' signifie 'classement général').

Voici ce que j'obtiens :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
DROP   DATABASE;
CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET ISO8859_1;
 
-- ===================
-- création "sequence"
-- ===================
 
CREATE SEQUENCE seq$rang;
 
-- =======================
-- création table "compet"
-- =======================
 
create table compet (
id       integer   not null primary key,
prenom   char(10)  not null collate fr_fr,
course   char(10)  not null collate fr_fr,
clagen   smallint  not null,
sexe     char(01)  not null  collate fr_fr
);
 
-- ===========================
-- création tigger 'increment'
-- ===========================
 
SET TERM #;
 
CREATE TRIGGER increment for compet
active before insert position 0
as
begin
  new.id = gen_id(seq$rang, 1);
END#
 
SET TERM ;#
 
-- =======================
-- insertion dans 'compet'
-- =======================
 
insert into compet (prenom, course, clagen, sexe) values ('Marc',       'leMans2005', 1, 'H');
insert into compet (prenom, course, clagen, sexe) values ('Jeanne',     'leMans2005', 2, 'F');
insert into compet (prenom, course, clagen, sexe) values ('Mireille',   'leMans2005', 3, 'F');
insert into compet (prenom, course, clagen, sexe) values ('Bruno',      'leMans2005', 4, 'H');
insert into compet (prenom, course, clagen, sexe) values ('Maurice',    'Monaco2006', 1, 'H');
insert into compet (prenom, course, clagen, sexe) values ('Christian',  'Monaco2006', 2, 'H');
insert into compet (prenom, course, clagen, sexe) values ('Sylvie',     'Monaco2006', 3, 'F');
insert into compet (prenom, course, clagen, sexe) values ('corinne',    'Monaco2006', 4, 'F');
insert into compet (prenom, course, clagen, sexe) values ('Bernadette', 'Monaco2006', 4, 'F');
insert into compet (prenom, course, clagen, sexe) values ('Elise',      'Monaco2006', 6, 'F');
 
-- ==================
-- Vidage de 'compet'
-- ==================
 
select * from compet;
 
          ID PRENOM     COURSE      CLAGEN SEXE
============ ========== ========== ======= ======
           1 Marc       leMans2005       1 H
           2 Jeanne     leMans2005       2 F
           3 Mireille   leMans2005       3 F
           4 Bruno      leMans2005       4 H
           5 Maurice    Monaco2006       1 H
           6 Christian  Monaco2006       2 H
           7 Sylvie     Monaco2006       3 F
           8 corinne    Monaco2006       4 F
           9 Bernadette Monaco2006       4 F
          10 Elise      Monaco2006       6 F
 
 
commit;
 
-- ====================
-- création "generator"
-- ====================
 
CREATE GENERATOR gen$hom;
CREATE GENERATOR gen$fem;
 
-- ================
-- Requête sans CTE
-- ================
 
select prenom, course, clagen, sexe,
       case sexe when 'H' then next value for gen$hom else null end as clahom,
       case sexe when 'F' then next value for gen$fem else null end as clafem
from      compet
order by course, clagen
;
 
PRENOM     COURSE      CLAGEN SEXE                  CLAHOM                CLAFEM
========== ========== ======= ====== ===================== =====================
Marc       leMans2005       1 H                          1                <null>
Jeanne     leMans2005       2 F                     <null>                     1
Mireille   leMans2005       3 F                     <null>                     2
Bruno      leMans2005       4 H                          2                <null>
Maurice    Monaco2006       1 H                          3                <null>
Christian  Monaco2006       2 H                          4                <null>
Sylvie     Monaco2006       3 F                     <null>                     3
Bernadette Monaco2006       4 F                     <null>                     4
corinne    Monaco2006       4 F                     <null>                     5
Elise      Monaco2006       6 F                     <null>                     6
 
 
-- ====================
-- création "generator"
-- ====================
 
DROP GENERATOR gen$hom;
DROP GENERATOR gen$fem;
 
exit;
 
Appuyez sur une touche pour continuer...

On remarque que pour la première course, j'obtiens bien la bonne séquence du classement homme (clahom) et femme (clafem).
Sauf que je ne sais pas comment le remettre à zéro sur la rupture de séquence.

Autre point important. Dans le classement général, il y deux fois 4. Autrement dit, il y a deux ex æquo !
Il faudra aussi que le résultat dans le classement des femmes donne deux fois la valeur '2'.
Le prochain résultat n'est pas trois, mais quatre.

@+

04/01/2016, 16h50
SergioMaster

Bonjour et meilleurs vœux

je vais faire une réponse rapide car je n'ai déjà pas le temps de m'étendre sur le sujet
avec Firebird 3 la question ne se poserait pas car RANK, DENSE_RANK sont implémentés
avec les versions inférieures je renvoi vers cette FAQ

enfin je n'utiliserais pas des Séquences mais de simples variables de contexte
04/01/2016, 19h18
Artemus24

Salut SergioMaster.

Merci pour les vœux.

J'ai découvert le lien que tu m'as communiqué avant que tu me le donnes et à vrai dire, je n'ai rien compris de ce qu'il faut faire. Peu importe.
Je vais basculer vers FireBird 3 pour faire le test.

@+

Salut à tous.

J'ai effectivement installé FireBird version 3.0.0 et j'ai fait le test.
Voici le résultat :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
DROP   DATABASE;
CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET ISO8859_1;
 
-- =======================
-- création table "compet"
-- =======================
 
create table compet (
prenom   char(10)  not null collate fr_fr,
course   char(10)  not null collate fr_fr,
clagen   smallint  not null,
sexe     char(01)  not null collate fr_fr
);
 
-- =======================
-- insertion dans 'compet'
-- =======================
 
insert into compet (prenom, course, clagen, sexe) values ('Marc',       'leMans2005', 1, 'H');
insert into compet (prenom, course, clagen, sexe) values ('Jeanne',     'leMans2005', 2, 'F');
insert into compet (prenom, course, clagen, sexe) values ('Mireille',   'leMans2005', 3, 'F');
insert into compet (prenom, course, clagen, sexe) values ('Bruno',      'leMans2005', 4, 'H');
insert into compet (prenom, course, clagen, sexe) values ('Maurice',    'Monaco2006', 1, 'H');
insert into compet (prenom, course, clagen, sexe) values ('Christian',  'Monaco2006', 2, 'H');
insert into compet (prenom, course, clagen, sexe) values ('Sylvie',     'Monaco2006', 3, 'F');
insert into compet (prenom, course, clagen, sexe) values ('corinne',    'Monaco2006', 4, 'F');
insert into compet (prenom, course, clagen, sexe) values ('Bernadette', 'Monaco2006', 4, 'F');
insert into compet (prenom, course, clagen, sexe) values ('Elise',      'Monaco2006', 6, 'F');
 
-- ==================
-- Vidage de 'compet'
-- ==================
 
select * from compet;
 
PRENOM     COURSE      CLAGEN SEXE
========== ========== ======= ======
Marc       leMans2005       1 H
Jeanne     leMans2005       2 F
Mireille   leMans2005       3 F
Bruno      leMans2005       4 H
Maurice    Monaco2006       1 H
Christian  Monaco2006       2 H
Sylvie     Monaco2006       3 F
corinne    Monaco2006       4 F
Bernadette Monaco2006       4 F
Elise      Monaco2006       6 F
 
 
commit;
 
-- ================
-- Requête avec CTE
-- ================
 
WITH temp AS
(SELECT prenom, course, clagen, sexe,
        rank() over (partition by course, sexe order by course, clagen) as rang
 FROM   COMPET)
SELECT  prenom, course, clagen, sexe,
        CASE WHEN Sexe = 'H' THEN rang END AS clahom,
        CASE WHEN Sexe = 'F' THEN rang END AS clafem
FROM    temp
order by course, clagen;
 
PRENOM     COURSE      CLAGEN SEXE                  CLAHOM                CLAFEM
========== ========== ======= ====== ===================== =====================
Marc       leMans2005       1 H                          1                <null>
Jeanne     leMans2005       2 F                     <null>                     1
Mireille   leMans2005       3 F                     <null>                     2
Bruno      leMans2005       4 H                          2                <null>
Maurice    Monaco2006       1 H                          1                <null>
Christian  Monaco2006       2 H                          2                <null>
Sylvie     Monaco2006       3 F                     <null>                     1
Bernadette Monaco2006       4 F                     <null>                     2
corinne    Monaco2006       4 F                     <null>                     2
Elise      Monaco2006       6 F                     <null>                     4
 
 
exit;
 
Appuyez sur une touche pour continuer...

En mettant 'dense_rank()', je n'obtiens pas tout à fait le résultat escompté. La solution est d'utiliser 'rank()' !

Intéressant comme fonctionnalité, surtout que c'est bien plus simple que de faire une procédure stockée et de gérer les ruptures de séquences.

@+

08/01/2016, 11h13
escartefigue

Citation:

Envoyé par Artemus24

En mettant 'dense_rank()', je n'obtiens pas tout à fait le résultat escompté. La solution est d'utiliser 'rank()' !
Intéressant comme fonctionnalité, surtout que c'est bien plus simple que de faire une procédure stockée et de gérer les ruptures de séquences.

Ce sera aussi plus performant
La différence entre RANK et DENSE_RANK consiste à ce que DENSE_RANK attribue une numérotation contigüe même si le rang qui précède présente des égalités
Exemple : s'il existe 2 classés 3ème ex-aequo
RANK donne 1, 2, 3, 3, 5, 6 etc..
DENSE_RANK donne 1, 2, 3, 3, 4, 5 etc...
09/01/2016, 12h24
Artemus24

Salut Escartefigue.

Merci Escartefigue, pour tes explications :D
J'ai fait quelques tests et je me suis aperçu de ces différences de fonctionnements.

@+