MySQL - Utilisation de variables dans une requête

Très souvent, ils demandent s'il y a des analogues de fonctions analytiques (fenêtre) dans MySQL. Remarque Au moment d'écrire ces lignes, il n'y avait pas de tels analogues, mais l'article présente toujours un intérêt académique en termes d'analyse de l'approche originale d'utilisation des variables pour MySQL.

Pour remplacer les fonctions analytiques, des requêtes auto-connectées, des sous-requêtes complexes et bien plus sont souvent utilisées. La plupart de ces solutions sont inefficaces en termes de performances.

Toujours dans MySQL, il n'y a pas de récursivité. Cependant, certaines des tâches qui sont généralement résolues par les fonctions analytiques ou la récursivité peuvent être gérées par les outils MySQL.

L'un de ces outils est un mécanisme unique et inhabituel pour d'autres mécanismes SGBD de travail avec des variables à l'intérieur d'une requête SQL. Nous pouvons déclarer une variable à l'intérieur de la requête, changer sa valeur et la remplacer dans SELECT pour la sortie. De plus, l'ordre de traitement des lignes dans la demande et, par conséquent, l'ordre d'affectation des valeurs aux variables peut être défini dans un tri personnalisé!

Avertissement L'article suppose que le traitement des expressions dans la clause SELECT s'effectue de gauche à droite, cependant, il n'y a pas de confirmation officielle de cet ordre de traitement dans la documentation MySQL. Ceci doit être gardé à l'esprit lors du changement de version du serveur. Pour garantir la cohérence, vous pouvez utiliser l'instruction factice CASE ou IF.

Analogue de récursivité

Prenons un exemple simple qui génère une séquence de Fibonacci (dans la séquence de Fibonacci, chaque terme est égal à la somme des deux précédents et les 2 premiers sont égaux à un):

SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, /* ,     1 */ (SELECT 1 X UNION ALL SELECT 2)X; 

Cette requête génère 18 numéros de Fibonacci, sans compter les deux premiers:

 2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765 

Voyons maintenant comment cela fonctionne.

Aux lignes 5) 6) 9 enregistrements sont générés. Rien d'inhabituel ici.

À la ligne 7), nous déclarons deux variables @I, @J et leur attribuons 1.

À la ligne 3), il se passe ce qui suit: tout d'abord, la variable @I se voit attribuer la somme des deux variables. Ensuite, nous attribuons la même chose à la variable @J, en tenant compte du fait que la valeur de @I a déjà changé.

En d'autres termes, les calculs dans SELECT sont effectués de gauche à droite - voir également la remarque au début de l'article.

De plus, le changement de variables s'effectue dans chacun de nos 9 enregistrements, soit lors du traitement de chaque nouvelle ligne, les variables @I et @J contiendront les valeurs calculées en traitant la ligne précédente.

Pour résoudre le même problème à l'aide d'autres SGBD, il faudrait écrire une requête récursive!

Remarque:
Les variables doivent être déclarées dans une sous-requête distincte (ligne 7), si nous déclarions une variable dans la clause SELECT, elle ne serait probablement évaluée qu'une seule fois (bien que le comportement spécifique dépende de la version du serveur). Le type d'une variable est déterminé par la valeur par laquelle elle est initialisée. Ce type peut changer dynamiquement. Si vous définissez la variable sur NULL, son type sera BLOB.

L'ordre dans lequel les lignes sont traitées dans SELECT, comme mentionné ci-dessus, dépend du tri personnalisé. Un exemple simple de numérotation de lignes dans un ordre donné:

 SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val; 

 Val Num 10 1 20 2 30 3 50 4 

Analogues des fonctions analytiques

Les variables peuvent également être utilisées pour remplacer les fonctions analytiques. Voici quelques exemples. Pour simplifier, nous supposons que tous les champs ne sont PAS NULS, et le tri et le partitionnement (PARTITION BY) se produisent sur un seul champ. L'utilisation de valeurs NULL et de tri plus complexes rendra les exemples plus lourds, mais l'essence ne changera pas.

Pour des exemples, créez la table TestTable:

 CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL ); 

où
group_id - identifiant de groupe (analogue de la fenêtre de fonction analytique);
order_id - un champ unique pour le tri;
valeur est une valeur numérique.

Remplissez notre tableau avec les données de test:

 INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T; 

Exemples de remplacement de certaines fonctions analytiques.

1) ROW_NUMBER () OVER (ORDER BY order_id)

 SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id; 

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10


