Fonctions SQL d'analyse fenêtrées, la frame perdue
par
, 19/05/2022 à 22h56 (3844 Affichages)
Connaissez-vous les fonctions SQL d'analyse fenêtrées ? Elles sont très utiles pour répondre à des questions comme "Quelle est la somme courante de telle colonne ? " ou "Quelle est la valeur précédente (ou suivante) de la ligne courante ?". MySQL 8 les prend en charge, et c'est un bonheur
Afin de raccourcir quelques requêtes, j'ai eu besoin de créer un colonne messages.is_last contenant une valeur 1 ou 0, le 1 devant être apposé sur le dernier message de chaque discussion.
Table :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 CREATE TABLE messages ( id INT UNSIGNED NOT NULL PRIMARY KEY, created_at DATETIME NOT NULL, body TEXT NOT NULL, thread_id INT UNSIGNED NOT NULL COMMENT 'ID discussion' );
Jeu de données :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 INSERT INTO messages (id, created_at, body, thread_id) VALUES (1, '2022-05-19 20:14:20', 'Message 1.1', 1), (2, '2022-05-19 21:57:37', 'Message 1.2', 1), (3, '2022-05-20 08:35:59', 'Message 2.1', 2), (4, '2022-05-22 15:44:40', 'Message 2.2', 2), (5, '2022-05-23 12:20:13', 'Message 3.1', 3), (6, '2022-05-23 17:00:21', 'Message 1.3', 1);
La fonction LAST_VALUE() m'a semblé toute indiquée. J'ai donc commencé à tester, pour débuter avec un simple SELECT...
Comment récupérer l'ID du dernier message de chaque discussion ?
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 SELECT ALL id, created_at, body, thread_id, -- Recherche du dernier ID des messages par discussion triés par ordre chronologique de création LAST_VALUE(id) OVER (PARTITION BY thread_id ORDER BY created_at ASC) AS last_message FROM messages ORDER BY thread_id ASC, created_at ASC;
Résultat obtenu :
Décevant n'est-ce pas ? last_message contient l'ID message de la ligne courante 8) Et la doc de LAST_VALUE() ne dit rien sur ce comportement.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 | id | created_at | body | thread_id | last_message | +----+---------------------+-------------+-----------+--------------+ | 1 | 2022-05-19 20:14:20 | Message 1.1 | 1 | 1 | | 3 | 2022-05-20 08:35:59 | Message 1.2 | 1 | 3 | | 4 | 2022-05-22 15:44:40 | Message 1.3 | 1 | 4 | | 2 | 2022-05-19 21:57:37 | Message 2.1 | 2 | 2 | | 5 | 2022-05-23 12:20:13 | Message 3.1 | 3 | 5 | | 6 | 2022-05-23 17:00:21 | Message 3.2 | 3 | 6 |
Tiens, il existe aussi un FIRST_VALUE(), et après tout le dernier message par ordre chronologique ascendant de création est aussi le premier par ordre chronologique descendant.
Testons :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 SELECT ALL id, created_at, body, thread_id, -- Recherche du premier ID des messages par discussion triés par ordre chronologique descendant de création FIRST_VALUE(id) OVER (PARTITION BY thread_id ORDER BY created_at DESC) AS last_message FROM messages ORDER BY thread_id ASC, created_at ASC;
Bingo ! Chaque message référence correctement l'ID message du dernier message de sa discussion, il ne manque plus qu'un test d'égalité id = last_message pour calculer ma nouvelle colonne is_last, mais... Est-ce vraiment satisfaisant d'utiliser une fonction FIRST_VALUE() pour obtenir une dernière valeur sachant qu'il existe LAST_VALUE() potentiellement dédiée à cette tâche ?
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 | id | created_at | body | thread_id | last_message | +----+---------------------+-------------+-----------+--------------+ | 1 | 2022-05-19 20:14:20 | Message 1.1 | 1 | 4 | | 3 | 2022-05-20 08:35:59 | Message 1.2 | 1 | 4 | | 4 | 2022-05-22 15:44:40 | Message 1.3 | 1 | 4 | | 2 | 2022-05-19 21:57:37 | Message 2.1 | 2 | 2 | | 5 | 2022-05-23 12:20:13 | Message 3.1 | 3 | 6 | | 6 | 2022-05-23 17:00:21 | Message 3.2 | 3 | 6 |
Bon, pris par le temps, j'ai finalement opté par pour les modalités de calcul suivantes à l'aide de ROW_NUMBER() et COUNT() :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10 SELECT ALL id, created_at, body, thread_id, -- Numéro du message dans la discussion ROW_NUMBER() OVER (PARTITION BY thread_id ORDER BY created_at ASC) AS message_index, -- Nombre de messages dans la discussion COUNT(id) OVER (PARTITION BY thread_id) AS messages_count, -- Si le numéro de message est égale au nombre de message, alors il s'agit du dernier message ROW_NUMBER() OVER (PARTITION BY thread_id ORDER BY created_at ASC) = COUNT(id) OVER (PARTITION BY thread_id) AS is_last FROM messages ORDER BY thread_id ASC, created_at ASC;
Avec cette requête, is_last est correctement valorisée, et la sémantique est ~sauve :
Mais toujours un goût amer...
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 | id | created_at | body | thread_id | message_index | messages_count | is_last | +----+---------------------+-------------+-----------+---------------+----------------+---------+ | 1 | 2022-05-19 20:14:20 | Message 1.1 | 1 | 1 | 3 | 0 | | 3 | 2022-05-20 08:35:59 | Message 1.2 | 1 | 2 | 3 | 0 | | 4 | 2022-05-22 15:44:40 | Message 1.3 | 1 | 3 | 3 | 1 | | 2 | 2022-05-19 21:57:37 | Message 2.1 | 2 | 1 | 1 | 1 | | 5 | 2022-05-23 12:20:13 | Message 3.1 | 3 | 1 | 2 | 0 | | 6 | 2022-05-23 17:00:21 | Message 3.2 | 3 | 2 | 2 | 1 |
Quelques mois plus tard...
Quelques mois plus tard, même type de problème avec cette fois-ci un peu plus de temps à consacrer Et je tombe sur la doc. MySQL des "frames".
Les frames permettent d'orienter la portée des fonctions d'analyse fenêtrées au sein des fenêtres définies avec PARTITION BY. Je les avais déjà croisées, mais sans jamais (croyais-je) en avoir besoin.
Dans la documentation je découvre que les fonctions d'analyse fenêtrées ont une frame par défaut différente selon l'utilisation d'un ORDER BY dans le OVER () :
Tout s'éclaire ! Je trie ma partition, mon LAST_VALUE() a donc une portée limitée aux lignes précédentes jusqu'à la ligne courante. Voilà pourquoi j'obtenais l'ID de la ligne courante : c'était bien le dernier ID de la frame implicite. Au passage je comprends mieux comment fonctionne le calcul d'une somme couranteIn the absence of a frame clause, the default frame depends on whether an ORDER BY clause is present:
- With ORDER BY: The default frame includes rows from the partition start through the current row, including all peers of the current row (rows equal to the current row according to the ORDER BY clause). The default is equivalent to this frame specification:
Code : Sélectionner tout - Visualiser dans une fenêtre à part RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW- Without ORDER BY: The default frame includes all partition rows (because, without ORDER BY, all partition rows are peers). The default is equivalent to this frame specification:
Code : Sélectionner tout - Visualiser dans une fenêtre à part RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Il me suffit donc de spécifier une frame sur la partition complète. Mon LAST_VALUE() va enfin pouvoir fonctionner
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 SELECT ALL id, created_at, body, thread_id, -- Contrôle du retour de LAST_VALUE() LAST_VALUE(id) OVER ( PARTITION BY thread_id ORDER BY created_at ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_message, -- Calcul de is_last id = LAST_VALUE(id) OVER ( PARTITION BY thread_id ORDER BY created_at ASC -- Tout se joue ici RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS is_last FROM messages ORDER BY thread_id ASC, created_at ASC;
Résultat :
Le résultat correspond bien à l'attendu, il ne me reste plus qu'à mettre cela en production, modification de la table :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 | id | created_at | body | thread_id | last_message | is_last | +----+---------------------+-------------+-----------+--------------+---------+ | 1 | 2022-05-19 20:14:20 | Message 1.1 | 1 | 4 | 0 | | 3 | 2022-05-20 08:35:59 | Message 1.2 | 1 | 4 | 0 | | 4 | 2022-05-22 15:44:40 | Message 1.3 | 1 | 4 | 1 | | 2 | 2022-05-19 21:57:37 | Message 2.1 | 2 | 2 | 1 | | 5 | 2022-05-23 12:20:13 | Message 3.1 | 3 | 6 | 0 | | 6 | 2022-05-23 17:00:21 | Message 3.2 | 3 | 6 | 1 |
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 ALTER TABLE messages ADD COLUMN is_last BIT(1) DEFAULT 0 AFTER body;
Mise à jour de l'existant :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10 UPDATE messages INNER JOIN ( SELECT ALL id, -- Pour la jointure id = LAST_VALUE(id) OVER ( PARTITION BY thread_id ORDER BY created_at ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS is_last FROM messages ) AS new ON messages.id = new.id SET messages.is_last = new.is_last;
Ma table messages et sa nouvelle colonne is_last valorisée :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 SELECT ALL id, created_at, body, is_last, thread_id FROM messages ORDER BY thread_id ASC, created_at ASC;
C'était les aventures de la frame perdue, accompagnée d'une piqure de rappel de l'utilité de la doc. MySQL
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 | id | created_at | body | is_last | thread_id | +----+---------------------+-------------+---------+-----------+ | 1 | 2022-05-19 20:14:20 | Message 1.1 | 0 | 1 | | 3 | 2022-05-20 08:35:59 | Message 1.2 | 0 | 1 | | 4 | 2022-05-22 15:44:40 | Message 1.3 | 1 | 1 | | 2 | 2022-05-19 21:57:37 | Message 2.1 | 1 | 2 | | 5 | 2022-05-23 12:20:13 | Message 3.1 | 0 | 3 | | 6 | 2022-05-23 17:00:21 | Message 3.2 | 1 | 3 |