-- 02. Familia M1 sobre `pobv` (01), contrato a contrato, en las tres variantes de población (columna `var`). -- Copia de p3/sql/02_m1.sql (guardada como sql/_p3_02_m1.sql) con `var` añadido a cada partición y sin las tablas de -- hechos. Definiciones idénticas a P3: -- M1a · un menor está en M1a si cae en alguna ventana de 365 días (fecha−364 → fecha) de su par órgano-empresa-clase con -- ≥ 2 menores y suma > umbral. -- M1r · igual con el par órgano-empresa-clase-objeto normalizado (≥ 5 caracteres). -- M1e · días (órgano, empresa, clase, fecha) con ≥ 2 menores cuya suma supera el umbral; cuentan solo los de ese día. -- Sumas en céntimos exactos (DECIMAL(18,2)), como P3. -- AÑADIDO DE P6 (robustez, D9), solo en la variante 'sin': M1a y M1r en AÑO NATURAL: el par (o par-objeto) tiene ≥ 2 -- menores en el año natural y su suma en el año supera el umbral; entonces todos sus menores de ese año están marcados. CREATE OR REPLACE TABLE m1a AS WITH w AS ( SELECT var, id_canonical, organo_clave, empresa_clave, clase, fecha_adj, base, umbral, count(*) OVER v AS n12, sum(base::DECIMAL(18,2)) OVER v AS s12 FROM pobv WINDOW v AS (PARTITION BY var, organo_clave, empresa_clave, clase ORDER BY fecha_adj RANGE BETWEEN INTERVAL 364 DAYS PRECEDING AND CURRENT ROW)), a AS (SELECT *, (n12 >= 2 AND s12 > umbral) AS activa FROM w) SELECT var, id_canonical, bool_or(activa) OVER (PARTITION BY var, organo_clave, empresa_clave, clase ORDER BY fecha_adj RANGE BETWEEN CURRENT ROW AND INTERVAL 364 DAYS FOLLOWING) AS en_m1a FROM a; ANALYZE m1a; CREATE OR REPLACE TABLE m1r AS WITH w AS ( SELECT var, id_canonical, organo_clave, empresa_clave, clase, objeto_norm, fecha_adj, base, umbral, count(*) OVER v AS n12, sum(base::DECIMAL(18,2)) OVER v AS s12 FROM pobv WHERE length(objeto_norm) >= 5 WINDOW v AS (PARTITION BY var, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN INTERVAL 364 DAYS PRECEDING AND CURRENT ROW)), a AS (SELECT *, (n12 >= 2 AND s12 > umbral) AS activa FROM w), m AS (SELECT *, bool_or(activa) OVER (PARTITION BY var, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN CURRENT ROW AND INTERVAL 364 DAYS FOLLOWING) AS en_m1r FROM a) SELECT var, id_canonical FROM m WHERE en_m1r; ANALYZE m1r; CREATE OR REPLACE TABLE m1e_dias AS SELECT var, organo_clave, empresa_clave, clase, fecha_adj, any_value(umbral) AS umbral, count(*) AS n_dia, sum(base::DECIMAL(18,2)) AS suma_dia, md5(var || '|' || organo_clave || '|' || empresa_clave || '|' || clase || '|' || fecha_adj::VARCHAR) AS dia_m1e FROM pobv GROUP BY var, organo_clave, empresa_clave, clase, fecha_adj HAVING count(*) >= 2 AND sum(base::DECIMAL(18,2)) > any_value(umbral); ANALYZE m1e_dias; -- Año natural (solo 'sin') CREATE OR REPLACE TABLE m1a_nat AS SELECT 'sin' AS var, p.id_canonical FROM pobv p JOIN ( SELECT organo_clave, empresa_clave, clase, anio FROM pobv WHERE var = 'sin' GROUP BY ALL HAVING count(*) >= 2 AND sum(base::DECIMAL(18,2)) > any_value(umbral)) g USING (organo_clave, empresa_clave, clase, anio) WHERE p.var = 'sin'; CREATE OR REPLACE TABLE m1r_nat AS SELECT 'sin' AS var, p.id_canonical FROM pobv p JOIN ( SELECT organo_clave, empresa_clave, clase, objeto_norm, anio FROM pobv WHERE var = 'sin' AND length(objeto_norm) >= 5 GROUP BY ALL HAVING count(*) >= 2 AND sum(base::DECIMAL(18,2)) > any_value(umbral)) g USING (organo_clave, empresa_clave, clase, objeto_norm, anio) WHERE p.var = 'sin'; -- Marcas por contrato CREATE OR REPLACE TABLE m1x AS SELECT p.var, p.id_canonical, p.organo_clave, p.empresa_clave, p.clase, p.anio, p.tipo, p.sector_aparte, p.base, p.fecha_adj, a.en_m1a, (r.id_canonical IS NOT NULL) AS en_m1r, (d.dia_m1e IS NOT NULL) AS en_m1e, d.dia_m1e, CASE WHEN p.var = 'sin' THEN (an.id_canonical IS NOT NULL) END AS en_m1a_nat, CASE WHEN p.var = 'sin' THEN (rn.id_canonical IS NOT NULL) END AS en_m1r_nat FROM pobv p JOIN m1a a ON a.var = p.var AND a.id_canonical = p.id_canonical LEFT JOIN m1r r ON r.var = p.var AND r.id_canonical = p.id_canonical LEFT JOIN m1e_dias d ON d.var = p.var AND d.organo_clave = p.organo_clave AND d.empresa_clave = p.empresa_clave AND d.clase = p.clase AND d.fecha_adj = p.fecha_adj -- (equi-join por var e id: una condición solo sobre p en el ON de un LEFT JOIN hacía que DuckDB 1.1.3 no usara hash join) LEFT JOIN m1a_nat an ON an.var = p.var AND an.id_canonical = p.id_canonical LEFT JOIN m1r_nat rn ON rn.var = p.var AND rn.id_canonical = p.id_canonical; ANALYZE m1x; -- @ Comprobaciones (todo 0): filas de m1x = pobv; M1e y M1r fuera de M1a; año natural fuera de 'sin' SELECT (SELECT count(*) FROM m1x) - (SELECT count(*) FROM pobv) filas_m1x_menos_pobv, (SELECT count(*) FROM m1x WHERE en_m1e AND NOT en_m1a) m1e_fuera_de_m1a, (SELECT count(*) FROM m1x WHERE en_m1r AND NOT en_m1a) m1r_fuera_de_m1a, (SELECT count(*) FROM m1x WHERE var <> 'sin' AND (en_m1a_nat IS NOT NULL OR en_m1r_nat IS NOT NULL)) nat_fuera_de_sin; -- @ ACEPTACIÓN frente a P3 · M1e y M1r por año y variante, principal (P3 2024: M1e 5.866, 2.271 días; M1r 4.203; P3 sin anulados M1e 2024: 5.724) SELECT var, anio, count(*) menores, count(*) FILTER (WHERE en_m1e) m1e_contratos, count(DISTINCT dia_m1e) m1e_dias, count(*) FILTER (WHERE en_m1r) m1r_contratos, count(*) FILTER (WHERE en_m1a) m1a_menores, round(100.0 * sum(base) FILTER (WHERE en_m1a) / sum(base), 1) m1a_pct_importe FROM m1x WHERE NOT sector_aparte GROUP BY ALL ORDER BY 1, 2; -- @ Año natural frente a 12 meses (variante 'sin', principal): contratos marcados SELECT anio, count(*) FILTER (WHERE en_m1a) m1a_12m, count(*) FILTER (WHERE en_m1a_nat) m1a_nat, count(*) FILTER (WHERE en_m1a AND en_m1a_nat) m1a_ambos, count(*) FILTER (WHERE en_m1r) m1r_12m, count(*) FILTER (WHERE en_m1r_nat) m1r_nat, count(*) FILTER (WHERE en_m1r AND en_m1r_nat) m1r_ambos FROM m1x WHERE var = 'sin' AND NOT sector_aparte GROUP BY 1 ORDER BY 1;