Funciones Lambda en SQL ... pensemos

De qué se tratará el artículo, y así lo implica el nombre.

Además, el autor explicará por qué esto es necesario desde su punto de vista, y dirá que SUBJ no es solo una tecnología de moda, sino también "un negocio doblemente necesario, tanto agradable como útil".

Siempre es interesante ver cómo varias personas talentosas hacen algo (un lenguaje de programación, por qué no), sabiendo exactamente qué problema están resolviendo y qué tareas se asignaron. Y también probar su creación en sí mismos. No se puede comparar con las creaciones monumentales de los comités gigantes, que ponen el mantenimiento de la armonía del universo en primer plano, quién entiende cómo.

Compare, por ejemplo, el destino de FORTRAN y PL / 1 . ¿Quién recordará ahora sobre este PL / 1?

Desde este punto de vista, AWK , por ejemplo, tiene mucho éxito. Vale la pena decir que en su nombre A es Alfred Aho , uno de los autores de Dragon Book , W es Peter Weinberger , que intervino en Fortran-77, K es Brian Kernigan , ¿dónde estaría sin él? El lenguaje está destinado a procesar secuencias de texto sobre la marcha en canalizaciones entre procesos.

El lenguaje no tiene tipo ( esto no es del todo cierto ), su sintaxis es muy similar a la de C, tiene capacidades de filtrado, matrices asociativas, eventos de inicio / final de flujo, evento de nueva línea ...

El autor siempre ha estado impresionado por este lenguaje también por el hecho de que su intérprete no necesita ser instalado, en sistemas similares a UNIX siempre está ahí, y en Windows es suficiente simplemente copiar el archivo ejecutable y todo funciona. Sin embargo, este no es el caso.

En el proceso, el autor tiene que usar el paquete SQL + AWK con bastante frecuencia, y es por eso. SQL sigue siendo un lenguaje inicialmente declarativo diseñado para controlar los flujos de datos. Ofrece oportunidades muy limitadas para trabajar con el contexto de ejecución de consultas en forma de funciones agregadas.

¿Cómo, por ejemplo, construir un histograma bidimensional usando SQL?

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

Pero digamos, usar GROUP BY implica ordenar, y no es un placer barato si tienes cientos de millones (o incluso más) filas.
Resumen: el procesador SQL recurre a la clasificación cuando no puede estimar el número de grupos después de GROUP BY. Sin embargo, la agrupación por valores calculados es (a menudo) solo el caso.

Por lo tanto, tienes que hacer algo como:

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

donde mk_hist2d.awk acumula estadísticas en la matriz asociativa y las muestra al finalizar el trabajo

 # 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"; } } 

Hay una PERO: el flujo de datos completo debe enviarse desde el servidor a la máquina de trabajo, y esto no es tan barato.

¿Es posible combinar de alguna manera lo agradable con lo útil: acumular estadísticas durante la ejecución de la consulta SQL, pero sin recurrir a la clasificación? Sí, por ejemplo, usando funciones de agregado personalizadas.

Funciones agregadas personalizadas

Subj está presente en diferentes sistemas, en todas partes se hace un poco a su manera.

1. PostgreSQL La documentación está aquí . Más detalles aquí .
   Aquí es donde se calcula el saldo máximo de la cuenta.
   Y este es un ejemplo que calcula qué hay más en la columna booleana: verdadero o falso.
   
   Se ve así:
   
   

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

   
   Aquí SFUNC es una función que se llama para cada línea en la secuencia,
   El primer argumento es de tipo STYPE .
   
   FINALFUNC se utiliza para finalizar los cálculos y devuelve el valor del agregado.
   INITCOND : inicialización del valor inicial del estado interno ( STYPE ), pasado como primer argumento.
   Dado que las funciones se pueden escribir en C (lo que significa que para el estado interno puede usar la memoria que se libera automáticamente cuando cierra la solicitud), esta es una herramienta muy poderosa. Fuera del alcance de su uso, uno todavía debe poder ir.
