Muy a menudo preguntan si hay análogos de funciones analíticas (ventana) en MySQL.
Nota Al momento de escribir, no existían tales análogos, pero el artículo sigue siendo de interés académico en términos de analizar el enfoque original para usar variables para MySQL.Para reemplazar las funciones analíticas, a menudo se utilizan consultas de conexión automática, subconsultas complejas y más. La mayoría de estas soluciones son ineficaces en términos de rendimiento.
También en MySQL no hay recursividad. Sin embargo, algunas de las tareas que generalmente se resuelven mediante funciones analíticas o recursivas pueden manejarse con herramientas MySQL.
Una de estas herramientas es única, no característica de otro mecanismo DBMS de trabajar con variables dentro de una consulta SQL. Podemos declarar una variable dentro de la consulta, cambiar su valor y sustituirla en SELECT para la salida. Además, el orden de procesamiento de las líneas en la solicitud y, como resultado, el orden de asignación de valores a las variables se puede establecer en una clasificación personalizada.
Advertencia El artículo asume que el procesamiento de expresiones en la cláusula SELECT se realiza de izquierda a derecha, sin embargo, no hay confirmación oficial de este orden de procesamiento en la documentación de MySQL. Esto debe tenerse en cuenta al cambiar la versión del servidor. Para garantizar la coherencia, puede utilizar la instrucción ficticia CASE o IF.
Analogía de recursión
Considere un ejemplo simple que genera una secuencia de Fibonacci (en la secuencia de Fibonacci, cada término es igual a la suma de los dos anteriores, y los primeros 2 son iguales a uno):
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, (SELECT 1 X UNION ALL SELECT 2)X;
Esta consulta genera 18 números de Fibonacci, sin contar los dos primeros:
2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765
Ahora veamos cómo funciona.
En las líneas 5) 6) se generan 9 registros. Nada inusual aquí.
En la línea 7) declaramos dos variables @I, @J y les asignamos 1.
En la línea 3), sucede lo siguiente: primero, a la variable @I se le asigna la suma de las dos variables. Luego asignamos lo mismo a la variable @J, teniendo en cuenta el hecho de que el valor de @I ya ha cambiado.
En otras palabras, los cálculos en SELECT se realizan de izquierda a derecha; consulte también la observación al comienzo del artículo.
Además, el cambio de variables se lleva a cabo en cada uno de nuestros 9 registros, es decir Al procesar cada nueva línea, las variables @I y @J contendrán los valores calculados al procesar la línea anterior.
Para resolver el mismo problema con la ayuda de otros DBMS, ¡tendríamos que escribir una
consulta recursiva!Nota:Las variables deben declararse en una subconsulta separada (línea 7), si declaramos una variable en la cláusula SELECT, lo más probable es que se evalúe solo 1 vez (aunque el comportamiento específico dependería de la versión del servidor). El tipo de una variable está determinada por el valor por el cual se inicializa. Este tipo puede cambiar dinámicamente. Si establece la variable en NULL, su tipo será BLOB.El orden en que se procesan las filas en SELECT, como se mencionó anteriormente, depende de la ordenación personalizada. Un ejemplo simple de numeración de líneas en un orden dado:
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
Análogos de funciones analíticas.
Las variables también se pueden usar para reemplazar las funciones analíticas. Los siguientes son algunos ejemplos. Por simplicidad, asumimos que todos los campos NO SON NULOS, y que la ordenación y el particionamiento (PARTITION BY) ocurren en un campo. El uso de valores NULL y clasificaciones más complejas hará que los ejemplos sean más engorrosos, pero la esencia no cambiará.
Por ejemplo, cree la tabla TestTable:
CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL );
donde
group_id: identificador de grupo (análogo de la ventana de función analítica);
order_id: un campo único para ordenar;
El valor es un valor numérico.
Complete nuestra tabla con datos de prueba:
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;
Ejemplos de reemplazo de algunas funciones analíticas.
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 (PARTICIÓN POR 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) SUMA (valor) OVER (PARTICIÓN POR 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 (valor) OVER (PARTICIÓN POR 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
Para LEAD, todo es igual, solo necesita cambiar la clasificación a ORDER BY group_id, order_id DESC
Para las funciones COUNT, MIN, MAX, todo es algo más complicado, porque hasta que analicemos todas las líneas en el grupo (ventana), no podremos encontrar el valor de la función. MS SQL, por ejemplo, "pone en cola" una ventana para estos fines (coloca temporalmente las filas de la ventana en una tabla de búfer oculta para acceder a ellas nuevamente), en MySQL no existe tal posibilidad. Pero podemos calcular para cada ventana el valor de la función en la última fila para una clasificación dada (es decir, después de analizar toda la ventana), y luego, ordenando las filas en la ventana en el orden inverso, anote el valor calculado en toda la ventana.
Entonces necesitamos dos clasificaciones. Para que la clasificación final siga siendo la misma que en los ejemplos anteriores, primero ordenamos por los campos group_id ASC, order_id DESC, luego por los campos group_id ASC, order_id ASC.
5) COUNT (*) OVER (PARTICIÓN POR group_id)
En el primer tipo, simplemente numeramos las entradas. En el segundo, asignamos el número máximo a todas las líneas de la ventana, que corresponderá al número de líneas en la ventana.
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
Las funciones MAX y MIN se calculan por analogía. Solo daré un ejemplo para MAX:
6) MAX (valor) OVER (PARTICIÓN POR id_grupo)
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 (valor DISTINCT) OVER (PARTITION BY group_id)
Una cosa interesante que no está disponible en MS SQL Server, pero se puede calcular con una subconsulta tomando MAX de RANK. Haremos lo mismo aquí. En el primer orden, calculamos RANK () OVER (PARTITION BY group_id ORDER BY value DESC), luego en el segundo orden ponemos el valor máximo a todas las líneas en cada ventana:
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
Rendimiento
Para comenzar, comparamos el rendimiento de la numeración de líneas en una consulta usando autounión y variables.
1) La forma clásica con auto-conexión
SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;
Lo que para 10000 registros en la tabla TestTable produce:
Duración / Fetch
16.084 segundos / 0.016 segundos
2) Usando variables:
SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;
Produce:
Duración / Fetch
0.016 segundos / 0.015 segundos
El resultado habla por sí mismo. Sin embargo, debe entenderse que los valores calculados usando variables no se usan de manera óptima en condiciones de filtrado. La clasificación y el cálculo ocurrirán para TODAS las filas, a pesar de que al final solo necesitamos una pequeña parte de ellas.
Consideremos con más detalle el ejemplo de tal tarea:
Imprima las 2 primeras filas de la tabla TestTable para cada valor group_id, ordenado por order_id.Así es como se resolvería esta tarea en un DBMS con soporte para funciones analíticas:
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;
Sin embargo, el optimizador MySQL no sabe nada acerca de las reglas por las cuales calculamos el campo RowNum. Tendrá que numerar TODAS las líneas, y solo luego seleccionar las necesarias.
Ahora imagine que tenemos 1 millón de registros y 20 valores únicos de group_id. Es decir para seleccionar 40 filas, MySQL calculará el valor RowNum para un millón de filas. No hay una solución hermosa para este problema con una sola consulta en MySQL. Pero primero puede obtener una lista de valores únicos de group_id, por ejemplo, así:
SELECT DISTINCT group_id FROM TestTable;
Luego, utilizando cualquier otro lenguaje de programación, genere una consulta del formulario:
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 consultas fáciles funcionarán mucho más rápido que calcular RowNum para un millón de líneas.