CTE et Window Functions avec MariaDB

GNU/Linux Magazine

n°

211

janvier 2018

Par

Tag(s)

SQL

MariaDB

La version 10.2 de MariaDB apporte un panel de nouveautés particulièrement attractif. Parmi ces nouveautés, j’en ai retenu deux que je vais développer ici : les Common Table Expressions normales et récursives, et les Window Functions. Avec ces deux nouveautés, MariaDB joue enfin dans la cour des grands, et vient concurrencer frontalement PostgreSQL, ainsi que plusieurs SGBD propriétaires.

Le support des CTE (Common Table Expressions) est apparu dans MariaDB avec la version 10.2.1. Les CTE récursives sont arrivées avec la version 10.2.2 alors que les Window Functions (WF) - ou en français « fonctions de fenêtrage » - sont, elles, apparues dès la version 10.2.0.

Découvrant la situation avec un métro de retard, je me suis empressé de télécharger la dernière version stable disponible (qui est la 10.2.9, à l’heure où j’écris ces lignes). Et surtout je me suis empressé de puiser dans mon arsenal quelques requêtes SQL complexes pour voir ce que MariaDB avait dans le ventre… et je dois dire que je ne suis pas déçu : avec cette version 10.2, MariaDB entre véritablement dans la cour des grands SGBD [1]. Pour vous en convaincre, je vous propose une visite guidée dans les CTE et WF à la sauce MariaDB.

Pour la préparation de cet article, j’ai testé les mêmes requêtes SQL sur MariaDB 10.2.9 et PostgreSQL 9.6.3. PostgreSQL m’a donné une excellente base de référence pour évaluer les nouveautés de MariaDB.

1. Petite présentation des CTE

J’en ai longtemps rêvé, ça y est, les développeurs de MariaDB l’ont fait. Il est enfin possible d’utiliser des CTE dans le petit monde du MySQL… à condition de migrer bien évidemment sur MariaDB.

Alors c’est quoi ces... CTE ?

La CTE, c’est la possibilité d’écrire autrement son code SQL. Quand j’écris « autrement », je veux dire par là, d’une manière plus lisible, plus maintenable… plus mieux quoi !!!

Avant les CTE, quand vous deviez écrire une requête complexe avec MySQL, vous pouviez être amené à écrire quelque chose dans ce genre :

SELECT *

FROM (

SELECT *

FROM (

SELECT *

FROM (

SELECT * FROM tablex WHERE x <> y

)

WHERE condition machin

)

WHERE condition truc

)

WHERE condition bidule

Cette requête (bidon) utilise plusieurs sous-requêtes imbriquées (via la clause FROM), la requête la plus « en profondeur » étant exécutée en premier, pour renvoyer un jeu de données à la requête de niveau supérieur, qui elle-même renvoie un jeu de données à la requête de niveau supérieur, qui...

C’est horrible, non ? On se croirait dans un cauchemar à la « Inception » (le film de Christopher Nolan).

Et encore, j’ai été gentil, je n’ai pas mis de jointure dans tout ça. C’est un exemple scolaire, rien à voir avec la vraie vie. Dans la vraie vie, je connais des requêtes de ce type qui font plus de 80 lignes, avec des jointures sur une douzaine de tables.

Quand j’interviens en maintenance sur une requête de ce type, chez un client qui se plaint de problèmes de maintenabilité ou de problèmes de performances (et souvent des deux à la fois), mon premier réflexe consiste à voir si je peux réécrire cette requête avec des CTE. Avec PostgreSQL (à partir de la version 9.1) comme avec certains SGBD propriétaires, les CTE permettent souvent de remettre d’aplomb un code SQL confus. Notre requête bidon précédente pourrait donc prendre la forme suivante :

WITH

cte1 AS (

SELECT * FROM tablex WHERE x <> y

)

cte2 AS (

SELECT * FROM cte1 WHERE condition machin

)

