-- impl2 · 10 · Etiqueta E0b (DEFINICIONES_E0b.md, md5 03b12d5d…): E0 y además la «cabeza» de la serie (sus dos -- primeros tokens distintos en orden de aparición; uno si solo tiene uno) toda entre los tokens de la fila casada. -- Se aplica fila a fila a las dos ramas: una fila casa en E0b si casa en E0 (por (a) o por (b)) y contiene la cabeza. -- Sin tokens no hay cabeza y no casa. Tablas de 06_tokens.py (cab, tok) y de 07_etiqueta.sql (m_a, m_b, q, lic). -- Filas casadas en E0, con su rama, y el objeto de cada lado CREATE OR REPLACE TABLE e0_filas AS SELECT f.qid, f.source, f.lic_key, f.id_canonical, bool_or(f.por_a) AS por_a, bool_or(f.por_b) AS por_b FROM (SELECT qid, source, lic_key, id_canonical, TRUE AS por_a, FALSE AS por_b FROM m_a UNION ALL SELECT qid, source, lic_key, id_canonical, FALSE, TRUE FROM m_b) f GROUP BY f.qid, f.source, f.lic_key, f.id_canonical; ANALYZE e0_filas; CREATE OR REPLACE TABLE e0_obj AS SELECT e.*, q.objeto_norm AS s_obj, l.objeto_norm AS l_obj FROM e0_filas e JOIN q USING (qid) JOIN lic l ON l.source = e.source AND l.id_canonical = e.id_canonical; ANALYZE e0_obj; -- Cabeza contenida: por pareja de objetos distinta, cuántos tokens de la cabeza de la serie están en la fila CREATE OR REPLACE TABLE ncab AS SELECT objeto_norm, count(*) AS n_cab FROM cab GROUP BY 1; ANALYZE ncab; CREATE OR REPLACE TABLE cab_ok AS SELECT p.s_obj, p.l_obj FROM (SELECT DISTINCT s_obj, l_obj FROM e0_obj) p JOIN ncab n ON n.objeto_norm = p.s_obj JOIN cab c ON c.objeto_norm = p.s_obj JOIN tok t ON t.objeto_norm = p.l_obj AND t.token = c.token GROUP BY p.s_obj, p.l_obj, n.n_cab HAVING count(*) = n.n_cab; ANALYZE cab_ok; CREATE OR REPLACE TABLE par_b AS SELECT e.qid, e.source, e.lic_key, bool_or(e.por_a) AS por_a, bool_or(e.por_b) AS por_b FROM e0_obj e SEMI JOIN cab_ok k ON k.s_obj = e.s_obj AND k.l_obj = e.l_obj GROUP BY e.qid, e.source, e.lic_key; ANALYZE par_b; CREATE OR REPLACE TABLE res_b AS SELECT q.qid, q.tipo, q.fuente, q.t, coalesce(bool_or(p.por_a), FALSE) AS pasa_a, coalesce(bool_or(p.por_b), FALSE) AS pasa_b, count(p.lic_key) AS n_licitaciones_casadas FROM q LEFT JOIN par_b p USING (qid) WHERE q.tipo IN ('serie', 'control') GROUP BY q.qid, q.tipo, q.fuente, q.t; ANALYZE res_b; -- @ comprobación: E0b ⊆ E0 (series y cadenas que pasan E0b sin pasar E0; debe ser 0) SELECT count(*) FROM res_b b JOIN res r USING (qid) WHERE (b.pasa_a OR b.pasa_b) AND NOT (r.pasa_a OR r.pasa_b); -- @ series y cadenas sin cabeza (sin tokens) SELECT q.tipo, count(*) FROM q LEFT JOIN ncab n USING (objeto_norm) WHERE q.tipo IN ('serie', 'control') AND n.n_cab IS NULL GROUP BY 1 ORDER BY 1; -- Salidas CREATE OR REPLACE TABLE series_e0b 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_b r ON r.qid = x.uid; COPY (SELECT * FROM series_e0b ORDER BY fuente, organo_clave, empresa_clave, clase, objeto_norm, fecha_ini) TO '/w/salidas/series_e0b.parquet' (FORMAT PARQUET, COMPRESSION ZSTD); CREATE OR REPLACE TABLE resumen_e0b 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_b GROUP BY 1, 2; COPY (SELECT * FROM resumen_e0b ORDER BY fuente, anio) TO '/w/salidas/resumen_e0b.csv' (HEADER, DELIMITER ','); -- @ resumen E0b por fuente y año, con E0 al lado SELECT b.fuente, b.anio, b.series, b.series_pasaron, e.series_pasaron AS series_pasaron_e0, b.control, b.control_pasaron, e.control_pasaron AS control_pasaron_e0, CASE WHEN b.series > 0 AND b.control_pasaron > 0 THEN round((b.series_pasaron / b.series) / (b.control_pasaron / b.control), 3) END AS lift, (b.series >= 100 AND b.control >= 100) AS evaluable FROM resumen_e0b b JOIN resumen e USING (fuente, anio) ORDER BY 1, 2; -- @ lift agrupado 2019-2024 por fuente con IC 95 % (Katz), E0b WITH g AS (SELECT fuente, sum(series) n1, sum(series_pasaron) a, sum(control) n0, sum(control_pasaron) c FROM resumen_e0b GROUP BY 1) SELECT 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 ORDER BY fuente; -- @ rama E0b 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_e0b WHERE marcada AND year(fecha_fin) BETWEEN 2019 AND 2024 GROUP BY 1 ORDER BY 1; -- @ series de E0 que E0b descarta, por rama de E0 SELECT s0.fuente, s0.rama AS rama_e0, count(*) FILTER (WHERE s0.marcada) series, count(*) FILTER (WHERE NOT s0.marcada) control FROM series_out s0 JOIN series_e0b s1 USING (fuente, organo_clave, empresa_clave, clase, objeto_norm, fecha_ini, marcada) WHERE s0.pasa AND NOT s1.pasa GROUP BY 1, 2 ORDER BY 1, 2; -- @ Extremadura 2023: control que pasa E0 y E0b del órgano de Badajoz SELECT s0.organo_clave, count(*) FILTER (WHERE s0.pasa) e0, count(*) FILTER (WHERE s1.pasa) e0b FROM series_out s0 JOIN series_e0b s1 USING (fuente, organo_clave, empresa_clave, clase, objeto_norm, fecha_ini, marcada) WHERE s0.fuente = 'EXTREMADURA' AND NOT s0.marcada AND year(s0.fecha_fin) = 2023 GROUP BY 1 HAVING count(*) FILTER (WHERE s0.pasa) > 20; -- @ totales E0b (t 2019-2024) SELECT marcada, count(*) FILTER (WHERE pasa IS NOT NULL) n, count(*) FILTER (WHERE pasa) pasaron FROM series_e0b GROUP BY 1 ORDER BY 1;