2. MS SQL
   Anteriormente (2000), antes de la solicitud, era necesario crear un objeto ActiveX, para hacer la agregación usando este objeto.
   Ahora (2016+) esto se hace en el entorno CLR. Deberá crear una función personalizada, crear y registrar un ensamblaje . Entonces puedes crear un agregado .
   Un ejemplo de cálculo de la media geométrica, así como la combinación de cadenas: con parámetros adicionales y un tipo definido por el usuario para almacenar un estado intermedio.
3. Oráculo
   En Oracle, esto se hace utilizando el cartucho de datos ODCIAggregate (interfaz).
   Para crear su propio agregado, debe escribir un tipo personalizado que implemente 4 métodos
   - la inicialización (ODCIAggregateInitialize), estática, debe crear una instancia del tipo deseado y volver a través del parámetro
   - iteraciones (ODCIAggregateIterate), llamadas en cada fila de datos
   - merge (ODCIAggregateMerge), usado para combinar agregados ejecutados en paralelo
   - terminar (ODCIAggregateTerminate) - salida de resultados
   Ejemplos: 1 , 2 , 3 , 4 .
4. DB2
   No hay una forma explícita de utilizar agregados personalizados en DB2.
   Pero puede deslizar una función estándar (aunque MAX) en un tipo definido por el usuario (en Java) y hacer que el sistema ejecute consultas del formulario
   
   

    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é es digno de mención en todos estos sistemas?

• De una forma u otra, deberá crear algunos objetos en la base de datos. Ya sea AGREGADO o TIPO. Como mínimo, se requieren derechos apropiados. Y solo quiero agregar algunos números en su rodilla.
  
• Puede que tenga que escribir algo en otro idioma, ya sea C, C # o Java.
  Para integrar lo que está escrito en el sistema, nuevamente, se requieren derechos. Pero todo lo que quiero ...
  
• Dificultad para inicializar. Suponga que desea leer histogramas con diferentes tamaños de canasta. Parece que es más fácil: indicaremos el INITCOND deseado al declarar el agregado (PostgreSQL) y todo el negocio. Pero luego, para cada tamaño de la cesta, necesitará su propio agregado, y para esto nuevamente se necesitan los derechos.
  
  Aquí puede recurrir a un truco sucio y deslizar el procesador de unión desde la línea de inicialización (hacia adelante) y los datos, construir el contexto no en el constructor, sino cuando se recibe la primera línea.
  
• Sin embargo, incluso con las limitaciones descritas, los agregados personalizados le permiten calcular cualquier cosa.
  
• Es importante que los agregados se puedan paralelizar , al menos PostgreSQL, y Oracle (Enterprise Edition) puede hacer esto. Para esto, la verdad tendrá que aprender a serializar / deserializar estados intermedios y también congelarlos recibidos de diferentes flujos.

Funciones de ventana

Las funciones de ventana aparecieron en el estándar SQL: 2003 . Por el momento, son compatibles con todos los sistemas anteriores. En esencia, las funciones de ventana son una extensión del trabajo con unidades. Y, por supuesto, las funciones agregadas personalizadas también funcionan en un contexto de ventana.

La extensión es esta. Y antes de SQL: 2003, las funciones agregadas funcionaban en una determinada ventana, que era el conjunto de resultados completo o su parte, correspondiente a la combinación de valores de campo de la expresión GROUP BY. El usuario ahora tiene cierta libertad para manipular esta ventana.

La diferencia es que los valores calculados usando las ventanas se agregan a la salida en una columna separada, y no requieren que toda la secuencia se colapse usando funciones agregadas. Entonces, en una solicitud, puede usar varios agregados de ventana, cada uno en su propio contexto (ventana). Podría haber varias funciones agregadas antes, pero todas funcionaron en una ventana.

Grandes trazos

• Sobre ()
  la ventana es todo el conjunto de resultados. Digamos que la consulta ' select count (1) from Samples ' devuelve 169. En este caso, al ejecutar ' select count (1) over () from Samples ', obtenemos una columna que se escribe 169 veces 169 veces.
