Antécédents et raisonnement

J’ai récemment travaillé sur certaines requêtes à l’aide des fonctions Oracle Spatial pour créer des chaînes de lignes géospatiales. Dans le cadre de l’un des paramètres de la fonction, j’avais besoin de totaliser une mesure de distance de course à partir d’un emplacement de départ (une coordonnée) sur la chaîne de lignes, à chaque intervalle de coordonnées.

Cela ressemble à un cas d’utilisation simple pour un total glissant utilisant SUM()et OVER(). Et, vous avez raison, ça l’est.

Presque .

Exemple de données

Pour des raisons de simplicité, je vais renoncer à utiliser des nords, des abscisses ou des références linéaires compliqués dans les exemples et je me contenterai plutôt de cet ensemble de données simple pour le billet de blog :

SELECT * FROM coords_measure ;

Étant donné que je blogue principalement sur – et utilise – MySQL ou MariaDB dans la plupart des applications de pile LAMP que je développe, j’utiliserai également MySQL pour ces exemples (bien que les exigences dont j’avais besoin étaient à l’origine dans Oracle SQL).

Vous remarquerez peut-être que la dernière ligne du jeu de résultats contient NULLdes valeurs pour les colonnes start_segment, end_segmentet .segment_length

Cela est dû au fait que cet emplacement est le dernier point (coordonnée) sur la chaîne de lignes et n’a pas de longueur de mesure réelle ; fondamentalement le « cul-de-sac » .

Roulant SUM() avec les fonctions de Windows MySQL

Calculer une somme glissante n’est pas si difficile puisque nous avons des fonctions de Windows à partir de MySQL v8.

Comme le montre cette requête, le simple fait d’utiliser la SUM()fonction d’agrégation conjointement avec la OVER()clause et un ordre spécifique nous donne ces informations :

SELECT id, start_segment, end_segment,SUM(segment_length) OVER(ORDER BY id ASC) AS rolling_sum FROM coords_measure;

Bien que nous ayons un calcul de somme glissante, il est incorrect dans ce contexte. Vous pouvez voir que les 2 dernières lignes ont 195,83 comme valeur de somme glissante.

Pourtant, ce dont nous avons vraiment besoin pour cette exigence particulière, c’est que la dernière ligne n’ait que 195,83 pour le calcul de la somme glissante et que la première ligne ait 0.

Presque comme si nous devions déplacer toutes les valeurs de la somme glissante vers le bas d’une ligne.

Mais pourquoi?

Pour cette exigence géospatiale, à chaque point de coordonnées, la distance doit être construite à partir de la coordonnée de départ ou de début (ligne 1).

Par conséquent, la ligne 1 devrait en fait avoir une distance de mesure de 0 puisqu’il s’agit du début de la chaîne de lignes (et n’a pas de valeur de distance – c’est le début ) tandis que la ligne 2 aurait la valeur de la distance mesurée entre ces 2 coordonnées depuis que est la distance jusqu’au départ (coordonnée 1).

Fonctions de la Windows MySQL LAG()

La LAG()fonction de Windows vous permet d’accéder aux données situées dans les lignes précédentes d’un ensemble de résultats, à partir de la ligne d’exploitation actuelle. Vous pouvez accéder à 1 (valeur par défaut) ou plusieurs lignes en fonction du 2ème paramètre fourni dans l’ LAG()appel de fonction.

Je vais utiliser LAG()dans la requête et renvoyer la valeur segment_length de chaque ligne précédente :

SELECT id, start_segment, end_segment,LAG(segment_length, 1) OVER(ORDER BY id ASC) AS one_lag FROM coords_measure;

Comme indiqué dans les résultats de la requête, dans le cas où il n’y a pas de valeurs de lignes précédentes, LAG()renvoie NULL.

Maintenant, nous effectuons simplement la somme glissante, n’est-ce pas ?

Peut-on combiner 2 Windows Functions au même niveau ?

Non.

L’exécution de cette requête renvoie une erreur spécifique indiquant que ce LAG()n’est pas autorisé ici :

SELECT id, start_segment, end_segment,SUM(LAG(segment_length, 1) OVER(ORDER BY id ASC)) OVER(ORDER BY id ASC) 
AS one_lag FROM coords_measure;

Error Code: 3593. You cannot use the window function 'lag' in this context.

Une requête SELECT qui génère une table dans la clause FROM est connue sous le nom de table dérivée dans MySQL.

Afin de calculer la somme glissante de la sortie de la fonction  LAG(), nous devons placer la requête génératrice dans la clause FROM à l’aide d’une table dérivée :

SELECT t.id, t.start_segment, t.end_segment,
SUM(t.one_lag) OVER(ORDER BY t.id ASC) AS rolling_sum 
FROM (SELECT id, start_segment, end_segment,LAG(segment_length, 1) OVER(ORDER BY id ASC) 
AS one_lag FROM coords_measure) AS t;

Conseil : les tables dérivées MySQL doivent être associées à un alias. Cependant, le mot-clé ASest facultatif.

Maintenant, comme indiqué dans les résultats de la requête, nous avons la mesure de distance correcte sous forme de somme glissante, à chaque intervalle de coordonnées.

Honnêtement, il peut exister d’autres moyens de récupérer ces résultats de requête, mais celui-ci a fonctionné pour moi pour cette exigence spécifique. J’ai beaucoup appris sur les calculs de somme glissante avec les fonctions LAG()de Windows pendant que je travaillais sur cette requête particulière et j’espère que vous l’avez également fait.