Estoy tratando de escribir una consulta en sql donde necesito encontrar el número máximo. de meses consecutivos durante un período de los últimos 12 meses excluyendo junio y julio.
entonces, por ejemplo, tengo una tabla inicial de la siguiente manera
+---------+--------------+-----------+------------+ | id | Payment | amount | Date | +---------+--------------+-----------+------------+ | 1 | CJ1 | 70000 | 11/3/2020 | | 1 | 1B4 | 36314000 | 12/1/2020 | | 1 | I21 | 119439000 | 1/12/2021 | | 1 | 0QO | 9362100 | 2/2/2021 | | 1 | 1G0 | 140431000 | 2/23/2021 | | 1 | 1G | 9362100 | 3/2/2021 | | 1 | g5d | 9362100 | 4/6/2021 | | 1 | rt5s | 13182500 | 4/13/2021 | | 1 | fgs5 | 48598 | 5/18/2021 | | 1 | sd8 | 42155 | 5/25/2021 | | 1 | wqe8 | 47822355 | 7/20/2021 | | 1 | cbg8 | 4589721 | 7/27/2021 | | 1 | jlk8 | 4589721 | 8/3/2021 | | 1 | cxn9 | 4589721 | 10/5/2021 | | 1 | qwe | 45897210 | 11/9/2021 | | 1 | mmm | 45897210 | 12/16/2021 | +---------+--------------+-----------+------------+He escrito debajo de la consulta:
SELECT customer_number, year, month, payment_month - lag(payment_month) OVER(partition by customer_number ORDER BY year, month) as previous_month_indicator, FROM ( SELECT DISTINCT Month(date) as month, Year(date) as year, CUSTOMER_NUMBER FROM Table1 WHERE Month(date) not in (6,7) and TO_DATE(date,'yyyy-MM-dd') >= DATE_SUB('2021-12-31', 425) and customer_number = 1 ) As Cy obtengo esta salida
+-----------------+------+-------+--------------------------+ | customer_number | year | month | previous_month_indicator | +-----------------+------+-------+--------------------------+ | 1 | 2020 | 11 | null | | 1 | 2020 | 12 | 1 | | 1 | 2021 | 1 | -11 | | 1 | 2021 | 2 | 1 | | 1 | 2021 | 3 | 1 | | 1 | 2021 | 4 | 1 | | 1 | 2021 | 5 | 1 | | 1 | 2021 | 8 | 3 | | 1 | 2021 | 10 | 2 | | 1 | 2021 | 11 | 1 | +-----------------+------+-------+--------------------------+Lo que quiero es obtener una vista como esta Salida esperada
+-----------------+------+-------+--------------------------+ | customer_number | year | month | previous_month_indicator | +-----------------+------+-------+--------------------------+ | 1 | 2020 | 11 | 1 | | 1 | 2020 | 12 | 1 | | 1 | 2021 | 1 | 1 | | 1 | 2021 | 2 | 1 | | 1 | 2021 | 3 | 1 | | 1 | 2021 | 4 | 1 | | 1 | 2021 | 5 | 1 | | 1 | 2021 | 8 | 1 | | 1 | 2021 | 9 | 0 | | 1 | 2021 | 10 | 1 | | 1 | 2021 | 11 | 1 | +-----------------+------+-------+--------------------------+Como junio/julio no importa, después de mayo se debe considerar agosto como mes consecutivo, y como en septiembre no hubo registro aparece como 0 y rompe la cadena de meses consecutivos.
Mi resultado final deseado es obtener el número máximo de meses consecutivos en los que se realizaron transacciones, que en el caso anterior es 8 desde noviembre de 2020 hasta agosto de 2021
Salida final deseada:
+-----------------+-------------------------+ | customer_number | Max_consecutive_months | +-----------------+-------------------------+ | 1 | 8 | +-----------------+-------------------------+Los CTE pueden desglosar esto un poco más fácilmente. En el código a continuación, el CTE de payment_streak es el bit clave; el campo start_of_streak marca primero las filas que cuentan como el inicio de una racha y luego toma el máximo de todas las filas anteriores (para encontrar el inicio de esta racha).
El último SELECT solo compara estas dos fechas, calcula cuántos meses hay entre ellas (excluyendo junio/julio) y luego encuentra la mejor racha por cliente.
WITH payments_in_context AS ( SELECT customer_number, date, lag(date) OVER (PARTITION BY customer_number ORDER BY date) AS prev_date FROM Table1 WHERE EXTRACT(month FROM date) NOT IN (6,7) ), payment_streak AS ( SELECT customer_number, date, max( CASE WHEN (prev_date IS NULL) OR (EXTRACT(month FROM date) <> 8 AND (date - prev_date >= 62 OR MOD(12 + EXTRACT(month FROM date) - EXTRACT(month FROM prev_date),12)) > 1)) OR (EXTRACT(month FROM date) = 8 AND (date - prev_date >= 123 OR EXTRACT(month FROM prev_date) NOT IN (5,8))) THEN date END ) OVER (PARTITION BY customer_number ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as start_of_streak FROM payments_in_context ) SELECT customer_number, max( 1 + 10*(EXTRACT(year FROM date) - EXTRACT(year FROM start_of_streak)) + (EXTRACT(month FROM date) - EXTRACT(month FROM start_of_streak)) + CASE WHEN (EXTRACT(month FROM date) > 7 AND EXTRACT(month FROM start_of_streak) < 6) THEN -2 WHEN (EXTRACT(month FROM date) < 6 AND EXTRACT(month FROM start_of_streak) > 7) THEN 2 ELSE 0 END ) AS max_consecutive_months FROM payment_streak GROUP BY 1;Puede usar un cte recursivo para generar todas las fechas en el período de doce meses para cada id de cliente y luego encontrar la cantidad máxima de fechas consecutivas, excluyendo junio y julio, en ese intervalo:
with recursive cte(id, m, c) as ( select cust_id, min(date), 1 from payments group by cust_id union all select c.id, cm + interval 1 month, c.c+1 from cte c where cc <= 12 ), dts(id, m, f) as ( select c.id, cm, cc = 1 or exists (select 1 from payments p where p.cust_id = c.id and extract(month from p.date) = extract(month from (cm - interval 1 month)) and extract(year from p.date) = extract(year from (cm - interval 1 month))) from cte c where extract(month from cm) not in (6,7) ), result(id, f, c) as ( select d.id, df, (select sum(d.id = d1.id and d1.m < dm and d1.f = 0)+1 from dts d1) from dts d where df != 0 ) select r1.id, max(r1.s)-1 from (select r.id, rc, sum(rf) s from result r group by r.id, rc) r1 group by r1.id