cte3 AS (

SELECT * FROM cte2 WHERE condition truc

)

cte4 AS (

SELECT * FROM cte1 WHERE condition bidule

)

SELECT * FROM cte4

Le mot-clé WITH permet de démarrer une CTE, ou une série de CTE (ici cte1, cte2, etc.). Chaque CTE peut être totalement indépendante des autres, ou au contraire exploiter le résultat des CTE qui la précèdent. On peut par exemple écrire une CTE qui effectue une jointure entre une CTE précédente et une table SQL (ou une vue SQL). Au fait, vous définissez comme vous voulez le nom de vos CTE, alors n’hésitez pas à utiliser des noms plus parlants que ceux que j’ai utilisés ici. Attention toutefois aux conflits de nommage avec des objets SQL existants par ailleurs. C’est la raison pour laquelle j’ai pris pour habitude de préfixer mes CTE par cte, mais ce n’est pas une obligation.

On notera qu’entre chaque CTE, il faut impérativement placer une virgule, et qu’il n’y a pas de virgule avant la toute dernière requête, qui est la seule à renvoyer le résultat final au client de la requête.

Un point très important : une CTE peut être réutilisée plusieurs fois (par les CTE suivantes et par la requête finale si besoin). Ce n’était pas possible avec le système des requêtes imbriquées tel qu’il était appliqué dans la requête précédente. C’est un axe important d’optimisation pour corriger des problèmes de performances.

Bon, la théorie, c’est bien beau, mais un exemple concret, c’est mieux.

Il nous faut un jeu de données, alors je vous propose celui-ci :

CREATE TABLE t_ventes (

codpays VARCHAR(20),

ville VARCHAR(20),

mnt_vte DECIMAL(10,2),

dat_vte DATE

);

INSERT INTO t_ventes (codpays, ville, mnt_vte, dat_vte)

VALUES

('FR', 'Paris', 200, '2017-10-01'),

('FR', 'Paris', 800, '2017-09-01'),

('FR', 'Paris', 190, '2017-08-01'),

('FR', 'Paris', 230, '2017-10-03'),

('FR', 'Lyon', 200, '2017-10-05'),

('FR', 'Lyon', 390, '2017-09-05'),

('FR', 'Lyon', 720, '2017-08-05'),

('FR', 'Lyon', 110, '2017-10-05'),

('FR', 'Bordeaux', 160, '2017-08-03'),

('FR', 'Bordeaux', 500, '2017-10-05'),

('FR', 'Bordeaux', 330, '2017-09-05'),

('FR', 'Bordeaux', 120, '2017-08-05'),

('FR', 'Toulouse', 360, '2017-08-03'),

('FR', 'Toulouse', 600, '2017-10-05'),

('FR', 'Toulouse', 450, '2017-09-05'),

('FR', 'Toulouse', 720, '2017-08-05'),

('UK', 'Londres', 450, '2017-10-05'),

('UK', 'Londres', 530, '2017-09-05'),

('UK', 'Londres', 790, '2017-08-05'),

('UK', 'Londres', 330, '2017-07-05'),

('UK', 'Manchester', 200, '2017-08-01'),

('UK', 'Manchester', 330, '2017-07-01'),

('UK', 'Manchester', 120, '2017-10-03'),

('UK', 'Manchester', 640, '2017-09-03');

Voici un exemple de requête - composé de 2 CTE - générant un total des ventes par pays, suivi d’un total général :

WITH

