rmorenobello · October 20, 2025 14:12
diff --git a/Oracle - 1er evento tipo B tras evento tipo A.sql b/Oracle - 1er evento tipo B tras evento tipo A.sql
 /* Optimizada con analytic functions (un pase por analytic function como mucho, no consulta anidada fila a fila: */
 WITH RankedOperations AS (
    SELECT
        ID,
        ASS_ID,
        FECHA,
        TIPO,
        -- Asignamos un número de secuencia a cada operación dentro de cada ciudadano
        ROW_NUMBER() OVER (PARTITION BY ASS_ID ORDER BY FECHA, ID) AS rn
    FROM
        HASS
 ),
 OperationA AS (
    -- Identificamos todas las operaciones de tipo 'A' y les asignamos un rango
    SELECT
        ID,
        ASS_ID,
        FECHA,
        TIPO,
        rn,
        -- Un rank que se incrementa cada vez que encontramos un 'A'
        SUM(CASE WHEN TIPO = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY ASS_ID ORDER BY FECHA, ID) AS group_a_rank
    FROM
        RankedOperations
 ),
 OperationB AS (
    -- Identificamos la primera operación de tipo 'B' que sigue a cada 'A'
    SELECT
        op.ASS_ID,
        op.FECHA AS FECHA_B,
        opA.FECHA AS FECHA_A_Previa, -- Para referencia, si se necesita ver la fecha del 'A'
        opA.group_a_rank,
        -- Usamos ROW_NUMBER para encontrar la PRIMERA 'B' después de un 'A'
        ROW_NUMBER() OVER (PARTITION BY op.ASS_ID, opA.group_a_rank ORDER BY op.FECHA, op.ID) AS rn_b_after_a
    FROM
        RankedOperations op
    JOIN
        OperationA opA ON op.ASS_ID = opA.ASS_ID
                       AND op.rn > opA.rn -- Aseguramos que 'B' ocurra después de 'A'
    WHERE
        op.TIPO = 'B'
        AND opA.TIPO = 'A'
 )
 SELECT
    ASS_ID,
    MIN(FECHA_B) AS Primera_Fecha_B_Tras_A
 FROM
    OperationB
 WHERE
    rn_b_after_a = 1 -- Seleccionamos solo la primera 'B' después de cada 'A'
 GROUP BY
    ASS_ID,
    group_a_rank -- Agrupamos por ciudadano y por el "grupo" de operaciones tras cada 'A' (QUITARLO SI QUEREMOS SOLO LA FECHA DEL CASO MÁS ANTIGUO O MÁS RECIENTE)
 HAVING MIN(FECHA_B) IS NOT NULL
 ORDER BY
    ASS_ID,
    Primera_Fecha_B_Tras_A;

 ------------------------------------------------------------------------------------------
 -- mucho menos optimo, consulta anidada fila a fila:
 SELECT a.ass_id,
       (
         SELECT b.fecha
         FROM hass b
         WHERE b.ass_id = a.ass_id
           AND b.tipo = 'B'
           AND b.fecha > a.fecha
         ORDER BY b.fecha
         FETCH FIRST 1 ROW ONLY
       ) AS fecha_b_despues_a
 FROM (
       SELECT ass_id, MAX(fecha) AS fecha
       FROM hass
       WHERE tipo = 'A'
       GROUP BY ass_id
     ) a;
     
 /*
 Índices recomendados

 Los índices correctos son clave.
 Debes crear índices compuestos que cubran las búsquedas principales del plan de ejecución.

 Índice 1 – para encontrar la última A:
 CREATE INDEX idx_hass_a ON hass (tipo, ass_id, fecha DESC);


 👉 Esto permite que Oracle obtenga la MAX(fecha) de las filas tipo 'A' sin ordenar toda la tabla.

 Índice 2 – para encontrar la primera B posterior:
 CREATE INDEX idx_hass_b ON hass (ass_id, tipo, fecha);


 👉 Permite buscar rápidamente la primera ‘B’ con ass_id y fecha > X.

 🔹 Estos dos índices reducirán los full scans drásticamente.
 */
	/* Optimizada con analytic functions (un pase por analytic function como mucho, no consulta anidada fila a fila: */
	WITH RankedOperations AS (
	SELECT
	ID,
	ASS_ID,
	FECHA,
	TIPO,
	-- Asignamos un número de secuencia a cada operación dentro de cada ciudadano
	ROW_NUMBER() OVER (PARTITION BY ASS_ID ORDER BY FECHA, ID) AS rn
	FROM
	HASS
	),
	OperationA AS (
	-- Identificamos todas las operaciones de tipo 'A' y les asignamos un rango
	SELECT
	ID,
	ASS_ID,
	FECHA,
	TIPO,
	rn,
	-- Un rank que se incrementa cada vez que encontramos un 'A'
	SUM(CASE WHEN TIPO = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY ASS_ID ORDER BY FECHA, ID) AS group_a_rank
	FROM
	RankedOperations
	),
	OperationB AS (
	-- Identificamos la primera operación de tipo 'B' que sigue a cada 'A'
	SELECT
	op.ASS_ID,
	op.FECHA AS FECHA_B,
	opA.FECHA AS FECHA_A_Previa, -- Para referencia, si se necesita ver la fecha del 'A'
	opA.group_a_rank,
	-- Usamos ROW_NUMBER para encontrar la PRIMERA 'B' después de un 'A'
	ROW_NUMBER() OVER (PARTITION BY op.ASS_ID, opA.group_a_rank ORDER BY op.FECHA, op.ID) AS rn_b_after_a
	FROM
	RankedOperations op
	JOIN
	OperationA opA ON op.ASS_ID = opA.ASS_ID
	AND op.rn > opA.rn -- Aseguramos que 'B' ocurra después de 'A'
	WHERE
	op.TIPO = 'B'
	AND opA.TIPO = 'A'
	)
	SELECT
	ASS_ID,
	MIN(FECHA_B) AS Primera_Fecha_B_Tras_A
	FROM
	OperationB
	WHERE
	rn_b_after_a = 1 -- Seleccionamos solo la primera 'B' después de cada 'A'
	GROUP BY
	ASS_ID,
	group_a_rank -- Agrupamos por ciudadano y por el "grupo" de operaciones tras cada 'A' (QUITARLO SI QUEREMOS SOLO LA FECHA DEL CASO MÁS ANTIGUO O MÁS RECIENTE)
	HAVING MIN(FECHA_B) IS NOT NULL
	ORDER BY
	ASS_ID,
	Primera_Fecha_B_Tras_A;

	------------------------------------------------------------------------------------------
	-- mucho menos optimo, consulta anidada fila a fila:
	SELECT a.ass_id,
	(
	SELECT b.fecha
	FROM hass b
	WHERE b.ass_id = a.ass_id
	AND b.tipo = 'B'
	AND b.fecha > a.fecha
	ORDER BY b.fecha
	FETCH FIRST 1 ROW ONLY
	) AS fecha_b_despues_a
	FROM (
	SELECT ass_id, MAX(fecha) AS fecha
	FROM hass
	WHERE tipo = 'A'
	GROUP BY ass_id
	) a;

	/*
	Índices recomendados

	Los índices correctos son clave.
	Debes crear índices compuestos que cubran las búsquedas principales del plan de ejecución.

	Índice 1 – para encontrar la última A:
	CREATE INDEX idx_hass_a ON hass (tipo, ass_id, fecha DESC);


	👉 Esto permite que Oracle obtenga la MAX(fecha) de las filas tipo 'A' sin ordenar toda la tabla.

	Índice 2 – para encontrar la primera B posterior:
	CREATE INDEX idx_hass_b ON hass (ass_id, tipo, fecha);


	👉 Permite buscar rápidamente la primera ‘B’ con ass_id y fecha > X.

	🔹 Estos dos índices reducirán los full scans drásticamente.
	*/
No results found