-- impl2 · 07 · «Pasó a licitación» (§4.2), variante E4 (§4.3) y control de referencia de Fable (§3.4) -- Las consultas se casan a nivel de objeto distinto: (órgano, t, objeto de la serie) contra (órgano, t + 1, objeto de la -- fila), y de ahí se vuelve a cada serie, cadena o par. Tokens: unión por token (tablas tok/ntok de py/06_tokens.py) -- con el recuento de los compartidos; JW: solo en las parejas que ya comparten CPV-5. -- ── Lo que se casa: series marcadas y cadenas de control de t = 2019-2024, y los pares año de Fable (PLACSP) ─────── CREATE OR REPLACE TABLE fpar AS SELECT md5('F|' || organo_clave || '|' || empresa_clave || '|' || clase || '|' || objeto_norm || '|' || year(fecha_adj)::VARCHAR) AS qid, organo_clave, empresa_clave, clase, objeto_norm, year(fecha_adj) AS anio, count(*) AS n, sum(base) AS suma, min(umbral) AS umbral FROM pob0 WHERE length(objeto_norm) >= 5 AND year(fecha_adj) BETWEEN 2019 AND 2024 GROUP BY organo_clave, empresa_clave, clase, objeto_norm, year(fecha_adj) HAVING count(*) >= 2; ANALYZE fpar; CREATE OR REPLACE TABLE q AS SELECT uid AS qid, CASE WHEN marcada THEN 'serie' ELSE 'control' END AS tipo, fuente, organo_clave, anio_fin AS t, objeto_norm FROM uni_x WHERE anio_fin BETWEEN 2019 AND 2024 UNION ALL SELECT qid, CASE WHEN suma > umbral THEN 'fable_supera' ELSE 'fable_no_supera' END, 'PLACSP', organo_clave, anio, objeto_norm FROM fpar; ANALYZE q; CREATE OR REPLACE TABLE q_cpv5 AS SELECT DISTINCT c.uid AS qid, substr(c.cpv8, 1, 5) AS cpv5 FROM uni_c c SEMI JOIN q ON q.qid = c.uid WHERE c.cpv8 IS NOT NULL UNION SELECT DISTINCT f.qid, substr(p.cpv8, 1, 5) FROM fpar f JOIN pob0 p ON p.organo_clave = f.organo_clave AND p.empresa_clave = f.empresa_clave AND p.clase = f.clase AND p.objeto_norm = f.objeto_norm AND year(p.fecha_adj) = f.anio WHERE p.cpv8 IS NOT NULL; ANALYZE q_cpv5; -- ── Objetos distintos de cada lado ───────────────────────────────────────────────────────────────────────────────── CREATE OR REPLACE TABLE so AS SELECT DISTINCT organo_clave, t, objeto_norm FROM q; CREATE OR REPLACE TABLE lo AS SELECT DISTINCT organo_clave, anio_ref, objeto_norm FROM lic WHERE anio_ref BETWEEN 2020 AND 2025; CREATE OR REPLACE TABLE so_cpv AS SELECT DISTINCT q.organo_clave, q.t, q.objeto_norm, c.cpv5 FROM q JOIN q_cpv5 c USING (qid); CREATE OR REPLACE TABLE lo_cpv AS SELECT DISTINCT organo_clave, anio_ref, objeto_norm, substr(cpv8, 1, 5) AS cpv5 FROM lic WHERE anio_ref BETWEEN 2020 AND 2025 AND cpv8 IS NOT NULL; ANALYZE so; ANALYZE lo; ANALYZE so_cpv; ANALYZE lo_cpv; -- (b) tokens: el 60 % o más de los tokens de la serie (2 o más) están en la fila CREATE OR REPLACE TABLE so_tok AS SELECT s.organo_clave, s.t, s.objeto_norm, n.n_tokens, k.token FROM so s JOIN ntok n USING (objeto_norm) JOIN tok k USING (objeto_norm) WHERE n.n_tokens >= 2; CREATE OR REPLACE TABLE lo_tok AS SELECT l.organo_clave, l.anio_ref, l.objeto_norm, k.token FROM lo l JOIN tok k USING (objeto_norm); ANALYZE so_tok; ANALYZE lo_tok; CREATE OR REPLACE TABLE pb AS SELECT s.organo_clave, s.t, s.objeto_norm AS s_obj, l.objeto_norm AS l_obj, count(*) AS compartidos, min(s.n_tokens) AS n_tokens FROM so_tok s JOIN lo_tok l ON l.organo_clave = s.organo_clave AND l.anio_ref = s.t + 1 AND l.token = s.token GROUP BY s.organo_clave, s.t, s.objeto_norm, l.objeto_norm HAVING 10 * count(*) >= 6 * min(s.n_tokens); ANALYZE pb; -- (a) JW >= 0,85 entre objetos que comparten CPV-5 (en la misma fila de la licitación) CREATE OR REPLACE TABLE pa_cand AS SELECT DISTINCT s.organo_clave, s.t, s.objeto_norm AS s_obj, s.cpv5, l.objeto_norm AS l_obj FROM so_cpv s JOIN lo_cpv l ON l.organo_clave = s.organo_clave AND l.anio_ref = s.t + 1 AND l.cpv5 = s.cpv5; ANALYZE pa_cand; CREATE OR REPLACE TABLE jw AS SELECT s_obj, l_obj, jaro_winkler_similarity(s_obj, l_obj) AS jw FROM (SELECT DISTINCT s_obj, l_obj FROM pa_cand); ANALYZE jw; CREATE OR REPLACE TABLE pa AS SELECT c.* FROM pa_cand c JOIN jw USING (s_obj, l_obj) WHERE jw.jw >= 0.85; ANALYZE pa; -- @ parejas de objetos: candidatas y casadas por rama SELECT (SELECT count(*) FROM pa_cand) cand_cpv, (SELECT count(*) FROM jw) jw_calculados, (SELECT count(*) FROM pa) casadas_jw_cpv, (SELECT count(*) FROM pb) casadas_tokens; -- ── De los objetos a cada serie / cadena / par, y a las filas de la licitación ───────────────────────────────────── -- Filas de licitación casadas por cada consulta, con su rama CREATE OR REPLACE TABLE m_a AS SELECT DISTINCT q.qid, r.source, r.lic_key, r.id_canonical FROM q JOIN q_cpv5 c USING (qid) JOIN pa p ON p.organo_clave = q.organo_clave AND p.t = q.t AND p.s_obj = q.objeto_norm AND p.cpv5 = c.cpv5 JOIN lic r ON r.organo_clave = q.organo_clave AND r.anio_ref = q.t + 1 AND r.objeto_norm = p.l_obj AND substr(r.cpv8, 1, 5) = p.cpv5; ANALYZE m_a; CREATE OR REPLACE TABLE m_b AS SELECT DISTINCT q.qid, r.source, r.lic_key, r.id_canonical FROM q JOIN pb p ON p.organo_clave = q.organo_clave AND p.t = q.t AND p.s_obj = q.objeto_norm JOIN lic r ON r.organo_clave = q.organo_clave AND r.anio_ref = q.t + 1 AND r.objeto_norm = p.l_obj; ANALYZE m_b; -- Parejas consulta → licitación (clave), con la rama de la pareja (unión de las de sus filas) CREATE OR REPLACE TABLE par AS SELECT qid, source, lic_key, bool_or(por_a) AS por_a, bool_or(por_b) AS por_b FROM (SELECT qid, source, lic_key, TRUE AS por_a, FALSE AS por_b FROM m_a UNION ALL SELECT qid, source, lic_key, FALSE, TRUE FROM m_b) GROUP BY qid, source, lic_key; ANALYZE par; CREATE OR REPLACE TABLE res 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, coalesce(bool_or(p.por_a AND p.por_b), FALSE) AS alguna_pareja_ambas, count(p.lic_key) AS n_licitaciones_casadas FROM q LEFT JOIN par p USING (qid) GROUP BY q.qid, q.tipo, q.fuente, q.t; ANALYZE res; -- ── E4: misma entidad (entidad_clave de los contratos de la serie frente a la de la fila) ───────────────────────── CREATE OR REPLACE TABLE q4 AS SELECT DISTINCT q.qid, q.t, q.objeto_norm, c.entidad_clave FROM q JOIN uni_c c ON c.uid = q.qid WHERE q.tipo IN ('serie', 'control') AND c.entidad_clave IS NOT NULL; ANALYZE q4; CREATE OR REPLACE TABLE se AS SELECT DISTINCT entidad_clave, t, objeto_norm FROM q4; CREATE OR REPLACE TABLE le AS SELECT DISTINCT entidad_clave, anio_ref, objeto_norm FROM lic WHERE anio_ref BETWEEN 2020 AND 2025 AND entidad_clave IS NOT NULL; CREATE OR REPLACE TABLE se_cpv AS SELECT DISTINCT q4.entidad_clave, q4.t, q4.objeto_norm, c.cpv5 FROM q4 JOIN q_cpv5 c USING (qid); CREATE OR REPLACE TABLE le_cpv AS SELECT DISTINCT entidad_clave, anio_ref, objeto_norm, substr(cpv8, 1, 5) AS cpv5 FROM lic WHERE anio_ref BETWEEN 2020 AND 2025 AND cpv8 IS NOT NULL AND entidad_clave IS NOT NULL; ANALYZE se; ANALYZE le; ANALYZE se_cpv; ANALYZE le_cpv; CREATE OR REPLACE TABLE se_tok AS SELECT s.entidad_clave, s.t, s.objeto_norm, n.n_tokens, k.token FROM se s JOIN ntok n USING (objeto_norm) JOIN tok k USING (objeto_norm) WHERE n.n_tokens >= 2; CREATE OR REPLACE TABLE le_tok AS SELECT l.entidad_clave, l.anio_ref, l.objeto_norm, k.token FROM le l JOIN tok k USING (objeto_norm); ANALYZE se_tok; ANALYZE le_tok; CREATE OR REPLACE TABLE pb4 AS SELECT s.entidad_clave, s.t, s.objeto_norm AS s_obj, l.objeto_norm AS l_obj FROM se_tok s JOIN le_tok l ON l.entidad_clave = s.entidad_clave AND l.anio_ref = s.t + 1 AND l.token = s.token GROUP BY s.entidad_clave, s.t, s.objeto_norm, l.objeto_norm HAVING 10 * count(*) >= 6 * min(s.n_tokens); ANALYZE pb4; CREATE OR REPLACE TABLE pa4 AS SELECT DISTINCT s.entidad_clave, s.t, s.objeto_norm AS s_obj, s.cpv5, l.objeto_norm AS l_obj FROM se_cpv s JOIN le_cpv l ON l.entidad_clave = s.entidad_clave AND l.anio_ref = s.t + 1 AND l.cpv5 = s.cpv5 WHERE jaro_winkler_similarity(s.objeto_norm, l.objeto_norm) >= 0.85; ANALYZE pa4; CREATE OR REPLACE TABLE m4 AS SELECT DISTINCT q4.qid, r.source, r.lic_key FROM q4 JOIN q_cpv5 c USING (qid) JOIN pa4 p ON p.entidad_clave = q4.entidad_clave AND p.t = q4.t AND p.s_obj = q4.objeto_norm AND p.cpv5 = c.cpv5 JOIN lic r ON r.entidad_clave = q4.entidad_clave AND r.anio_ref = q4.t + 1 AND r.objeto_norm = p.l_obj AND substr(r.cpv8, 1, 5) = p.cpv5 UNION SELECT DISTINCT q4.qid, r.source, r.lic_key FROM q4 JOIN pb4 p ON p.entidad_clave = q4.entidad_clave AND p.t = q4.t AND p.s_obj = q4.objeto_norm JOIN lic r ON r.entidad_clave = q4.entidad_clave AND r.anio_ref = q4.t + 1 AND r.objeto_norm = p.l_obj; ANALYZE m4; CREATE OR REPLACE TABLE res4 AS SELECT q.qid, q.tipo, q.fuente, q.t, (count(m.lic_key) > 0) AS pasa_e4, count(m.lic_key) AS n_lic_e4 FROM q LEFT JOIN m4 m USING (qid) WHERE q.tipo IN ('serie', 'control') GROUP BY q.qid, q.tipo, q.fuente, q.t; ANALYZE res4; -- ── E1 (Fable): solo JW >= 0,85 con cualquier fila del órgano en t + 1, sin CPV ni tokens (para el control de Fable) ── CREATE OR REPLACE TABLE so_f AS SELECT DISTINCT organo_clave, t, objeto_norm FROM q WHERE tipo LIKE 'fable%' OR (tipo IN ('serie', 'control') AND fuente = 'PLACSP'); ANALYZE so_f; CREATE OR REPLACE TABLE pe1 AS SELECT DISTINCT s.organo_clave, s.t, s.objeto_norm FROM so_f s JOIN lo l ON l.organo_clave = s.organo_clave AND l.anio_ref = s.t + 1 WHERE jaro_winkler_similarity(s.objeto_norm, l.objeto_norm) >= 0.85; ANALYZE pe1;