-- 09. Estabilidad entre descargas (DEFINICIONES_PREVIAS D10). MISMO código mínimo sobre dos crudos de la PLACSP: -- abr = /abr/raw_preserved/PLACSP/licitaciones_espana.parquet (conservado el 25-abr-2026; datos hasta el 27-ene-2026) -- oct = /h5/licitaciones_completo__20261005T064313Z.parquet (instantánea del 5-oct-2026, la que leyó P1b) -- Código mínimo (idéntico en los dos): conjunto 'menores'; una fila por id (la de fecha_updated más reciente); fecha de -- adjudicación 2019-2024; importe_adjudicacion (sin IVA) > 0; adjudicatario con NIF que no tiene forma de DNI/NIE -- (fuera personas físicas por forma) y no nulo; órgano = organo_clave de la identidad de P1b a través de un DICCIONARIO -- clave cruda del órgano (dir3 | nif | nombre, mayúsculas y sin espacios en los bordes) -> organo_clave, construido con las -- filas de la población de P1b (pm) casadas por el número datosAbiertosMenores/N; el mismo diccionario para los dos crudos. -- Empresa = NIF del adjudicatario. Clase obras/resto; umbral 40.000 / 15.000 € (50.000 € en servicios y suministros de -- órganos de tipo 'investigacion'). Indicadores: M1e y M1r (% del importe, reglas de P3), M2 (100 €), M6 (% con 1 oferta), -- C1 (% con fecha_publicacion − fecha_adjudicacion > 90 días). Elegibles: >= 20 (como P3). Decil superior: percentil en su -- tipo y año >= 90. Coincidencia = |decil oct ∩ decil abr| / |decil oct| entre órgano-años elegibles en los dos. -- Sin dudosos, DA 9.ª, vínculos ni publicaciones dobles: es un código mínimo para comparar descargas, no la población común. CREATE OR REPLACE TABLE crudo AS WITH a AS ( SELECT 'abr' d, id, nif_organo, dir3_organo, organo_contratante, nif_adjudicatario, tipo_contrato, objeto, importe_adjudicacion, num_ofertas::DOUBLE num_ofertas, fecha_adjudicacion::DATE fecha_adj, fecha_publicacion::DATE fecha_pub, fecha_updated, archivo_origen FROM read_parquet('/abr/raw_preserved/PLACSP/licitaciones_espana.parquet') WHERE conjunto = 'menores' UNION ALL SELECT 'oct', id, nif_organo, dir3_organo, organo_contratante, nif_adjudicatario, tipo_contrato, objeto, importe_adjudicacion, num_ofertas::DOUBLE, fecha_adjudicacion::DATE, fecha_publicacion::DATE, fecha_updated, archivo_origen FROM read_parquet('/h5/licitaciones_completo__20261005T064313Z.parquet') WHERE conjunto = 'menores'), -- POSTERIOR (determinismo, 00d): varias filas de un id comparten la fecha_updated máxima (el mismo registro leído de ZIP -- mensuales y anuales; en 7.163 ids de abr con contenido distinto). Desempate fijo e igual en los dos crudos. u1 AS (SELECT *, row_number() OVER (PARTITION BY d, id ORDER BY fecha_updated DESC NULLS LAST, archivo_origen DESC NULLS LAST, fecha_adj DESC NULLS LAST, importe_adjudicacion DESC NULLS LAST, nif_adjudicatario NULLS LAST, tipo_contrato NULLS LAST, organo_contratante NULLS LAST, dir3_organo NULLS LAST, nif_organo NULLS LAST, objeto NULLS LAST, num_ofertas NULLS LAST, fecha_pub DESC NULLS LAST) rk FROM a) SELECT d, id, regexp_extract(id, 'datosAbiertosMenores/[0-9]+') clave_id, coalesce(upper(trim(dir3_organo)), '') || '|' || coalesce(upper(trim(nif_organo)), '') || '|' || coalesce(upper(trim(organo_contratante)), '') org_raw, upper(trim(nif_adjudicatario)) empresa, CASE WHEN tipo_contrato = 'Obras' THEN 'obras' ELSE 'resto' END clase, tipo_contrato, trim(regexp_replace(regexp_replace(lower(strip_accents(coalesce(objeto, ''))), '[^a-z ]', ' ', 'g'), '\s+', ' ', 'g')) objeto_norm, importe_adjudicacion base, num_ofertas, fecha_adj, fecha_pub, year(fecha_adj) anio FROM u1 WHERE rk = 1; -- @ tipo_contrato en cada crudo (menores 2019-2024): los literales deben coincidir SELECT d, tipo_contrato, count(*) n FROM crudo WHERE fecha_adj BETWEEN DATE '2019-01-01' AND DATE '2024-12-31' GROUP BY ALL ORDER BY 2, 1; -- Diccionario clave cruda -> organo_clave con las filas de pm (P1b) casadas por número CREATE OR REPLACE TABLE dic AS WITH p AS (SELECT regexp_extract(id_canonical, 'datosAbiertosMenores/[0-9]+') clave_id, organo_clave FROM pm), j AS (SELECT c.org_raw, p.organo_clave, count(*) n FROM crudo c JOIN p USING (clave_id) WHERE c.d = 'oct' GROUP BY ALL), r AS (SELECT *, row_number() OVER (PARTITION BY org_raw ORDER BY n DESC, organo_clave) rk, count(*) OVER (PARTITION BY org_raw) cands FROM j) SELECT org_raw, organo_clave, cands FROM r WHERE rk = 1; -- @ Diccionario: claves crudas, órganos y claves con más de un organo_clave candidato SELECT count(*) claves_crudas, count(DISTINCT organo_clave) organos, count(*) FILTER (WHERE cands > 1) claves_ambiguas FROM dic; CREATE OR REPLACE TABLE est AS SELECT c.*, k.organo_clave, coalesce(t.tipo, 'sin_tipo') tipo, CASE WHEN c.clase = 'obras' THEN 40000 WHEN t.tipo = 'investigacion' THEN 50000 ELSE 15000 END umbral FROM crudo c LEFT JOIN dic k USING (org_raw) LEFT JOIN otipo t USING (organo_clave) WHERE c.fecha_adj BETWEEN DATE '2019-01-01' AND DATE '2024-12-31' AND c.base > 0 AND c.empresa IS NOT NULL AND c.empresa <> '' AND NOT regexp_matches(c.empresa, '^[0-9XYZKLM][0-9]{7}[A-Z]$|^[0-9]{8}$'); -- @ Filas del código mínimo por descarga y año; % con organo_clave por el diccionario SELECT d, anio, count(*) menores, count(*) FILTER (WHERE organo_clave IS NOT NULL) con_organo, round(100.0 * count(*) FILTER (WHERE organo_clave IS NOT NULL) / count(*), 2) pct_con_organo, round(sum(base) / 1e6, 1) meur FROM est GROUP BY ALL ORDER BY 2, 1; -- @ Ids presentes en una descarga y no en la otra (código mínimo), por año WITH a AS (SELECT clave_id, anio FROM est WHERE d = 'abr'), o AS (SELECT clave_id, anio FROM est WHERE d = 'oct') SELECT coalesce(a.anio, o.anio) anio, count(*) FILTER (WHERE a.clave_id IS NOT NULL AND o.clave_id IS NOT NULL) en_las_dos, count(*) FILTER (WHERE o.clave_id IS NULL) solo_abr, count(*) FILTER (WHERE a.clave_id IS NULL) solo_oct FROM a FULL JOIN o USING (clave_id) GROUP BY 1 ORDER BY 1; CREATE OR REPLACE TABLE est_ok AS SELECT * FROM est WHERE organo_clave IS NOT NULL; -- M1e, M1r CREATE OR REPLACE TABLE est_m1 AS WITH dd AS (SELECT d, organo_clave, empresa, clase, fecha_adj FROM est_ok GROUP BY ALL HAVING count(*) >= 2 AND sum(base::DECIMAL(18,2)) > any_value(umbral)), w AS (SELECT d, id, organo_clave, empresa, clase, objeto_norm, fecha_adj, umbral, count(*) OVER v n12, sum(base::DECIMAL(18,2)) OVER v s12 FROM est_ok WHERE length(objeto_norm) >= 5 WINDOW v AS (PARTITION BY d, organo_clave, empresa, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN INTERVAL 364 DAYS PRECEDING AND CURRENT ROW)), r AS (SELECT d, id, bool_or(n12 >= 2 AND s12 > umbral) OVER (PARTITION BY d, organo_clave, empresa, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN CURRENT ROW AND INTERVAL 364 DAYS FOLLOWING) en_m1r FROM w) SELECT e.d, e.id, (dd.organo_clave IS NOT NULL) en_m1e, coalesce(r.en_m1r, FALSE) en_m1r FROM est_ok e LEFT JOIN dd ON dd.d = e.d AND dd.organo_clave = e.organo_clave AND dd.empresa = e.empresa AND dd.clase = e.clase AND dd.fecha_adj = e.fecha_adj LEFT JOIN r ON r.d = e.d AND r.id = e.id; CREATE OR REPLACE TABLE est_oa AS SELECT e.d, e.organo_clave, e.anio, any_value(e.tipo) tipo, count(*) menores, sum(e.base) imp, coalesce(sum(e.base) FILTER (WHERE m.en_m1e), 0) m1e_imp, coalesce(sum(e.base) FILTER (WHERE m.en_m1r), 0) m1r_imp, count(*) FILTER (WHERE e.tipo_contrato IN ('Servicios', 'Suministros') AND e.base >= (CASE WHEN e.umbral = 50000 THEN 20000 ELSE 5000 END) AND e.base < e.umbral) m2_n, count(*) FILTER (WHERE e.tipo_contrato IN ('Servicios', 'Suministros') AND e.base >= e.umbral - 100 AND e.base < e.umbral) m2_b, count(e.num_ofertas) m6_n, count(*) FILTER (WHERE e.num_ofertas = 1) m6_1, count(*) FILTER (WHERE e.fecha_pub IS NOT NULL) c1_n, count(*) FILTER (WHERE e.fecha_pub - e.fecha_adj > 90) c1_90 FROM est_ok e JOIN est_m1 m ON m.d = e.d AND m.id = e.id GROUP BY e.d, e.organo_clave, e.anio; CREATE OR REPLACE TABLE est_l AS WITH l AS ( SELECT d, organo_clave, anio, tipo, 'm1e' ind, round(100.0 * m1e_imp / imp, 6) val, menores >= 20 el FROM est_oa UNION ALL SELECT d, organo_clave, anio, tipo, 'm1r', round(100.0 * m1r_imp / imp, 6), menores >= 20 FROM est_oa UNION ALL SELECT d, organo_clave, anio, tipo, 'm2', 100.0 * m2_b / m2_n, m2_n >= 20 FROM est_oa WHERE m2_n > 0 UNION ALL SELECT d, organo_clave, anio, tipo, 'm6', 100.0 * m6_1 / m6_n, m6_n >= 20 FROM est_oa WHERE m6_n > 0 UNION ALL SELECT d, organo_clave, anio, tipo, 'c1', 100.0 * c1_90 / c1_n, c1_n >= 20 FROM est_oa WHERE c1_n > 0) SELECT *, 100 * percent_rank() OVER (PARTITION BY d, ind, tipo, anio ORDER BY val) pct FROM l WHERE el; COPY (SELECT * FROM est_l ORDER BY d, ind, anio, organo_clave) TO '/w/salidas/09_estabilidad_organo_anio.parquet' (FORMAT PARQUET); -- @ Estabilidad: coincidencia del decil superior entre descargas (elegibles en las dos), 2019-2024 en conjunto y por año; pasa si >= 90 % WITH j AS (SELECT o.ind, o.anio, (o.pct >= 90) top_o, (a.pct >= 90) top_a FROM est_l o JOIN est_l a ON a.d = 'abr' AND a.ind = o.ind AND a.organo_clave = o.organo_clave AND a.anio = o.anio WHERE o.d = 'oct'), y AS (SELECT ind, anio, count(*) n, count(*) FILTER (WHERE top_o) n_o, count(*) FILTER (WHERE top_a) na, count(*) FILTER (WHERE top_o AND top_a) nab FROM j GROUP BY ALL) SELECT ind, sum(n) elegibles_en_las_dos, sum(n_o) decil_oct, sum(na) decil_abr, sum(nab) coinciden, round(100.0 * sum(nab) / sum(n_o), 1) pct_coincidencia_sobre_oct, round(100.0 * sum(nab) / sum(na), 1) pct_sobre_abr, round(min(100.0 * nab / nullif(n_o, 0)), 1) peor_anio_pct, arg_min(anio, 100.0 * nab / nullif(n_o, 0)) peor_anio, (100.0 * sum(nab) / sum(n_o) >= 90) pasa_90 FROM y GROUP BY 1 ORDER BY 1; -- @ Estabilidad por año (detalle) WITH j AS (SELECT o.ind, o.anio, (o.pct >= 90) top_o, (a.pct >= 90) top_a FROM est_l o JOIN est_l a ON a.d = 'abr' AND a.ind = o.ind AND a.organo_clave = o.organo_clave AND a.anio = o.anio WHERE o.d = 'oct') SELECT ind, anio, count(*) n, count(*) FILTER (WHERE top_o) decil_oct, count(*) FILTER (WHERE top_o AND top_a) coinciden, round(100.0 * count(*) FILTER (WHERE top_o AND top_a) / nullif(count(*) FILTER (WHERE top_o), 0), 1) pct FROM j GROUP BY ALL ORDER BY 1, 2; -- @ Control: el mismo código mínimo frente a la tabla de P6 (variante 'sin'), decil superior 2019-2024, descarga de oct WITH j AS (SELECT o.ind, (o.pct >= 90) top_o, (p.pct_tipo >= 90) top_p FROM est_l o JOIN oal p ON p.var = 'sin' AND p.indicador = o.ind AND p.organo_clave = o.organo_clave AND p.anio = o.anio AND p.elegible WHERE o.d = 'oct') SELECT ind, count(*) n, count(*) FILTER (WHERE top_p) decil_p6, count(*) FILTER (WHERE top_p AND top_o) coinciden, round(100.0 * count(*) FILTER (WHERE top_p AND top_o) / nullif(count(*) FILTER (WHERE top_p), 0), 1) pct FROM j GROUP BY 1 ORDER BY 1;