-- @cruce E1-E5, ventana +__W__ días (DEFINICIONES_PREVIAS §4). Tablas M___W__ (unidad casada) y A___W__ (aviso -> licitación) CREATE OR REPLACE TABLE r AS SELECT id_canonical AS rid, licitacion_id AS lic, licitacion_id||'#'||lote AS unit, ref, fecha_adj AS fadj, fecha_formalizacion AS fform, imp_sin, imp_con, nif_adj, ent, organo_nombre_norm AS org_nom, exp_n, nom_adj FROM uf ; --; CREATE OR REPLACE TABLE M___W__ (unit VARCHAR, lic VARCHAR, est VARCHAR, aviso VARCHAR, score DOUBLE, dd INTEGER) ; --; CREATE OR REPLACE TABLE A___W__ (aviso VARCHAR, lic VARCHAR) ; --; -- E1: NIF adjudicatario + importe ±10 % (sin o con IVA) + fecha CREATE OR REPLACE TEMP TABLE cand AS SELECT r.unit, r.lic, t.aviso, least(abs(t.v/r.imp_sin-1), abs(t.v/r.imp_con-1)) AS score, abs(date_diff('day', r.ref, t.tpub)) AS dd FROM r JOIN tp t ON t.nif_adj = r.nif_adj WHERE r.imp_sin > 0 AND t.v >= 1.5 AND ((t.v BETWEEN 0.9*r.imp_sin AND 1.1*r.imp_sin) OR (t.v BETWEEN 0.9*r.imp_con AND 1.1*r.imp_con)) AND t.tpub BETWEEN r.ref - 30 AND r.ref + __W__ AND (t.tadj IS NULL OR abs(date_diff('day', t.tadj, r.fadj)) <= 60 OR abs(date_diff('day', t.tadj, r.fform)) <= 60) ; --; CREATE OR REPLACE TEMP TABLE c1 AS SELECT c.* FROM cand c WHERE c.unit NOT IN (SELECT unit FROM M___W__) AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=c.aviso AND a.lic<>c.lic) ; --; CREATE OR REPLACE TEMP TABLE c2 AS SELECT * FROM c1 QUALIFY lic = first_value(lic) OVER (PARTITION BY aviso ORDER BY score, dd, lic) ; --; INSERT INTO M___W__ SELECT unit, lic, 'E1', aviso, score, dd FROM c2 QUALIFY row_number() OVER (PARTITION BY unit ORDER BY score, dd, aviso)=1 ; --; INSERT INTO A___W__ SELECT DISTINCT aviso, lic FROM M___W__ m WHERE est='E1' AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=m.aviso) ; --; -- E2: expediente + entidad del órgano (o nombre normalizado si TED no trae entidad); nivel licitación CREATE OR REPLACE TEMP TABLE cl AS SELECT r.lic, t.aviso, min(abs(date_diff('day', r.ref, t.tpub))) AS dd FROM r JOIN tp t ON t.exp_n = r.exp_n WHERE (CASE WHEN t.ent IS NOT NULL THEN t.ent = r.ent ELSE t.org_nom = r.org_nom END) AND t.tpub BETWEEN r.ref - 30 AND r.ref + __W__ GROUP BY 1,2 ; --; CREATE OR REPLACE TEMP TABLE cand AS SELECT DISTINCT r.unit, r.lic, cl.aviso, 0.0 AS score, cl.dd FROM r JOIN cl USING (lic) ; --; CREATE OR REPLACE TEMP TABLE c1 AS SELECT c.* FROM cand c WHERE c.unit NOT IN (SELECT unit FROM M___W__) AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=c.aviso AND a.lic<>c.lic) ; --; CREATE OR REPLACE TEMP TABLE c2 AS SELECT * FROM c1 QUALIFY lic = first_value(lic) OVER (PARTITION BY aviso ORDER BY score, dd, lic) ; --; INSERT INTO M___W__ SELECT unit, lic, 'E2', aviso, score, dd FROM c2 QUALIFY row_number() OVER (PARTITION BY unit ORDER BY score, dd, aviso)=1 ; --; INSERT INTO A___W__ SELECT DISTINCT aviso, lic FROM M___W__ m WHERE est='E2' AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=m.aviso) ; --; -- E3: entidad del órgano + importe ±2 % + fecha; un solo aviso candidato por unidad CREATE OR REPLACE TEMP TABLE cand AS SELECT r.unit, r.lic, t.aviso, least(abs(t.v/r.imp_sin-1), abs(t.v/r.imp_con-1)) AS score, abs(date_diff('day', r.ref, t.tpub)) AS dd FROM r JOIN tp t ON t.ent = r.ent WHERE r.imp_sin > 0 AND t.v >= 1.5 AND ((t.v BETWEEN 0.98*r.imp_sin AND 1.02*r.imp_sin) OR (t.v BETWEEN 0.98*r.imp_con AND 1.02*r.imp_con)) AND t.tpub BETWEEN r.ref - 30 AND r.ref + __W__ AND (t.tadj IS NULL OR abs(date_diff('day', t.tadj, r.fadj)) <= 60 OR abs(date_diff('day', t.tadj, r.fform)) <= 60) ; --; CREATE OR REPLACE TEMP TABLE c1 AS SELECT c.* FROM cand c WHERE c.unit NOT IN (SELECT unit FROM M___W__) AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=c.aviso AND a.lic<>c.lic) AND c.unit IN (SELECT unit FROM cand c0 WHERE NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=c0.aviso AND a.lic<>c0.lic) GROUP BY unit HAVING count(DISTINCT aviso)=1) ; --; CREATE OR REPLACE TEMP TABLE c2 AS SELECT * FROM c1 QUALIFY lic = first_value(lic) OVER (PARTITION BY aviso ORDER BY score, dd, lic) ; --; INSERT INTO M___W__ SELECT unit, lic, 'E3', aviso, score, dd FROM c2 QUALIFY row_number() OVER (PARTITION BY unit ORDER BY score, dd, aviso)=1 ; --; INSERT INTO A___W__ SELECT DISTINCT aviso, lic FROM M___W__ m WHERE est='E3' AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=m.aviso) ; --; -- E4: suma de lotes de la licitación frente a la suma del aviso (±10 %, sin o con IVA) CREATE OR REPLACE TEMP TABLE sp AS SELECT lic, any_value(ent) ent, any_value(org_nom) org_nom, min(ref) ref_min, max(ref) ref_max, count(*) n, sum(imp_sin) s_sin, sum(imp_con) s_con FROM r WHERE imp_sin > 0 GROUP BY lic ; --; CREATE OR REPLACE TEMP TABLE st AS WITH w AS (SELECT aviso, bool_or(capa='web') hay_web FROM tp GROUP BY aviso) SELECT t.aviso, any_value(t.ent) ent, any_value(t.org_nom) org_nom, min(t.tpub) tpub, count(*) n, sum(t.v) s FROM tp t JOIN w USING (aviso) WHERE t.v >= 1.5 AND (t.capa='web' OR NOT w.hay_web) GROUP BY t.aviso ; --; CREATE OR REPLACE TEMP TABLE cl AS SELECT sp.lic, st.aviso, least(abs(st.s/sp.s_sin-1), abs(st.s/sp.s_con-1)) score, abs(date_diff('day', sp.ref_min, st.tpub)) dd FROM sp JOIN st ON (CASE WHEN st.ent IS NOT NULL THEN st.ent = sp.ent ELSE st.org_nom = sp.org_nom END) WHERE (sp.n >= 2 OR st.n >= 2) AND ((st.s BETWEEN 0.9*sp.s_sin AND 1.1*sp.s_sin) OR (st.s BETWEEN 0.9*sp.s_con AND 1.1*sp.s_con)) AND st.tpub BETWEEN sp.ref_min - 30 AND sp.ref_max + __W__ ; --; CREATE OR REPLACE TEMP TABLE cand AS SELECT DISTINCT r.unit, r.lic, cl.aviso, cl.score, cl.dd FROM r JOIN cl USING (lic) ; --; CREATE OR REPLACE TEMP TABLE c1 AS SELECT c.* FROM cand c WHERE c.unit NOT IN (SELECT unit FROM M___W__) AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=c.aviso AND a.lic<>c.lic) ; --; CREATE OR REPLACE TEMP TABLE c2 AS SELECT * FROM c1 QUALIFY lic = first_value(lic) OVER (PARTITION BY aviso ORDER BY score, dd, lic) ; --; INSERT INTO M___W__ SELECT unit, lic, 'E4', aviso, score, dd FROM c2 QUALIFY row_number() OVER (PARTITION BY unit ORDER BY score, dd, aviso)=1 ; --; INSERT INTO A___W__ SELECT DISTINCT aviso, lic FROM M___W__ m WHERE est='E4' AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=m.aviso) ; --; -- E5: nombre normalizado del adjudicatario + importe ±10 % + fecha CREATE OR REPLACE TEMP TABLE cand AS SELECT r.unit, r.lic, t.aviso, least(abs(t.v/r.imp_sin-1), abs(t.v/r.imp_con-1)) AS score, abs(date_diff('day', r.ref, t.tpub)) AS dd FROM r JOIN tp t ON t.nom_adj = r.nom_adj WHERE r.imp_sin > 0 AND t.v >= 1.5 AND ((t.v BETWEEN 0.9*r.imp_sin AND 1.1*r.imp_sin) OR (t.v BETWEEN 0.9*r.imp_con AND 1.1*r.imp_con)) AND t.tpub BETWEEN r.ref - 30 AND r.ref + __W__ AND (t.tadj IS NULL OR abs(date_diff('day', t.tadj, r.fadj)) <= 60 OR abs(date_diff('day', t.tadj, r.fform)) <= 60) ; --; CREATE OR REPLACE TEMP TABLE c1 AS SELECT c.* FROM cand c WHERE c.unit NOT IN (SELECT unit FROM M___W__) AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=c.aviso AND a.lic<>c.lic) ; --; CREATE OR REPLACE TEMP TABLE c2 AS SELECT * FROM c1 QUALIFY lic = first_value(lic) OVER (PARTITION BY aviso ORDER BY score, dd, lic) ; --; INSERT INTO M___W__ SELECT unit, lic, 'E5', aviso, score, dd FROM c2 QUALIFY row_number() OVER (PARTITION BY unit ORDER BY score, dd, aviso)=1 ; --; INSERT INTO A___W__ SELECT DISTINCT aviso, lic FROM M___W__ m WHERE est='E5' AND NOT EXISTS (SELECT 1 FROM A___W__ a WHERE a.aviso=m.aviso) ; --; -- @controles: unidad casada una vez; aviso con una sola licitación SELECT (SELECT count(*) - count(DISTINCT unit) FROM M___W__) unidades_repetidas, (SELECT count(*) FROM (SELECT aviso FROM M___W__ GROUP BY aviso HAVING count(DISTINCT lic)>1)) avisos_con_varias_lic, (SELECT count(*) FROM M___W__) casadas ; --; -- @casadas por estrategia (todas las unidades de uf) SELECT est, count(*) unidades, count(DISTINCT lic) lic, count(DISTINCT aviso) avisos FROM M___W__ GROUP BY 1 ORDER BY 1