Séb.

Fonctions SQL d'analyse fenêtrées, la frame perdue

1 Commentaire

par

Séb.

, 19/05/2022 à 22h56 (3749 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 :

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 |

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.

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;

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 |

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 ?

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 :

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 |

Mais toujours un goût amer...

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 () :

In 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
```

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 courante

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 :

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 |

Le résultat correspond bien à l'attendu, il ne me reste plus qu'à mettre cela en production, modification de la table :

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;

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 |

C'était les aventures de la frame perdue, accompagnée d'une piqure de rappel de l'utilité de la doc. MySQL

Mis à jour 27/05/2022 à 10h26 par Séb.

Tags: mysql, sql

Catégories: Programmation

« Export SQL et noms de colonnes Liste des billets Pensez alternative (et short echo tag) »

Commentaires

Séb. - 20/05/2022 12h47

Ça ne gène pas autant MySQL que moi d'utiliser FIRST_VALUE() pour calculer un last

https://dev.mysql.com/doc/refman/8.0...d-windows.html
this query defines a window that includes partitioning, and uses ORDER BY in the OVER clauses to modify the window in different ways:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
SELECT
  DISTINCT year, country,
  FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
  FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);
Faudrait que je prenne le temps de mesurer les perfs un jour.

Séb.

Fonctions SQL d'analyse fenêtrées, la frame perdue

Commentaires

+ Créer un blog

Catégories globales

Catégories de l'utilisateur

Archives

<	Avril 2024
Di	Lu	Ma	Me	Je	Ve	Sa
24	25	26	27	28	29	30
31	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	1	2	3	4

<	Avril 2024
Di	Lu	Ma	Me	Je	Ve	Sa
24	25	26	27	28	29	30
31	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	1	2	3	4

<	Avril 2024
Di	Lu	Ma	Me	Je	Ve	Sa
24	25	26	27	28	29	30
31	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	1	2	3	4