cte_tot_country AS (

SELECT codpays, SUM(mnt_vte) AS tot_vte

FROM t_ventes

GROUP BY codpays

cte_tot_general AS (

SELECT SUM(tot_vte) as tot_vte

FROM cte_tot_country

)

SELECT codpays, tot_vte

FROM cte_tot_country

UNION

SELECT 'Total général', tot_vte

FROM cte_tot_general;

Le résultat produit par la requête précédente est représenté dans le tableau suivant :

codpays	tot_vte
UK	3390.00
FR	6080.00
Total général	9470.00

C’est un exemple un peu scolaire, mais je pense qu’il montre assez bien certaines possibilités des CTE. Quelques points intéressants à noter :

la seconde CTE s’appuie sur le jeu de données produit par la première CTE ;
le SELECT final utilise les jeux de données produits par les 2 CTE, en les liant via une clause UNION.

Nous étudierons d’autres exemples de CTE dans la suite de cet article.

Après les CTE normales, il est temps d’aborder l’autre catégorie de CTE...

2. Les CTE récursives

2.1 Principes généraux et cas relativement simples

Les CTE récursives obéissent toutes au schéma suivant :

WITH RECURSIVE r AS (

SELECT anchor_data

UNION [ALL]

SELECT recursive_part

FROM r, ...

)

SELECT ...

On a donc une requête initiale, ce que nos amis anglophones désignent par le terme « anchor data » (données d’ancrage), suivi d’une clause UNION (ou UNION ALL), et d’une sous-requête récursive.

Voici un exemple de requête renvoyant une série de valeurs, numérotées de 1 à 5 :

WITH RECURSIVE compteur(nx) AS (

SELECT 1 AS n1

UNION ALL

SELECT nx+1 AS n2 FROM compteur WHERE nx < 5

)

SELECT nx FROM compteur;

Le tableau obtenu est le suivant :

Cette requête en apparence anodine est très pratique pour reconstituer des données manquantes, par exemple pour générer la liste des 12 mois d’une année et réaliser une jointure avec une table des ventes dans laquelle certains produits n’ont pas de ventes réalisées sur certains mois. On pourra ainsi faire ressortir un montant de vente à zéro pour les produits et mois pour lesquels certains produits ont été invendus.

Autre exemple, peut être plus anecdotique celui-là, mais qui fait son petit effet dans les discussions de salon… la bonne vieille fonction « factorielle », en version récursive :

WITH RECURSIVE factorielle (n, fact) AS

(SELECT 0, 1 -- requête initiale

UNION ALL

SELECT n+1, (n+1)*fact

FROM factorielle

WHERE n < 9)

SELECT * FROM factorielle;

Bon, mais dans la « vraie vie », est-ce vraiment exploitable ?

Eh bien, disposer d’une CTE générant une série de valeurs, c’est très pratique, mais les limites minimums et maximums de cette série de valeurs ne sont pas toujours connues dès le départ. Or j’ai découvert à mes dépens que, si je souhaite utiliser une CTE récursive, elle doit être définie en premier dans la liste des CTE.

Voilà qui ne m’arrange guère, c’est un problème que je n’avais pas sur DB2, mais je découvre que PostgreSQL et MariaDB présentent la même contrainte. Heureusement, après quelques recherches dans les docs, j’ai découvert une solution qui fonctionne aussi bien avec PostgreSQL qu’avec MariaDB et qui consiste à encapsuler la déclaration de la CTE récursive à l’intérieur d’une CTE normale (qui peut porter le même nom que la CTE récursive, a priori ça ne pose pas de problème).

Voici un exemple dans lequel les limites minimums et maximums de la série de valeurs à générer sont définies dans une CTE d’initialisation. Ces valeurs limites sont exploitées un peu plus bas, à l’intérieur de la CTE récursive :

WITH

cte_init AS (

`SELECT 10 AS min, 20 AS max`

gen_ids AS (

WITH RECURSIVE gen_ids(nx) AS (

SELECT `min` AS n1 FROM `cte_init`

UNION ALL

SELECT nx+1 AS n2 FROM gen_ids WHERE nx < (SELECT `max` FROM `cte_init`)

) SELECT nx FROM gen_ids

cte2 AS (

SELECT * FROM gen_ids

)

SELECT * FROM cte2;

Je tiens à souligner que la CTE d’initialisation est une très bonne pratique si vous avez besoin de fixer des valeurs initiales qui devront être exploitées sur plusieurs niveaux d’une requête complexe. Cela évite de disséminer ces valeurs un peu partout dans la requête, et cela contribue à améliorer la robustesse et la maintenabilité du code.

Vous vous souvenez de notre table des ventes, créée en première section. Eh bien, je vous propose de l’exploiter, en coordination avec une CTE récursive, pour afficher le montant des ventes sur 52 semaines. Pour les semaines sans vente, une ligne avec un montant à zéro devra être générée. Voici la requête :

WITH

RECURSIVE tmp_gensem(nx) AS (

SELECT 1 as n1

UNION ALL

SELECT nx+1 as n2 FROM tmp_gensem WHERE nx < 52

tmp_tot_ventes AS (

SELECT codpays, ville, mnt_vte, dat_vte, DATE_FORMAT(dat_vte, '%u') AS semaine

FROM t_ventes

tmp_vte_sem AS (

SELECT semaine, SUM(mnt_vte) AS tot_vte

FROM tmp_tot_ventes

GROUP BY semaine

)

SELECT A.nx AS sem, ifnull(B.tot_vte, 0) AS tot_vte

FROM tmp_gensem A

LEFT OUTER JOIN tmp_vte_sem B

ON A.nx = B.semaine

ORDER BY A.nx

Je ne vous mets ci-dessous qu’un tout petit échantillon du tableau obtenu (car il fait 52 lignes dans sa version complète) :

sem	tot_vte
25	0.00
26	330.00
27	330.00
28	0.00
29	0.00
30	0.00
31	3260.00
32	0.00

Bon, c’est génial tout ça, on va pouvoir s’éclater avec ces CTE récursives, non ?

Eh bien, attention quand même, car j’ai rencontré un problème avec une CTE récursive, sur un cas assez spécifique. J’ai signalé le problème à l’équipe de dev de MariaDB, il y a quelques jours à peine, et je viens d’être averti du fait que cette anomalie sera corrigée sur la version 10.2.11 [2]. Cette version n’est pas disponible en téléchargement à l’heure où j’écris ces lignes, mais elle le sera très certainement au moment où vous lirez l’article. Dès que j’aurai pu tester la version 10.2.11, et si le bug est réellement éliminé, cela me donnera l’opportunité de vous proposer un article complémentaire ;-). Affaire à suivre, donc...

2.2 CTE récursives et structures hiérarchiques

Ah, les structures hiérarchiques en SQL, c’est toujours un régal à traiter… Non, je plaisante, c’est plutôt embêtant en fait. Mais avec les CTE récursives, ça deviendrait presque amusant (j’insiste sur le « presque »).

Pour les besoins de ma démonstration, voici un nouveau jeu de données :

CREATE TABLE squad(

empid INTEGER NOT NULL,

empname VARCHAR(10),

empsalary DECIMAL(9, 2),

managerid INTEGER,

PRIMARY KEY(empid)

);

INSERT INTO squad (empid, empname, empsalary, managerid)

VALUES

( 1, 'Smith', 30000, 10),

( 2, 'Wesson', 35000, 10),

( 3, 'Kim', 40000, 10),

( 4, 'Basinger', 38000, 10),

( 5, 'Tarzan', 42000, 11),

( 6, 'Jane', 41000, 11),

( 7, 'Connor', 36000, 12),

( 8, 'McLeod', 34000, 12),

( 9, 'Marilyn', 33000, 12),

(10, 'Monroe', 50000, 15),

(11, 'Zander', 52000, 16),

(12, 'Henry', 51000, 16),

(13, 'Aaron', 54000, 15),

(14, 'Jeff', 53000, 16),

(15, 'Mills', 70000, 17),

(16, 'Gustavo', 80000, 17),

(17, 'Kosnisky', 95000, NULL);

Voici donc un jeu de données représentant une équipe au sein d’une startup. Bon, vu les salaires de certains, on va supposer qu’elle a fait l’objet d’une belle levée de fonds, cette startup.

Sachant que le numéro 1 de la structure, c’est l’employé n° 17, je voudrais écrire une requête me permettant de faire apparaître la structure hiérarchique de l’équipe (en partant du sommet de la pyramide). Et je souhaite ne faire apparaître que les personnes qui sont de niveau 1, 2 et 3 dans la hiérarchie.

Voici une requête permettant d’obtenir ce résultat, requête que vous pourrez améliorer par la suite en fonction de vos besoins :

WITH RECURSIVE management_tree (managerid, empid, empname, empsalary, level) AS (

SELECT managerid, empid, empname, empsalary, 1 as level

FROM squad WHERE empid = 17

UNION

SELECT squad.managerid, squad.empid, squad.empname, squad.empsalary, level+1 as level

FROM squad, management_tree

WHERE squad.managerid = management_tree.empid

)

SELECT managerid, empid,

LPAD(' ', level * 4, ' ') concat empname as empname,

empsalary, level

FROM management_tree

WHERE level < 4;

Voici le résultat obtenu avec la requête ci-dessus :

managerid	manager	empid	empname	empsalary	level
		17	>Kosnisky	95000.00	1
17	Kosnisky	15	>Mills	70000.00	2
17	Kosnisky	16	>Gustavo	80000.00	2
15	Mills	10	>Monroe	50000.00	3
16	Gustavo	11	>Zander	52000.00	3
16	Gustavo	12	>Henry	51000.00	3
15	Mills	13	>Aaron	54000.00	3
16	Gustavo	14	>Jeff	53000.00	3

Il y aurait beaucoup à dire sur ce sujet, mais comme nous avons encore pas mal de choses à voir ensemble, je vous laisse le soin de lire les exemples de la documentation officielle, qui est plutôt bien faite [3].

3. Les Window Functions

Pour cette section, je vous propose de repartir de la table t_ventes que nous avions créée à la première section. Mais pour simplifier l’écriture des premières requêtes, je vous propose de créer une vue sur la table t_ventes, vue qui nous fournira une consolidation des ventes par pays et ville :

CREATE VIEW v_total_ventes AS

SELECT codpays, ville, sum(mnt_vte) AS tot_vte

FROM t_ventes

GROUP BY codpays, ville;

Nous utiliserons cette vue alternativement avec la table t_ventes, selon les besoins.

3.1 La fonction RANK()

La fonction RANK() classe les lignes en affectant à chacune un numéro d'ordre. Ce numéro est défini par l'addition du chiffre 1 au nombre de lignes distinctes précédant la ligne concernée par le tri. S'il est impossible de déterminer l'ordre relatif de deux lignes ou plus contenant des valeurs de ligne identiques, alors le même numéro d'ordre est affecté à ces différentes lignes. Dans ce cas, la numérotation du classement peut être discontinue. Ça doit vous paraître un peu du charabia, alors voyons un exemple :

SELECT ville, tot_vte,

RANK() OVER (ORDER BY tot_vte DESC) AS rang

FROM v_total_ventes

ORDER BY rang;

Dans l’exemple ci-dessus, la colonne rang donne le classement des meilleures ventes par ville. Si deux villes ou plus ont le même total de ventes, alors elles ont le même rang, elles sont donc à égalité dans le classement final.

Voici le résultat obtenu :

ville	tot_vte	rang
Toulouse	2130.00	1
Londres	2100.00	2
Lyon	1420.00	3
Paris	1420.00	3
Manchester	1290.00	5
Bordeaux	1110.00	6

On peut coupler la fonction RANK() avec la clause PARTITION BY, comme dans l’exemple suivant :

SELECT codpays, ville, tot_vte,

RANK() OVER (PARTITION BY codpays ORDER BY tot_vte DESC) AS rang

FROM v_total_ventes

ORDER BY codpays, rang;

La clause PARTITION BY nous permet ici de déclencher une rupture par code pays, pour un résultat assez différent (observez le contenu de la colonne rang) :

codpays	ville	tot_vte	rang
FR	Toulouse	2130.00	1
FR	Paris	1420.00	2
FR	Lyon	1420.00	2
FR	Bordeaux	1110.00	4
UK	Londres	2100.00	1
UK	Manchester	1290.00	2

3.2 La fonction DENSE_RANK()

La fonction DENSE_RANK() classe les lignes en affectant à chacune un numéro d'ordre. Ce numéro est défini par l'addition du chiffre 1 au nombre total de lignes précédant la ligne concernée dans le classement. En conséquence, le classement sera séquentiel, sans discontinuités dans la numérotation.

Voici un exemple permettant de comparer les fonctions RANK() et DENSE_RANK() :

SELECT codpays, ville, tot_vte,

RANK() OVER (PARTITION BY codpays ORDER BY tot_vte DESC) AS RANK,

DENSE_RANK() OVER (PARTITION BY codpays ORDER BY tot_vte DESC) AS DENSE_RANK

FROM v_total_ventes;

Résultat obtenu :

codpays	ville	tot_vte	RANK	DENSE_RANK
FR	Bordeaux	1110.00	4	3
FR	Lyon	1420.00	2	2
FR	Paris	1420.00	2	2
FR	Toulouse	2130.00	1	1
UK	Londres	2100.00	1	1
UK	Manchester	1290.00	2	2

3.3 La fonction ROW_NUMBER()

ROW_NUMBER() calcule le numéro séquentiel de la ligne en fonction de l'ordre indiqué dans la clause OVER, la première ligne portant le numéro 1. Si la clause ORDER BY n'est pas spécifiée, les lignes sont numérotées arbitrairement. On peut aussi associer ROW_NUMBER() à la clause PARTITION BY pour obtenir une rupture dans la numérotation en fonction d’une ou plusieurs colonnes.

Exemple avec une CTE (parce que c’est plus sympa) :

WITH

temp_ventes AS (

SELECT codpays, ville, tot_vte,

ROW_NUMBER() OVER (PARTITION BY codpays ORDER BY tot_vte DESC) AS rang

FROM v_total_ventes

)

select * from temp_ventes ;

Résultat :

codpays	ville	tot_vte	rang
FR	Toulouse	4260.00	1
FR	Paris	2840.00	2
FR	Lyon	2840.00	3
FR	Bordeaux	2220.00	4
UK	Londres	4200.00	1
UK	Manchester	2580.00	2

Je vous propose une variante, dans laquelle les quatre meilleures ventes sont disposées en colonnes :

WITH

temp_ventes AS (

SELECT codpays, ville, tot_vte,

ROW_NUMBER() OVER (PARTITION BY codpays ORDER BY tot_vte DESC) AS rang

FROM v_total_ventes

temp_pays AS (

SELECT DISTINCT codpays AS codpays FROM temp_ventes

)

SELECT A.codpays, B1.tot_vte AS tot_vte1, B2.tot_vte AS tot_vte2,

B3.tot_vte AS tot_vte3, B4.tot_vte AS tot_vte4

FROM temp_pays A

LEFT OUTER JOIN temp_ventes B1 ON A.codpays = B1.codpays AND B1.rang = 1

LEFT OUTER JOIN temp_ventes B2 ON A.codpays = B2.codpays AND B2.rang = 2

LEFT OUTER JOIN temp_ventes B3 ON A.codpays = B3.codpays AND B3.rang = 3

LEFT OUTER JOIN temp_ventes B4 ON A.codpays = B4.codpays AND B4.rang = 4;

Résultat :

codpays	tot_vte1	tot_vte2	tot_vte3	tot_vte4
FR	4260.00	2840.00	2840.00	2220.00
UK	4200.00	2580.00	null	null

3.4 Autres fonctions

Je me suis focalisé dans cette présentation sur les fonctions ROW_NUMBER(), RANK() et DENSE_RANK().

Mais MariaDB fournit également d’autres fonctions qui me semblent intéressantes, même si je manque de recul pour en parler, faute de les avoir pratiquées. Ce sont les fonctions PERCENT_RANK(), CUME_DIST() et NTILE().

Certaines fonctions d’agrégation supportent également des fonctions de fenêtrage, et ça c’est un plus indéniable pour MariaDB. Les fonctions concernées sont : COUNT, SUM, AVG, BIT_OR, BIT_AND et BIT_XOR. Là encore, je manque de recul pour en parler, et je préfère vous renvoyer vers la documentation officielle [4].

4. Petite étude de cas

Je vous propose de mettre en pratique ce que nous venons de voir, avec une belle requête SQL faisant appel à quelques CTE et à une fonction de fenêtrage.

La problématique est la suivante : comment faire pour détecter les périodes de vacances ou d’inactivité au sein de données d’entreprise. C’est en effet un besoin vital pour certaines organisations. Par exemple, une société qui loue des biens immobiliers aura tout intérêt à ce que ces biens ne demeurent pas vacants trop longtemps. Quand cela arrive, elle aura besoin de déterminer combien d’argent elle a perdu par rapport aux périodes de vacances des biens qu’elle gère. Dans un autre registre, une société de services qui place du personnel en régie aura besoin de déterminer les périodes d’inactivité (on parle généralement de périodes d’inter-contrat) de ses employés.

Si on prend l’exemple de logements en location, nous aurons dans notre base de données une table des occupations de logement, avec pour chaque logement, une série de lignes définissant les dates d’entrée et de sortie des occupants respectifs. Si on souhaite dans ce contexte déterminer les périodes de vacances, nous devons trier notre jeu de données par référence de logement et dates d’entrée, et identifier les périodes de vacances en analysant la date de sortie de l’occupant N par rapport à la date d’entrée de l’occupant N+1. Et c’est là où ça se corse, car l’occupant N est sur une ligne SQL, l’occupant N+1 est sur une autre ligne de la même table. On pourrait se dire que cela va être très pénible de faire ça avec SQL, et vouloir déporter la logique d’analyse dans un langage annexe (PHP, Python ou autre). Ce serait vraiment dommage, car en réalité cela va être fun de faire ça en SQL.

Commençons par créer un jeu de données avec une table des occupants :

create table occupants (

ref_logement char(10) default null,

date_entree date default null,

date_sortie date default null

) ;

insert into occupants

( ref_logement, date_entree, date_sortie )

values

( '11492', '2016–09–01', null ),

( '11492', '2016–05–15', '2016–07–31' ),

( '11492', '2016–02–01', '2016–04–30' ),

( '11481', '2016–12–15', '2017–03–31' ),

( '11481', '2016–08–01', '2016–10–31' ),

( '11481', '2016–02–01', '2016–02–29' ),

( '11182', '2016–11–15', '2017–01–31' ),

( '11182', '2016–01–01', '2016–10–31' );

Dans cette table, nous avons pour chaque logement plusieurs dates d’entrée et de sortie d’occupants divers.

Commençons par une première CTE qui va nous permettre de forcer une date de fin, positionnée très loin dans le futur, ceci afin de prendre en compte les occupants encore présents dans leurs logements respectifs :

with

cte_step1 as (

select ref_logement, date_entree, ifnull(date_sortie, '2030-12-31') as date_sortie

from occupants

order by ref_logement, date_entree

)

Passons à la seconde CTE :

cte_step2 as (

select ref_logement, date_entree, date_sortie,

row_number() over(partition by ref_logement order by ref_logement,

date_entree) as rupture

from cte_step1 a

order by ref_logement, date_entree

)

Un simple SELECT sur cette seconde CTE va nous permettre de comprendre quelle est sa finalité :

ref_logement	date_entree	date_sortie	rupture
11182	2016-01-01	2016-10-31	1
11182	2016-11-15	2017-01-31	2
11481	2016-02-01	2016-02-29	1
11481	2016-08-01	2016-10-31	2
11481	2016-12-15	2017-03-31	3
11492	2016-02-01	2016-04-30	1
11492	2016-05-15	2016-07-31	2
11492	2016-09-01	2030-12-31	3

La fonction ROW_NUMBER() nous permet ici de définir une notion de rupture (sur la référence de logement), et de générer un compteur qui repart à 1 à chaque nouveau logement. Cette information est stratégique pour la suite du traitement.

Passons à la troisième CTE :

cte_step3 as (

select a.*, a.rupture+1 as next_rupture from cte_step2 a

)

C’est une requête toute simple qui va ajouter la valeur 1 à la colonne rupture existante et générer une nouvelle colonne (next_rupture).

Là encore, un SELECT intermédiaire va nous permettre d’apprécier le travail effectué :

ref_logement	date_entree	date_sortie	rupture	next_rupture
11182	2016-01-01	2016-10-31	1	2
11182	2016-11-15	2017-01-31	2	3
11481	2016-02-01	2016-02-29	1	2
11481	2016-08-01	2016-10-31	2	3
11481	2016-12-15	2017-03-31	3	4
11492	2016-02-01	2016-04-30	1	2
11492	2016-05-15	2016-07-31	2	3
11492	2016-09-01	2030-12-31	3	4

Avec la présence sur une même ligne des 2 colonnes rupture et next_rupture, il va être facile de :

faire une jointure entre une période N et une période N+1 ;
et calculer l’écart entre la date de fin d’occupation N et la date de début d’occupation N+1.

C’est ce que je vous propose de faire avec la 5ème CTE, que voici :

cte_step4 as (

select a.ref_logement, DATE_ADD(a.date_sortie, INTERVAL + 1 DAY) as dat_deb_vacances,

DATE_ADD(b.date_entree, INTERVAL -1 DAY) as dat_fin_vacances,

DATEDIFF(b.date_entree, a.date_sortie) - 1 as vacances

from (select * from cte_step3 order by ref_logement, date_entree) a

inner join (select * from cte_step3 order by ref_logement, date_entree) b

on a.ref_logement = b.ref_logement and a.next_rupture = b.rupture

)

select * from cte_step4 where vacances > 0 ;

Et voilà le résultat final :

ref_logement	dat_deb_vacances	dat_fin_vacances	vacances
11182	2016-11-01	2016-11-14	14
11481	2016-03-01	2016-07-31	153
11481	2016-11-01	2016-12-14	44
11492	2016-05-01	2016-05-14	14
11492	2016-08-01	2016-08-31	31

Ainsi, pour chaque logement, nous avons pu déterminer les différentes périodes d’inoccupation, et le nombre de jours de vacances correspondant. Elle n’est pas belle la vie ?

Conclusion

Nous avons vu dans cet article comment utiliser les CTE, les CTE récursives et les fonctions de fenêtrage avec MariaDB. Il y a bien d’autres nouveautés à découvrir tout au long de cette version 10.2 de MariaDB, mais les quelques techniques que nous venons de voir constituent à mes yeux des avancées majeures. On change de paradigme, il y a un « avant » et un « après » la version 10.2. Avec les CTE, la programmation de requêtes SQL complexes n’est plus une sinécure. Et le portage de code SQL en provenance d’autres SGBD devient une option tout à fait envisageable. Bref, l’avenir s’annonce radieux pour les développeurs MySQL qui vont pouvoir faire évoluer leurs pratiques de développement. De beaux chantiers de modernisation d’applications se profilent à l’horizon.