-- impl2 · 08 · Salidas: resumen.csv, resumen_e4.csv, series.parquet, hechos_series.parquet y cifras para IMPL2.md -- Órganos que publican como NIF el de una persona (la regla del job, ORGANO_DE_PERSONA): clave seudónima en las salidas CREATE OR REPLACE MACRO clave_pub(k) AS CASE WHEN regexp_matches(k, '^([0-9]{8}[A-Z]|[XYZKLM][0-9]{7}[A-Z])(\||$)') THEN 'PF:' || substr(md5(k), 1, 16) ELSE k END; -- @ claves con forma de persona en las unidades (órgano / empresa) SELECT count(*) FILTER (WHERE clave_pub(organo_clave) <> organo_clave) organos_pf, count(*) FILTER (WHERE regexp_matches(empresa_clave, '^([0-9]{7,8}[A-Z]?|[XYZKLM][0-9]{7}[A-Z])(\||$)')) empresas_forma_dni FROM uni_x; CREATE OR REPLACE TABLE series_out AS SELECT x.fuente, clave_pub(x.organo_clave) AS organo_clave, x.empresa_clave, x.clase, x.objeto_norm, x.fecha_ini, x.fecha_fin, x.n, x.suma, x.marcada, CASE WHEN r.qid IS NOT NULL THEN r.pasa_a OR r.pasa_b END AS pasa, CASE WHEN r.pasa_a AND r.pasa_b THEN 'ambas' WHEN r.pasa_a THEN 'jw_cpv' WHEN r.pasa_b THEN 'tokens' END AS rama, r.n_licitaciones_casadas::BIGINT AS n_licitaciones_casadas FROM uni_x x LEFT JOIN res r ON r.qid = x.uid; COPY (SELECT * FROM series_out ORDER BY fuente, organo_clave, empresa_clave, clase, objeto_norm, fecha_ini) TO '/w/salidas/series.parquet' (FORMAT PARQUET, COMPRESSION ZSTD); CREATE OR REPLACE TABLE resumen AS SELECT fuente, t AS anio, count(*) FILTER (WHERE tipo = 'serie') AS series, count(*) FILTER (WHERE tipo = 'serie' AND (pasa_a OR pasa_b)) AS series_pasaron, count(*) FILTER (WHERE tipo = 'control') AS control, count(*) FILTER (WHERE tipo = 'control' AND (pasa_a OR pasa_b)) AS control_pasaron FROM res WHERE tipo IN ('serie', 'control') GROUP BY 1, 2; COPY (SELECT * FROM resumen ORDER BY fuente, anio) TO '/w/salidas/resumen.csv' (HEADER, DELIMITER ','); CREATE OR REPLACE TABLE resumen_e4 AS SELECT fuente, t AS anio, count(*) FILTER (WHERE tipo = 'serie') AS series, count(*) FILTER (WHERE tipo = 'serie' AND pasa_e4) AS series_pasaron, count(*) FILTER (WHERE tipo = 'control') AS control, count(*) FILTER (WHERE tipo = 'control' AND pasa_e4) AS control_pasaron FROM res4 GROUP BY 1, 2; COPY (SELECT * FROM resumen_e4 ORDER BY fuente, anio) TO '/w/salidas/resumen_e4.csv' (HEADER, DELIMITER ','); -- Hechos: cada pareja serie marcada → licitación casada (sin adjudicatarios) COPY ( SELECT x.fuente AS fuente_serie, clave_pub(x.organo_clave) AS organo_clave, x.empresa_clave, x.clase, x.objeto_norm AS objeto_serie, x.fecha_ini, x.fecha_fin, x.anio_fin AS t, p.source AS fuente_licitacion, p.lic_key, CASE WHEN p.por_a AND p.por_b THEN 'ambas' WHEN p.por_a THEN 'jw_cpv' ELSE 'tokens' END AS rama, l.procedimiento, l.anio_ref, l.fecha_pub, l.expediente, l.objeto, l.imp_lic_eur, l.url_origen FROM par p JOIN uni_x x ON x.uid = p.qid JOIN (SELECT source, lic_key, min(procedimiento) procedimiento, min(anio_ref) anio_ref, min(fecha_pub) fecha_pub, min(expediente) expediente, min(objeto) objeto, max(imp_lic_eur) imp_lic_eur, min(url_origen) url_origen FROM lic GROUP BY 1, 2) l ON l.source = p.source AND l.lic_key = p.lic_key WHERE x.marcada ORDER BY 1, 2, 3, 4, 5, 6, 9, 10) TO '/w/salidas/hechos_series.parquet' (FORMAT PARQUET, COMPRESSION ZSTD); -- @ resumen (etiqueta principal) por fuente y año SELECT * FROM resumen ORDER BY fuente, anio; -- @ resumen E4 SELECT * FROM resumen_e4 ORDER BY fuente, anio; -- @ lift agrupado 2019-2024 por fuente con IC 95 % (Katz), etiqueta principal y E4 WITH g AS (SELECT 'principal' etiqueta, fuente, sum(series) n1, sum(series_pasaron) a, sum(control) n0, sum(control_pasaron) c FROM resumen GROUP BY 1, 2 UNION ALL SELECT 'E4', fuente, sum(series), sum(series_pasaron), sum(control), sum(control_pasaron) FROM resumen_e4 GROUP BY 1, 2) SELECT etiqueta, fuente, n1 series, a pasaron, round(100.0 * a / n1, 2) tasa_m1r, n0 control, c control_pasaron, round(100.0 * c / n0, 2) tasa_control, round((a / n1) / (c / n0), 3) lift, CASE WHEN a > 0 AND c > 0 THEN round(exp(ln((a / n1) / (c / n0)) - 1.96 * sqrt(1.0 / a - 1.0 / n1 + 1.0 / c - 1.0 / n0)), 3) END ic_inf, CASE WHEN a > 0 AND c > 0 THEN round(exp(ln((a / n1) / (c / n0)) + 1.96 * sqrt(1.0 / a - 1.0 / n1 + 1.0 / c - 1.0 / n0)), 3) END ic_sup FROM g WHERE n1 > 0 AND n0 > 0 AND c > 0 ORDER BY etiqueta DESC, fuente; -- @ lift por fuente y año (etiqueta principal) SELECT fuente, anio, series, series_pasaron, control, control_pasaron, CASE WHEN series > 0 AND control_pasaron > 0 THEN round((series_pasaron / series) / (control_pasaron / control), 3) END lift, (series >= 100 AND control >= 100) AS evaluable FROM resumen ORDER BY fuente, anio; -- @ rama de las series marcadas que pasaron (t 2019-2024) SELECT fuente, count(*) FILTER (WHERE rama = 'ambas') ambas, count(*) FILTER (WHERE rama = 'jw_cpv') jw_cpv, count(*) FILTER (WHERE rama = 'tokens') tokens, count(*) FILTER (WHERE pasa) pasaron FROM series_out WHERE marcada AND year(fecha_fin) BETWEEN 2019 AND 2024 GROUP BY 1 ORDER BY 1; -- @ series que pasaron con alguna pareja casada por las dos ramas a la vez SELECT q.fuente, count(*) FILTER (WHERE alguna_pareja_ambas) pareja_ambas, count(*) FILTER (WHERE pasa_a AND pasa_b) serie_ambas FROM res q WHERE tipo = 'serie' GROUP BY 1 ORDER BY 1; -- @ regla de respaldo de la E4: % de series marcadas (t 2019-2024) con su órgano entre los que tienen alguna licitación 2019-2025 WITH o AS (SELECT DISTINCT organo_clave FROM lic) SELECT x.fuente, count(*) series, count(o.organo_clave) con_licitaciones, round(100.0 * count(o.organo_clave) / count(*), 2) pct, (100.0 * count(o.organo_clave) / count(*) < 50) AS va_por_e4 FROM uni_x x LEFT JOIN o USING (organo_clave) WHERE x.marcada AND x.anio_fin BETWEEN 2019 AND 2024 GROUP BY 1 ORDER BY 1; -- @ control de referencia de Fable (PLACSP, pares órgano-empresa-clase-objeto-año natural con 2 menores o más, t 2019-2024) SELECT q.tipo, count(*) pares, round(100.0 * count(e.objeto_norm) / count(*), 2) pct_e1_jw085, round(100.0 * count(*) FILTER (WHERE r.pasa_a OR r.pasa_b) / count(*), 2) pct_etiqueta_principal FROM q JOIN res r USING (qid) LEFT JOIN pe1 e ON e.organo_clave = q.organo_clave AND e.t = q.t AND e.objeto_norm = q.objeto_norm WHERE q.tipo LIKE 'fable%' GROUP BY 1 ORDER BY 1; -- @ E1 (solo JW) en las series y cadenas de la PLACSP, por tipo (sensibilidad) SELECT q.tipo, count(*) n, round(100.0 * count(e.objeto_norm) / count(*), 2) pct_e1 FROM q LEFT JOIN pe1 e ON e.organo_clave = q.organo_clave AND e.t = q.t AND e.objeto_norm = q.objeto_norm WHERE q.tipo IN ('serie', 'control') AND q.fuente = 'PLACSP' GROUP BY 1 ORDER BY 1; -- @ fuentes de las licitaciones casadas con las series marcadas (parejas) SELECT x.fuente fuente_serie, p.source fuente_licitacion, count(*) parejas FROM par p JOIN uni_x x ON x.uid = p.qid WHERE x.marcada GROUP BY 1, 2 ORDER BY 1, 3 DESC; -- @ tamaño de salidas SELECT (SELECT count(*) FROM series_out) series_out, (SELECT count(*) FROM series_out WHERE pasa IS NULL) sin_etiqueta_2025;