Discussion :

[enesideme]

Bonjour à tous,

je dispose d'un dump SQL d'une base de licences sportives fédérales qui contient, pour ce qui m'intéresse ici, une table licences. Cette table contient toutes les licences prises depuis 2010 jusqu'à aujourd'hui. La table contient entre autre 3 colonnes utiles pour mon propos. Une colonne saison qui indique la saison sportive de la prise de licence (2010, 2011,....2021), une colonne comportant les "code_adherent" et une colonne primo indiquant par 1 ou 0 si le licencié est un primo adhérent. Malheureusement cette colonne n'est renseignée que pour la saison 2021 mais pas pour les plus anciennes.
Je me propose donc de mettre à jour cette colonne sur ma copie locale afin d'identifier saison par saison les primo-adhérents et les renouvellements de licence à des fins statistiques.

voilà comment j'ai pensé résoudre mon problème:

1. Faire de toutes les licences prises en 2010 des primo-licences puisqu'il n'y a pas de saison antérieure (la valeur par défaut de 2010 à 2020 est 0).

Code :

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

1
2
3
4
5
 
/* set primo saison 2010 default 1 */
UPDATE licences 
set primo = 1
WHERE saison = 2010;

2. Mettre à jour la colonne primo pour la saison suivante en cherchant si les" code_adherent" en 2011 existent pour la saison 2010. Si c'est le cas ce sont des renouvellements sinon des primo-licences.

Code :

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

1
2
3
4
5
6
7
8
9
10
 
/* set primo saison 2011 */
UPDATE licences 
set primo = 1
where saison = 2011 and code_adherent in 
(SELECT code_adherent from licences l2 
where saison = 2011 and code_adherent not in 
(SELECT code_adherent FROM  licences l 
WHERE saison = 2010))
;

3. ...
et ainsi jusqu'à 2020

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
/* set primo saison 2020 */
UPDATE licences 
set primo = 1
where saison = 2020 and code_adherent in 
(SELECT code_adherent from licences l2 
where saison = 2020 and code_adherent not in 
(SELECT code_adherent FROM  licences l 
WHERE saison = 2010 or saison = 2011 or saison = 2012 or saison = 2013 or saison = 2014 
      or saison = 2015 or saison = 2016 or saison = 2017 or saison = 2018 or saison = 2019))
;

Ma première question est la suivante:

La démarche est-elle correcte ? i.e. Est-ce qu'elle fait bien ce que je veux faire.
Et éventuellement, y a-t-il une manière plus élégante et plus économe en code de faire la même chose ?

Ma deuxième question concerne un problème lié à la saison 2021 dont le champ primo est déjà renseigné.

si j'effectue la requête suivante pour déterminer le nombre de renouvellements en 2021:

Code :

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

1
2
3
4
5
6
 
SELECT count(code_adherent) from licences l2 
where saison = 2021 and code_adherent in 
(SELECT code_adherent FROM  licences l 
WHERE saison = 2010 or saison = 2011 or saison = 2012 or saison = 2013 or saison = 2014 
or saison = 2015 or saison = 2016 or saison = 2017 or saison = 2018 or saison = 2019 or saison = 2020);

j'obtiens 9126 licences qui existent déjà de 2010 à 2020 et 6935 primo licenciés en substituant au IN de ma requête NOT IN.

si maintenant je vérifie sur la base

Code :

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

1
2
3
4
 
select primo, count(primo) from licences l 
where saison = 2021
group by primo;

j'obtiens respectivement 9101 et 6960 sot une différence de 25 (25 renouvellements en moins qui basculent en primo). Le total restant le même. Il y a donc une erreur.

Comment donc identifier les licences (code_adherent ) concernées ?

Merci d'avance pour vos réponses

[Waldar]

Non ce n'est pas terrible comme approche, il faut raisonner en ensemble.
Vous n'avez pas besoin de la colonne dans la table, une vue fait le travail.

Vous n'avez pas indiqué votre SGBD donc cette solution pourrait ne pas fonctionner.

Code :

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

1
2
3
4
5
create view v_licences (code_adherent, saison, primo) as
select code_adherent
     , saison
     , case saison when min(saison) over(partition by code_adherent) then 1 else 0 end
  from licences;

Si vous voulez vraiment le stocker dans une table, faites un seul update :

Code :

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

1
2
3
4
5
6
update licences t1
   set t1.primo = 1
 where not exists (select null
                     from licences t2
                    where t2.code_adherent = t1.code_adherent 
                      and t2.saison        < t1.saison);

Et gérez la vie de cette colonne avec une valeur par défaut à 0 et un déclencheur before insert pour vérifier s'il s'agit d'un nouvel adhérent - la syntaxe des déclencheurs étant encore plus spécifiques aux différents SGBD je ne me risque pas à en proposer une.

[enesideme]

Merci pour votre réponse,

Le SGBD est mariaDB. mais je n'administre pas la base de données, j'ai accès à un dump partiel de la base pour faire mes essais (je développe une application de visualisations statistiques en python) et un accès à Redash qui est connecté à la base pour faire des tableaux de boards mais à partir duquel je ne peux modifier la base. Redash suppose des requêtes sql (voilà pourquoi il faut que je m'y mette un peu plus sérieusement).
Je peux par contre modifier la base créée à partir du dump qui sert pour mes tests en local.
Je ne sais pas si Redash permet de créer des vues.
En tout cas merci je vais tester votre solution dès que possible.

[enesideme]

Comme je le craignais, Redash ne permet pas de créer des vues.
Votre solution marche à merveille avec une très belle économie de code.

[enesideme]

Si vous voulez vraiment le stocker dans une table, faites un seul update :

Code :

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

1
2
3
4
5
6
update licences t1
   set t1.primo = 1
 where not exists (select null
                     from licences t2
                    where t2.code_adherent = t1.code_adherent 
                      and t2.saison        < t1.saison);

Et gérez la vie de cette colonne avec une valeur par défaut à 0 et un déclencheur before insert pour vérifier s'il s'agit d'un nouvel adhérent - la syntaxe des déclencheurs étant encore plus spécifiques aux différents SGBD je ne me risque pas à en proposer une.

Si la création d'une vue fonctionne parfaitement, le code proposé ci-dessus semble ne jamais s'arrêter - j'ai dû l'interrompre au bout de 500 secondes. (la table n'a que 225 000 lignes). Mon script inélégant s’exécute en 3 secondes et donne un résultat parfaitement identique sur le champ primo à celui de la vue que vous avez proposée. De toute façon seul l'administrateur de la base qui relève d'une entreprise externe à ma fédération sera en mesure de faire la modification.

[SQLpro]

Si la création d'une vue fonctionne parfaitement, le code proposé ci-dessus semble ne jamais s'arrêter - j'ai dû l'interrompre au bout de 500 secondes. (la table n'a que 225 000 lignes). Mon script inélégant s’exécute en 3 secondes et donne un résultat parfaitement identique sur le champ primo à celui de la vue que vous avez proposée. De toute façon seul l'administrateur de la base qui relève d'une entreprise externe à ma fédération sera en mesure de faire la modification.

C'est sans doute normal.... MySQL en est à l'âge de pierre au niveau du relationnel comparé à Oracle ou SQL Server....
En particulier MySQL (ou MariaDB qui est encore pire) font une table temporaires pour stocker les résultats de la vue plutôt que d'intégrer le SQL dans la requête finale, d’où les temps exorbitants de traitement...

A lire sur MySQL https://blog.developpez.com/sqlpro/p...oudre_aux_yeux


A +