# CANT-1 · impl2 (SQL de DuckDB) de prereg/DEFINICIONES_CANT1.md (md5 d3fec8f5…). Escrita desde el texto del pre-registro: # población y motivos, vector, screens con fórmulas explícitas (y skewness/kurtosis nativas como tercera comprobación), # percentiles con rango medio, S, descriptivos del §4 (sin IC) y descriptores de presencia. # Salida: interno/screens_impl2.parquet, interno/impl2.json (600). import duckdb, json, os con = duckdb.connect(); con.execute("SET threads=6") con.execute("CREATE TEMP TABLE c AS SELECT * FROM read_parquet('/c/interno/contratos_cob.parquet')") con.execute("CREATE TEMP TABLE o AS SELECT * FROM read_parquet('/c/interno/ofertas.parquet')") FAC = lambda s, k: f"""CASE WHEN {s} IS NULL OR {s}<=0 THEN NULL WHEN {k} IS NULL OR {k}=0 THEN 'iva_0' WHEN abs({k}/{s}/1.00-1)<=0.005 THEN '1.00' WHEN abs({k}/{s}/1.04-1)<=0.005 THEN '1.04' WHEN abs({k}/{s}/1.10-1)<=0.005 THEN '1.10' WHEN abs({k}/{s}/1.21-1)<=0.005 THEN '1.21' ELSE 'otro' END""" # 1) vector principal: detalle (adjudicataria con importe <= 0 -> importe de adjudicación) + adjudicataria añadida si no está con.execute(f"""CREATE TEMP TABLE v AS SELECT o.id_contrato, o.clave, coalesce(o.adjudicataria,false) es_adj, CASE WHEN coalesce(o.adjudicataria,false) AND (o.sin_iva IS NULL OR o.sin_iva<=0) THEN c.adj_sin ELSE o.sin_iva END imp, CASE WHEN coalesce(o.adjudicataria,false) AND (o.sin_iva IS NULL OR o.sin_iva<=0) THEN {FAC('c.adj_sin','c.adj_con')} ELSE {FAC('o.sin_iva','o.con_iva')} END fac, coalesce(o.adjudicataria,false) AND (o.sin_iva IS NULL OR o.sin_iva<=0) anadida FROM o JOIN c USING (id_contrato) WHERE o.origen='detalle' UNION ALL SELECT c.id_contrato, c.adj_clave, true, c.adj_sin, {FAC('c.adj_sin','c.adj_con')}, true FROM c WHERE c.adj_clave IS NOT NULL AND EXISTS (SELECT 1 FROM o WHERE o.id_contrato=c.id_contrato AND o.origen='detalle') AND NOT EXISTS (SELECT 1 FROM o WHERE o.id_contrato=c.id_contrato AND o.origen='detalle' AND coalesce(o.adjudicataria,false))""") # 2) reglas por contrato con.execute("""CREATE TEMP TABLE r AS WITH d AS (SELECT id_contrato, count(*) ndet FROM o WHERE origen='detalle' GROUP BY 1), a AS (SELECT id_contrato, count(*) n, bool_or(imp IS NULL OR imp<=0) cero, count(clave) - count(DISTINCT clave) rep, bool_or(imp>0 AND (fac IS NULL OR fac NOT IN ('1.00','1.04','1.10','1.21','iva_0'))) ivainc, min(imp) bmin, max(imp) bmax, bool_and(imp>0) todos_pos, max(imp) FILTER (WHERE anadida) imp_anadida, bool_or(anadida) hay_anadida, median(imp) FILTER (WHERE NOT es_adj AND imp>0) med_resto FROM v GROUP BY 1), pn AS (SELECT v.id_contrato, count(*) FILTER (WHERE c.pbl_sin>0 AND abs(v.imp-c.pbl_sin)<=0.001*c.pbl_sin) npbl FROM v JOIN c USING (id_contrato) GROUP BY 1) SELECT c.id_contrato, CASE WHEN c.bloque NOT IN ('menor','general','otros') OR c.anio<2018 THEN 'bloque' WHEN c.tipo NOT IN ('Obras','Servicios','Suministro') THEN 'al_alza_tipo' WHEN coalesce(d.ndet,0)=0 THEN 'sin_detalle' WHEN c.adj_clave IS NULL THEN 'adj_desconocida' WHEN a.n<3 THEN 'menos_de_3' WHEN a.cero THEN 'importe_cero' WHEN a.rep>0 THEN 'nif_repetido' WHEN a.ivainc THEN 'iva_incoherente' WHEN a.hay_anadida AND (a.med_resto IS NULL OR a.imp_anadida < 0.5*a.med_resto OR a.imp_anadida > 2*a.med_resto) THEN 'adj_incoherente' WHEN a.todos_pos AND a.bmax=a.bmin THEN 'sin_dispersion' WHEN a.todos_pos AND c.pbl_sin>0 AND pn.npbl >= 0.5*a.n THEN 'precio_no_competido' ELSE 'P' END motivo FROM c LEFT JOIN d USING (id_contrato) LEFT JOIN a USING (id_contrato) LEFT JOIN pn USING (id_contrato)""") # 3) screens (P) con.execute("""CREATE TEMP TABLE s AS WITH x AS (SELECT v.*, row_number() OVER (PARTITION BY id_contrato ORDER BY imp) rn FROM v WHERE id_contrato IN (SELECT id_contrato FROM r WHERE motivo='P')), g AS (SELECT id_contrato, count(*) n, avg(imp) m, stddev_samp(imp) sd, min(imp) b1, max(imp) bn, max(imp) FILTER (WHERE rn=2) b2, stddev_samp(imp) FILTER (WHERE rn>=2) sp, skewness(imp) sk_nat, kurtosis(imp) ku_nat, max(imp) FILTER (WHERE es_adj) adj FROM x GROUP BY 1), z AS (SELECT x.id_contrato, sum(power((x.imp-g.m)/g.sd,3)) z3, sum(power((x.imp-g.m)/g.sd,4)) z4 FROM x JOIN g USING (id_contrato) GROUP BY 1), oa AS (SELECT x.id_contrato, min(x.imp) otra FROM x JOIN (SELECT id_contrato, min(rn) FILTER (WHERE es_adj) rn_adj FROM x GROUP BY 1) q USING (id_contrato) WHERE x.rn<>q.rn_adj GROUP BY 1) SELECT g.id_contrato, g.n, g.sd/g.m "CV", (g.b2-g.b1)/g.b1 "DIFFP", (g.bn-g.b1)/g.b1 "SPD", CASE WHEN g.sp>0 THEN (g.b2-g.b1)/g.sp WHEN g.b2>g.b1 THEN 'inf'::DOUBLE ELSE 0 END "RD", g.n::DOUBLE/((g.n-1)*(g.n-2))*z.z3 "SKEW", CASE WHEN g.n>=4 THEN g.n::DOUBLE*(g.n+1)/((g.n-1)*(g.n-2)*(g.n-3))*z.z4 - 3.0*(g.n-1)*(g.n-1)/((g.n-2)*(g.n-3)) END "KURTO", CASE WHEN g.b2=g.b1 THEN 0 WHEN g.bn-g.b2>0 THEN (g.b2-g.b1)/((g.bn-g.b2)/(g.n-2)) ELSE 'inf'::DOUBLE END "RDALT", (g.b2-g.b1)/((g.bn-g.b1)/(g.n-1)) "RDNOR", g.sk_nat, g.ku_nat, CASE WHEN g.adj > g.b1*(1+0.00005) THEN 1 ELSE 0 END "ADJ_NO_MIN", (oa.otra-g.adj)/g.adj "DIFFP_ADJ", CASE WHEN (g.b2-g.b1)/g.b1 < 0.005 THEN 1 ELSE 0 END "HUECO" FROM g JOIN z USING (id_contrato) LEFT JOIN oa USING (id_contrato)""") con.execute("""CREATE TEMP TABLE sp AS SELECT s.*, c.tipo||'|'||CASE WHEN c.bloque='menor' THEN 'menor' ELSE 'no_menor' END mercado, c.anio, c.consejeria, c.organo, c.ambito_sobre FROM s JOIN c USING (id_contrato)""") for sc, sg in (('CV', -1), ('DIFFP', 1), ('RD', 1), ('SKEW', -1), ('KURTO', 1), ('SPD', -1)): con.execute(f"""CREATE OR REPLACE TEMP TABLE p_{sc} AS WITH t AS (SELECT id_contrato, mercado, {sg}*CASE WHEN isinf("{sc}") THEN "{sc}" ELSE round("{sc}", 9) END val FROM sp WHERE "{sc}" IS NOT NULL), t2 AS (SELECT *, count(*) OVER (PARTITION BY mercado) nn, rank() OVER (PARTITION BY mercado ORDER BY val) rk, count(*) OVER (PARTITION BY mercado, val) emp FROM t) SELECT id_contrato, (rk + (emp-1)/2.0 - 0.5)/nn pct FROM t2 WHERE nn>=20""") con.execute("""CREATE TEMP TABLE fin AS SELECT sp.*, a.pct pct_CV, b.pct pct_DIFFP, d.pct pct_RD, e.pct pct_SKEW, f.pct pct_KURTO, h.pct pct_SPD, (coalesce(a.pct,0)+coalesce(b.pct,0)+coalesce(d.pct,0)+coalesce(e.pct,0)+coalesce(f.pct,0)) / nullif((a.pct IS NOT NULL)::INT+(b.pct IS NOT NULL)::INT+(d.pct IS NOT NULL)::INT+(e.pct IS NOT NULL)::INT+(f.pct IS NOT NULL)::INT,0) "S" FROM sp LEFT JOIN p_CV a USING (id_contrato) LEFT JOIN p_DIFFP b USING (id_contrato) LEFT JOIN p_RD d USING (id_contrato) LEFT JOIN p_SKEW e USING (id_contrato) LEFT JOIN p_KURTO f USING (id_contrato) LEFT JOIN p_SPD h USING (id_contrato)""") con.execute("COPY (SELECT r.id_contrato, r.motivo, fin.* EXCLUDE (id_contrato) FROM r LEFT JOIN fin ON fin.id_contrato=r.id_contrato) TO '/c/interno/screens_impl2.parquet' (FORMAT parquet)") res = dict(motivos=dict(con.execute("SELECT motivo, count(*) FROM r GROUP BY 1").fetchall())) def desc(where): q = f"""SELECT count(*) n, count(DISTINCT organo) organos, avg(n) ofertas_media, {', '.join(f'quantile_cont("{s}", 0.25) FILTER (WHERE "{s}" IS NOT NULL) {s}_p25, median("{s}") {s}_med, quantile_cont("{s}", 0.75) {s}_p75' for s in ('CV','DIFFP','SKEW','KURTO','SPD'))}, median("RD") RD_med, avg("ADJ_NO_MIN") ADJ_NO_MIN, avg("HUECO") HUECO, median("DIFFP_ADJ") DIFFP_ADJ_med, avg(("CV"<0.0325)::INT) CV_lt_0325, avg(("CV"<0.0951)::INT) CV_lt_0951 FROM fin WHERE {where}""" r = con.execute(q); return dict(zip([d[0] for d in r.description], r.fetchone())) res['por_mercado'] = {m: desc(f"mercado='{m}'") for (m,) in con.execute("SELECT DISTINCT mercado FROM fin ORDER BY 1").fetchall()} res['ambito'] = {'ambito_sobre': desc('ambito_sobre'), 'resto': desc('NOT ambito_sobre')} res['resto_por_mercado'] = {m: desc(f"mercado='{m}' AND NOT ambito_sobre") for (m,) in con.execute("SELECT DISTINCT mercado FROM fin ORDER BY 1").fetchall()} res['P_todo'] = desc('true') TR = "CASE WHEN n=3 THEN '3' WHEN n<=5 THEN '4-5' WHEN n<=9 THEN '6-9' ELSE '≥ 10' END" res['por_tramo'] = {f"{a}|{b}": desc(f"split_part(mercado,'|',2)='{a}' AND {TR}='{b}'") for a, b in con.execute(f"SELECT DISTINCT split_part(mercado,'|',2), {TR} FROM fin ORDER BY 1,2").fetchall()} res['por_consejeria'] = {k: desc(f"consejeria='{k}'") for (k,) in con.execute("SELECT consejeria FROM fin GROUP BY 1 HAVING count(*)>=20 ORDER BY 1").fetchall()} # presencia con.execute("""CREATE TEMP TABLE pres AS SELECT c.id_contrato, c.organo, c.consejeria, c.tipo||'|'||CASE WHEN c.bloque='menor' THEN 'menor' ELSE 'no_menor' END mercado, c.adj_clave, string_agg(DISTINCT o.clave, '|' ORDER BY o.clave) conj FROM c JOIN o USING (id_contrato) WHERE c.bloque IN ('menor','general','otros') AND c.anio>=2018 AND c.tipo IN ('Obras','Servicios','Suministro') AND o.clave IS NOT NULL AND c.id_contrato IN (SELECT id_contrato FROM o WHERE origen='detalle') GROUP BY ALL HAVING count(DISTINCT o.clave)>=3""") con.execute("CREATE TEMP TABLE grp AS SELECT organo, conj, count(*) k, count(DISTINCT adj_clave) gan FROM pres GROUP BY 1,2") con.execute("CREATE TEMP TABLE gana AS SELECT DISTINCT adj_clave FROM c WHERE anio>=2018 AND adj_clave IS NOT NULL") def presq(where): a = con.execute(f"""SELECT count(*), avg((g.k>=3)::INT) FROM pres p JOIN grp g USING (organo, conj) WHERE {where}""").fetchone() b = con.execute(f"""SELECT count(*), avg(gan), avg((gan=1)::INT) FROM grp g WHERE k>=3 AND EXISTS (SELECT 1 FROM pres p WHERE p.organo=g.organo AND p.conj=g.conj AND {where})""").fetchone() n = con.execute(f"""SELECT count(*), avg((o.clave NOT IN (SELECT adj_clave FROM gana))::INT) FROM pres p JOIN o USING (id_contrato) WHERE {where} AND o.clave IS NOT NULL AND NOT coalesce(o.adjudicataria,false)""").fetchone() return dict(n=a[0], REPE=a[1], conjuntos_repetidos=b[0], rotacion_media=b[1], conjuntos_un_solo_ganador=b[2], ofertas_perdedoras=n[0], NUNCA_GANA=n[1]) res['presencia_mercado'] = {m: presq(f"p.mercado='{m}'") for (m,) in con.execute("SELECT DISTINCT mercado FROM pres ORDER BY 1").fetchall()} res['presencia_todo'] = presq('true') res['presencia_consejeria'] = {k: presq(f"p.consejeria='{k}'") for (k,) in con.execute("SELECT consejeria FROM pres GROUP BY 1 HAVING count(*)>=20 ORDER BY 1").fetchall()} res['nativas'] = dict(zip(['n', 'max_dif_skew', 'max_dif_kurt'], con.execute("""SELECT count(*), max(abs(sk_nat-"SKEW")), max(abs(ku_nat-"KURTO")) FILTER (WHERE n>=4) FROM fin""").fetchone())) json.dump(res, open('/c/interno/impl2.json', 'w'), ensure_ascii=False, indent=1, default=str) for p in ('/c/interno/screens_impl2.parquet', '/c/interno/impl2.json'): os.chmod(p, 0o600) print(json.dumps(dict(motivos=res['motivos'], nativas=res['nativas']), ensure_ascii=False, default=str))