• SOBRE (PARTICIÓN POR)
  es un análogo de GROUP BY, para cada combinación de valores se crea una ventana en la que se realizan funciones agregadas. Digamos que en la tabla Muestras una columna entera es val, los datos son números del 1 al 169.
  Luego, la consulta ' seleccionar recuento (1) sobre (partición por (12 + val) / 13) de Muestras ' devolverá una columna en la que el valor 13 se escribe 169 veces.
  
• SOBRE (ORDENAR POR)
  se puede combinar con PARTITION BY, le permite cambiar dinámicamente el tamaño de la ventana durante el cursor, en este caso, la ventana se extiende desde el comienzo del grupo hasta la posición actual del cursor. Como resultado, para el grupo, no resulta el mismo valor en la columna agregada, sino el propio. Conveniente para calcular cantidades acumuladas. Resultado de la consulta
  'seleccionar sum (val) sobre (ordenar por val) de Muestras ' será una columna en la que el enésimo elemento contendrá la suma de números naturales del 1 al n.
• SOBRE (FILAS)
  le permite definir los marcos de las ventanas, comenzando desde la posición del cursor o el comienzo / final del rango ORDER BY.
  
  Por ejemplo, ' ... ROWS 1 PRECEDING ... ' significa que la ventana consiste en la línea actual y 1 antes. A ' ... FILAS ENTRE 1 SIGUIENTE Y 2 SIGUIENTES ... ' - la ventana consta de dos líneas inmediatamente después del cursor.
  
  FILA ACTUAL en este modo indica la posición actual del cursor. Por ejemplo, ' FILAS ENTRE FILA ACTUAL Y SIGUIENTE SIN LÍMITES ' significa desde la línea actual hasta el final del rango.
• SOBRE (RANGO)
  difiere de las FILAS en que la FILA ACTUAL aquí significa como el comienzo de la ventana el comienzo del rango de ORDER BY, y como el final de la ventana, la última línea del rango de ORDER BY.

La sintaxis para usar funciones de ventana en diferentes sistemas es ligeramente diferente.

Para resumir lo anterior, sigue habiendo una sensación ligeramente dolorosa de que los desarrolladores, después de analizar la construcción de varios informes en SQL, destacaron los casos más comunes y los concretaron en la sintaxis.

Funciones de devolución de registros

En la salida de las funciones de agregado / ventana, cada fila resultante corresponde a un cierto rango de filas del flujo de datos entrantes. En la vida, tal correspondencia no siempre existe.

Por ejemplo, se requiere construir una matriz de covarianza 10X10 (para esto, tomaría 672X672). Esto se puede hacer de una vez, para esto ejecutamos la función agregada escrita por nosotros con 10 parámetros numéricos. El resultado de su trabajo es un conjunto de registros de 10 filas de 10 valores, cada elemento de la matriz se refiere a todas las filas de la secuencia de entrada (no importa cuántas haya).

Podemos decir, entonces, qué, en PostgreSQl, por ejemplo, puede devolver una matriz bidimensional de una función (Ej: 'ARRAY [[1,2], [3,4]'). O simplemente serialice la matriz en una fila.

Es bueno, pero no siempre es posible mantener el tamaño del resultado dentro del marco aceptable para este enfoque.


La matriz de covarianza todavía no es un muy buen ejemplo de la desincronización entre los flujos de entrada y salida, ya que todo el resultado se obtiene simultáneamente al final. Suponga que desea procesar / comprimir una secuencia de datos de origen. Al mismo tiempo

• hay muchos datos, están en el "montón" sin índices, de hecho, simplemente se escribieron "rápidamente" en el disco
• necesita clasificarlos en diferentes categorías, que son relativamente pocas
• dentro de las categorías, promedio en intervalos de tiempo, solo almacenamiento promedio, número de mediciones y varianza
• todo esto debe hacerse rápidamente

Cuales son las opciones?

1. Dentro de SQL, se requiere ordenar por intervalo de tiempo / categoría, lo que contradice el último punto.
2. Si los datos ya están ordenados por tiempo (lo que, de hecho, no está garantizado), y será posible transmitir este hecho al procesador SQL, puede hacerlo con funciones de ventana y una pasada.
3. Escriba una aplicación separada que haga todo esto. En PL / SQL o, más probablemente, dado que hay muchos datos, en C / C ++.
4. Funciones que devuelven registros. Quizás nos puedan ayudar.

