A veces, al analizar datos, surge el
problema de
distinguir las "cadenas" en una muestra , es decir,
secuencias ordenadas de registros, para cada una de las cuales
se cumple una determinada condición .
Esto puede ser una condición en los datos del registro en sí o una expresión compleja en relación con uno o más registros anteriores, por ejemplo, la duración del intervalo entre muestras de tiempo de cierre.
Las soluciones tradicionales proporcionan diferentes opciones para "autounirse", cuando la muestra está conectada a sí misma, o el uso de ciertos hechos "fuera de los datos" - por ejemplo, que los registros deben tener un paso estrictamente definido (N + 1, "para cada día", ... )
La primera opción a menudo conduce a la
complejidad cuadrática del algoritmo en términos del número de registros, lo cual es
inaceptable en muestras grandes , y la segunda
puede fácilmente "desmoronarse" si de repente no hay muestras en los datos de origen.
Pero esta tarea nos ayudará a resolver eficazmente las
funciones de ventana en PostgreSQL.
Tarea: contar el dinero de otras personas
Considere el caso más simple de una cadena: cuando la condición de continuidad está determinada por los datos del registro en sí.
Todas las operaciones adicionales no tienen que hacerse por separado. Pero en aras de la claridad del algoritmo, lo dividiré en pasos sucesivos y,
al final, mostraré qué y cómo optimizarlo .
Imaginemos que tenemos un pequeño banco que mantiene los saldos de las cuentas de los clientes en la tabla. Tan pronto como se produce la transacción de recibo y gasto, esta fecha también fija el monto total de la factura al final del día.
Después de un largo feriado de Año Nuevo, el banco decidió recompensar a sus clientes, y cada persona que abrió una cuenta este año acumula además + 1% del saldo diario promedio durante el período continuo más largo cuando la cuenta no se "restableció" .
Aquí está nuestro criterio para la continuidad de la "cadena". Bueno, el orden de los datos estará determinado por las fechas de los saldos.
Nos trajeron un CSV de este tipo y nos pidieron que calculen rápidamente quién y cuánto debería obtener esa generosidad del banco:
date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5
Solo tenga en cuenta algunos hechos que se notan en estos datos:
- 01.01 era feriado y el banco no funcionaba. Por lo tanto, ninguno de los clientes tiene registros de cambios en el saldo ese día, pero tienen dinero en sus cuentas. Es decir, los algoritmos de "fuerza bruta" que iteran por día no funcionarán normalmente.
- 04.01 Alice no realizó ninguna operación, por lo que no hay entrada. Pero antes del 05.01, el monto en su cuenta no era cero; esto deberá tenerse en cuenta en el análisis.
- Llevamos a cabo el análisis el 01.01-12.01, pero el saldo de la cuenta de Alice al final de este período no es cero. También tenemos en cuenta la necesidad de limitar el período.
CSV a la mesa
La mejor manera de importar desde CSV es
usar el operador COPY . Pero intentaremos hacer esto mediante expresiones regulares para calentar:
CREATE TEMPORARY TABLE tbl AS SELECT to_date(prt[1], 'DD.MM.YYYY') dt , prt[2] client , prt[3]::numeric(32,2) balance FROM ( SELECT regexp_split_to_array(str, ';') prt FROM ( SELECT regexp_split_to_table( $$ date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5 $$ , E'\\n') str ) T WHERE str <> '' OFFSET 1 ) T;
Este es un método "deshonesto" en el sentido de que no se digiere correctamente, por ejemplo, protegiendo un separador en el cuerpo de un campo. Pero para la mayoría de las aplicaciones simples: adecuado.
Paso 1: corrige la condición de la aplicación
En nuestro caso, la condición de continuidad de la cadena es un equilibrio distinto de cero. Lo mostramos como un campo separado, para mayor claridad, ordenando cronológicamente por cliente:
SELECT * , balance > 0 cond FROM tbl ORDER BY client, dt;
dt | client | balance | cond ------------------------------------ 2020-01-01 | | 150.00 | t 2020-01-02 | | 100.00 | t 2020-01-03 | | 200.00 | t 2020-01-05 | | 0.00 | f 2020-01-06 | | 50.00 | t 2020-01-08 | | 0.00 | f 2020-01-09 | | 0.00 | f 2020-01-10 | | 5.00 | t 2020-01-01 | | 100.00 | t 2020-01-02 | | 150.00 | t 2020-01-08 | | 200.00 | t 2020-01-09 | | 0.00 | f
Paso 2: Calcule lo que falta
Tenga en cuenta que la cantidad de Bob no cambió de 02.01 a 08.01. Y de acuerdo con las condiciones del problema, debemos calcular el resto
diario promedio , es decir, necesitamos información sobre estos días "perdidos". O al menos el número de días en que el valor se mantuvo igual:
coalesce(lead(dt) OVER(PARTITION BY client ORDER BY dt), '2020-01-12') - dt days
dt | client | balance | cond | days ------------------------------------------- 2020-01-01 | | 150.00 | t | 1 2020-01-02 | | 100.00 | t | 1 2020-01-03 | | 200.00 | t | 2 2020-01-05 | | 0.00 | f | 1 2020-01-06 | | 50.00 | t | 2 2020-01-08 | | 0.00 | f | 1 2020-01-09 | | 0.00 | f | 1 2020-01-10 | | 5.00 | t | 2 2020-01-01 | | 100.00 | t | 1 2020-01-02 | | 150.00 | t | 6 2020-01-08 | | 200.00 | t | 1 2020-01-09 | | 0.00 | f | 3
Usando
la función de ventana lead (), aprendimos la fecha del
siguiente registro en orden, y a través de la
fusión limitamos el intervalo para el último. Al mismo tiempo, utilizaron la propiedad útil de que la
diferencia de dos fechas en PostgreSQL devuelve un número entero de días entre ellas.
Como un bono casi gratis, obtuvimos la misma información para registros con saldo cero. Pero si hay muchas líneas con una condición de incumplimiento que no nos interesan, tiene sentido
conducir tales
cálculos bajo CASE para ahorrar recursos del servidor.
Paso 3: Encuentra los puntos de ruptura
El comienzo de cada cadena que nos interesa es el punto en el que el valor de la condición calculada previamente cambia en relación con el registro
anterior . Usaremos la función
lag () para encontrar estos puntos:
lag(cond) OVER(PARTITION BY client ORDER BY dt) IS DISTINCT FROM cond chain_start
dt | client | balance | cond | days | chain_start --------------------------------------------------------- 2020-01-01 | | 150.00 | t | 1 | t 2020-01-02 | | 100.00 | t | 1 | f 2020-01-03 | | 200.00 | t | 2 | f 2020-01-05 | | 0.00 | f | 1 | t 2020-01-06 | | 50.00 | t | 2 | t 2020-01-08 | | 0.00 | f | 1 | t 2020-01-09 | | 0.00 | f | 1 | f 2020-01-10 | | 5.00 | t | 2 | t 2020-01-01 | | 100.00 | t | 1 | t 2020-01-02 | | 150.00 | t | 6 | f 2020-01-08 | | 200.00 | t | 1 | f 2020-01-09 | | 0.00 | f | 3 | t
Usando el operador
IS DISTINCT FROM en lugar de <>, evitamos los problemas de comparar con NULL para los primeros registros de cada cliente. En consecuencia, todas las líneas donde el valor VERDADERO es el comienzo de una nueva cadena, y FALSO es su continuación.
Paso 4: encadena los enlaces
Para agrupar datos dentro de cada cadena individual, es más fácil asignar
el mismo identificador a todos sus registros. El número de serie de la cadena en sí es perfecto para ello. Y es exactamente igual al
número de "comienzos" de cadenas que se encontraron más arriba en la muestra.
Se pueden calcular a través de la suma de "ventana" de la suma de valores bool ({boolean} :: integer) o contando el número de registros que coinciden con la condición count (*) FILTER (WHERE {boolean}. Utilizaremos la segunda opción:
count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid
dt | client | balance | cond | days | chain_start | grpid ----------------------------------------------------------------- 2020-01-01 | | 150.00 | t | 1 | t | 1 2020-01-02 | | 100.00 | t | 1 | f | 1 2020-01-03 | | 200.00 | t | 2 | f | 1 2020-01-06 | | 50.00 | t | 2 | t | 2 2020-01-10 | | 5.00 | t | 2 | t | 3 2020-01-01 | | 100.00 | t | 1 | t | 1 2020-01-02 | | 150.00 | t | 6 | f | 1 2020-01-08 | | 200.00 | t | 1 | f | 1
En este paso, ya conocemos la longitud de todos los enlaces en cada cadena, ya no necesitamos registros "poco interesantes", por lo que simplemente los filtramos.
Paso 5: poner cadenas
Para calcular el promedio de todos los días en una cadena, necesitamos el número total de días y el saldo "integral":
SELECT client , min(dt) chain_dt , sum(days * balance) balance , sum(days) days FROM ... GROUP BY 1, grpid ORDER BY 1, grpid;
client | chain_dt | balance | days ------------------------------------- | 2020-01-01 | 650.00 | 4 | 2020-01-06 | 100.00 | 2 | 2020-01-10 | 10.00 | 2 | 2020-01-01 | 1200.00 | 8
Paso 6: buscando máximos aplicados
Usando
DISTINCT ON, dejaremos un único registro (con un valor máximo de días) para cada cliente:
SELECT DISTINCT ON(client) * FROM ... ORDER BY client, days DESC;
client | chain_dt | balance | days ------------------------------------- | 2020-01-01 | 650.00 | 4 | 2020-01-01 | 1200.00 | 8
En realidad, eso es todo, todo lo que queda es ...
Combinamos y optimizamos
Solicitud de resumen WITH step123 AS ( SELECT * , CASE WHEN cond THEN lag(cond) OVER(w) IS DISTINCT FROM cond END chain_start , CASE WHEN cond THEN coalesce(lead(dt) OVER(w), '2020-01-12') - dt END days FROM tbl , LATERAL(SELECT balance > 0 cond) T WINDOW w AS (PARTITION BY client ORDER BY dt) ) , step4 AS ( SELECT * , count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid FROM step123 WHERE cond ) SELECT DISTINCT ON(client) client , sum(days) OVER(w) days , min(dt) OVER(w) chain_dt , sum(days * balance) OVER(w) balance FROM step4 WINDOW w AS (PARTITION BY client, grpid) ORDER BY 1, 2 DESC;
Aquí combinamos y optimizamos los primeros tres pasos:
- La subconsulta LATERAL nos permitió calcular un campo adicional sin pasar innecesariamente por la selección e inmediatamente usarlo en la función
- la eliminación de una definición general en WINDOW ayuda a PostgreSQL a no hacer una doble clasificación para formar una "ventana" y calcular ambas funciones en un nodo WindowAgg
- El cálculo de la función "perezosa" en CASE reduce el número de operaciones realizadas
Del mismo modo, combinamos los siguientes dos pasos. Pero el orden de la "ventana" de cálculo de agregados (cliente, grpid) y de unificación (cliente, suma (días)) no coincidió, por lo tanto, todavía hay dos nodos Ordenar en el último bloque: antes de WindowAgg y antes de Unique.
[mira explicar.tensor.ru]Observo que al numerar cadenas
, la condición WHERE se cumple por primera vez , por lo que los números generados por la
función de ventana resultan ser secuenciales.