Fonctions lambda en SQL ... réfléchissons

De quoi parlera l'article, et donc son nom l'indique.

De plus, l'auteur expliquera pourquoi cela est nécessaire de son point de vue, et expliquera que SUBJ n'est pas seulement une technologie à la mode, mais aussi «une entreprise doublement nécessaire - à la fois agréable et utile».

Il est toujours intéressant de voir comment plusieurs personnes talentueuses font quelque chose (un langage de programmation, pourquoi pas), sachant exactement quel problème elles résolvent et quelles tâches elles se fixent. Et aussi tester leur création sur eux-mêmes. À ne pas comparer avec les créations monumentales des comités géants, qui mettent au premier plan le maintien de l'harmonie de l'univers, qui sait comment.

Comparez, par exemple, le sort de FORTRAN et de PL / 1 . Qui se souviendra maintenant de ce PL / 1.

De ce point de vue, AWK , par exemple, est très réussi. Il vaut la peine de dire qu'en son nom A est Alfred Aho , l'un des auteurs de Dragon Book , W est Peter Weinberger , qui avait un coup de main à Fortran-77, K est Brian Kernigan , où serait-il sans lui. Le langage est destiné au traitement de flux de texte à la volée dans des canaux entre les processus.

Le langage est sans type ( ce n'est pas tout à fait vrai ), sa syntaxe est très similaire à C, il a des capacités de filtrage, des tableaux associatifs, des événements de début / fin de flux, un événement de nouvelle ligne ...

L'auteur a toujours été impressionné par cette langue également par le fait que son interprète n'a pas besoin d'être installé, sous les systèmes de type UNIX, il est toujours là, et sous Windows, il suffit de copier le fichier exécutable et tout fonctionne. Mais ce n'est pas le cas.

Dans le processus, l'auteur doit utiliser le bundle SQL + AWK assez souvent, et c'est pourquoi. SQL est toujours un langage initialement déclaratif conçu pour contrôler les flux de données. Il offre des possibilités très limitées de travailler avec le contexte d'exécution de requête sous la forme de fonctions d'agrégation.

Comment, par exemple, construire un histogramme bidimensionnel en utilisant SQL?

--   100 x 100 SELECT count(), round(x, -2) AS cx, round(y, -2) AS cy FROM samples GROUP BY cx, xy 

Mais disons que l'utilisation de GROUP BY implique le tri, et ce n'est pas un plaisir bon marché si vous avez des centaines de millions (voire plus) de lignes.
Résumé: Le processeur SQL a recours au tri lorsqu'il ne peut pas estimer le nombre de groupes après GROUP BY. Cependant, le regroupement par valeurs calculées est (souvent) juste le cas.

Par conséquent, vous devez faire quelque chose comme:

 psql -t -q -c 'select x, y from samples' | gawk -f mk_hist2d.awk 

où mk_hist2d.awk accumule des statistiques dans le tableau associatif et les affiche à la fin du travail

 # mk_hist2d.awk { bucket[int($2*0.01), int($3*0.01)]+=$1; } END { for (i=0; i < 500; i++) for (j=0; j < 500; j++) { if ((i, j) in bucket) print i*100." "j*100." "bucket[i, j]; else print i*100." "j*100." 0"; } } 

Il y a un MAIS - le flux de données complet doit être envoyé du serveur à la machine qui fonctionne, et ce n'est pas si bon marché.

Est-il possible de combiner en quelque sorte l'agréable avec l'utile - d'accumuler des statistiques lors de l'exécution de la requête SQL, mais sans recourir au tri? Oui, par exemple, en utilisant des fonctions d'agrégation personnalisées.

Fonctions d'agrégation personnalisées

Subj est présent dans différents systèmes, partout il se fait un peu à sa manière.

1. PostgreSQL La documentation est ici . Plus de détails ici .
   C'est là que le solde maximum du compte est calculé.
   Et ceci est un exemple qui calcule ce qui est le plus dans la colonne booléenne - vrai ou faux.
   
   Cela ressemble à ceci -
   
   

    CREATE AGGREGATE mode(boolean) ( SFUNC = mode_bool_state, STYPE = INT[], FINALFUNC = mode_bool_final, INITCOND = '{0,0}' ); 

   
   Ici SFUNC est une fonction qui est appelée pour chaque ligne du flux,
   le premier argument est de type STYPE .
   
   FINALFUNC est utilisé pour finaliser les calculs et renvoie la valeur de l'agrégat.
   INITCOND - initialisation de la valeur initiale de l'état interne ( STYPE ), passée comme premier argument.
   Étant donné que les fonctions peuvent être écrites en C (ce qui signifie que pour l'état interne, vous pouvez utiliser la mémoire qui est automatiquement libérée lorsque vous fermez la demande), c'est un outil très puissant. Hors du cadre de son utilisation, il faut encore pouvoir y aller.
2. MS SQL
   Auparavant (2000), avant la requête, il était nécessaire de créer un objet ActiveX, pour faire une agrégation en utilisant cet objet.
   Maintenant (2016+), cela se fait dans l'environnement CLR. Vous devrez créer une fonction personnalisée, créer et enregistrer un assemblage . Ensuite, vous pouvez créer un agrégat .
   Un exemple de calcul de la moyenne géométrique, ainsi que de fusion de chaînes: avec des paramètres supplémentaires et un type défini par l'utilisateur pour stocker un état intermédiaire.
3. Oracle
   Dans Oracle, cela se fait à l'aide de la cartouche de données ODCIAggregate (interface).
   Pour créer votre propre agrégat, vous devez écrire un type personnalisé qui implémente 4 méthodes
   - l'initialisation (ODCIAggregateInitialize), statique, doit créer une instance du type souhaité et retourner via le paramètre
   - itérations (ODCIAggregateIterate), appelées sur chaque ligne de données
   - merge (ODCIAggregateMerge), utilisé pour fusionner des agrégats exécutés en parallèle
   - finition (ODCIAggregateTerminate) - sortie du résultat
   Exemples: 1 , 2 , 3 , 4 .
4. DB2
   Il n'existe aucun moyen explicite d'utiliser des agrégats personnalisés dans DB2.
   Mais vous pouvez glisser une fonction standard (bien que MAX) dans un type défini par l'utilisateur (en Java) et obliger le système à exécuter des requêtes de la forme
   
   

    CREATE TYPE Complex AS ( real DOUBLE, i DOUBLE ) … CREATE TABLE complexNumbers ( id INTEGER NOT NULL PRIMARY KEY, number Complex ) … SELECT sum..real, sum..i FROM ( SELECT GetAggrResult(MAX(BuildComplexSum(number))) FROM complexNumbers ) AS t(sum) 

Qu'est-ce qui est remarquable dans tous ces systèmes?

• D'une manière ou d'une autre, vous devrez créer des objets dans la base de données. Que ce soit AGRÉGÉ ou TYPE. Au minimum, des droits appropriés sont requis. Et je veux juste ajouter quelques chiffres sur son genou.
  
• Vous devrez peut-être écrire quelque chose dans un autre langage, que ce soit C, C # ou Java.
  Pour intégrer ce qui est écrit dans le système, encore une fois, des droits sont nécessaires. Mais tout ce que je veux ...
  
• Difficulté à s'initialiser. Supposons que vous souhaitiez lire des histogrammes avec différentes tailles de panier. Il semblerait que ce soit plus facile - nous indiquerons l'INITCOND souhaité lors de la déclaration de l'agrégat (PostgreSQL) et de l'ensemble de l'entreprise. Mais alors, pour chaque taille du panier, vous aurez besoin de votre propre agrégat, et pour cela, encore une fois, les droits sont nécessaires.
  
  Ici, vous pouvez recourir à une sale astuce et faire glisser le processeur d'union de la ligne d'initialisation (vers l'avant) et des données, construire le contexte non pas dans le constructeur, mais lorsque la première ligne est reçue.
  
• Néanmoins, même avec les limitations décrites, les agrégats personnalisés vous permettent de calculer n'importe quoi.
  
• Il est important que les agrégats puissent être parallélisés , au moins PostgreSQL et Oracle (Enterprise Edition) peuvent le faire. Pour cela, la vérité devra apprendre à sérialiser / désérialiser les états intermédiaires et également les figer reçus de différents flux.

Fonctions de fenêtre

Les fonctions de fenêtre sont apparues dans la norme SQL: 2003 . À l'heure actuelle, ils sont pris en charge par tous les systèmes ci-dessus. En substance, les fonctions de fenêtre sont une extension du travail avec les unités. Et, bien sûr, les fonctions d'agrégation personnalisées fonctionnent également dans un contexte fenêtré.

L'extension est la suivante. Et avant SQL: 2003, les fonctions d'agrégation fonctionnaient dans une certaine fenêtre, qui était soit l'ensemble des résultats, soit sa partie, correspondant à la combinaison des valeurs de champ de l'expression GROUP BY. L'utilisateur dispose désormais d'une certaine liberté pour manipuler cette fenêtre.

La différence est que les valeurs calculées à l'aide des fenêtres sont ajoutées à la sortie dans une colonne distincte et ne nécessitent pas que le flux entier se réduise à l'aide des fonctions d'agrégation. Ainsi, dans une demande, vous pouvez utiliser plusieurs agrégats de fenêtres chacun dans son propre contexte (fenêtre). Il pouvait y avoir plusieurs fonctions agrégées auparavant, mais elles fonctionnaient toutes dans une seule fenêtre.

Grands coups

• Plus ()
  la fenêtre est l'ensemble des résultats. Supposons que la requête « select count (1) from Samples » renvoie 169. Dans ce cas, en exécutant « select count (1) over () from Samples », nous obtenons une colonne qui est écrite 169 fois 169 fois.
• OVER (PARTITION BY)
  il s'agit d'un analogue de GROUP BY, pour chaque combinaison de valeurs, une fenêtre est créée dans laquelle des fonctions d'agrégation sont exécutées. Disons que dans la table Samples, une colonne entière est val, les données sont des nombres de 1 à 169.
  Ensuite, la requête « sélectionner le nombre (1) sur (partitionner par (12 + val) / 13) à partir des échantillons » renverra une colonne dans laquelle la valeur 13 est écrite 169 fois.
  
• PLUS (COMMANDER PAR)
  peut être combiné avec PARTITION BY, vous permet de modifier dynamiquement la taille de la fenêtre pendant le curseur, dans ce cas, la fenêtre s'étend du début du groupe à la position actuelle du curseur. Par conséquent, pour le groupe, il s'avère que ce n'est pas la même valeur dans la colonne agrégée, mais la sienne. Pratique pour calculer les montants cumulés. Résultat de la requête
  'sélectionner la somme (val) sur (ordre par val) des échantillons ' sera une colonne dans laquelle le nième élément contiendra la somme des nombres naturels de 1 à n.
• PLUS (RANGS)
  vous permet de définir les cadres de fenêtre, à partir de la position du curseur ou du début / fin de la plage ORDER BY.
  
  Par exemple, ' ... ROWS 1 PRECEDING ... ' signifie que la fenêtre se compose de la ligne actuelle et de 1 avant celle-ci. A ' ... RANGS ENTRE 1 SUIVANT ET 2 SUIVANTS ... ' - la fenêtre se compose de deux lignes immédiatement après le curseur.
  
  LIGNE ACTUELLE dans ce mode indique la position actuelle du curseur. Par exemple, « RANGS ENTRE RANGÉE ACTUELLE ET SANS LIMITE SUIVANTE » signifie de la ligne actuelle jusqu'à la fin de la plage.
• OVER (RANGE)
  diffère de ROWS dans la mesure où CURRENT ROW signifie ici comme début de la fenêtre le début de la plage de ORDER BY, et comme fin de la fenêtre - la dernière ligne de la plage ORDER BY.

La syntaxe d'utilisation des fonctions de fenêtre sur différents systèmes est légèrement différente.

Pour résumer ce qui précède, il reste un sentiment légèrement douloureux que les développeurs, après avoir analysé la construction de divers rapports en SQL, aient mis en évidence les cas les plus courants et les aient concrétisés dans la syntaxe.

Fonctions de retour d'enregistrement

Dans la sortie des fonctions d'agrégation / fenêtre, chaque ligne résultante correspond à une certaine plage de lignes du flux de données entrant. Dans la vie, une telle correspondance n'existe pas toujours.

Par exemple, il est nécessaire de construire une matrice de covariance 10X10 (pour cela, il faudrait 672X672). Cela peut être fait en un seul passage, pour cela nous exécutons la fonction d'agrégation écrite par nous avec 10 paramètres numériques. Le résultat de son travail est un jeu d'enregistrements de 10 lignes de 10 valeurs, chaque élément de matrice se réfère à toutes les lignes du flux d'entrée (peu importe le nombre).

Nous pouvons dire - alors quoi, dans PostgreSQl, par exemple, vous pouvez retourner un tableau à deux dimensions à partir d'une fonction (Ex: 'ARRAY [[1,2], [3,4]'). Ou sérialisez simplement la matrice en ligne.

C'est bien, mais il n'est pas toujours possible de maintenir la taille du résultat dans le cadre acceptable pour cette approche.


La matrice de covariance n'est pas encore un très bon exemple de désynchronisation entre les flux d'entrée et de sortie, car le résultat global est obtenu simultanément à la fin. Supposons que vous souhaitiez traiter / compresser un flux de données source. En même temps

• il y a beaucoup de données, elles sont dans le «tas» sans index, en fait elles ont été simplement «rapidement» écrites sur le disque
• vous devez les trier en différentes catégories, qui sont relativement peu nombreuses
• dans les catégories, moyenne sur des intervalles de temps, stocke uniquement la moyenne, le nombre de mesures et la variance
• tout cela doit être fait rapidement

Quelles sont les options?

1. Dans SQL, un tri par intervalle de temps / catégorie est requis, ce qui contredit le dernier point.
2. Si les données sont déjà triées par heure (ce qui, en fait, n'est pas garanti), et qu'il sera possible de transmettre ce fait au processeur SQL, vous pouvez le faire avec des fonctions de fenêtre et un passage.
3. Écrivez une application distincte qui fera tout cela. En PL / SQL ou, plus probablement, étant donné qu'il y a beaucoup de données, en C / C ++.
4. Fonctions qui renvoient des enregistrements. Ils peuvent peut-être nous aider.

Plus de détails sur A.4. Il existe deux mécanismes pour cela: les tables temporaires et les fonctions de pipeline.

1. Fonctions de convoyeur.
   Ce mécanisme est apparu dans Oracle (à partir du 9i, 2001) et permet à la fonction qui a renvoyé le jeu d'enregistrements de ne pas accumuler de données, mais de les calculer au besoin (par analogie avec la synchronisation de stdout et stdin de deux processus connectés via pipe).
   C'est-à-dire Les résultats des fonctions en pipeline peuvent commencer à être traités avant de quitter cette fonction. Pour cela, il suffit de dire dans la fonction
   
   

     FUNCTION f_trans(p refcur_t) RETURN outrecset PIPELINED IS … 

   
   et enregistrer les lignes de résultats dans le corps
   
   

    LOOP … out_rec.var_char1 := in_rec.email; out_rec.var_char2 := in_rec.phone_number; PIPE ROW(out_rec); … END LOOP; 

   
   En conséquence, nous avons
   
   

    SELECT * FROM TABLE( refcur_pkg.f_trans( CURSOR(SELECT * FROM employees WHERE department_id = 60))); 

   
   Les agrégats personnalisés ne sont tout simplement pas nécessaires lorsqu'il existe des fonctions de pipeline.
   
   Bravo, Oracle!
   
   Il n'y a pas si longtemps (2014), les fonctions de pipeline sont également apparues dans DB2 (IBM i 7.1 TR9, i 7.2 TR1).
2. Tables temporaires.
   Pour commencer, il semble que ni MS SQL ni PostgreSQL ne peuvent retourner un curseur à partir d'une fonction d'agrégation.
   
   Eh bien, par analogie avec les fonctions du pipeline, obtenons le curseur en tant que paramètre, le traitons, l'ajoutons à une table temporaire et y retournons le curseur.
   
   Cependant, dans MS SQL, il n'est pas possible de passer le curseur à une procédure stockée par un paramètre, il est uniquement possible de créer un curseur dans la procédure et de renvoyer le paramètre via la sortie. La même chose peut être dite pour PostgreSQL.
   
   Eh bien, il suffit d'ouvrir le curseur, de le soustraire, de traiter les valeurs, de calculer le résultat, de l'ajouter à la table temporaire et de rendre le curseur.
   
   Ou encore plus simple, nous ajoutons les résultats de la requête à une table temporaire, les traitons et renvoyons les résultats via le curseur à une autre table temporaire.
   
   Que puis-je dire. Tout d'abord et surtout, la lecture des données via le curseur est plus lente que le traitement dans le flux. Deuxièmement, pourquoi avez-vous besoin d'un processeur SQL, lisons des tables avec des curseurs, créons des tables temporaires avec nos mains, écrivons la logique de jointure en boucles ... C'est comme des insertions d'assembleur en C / C ++, parfois vous pouvez vous faire plaisir, mais il vaut mieux ne pas en abuser.

Ainsi, après avoir examiné une question avec des fonctions renvoyant un jeu d'enregistrements, nous arrivons à des conclusions:

• Les agrégats personnalisés ne nous seront pas vraiment utiles ici.
• Dans tous les cas, vous devrez créer des objets dans la base de données. Que ce soit des fonctions ou des tables temporaires. Au minimum, des droits appropriés sont requis. Et je veux juste traiter quelques chiffres.
  
• Néanmoins, même avec les limitations décrites, il n'est parfois pas très élégant, mais avec cette méthode, vous pouvez résoudre le problème.

Quoi d'autre

En fait, si nous avons déjà la possibilité de résoudre des problèmes, de quoi d'autre l'auteur a-t-il besoin?
En fait, la machine de Turing peut également calculer n'importe quoi, ce n'est pas très rapide et pas trop pratique.

Nous formulons les exigences comme suit:

1. ce doit être un opérateur relationnel utilisable au même titre que le reste (sélection, projection, ...)
2. ce doit être un opérateur qui transforme un flux de données en un autre
3. il n'y a pas de synchronisation entre les flux d'entrée et de sortie
4. La déclaration de l'opérateur définit la structure du flux de sortie
5. l'opérateur a la capacité d'initialiser dynamiquement (sous la forme d'une fonction, plus précisément son corps, spécifié directement dans la définition de l'opérateur)
6. ainsi qu'un destructeur sous forme de fonction (...)
7. ainsi qu'une fonction (...) qui est appelée à chaque fois qu'une nouvelle ligne est reçue du flux d'entrée
8. l'opérateur a un contexte d'exécution - un ensemble de variables et / ou de collections définies par l'utilisateur qui sont nécessaires pour le travail
9. pour exécuter cette instruction, vous n'avez pas besoin de créer d'objets de base de données, vous n'avez pas besoin de droits supplémentaires
10. tout ce qui est nécessaire au travail est défini en un seul endroit, dans une seule langue

Il était une fois, l' auteur a fait un tel opérateur qui étend le processeur self-made du sous-ensemble implémenté de TTM / Tutorial D. Maintenant, la même idée est proposée pour SQL.

Cela vaut la peine d'être averti, ici SQL se termine et l'improvisation commence. La syntaxe est laissée telle qu'elle était dans l'original, au final, le sucre syntaxique peut être n'importe quoi, il ne change pas l'essence.

Ainsi, l'opérateur de mastication se compose de

1. Un en-tête qui contient une liste des champs de sortie et leurs types.
   Chaque champ de sortie (et d'entrée) est une variable locale.
   Ex: "chew {" var1 "float," var2 "integer}" signifie qu'il y aura deux colonnes dans le flux de sortie - un point flottant et un entier
2. Corps - une liste de rappels pour les événements, pour le moment - le début du flux, la fin du flux, la ligne. Par syntaxe, les fonctions sont proches de PL / SQL. La fonction prédéfinie __interrupt () est un analogue de PIPE, elle prend les valeurs des variables correspondant aux colonnes de sortie et les place dans le flux de sortie. Si la mémoire tampon du flux de sortie déborde, le travail du gestionnaire s'arrête et le travail du côté récepteur du flux commence.
   Ex: "hook" init "{var1: = 0; var2: = -1; } "

La façon la plus simple de montrer des exemples.

• Un analogue de la fonction d'agrégation SUM.
  
  

   --  'select sum(val) from samples' -- select * from samples chew {“sum(val)” float} --    hook “init” { “sum(val)” := 0; --      } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; end if; } hook “finit” { call __interrupt(); --  PIPE } 

  
  Il semble volumineux, mais ce n'est qu'un exemple,
  il n'est pas nécessaire d'écrire un programme C pour ajouter quelques chiffres.
• SUM + AVG
  
  

   --  'select sum(val), avg(val) from samples' -- select * from samples chew { “sum(val)” float, “avg(val)” float --       } hook “init” { “sum(val)” := 0; “avg(val)” := 0; var num integer; num := 0; --    ,       } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then “avg(val)” := “sum(val)” / num; end if; call __interrupt(); } 

  
  Ici, nous attirons l'attention sur le fait que la sommation ne se produit qu'une seule fois.
• SUM + GROUP BY
  
  

   --  'select sum(val) from samples group by type' -- select * from --     ( samples val, type from samples order by type ) chew { “sum(val)” float } hook “init” { “sum(val)” := 0; var gtype integer; gtype := NULL; var num integer; --   num := 0; } hook “row” { if (gtype <> “type”) then __interrupt(); “gtype” := type; "sum(val)" := 0; num := 0; end if; if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then call __interrupt(); end if; } 

• ROW_NUMBER () OVER ()
  
  

   -- select row_number() over() as num, * from samples -- select * from samples chew { “num” integer, * --        --   '* except val1, ...valX',   TTM } hook “init” { num := 0; } hook “row” { num := num + 1; call __interrupt(); } 

Est-il possible de proposer un exemple sur lequel cette approche donne des résultats fondamentalement inaccessibles de la manière habituelle? Nous les avons.

Parfois, il arrive que les données soient presque triées. Ils peuvent même être complètement triés, mais ce n'est pas sûr.

( ) . C'est-à-dire T1 T2 T1 < T2.

, T1 T2 () , ( ) .

, , , , .

.

, .

.

, .
, .

SQL- .

lambda- SQL- , , .

Conclusion

.

PL/SQL.

.

, , GROUP BY.

, , SQL- .

, , .

PS: .