Más detalles sobre A.4. Hay dos mecanismos para esto: tablas temporales y funciones de canalización.

1. Funciones del transportador.
   Este mecanismo apareció en Oracle (a partir del 9i, 2001) y permite que la función que devolvió el conjunto de registros no acumule datos, sino que los calcule según sea necesario (por analogía con la sincronización de stdout y stdin de dos procesos conectados a través de una tubería).
   Es decir Los resultados de las funciones canalizadas pueden comenzar a procesarse antes de salir de esta función. Para hacer esto, es suficiente decir en la definición de la función
   
   

     FUNCTION f_trans(p refcur_t) RETURN outrecset PIPELINED IS … 

   
   y registrar líneas de resultado en el cuerpo
   
   

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

   
   Como resultado, tenemos
   
   

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

   
   Los agregados personalizados simplemente no son necesarios cuando hay funciones de canalización.
   
   Bravo, Oracle!
   
   No hace mucho tiempo (2014), las funciones de canalización también aparecieron en DB2 (IBM i 7.1 TR9, i 7.2 TR1).
2. Tablas temporales.
   Para empezar, parece que ni MS SQL ni PostgreSQL pueden devolver un cursor desde una función agregada.
   
   Bueno, por analogía con las funciones de canalización, obtengamos el cursor como parámetro, lo procesemos, lo agreguemos a una tabla temporal y le devolvamos el cursor.
   
   Sin embargo, en MS SQL no es posible pasar el cursor a un procedimiento almacenado por un parámetro, solo es posible crear un cursor en el procedimiento y devolver el parámetro a través de la salida. Lo mismo puede decirse de PostgreSQL.
   
   Bueno, está bien, simplemente abra el cursor, reste, procese los valores, calcule el resultado, agréguelo a la tabla temporal y renderice el cursor.
   
   O incluso más simple, agregamos los resultados de la consulta a una tabla temporal, la procesamos y devolvemos los resultados a través del cursor a otra tabla temporal.
   
   Que puedo decir Primero, y lo más importante, la lectura de datos a través del cursor es más lenta que el procesamiento en la secuencia. En segundo lugar, ¿por qué necesita un procesador de SQL? Vamos a leer tablas con cursores, crear tablas temporales con nuestras manos, escribir lógica de unión en bucles ... Es como insertos de ensamblador en C / C ++, a veces puede tratarse a sí mismo, pero es mejor no abusar de él.

Entonces, después de considerar una pregunta con funciones que devuelven un conjunto de registros, llegamos a conclusiones:

• Los agregados personalizados realmente no nos ayudarán aquí.
• En cualquier caso, deberá crear algunos objetos en la base de datos. Ya sea funciones o tablas temporales. Como mínimo, se requieren derechos apropiados. Y solo quiero procesar algunos números.
  
• Sin embargo, incluso con las limitaciones descritas, a veces no es muy elegante, pero con este método puede resolver el problema.

Que mas

De hecho, si ya tenemos la oportunidad de resolver problemas, ¿qué más necesita el autor?
En realidad, la máquina Turing también puede calcular cualquier cosa, simplemente no lo hace muy rápido y no es muy conveniente.

Formulamos los requisitos de la siguiente manera:

1. debe ser un operador relacional que pueda usarse a la par del resto (selección, proyección, ...)
2. debe ser un operador que convierta un flujo de datos en otro
3. no hay sincronización entre flujos de entrada y salida
4. La declaración del operador define la estructura del flujo de salida
5. el operador tiene la capacidad de inicializar dinámicamente (en forma de una función, más precisamente su cuerpo, especificado directamente en la definición del operador)
6. así como un destructor en forma de función (...)
7. así como una función (...) que se llama cada vez que se recibe una nueva línea del flujo de entrada
8. el operador tiene un contexto de ejecución: un conjunto de variables y / o colecciones definidas por el usuario que son necesarias para el trabajo
9. para ejecutar este operador, no necesita crear objetos de base de datos, no necesita derechos adicionales
10. todo lo que se requiere para el trabajo se define en un lugar, en un idioma