2) ROW_NUMBER () OVER (PARTITION BY group_id ORDER BY order_id)

 SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T; 

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1


3) SUM (valeur) OVER (PARTITION BY group_id ORDER BY order_id)

 SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T; 

group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1


4) LAG (valeur) OVER (PARTITION BY group_id ORDER BY order_id)

 SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T; 

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

Pour LEAD, tout est pareil, il vous suffit de changer le tri en ORDER BY group_id, order_id DESC

Pour les fonctions COUNT, MIN, MAX, tout est un peu plus compliqué, car tant que nous n'aurons pas analysé toutes les lignes du groupe (fenêtre), nous ne pourrons pas trouver la valeur de la fonction. MS SQL, par exemple, «spoule» une fenêtre à ces fins (place temporairement les lignes de fenêtre dans une table tampon cachée pour y accéder à nouveau), dans MySQL, il n'y a pas une telle possibilité. Mais nous pouvons calculer la valeur de la fonction dans la dernière ligne pour chaque fenêtre pour un tri donné (c'est-à-dire après avoir analysé la fenêtre entière), puis, en triant les lignes de la fenêtre dans l'ordre inverse, placez la valeur calculée sur toute la fenêtre.

Nous avons donc besoin de deux tri. Pour que le tri final reste le même que dans les exemples ci-dessus, nous trions d'abord par les champs group_id ASC, order_id DESC, puis par les champs group_id ASC, order_id ASC.

5) COUNT (*) OVER (PARTITION BY group_id)

Dans le premier tri, nous numérotons simplement les entrées. Dans le second, nous attribuons le nombre maximum à toutes les lignes de la fenêtre, ce qui correspondra au nombre de lignes de la fenêtre.

 SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC /* */ )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id /* */ )T; 

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

Les fonctions MAX et MIN sont calculées par analogie. Je ne donnerai qu'un exemple pour MAX:

6) MAX (valeur) OVER (PARTITION BY group_id)

 SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T; 

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1


7) COUNT (valeur DISTINCT) OVER (PARTITION BY group_id)

Une chose intéressante qui n'est pas disponible dans MS SQL Server, mais elle peut être calculée avec une sous-requête en prenant MAX de RANK. Nous ferons de même ici. Dans le premier tri, nous calculons RANK () OVER (PARTITION BY group_id ORDER BY value DESC), puis dans le second tri, nous mettons la valeur maximale à toutes les lignes de chaque fenêtre:

 SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T; 

group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1


Performances

Pour commencer, nous comparons les performances de la numérotation des lignes dans une requête à l'aide de l'auto-jointure et de variables.

1) La méthode classique avec auto-connexion

 SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id; 

Que pour 10000 enregistrements dans la table TestTable produit:

Durée / Récupération
16,084 s / 0,016 s

2) Utilisation de variables:

 SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id; 

Il produit:

Durée / Récupération
0,016 s / 0,015 s

Le résultat parle de lui-même. Cependant, il faut comprendre que les valeurs calculées à l'aide de variables ne sont pas utilisées de manière optimale dans les conditions de filtrage. Le tri et le calcul auront lieu pour TOUTES les lignes, malgré le fait qu'en fin de compte, nous n'en avons besoin que d'une petite partie.

Examinons plus en détail par l'exemple d'une telle tâche:

Imprimez les 2 premières lignes de la table TestTable pour chaque valeur group_id, triées par order_id.

Voici comment cette tâche serait résolue dans un SGBD avec prise en charge des fonctions analytiques:

 SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum <= 2; 

Cependant, l'optimiseur MySQL ne sait rien des règles par lesquelles nous calculons le champ RowNum. Il devra numéroter TOUTES les lignes, puis seulement sélectionner celles nécessaires.

Imaginez maintenant que nous avons 1 million d'enregistrements et 20 valeurs group_id uniques. C'est-à-dire pour sélectionner 40 lignes, MySQL calculera la valeur RowNum pour un million de lignes! Il n'y a pas de belle solution à ce problème avec une seule requête dans MySQL. Mais vous pouvez d'abord obtenir une liste de valeurs group_id uniques, par exemple, comme ceci:

 SELECT DISTINCT group_id FROM TestTable; 

Ensuite, en utilisant tout autre langage de programmation, générez une requête de la forme:

 SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2; 

20 requêtes simples fonctionneront beaucoup plus rapidement que le calcul de RowNum pour un million de lignes.