Érase una vez, el autor creó un operador de este tipo que amplía el procesador de fabricación propia del subconjunto implementado de TTM / Tutorial D. Ahora se propone la misma idea para SQL.

Vale la pena advertir, aquí termina SQL y comienza la improvisación. La sintaxis se deja como estaba en el original, al final, el azúcar sintáctico puede ser cualquier cosa, no cambia la esencia.

Entonces, el operador de masticación consiste en

1. Un encabezado que contiene una lista de campos de salida y sus tipos.
   Cada campo de salida (y entrada) es una variable local.
   Ejemplo: "masticar {" var1 "flotante," var2 "entero}" significa que habrá dos columnas en la secuencia de salida: un punto flotante y un entero
2. Cuerpos: una lista de devoluciones de llamada para eventos, en este momento, el inicio de la transmisión, el final de la transmisión, la línea. Por sintaxis, las funciones están cerca de PL / SQL. La función predefinida __interrupt () es un análogo de PIPE, toma valores de las variables correspondientes a las columnas de salida y lo coloca en la secuencia de salida. Si el búfer del flujo de salida está lleno, el trabajo del controlador se detendrá y comenzará el trabajo del lado receptor del flujo.
   Ej: "hook" init "{var1: = 0; var2: = -1; } "

La forma más fácil de mostrar ejemplos.

• Un análogo de la función agregada 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 } 

  
  Parece voluminoso, pero es solo un ejemplo,
  no es necesario escribir un programa en C para agregar un par de números.
• SUMA + 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(); } 

  
  Aquí llamamos la atención sobre el hecho de que la suma se produce solo una vez.
• 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(); } 

¿Es posible ofrecer un ejemplo en el que este enfoque dé resultados que son fundamentalmente inalcanzables de la manera habitual? Los tenemos

A veces sucede que los datos están casi ordenados. Incluso pueden estar completamente ordenados, pero no se sabe con certeza.

Suponga que en el ejemplo anterior (compresión de flujo de datos) los datos provienen de diferentes fuentes y, por diversas razones, pueden mezclarse ligeramente. Es deciruna línea de una fuente con una marca de tiempo T1 puede estar en la base de datos después de una línea de otra fuente con la hora T2, mientras que T1 <T2.

Incluso si garantizamos que la diferencia entre T1 y T2 nunca excederá una cierta (escasa) constante, no podemos hacerlo sin ordenar aquí (de la manera tradicional).

Sin embargo, utilizando el enfoque propuesto, es posible almacenar en búfer el flujo de entrada y procesar los datos del intervalo de tiempo actual solo después de que la entrada haya recibido líneas con una marca de tiempo que exceda al menos una constante dada por el límite derecho del intervalo.

Hay un punto muy importante aquí.

Solo nosotros sabemos que los datos están casi ordenados.

Solo nosotros sabemos el valor de esa constante.

Esta constante es característica solo para este problema, y ​​tal vez solo para este experimento.
Y usamos este truco bajo nuestra propia responsabilidad para evitar la clasificación.

Nuestro conocimiento estándar de la tarea no existe de manera estándar para decirle al procesador SQL, y es difícil de imaginar.

Y el uso de funciones lambda proporciona una forma universal de forzar al procesador SQL a hacer exactamente lo que necesitamos exactamente donde lo necesitamos.

Conclusión

El diseño propuesto no parece muy difícil de implementar.

En cualquier caso, con PL / SQL válido.

La idea en sí misma es simple e intuitiva y no agrega nuevas entidades al lenguaje.

Esta es una sola unidad, que, si es necesario, reemplaza las funciones de agregado y ventana, GROUP BY.

Un mecanismo que le permite prescindir de clasificaciones donde no hay forma con un procesador SQL tradicional.

Pero lo más importante, es un mecanismo que le da la libertad de hacer lo que quiera con la forma más imperativa con los datos.

PD: gracias a Dorofei Proleskovsky por participar en la preparación del artículo.