"""D1b · base y recuentos para la potencia (ANTES del sellado). No lee ningún valor de K-CUOTA ni de la cuota relativa cruzado con la etiqueta: solo tamaño, mercado, año y recuentos. Escribe /w/interno/d1b.duckdb.""" import time, duckdb from pathlib import Path T0 = time.time() DB = Path('/w/interno/d1b.duckdb'); DB.unlink(missing_ok=True) con = duckdb.connect(DB.as_posix()); x = con.execute x("SET threads=4; SET memory_limit='6GB'; SET temp_directory='/w/tmp/duck'; SET preserve_insertion_order=false") def log(*a): print(f"[{time.time()-T0:6.1f} s]", *a, flush=True) def show(t, q): r = x(q); cols = [d[0] for d in r.description]; rows = r.fetchall() print(f"\n## {t}\n\n| " + ' | '.join(cols) + ' |\n|' + '|'.join('---' for _ in cols) + '|') for row in rows: print('| ' + ' | '.join('' if v is None else (f'{v:.4g}' if isinstance(v, float) else str(v)) for v in row) + ' |') x("ATTACH '/et/sigma/interno/sg.duckdb' AS sg (READ_ONLY)") # lotes del universo de D-SIGMA (PLACSP no menores 2014-2025, sin personas físicas) con su mercado x("""CREATE TABLE lic AS SELECT l.id_canonical, l.licitacion_id, l.organo_clave, l.tipo, l.empresa_clave, l.cpv2, l.adj, l.anio, l.fadj, l.pos_alim, l.pos_mil, l.pos_carr, l.pos_cant, l.m_alim, l.m_mil, l.m_carr, l.m_obras, l.en_cantabria, l.excluido, k.merc FROM sg.lic l JOIN sg.lmk k USING (id_canonical)""") # tamaño: importe anual nacional del adjudicatario (todos los lotes del universo, todos los mercados) x("CREATE TABLE tam AS SELECT empresa_clave, anio, sum(adj) FILTER (WHERE adj > 0) s, count(*) n FROM lic GROUP BY ALL") x("UPDATE tam SET s = 0 WHERE s IS NULL") # deciles ponderados por lote (principal), por empresa (sensibilidad) y centiles por lote (sensibilidad) x("""CREATE TABLE cortes_lote AS SELECT l.anio, quantile_cont(t.s, [0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9]) q10, quantile_cont(t.s, [i / 100 for i in range(1, 100)]) q100 FROM lic l JOIN tam t USING (empresa_clave, anio) GROUP BY 1""") x("""CREATE TABLE cortes_emp AS SELECT anio, quantile_cont(s, [0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9]) q10 FROM tam GROUP BY 1""") x("""CREATE TABLE ltam AS SELECT l.id_canonical, t.s tam, 1 + list_count(list_filter(cl.q10, q -> t.s > q * (1 + 1e-9) + 1e-6)) dec_l, 1 + list_count(list_filter(ce.q10, q -> t.s > q * (1 + 1e-9) + 1e-6)) dec_e, 1 + list_count(list_filter(cl.q100, q -> t.s > q * (1 + 1e-9) + 1e-6)) cen_l FROM lic l JOIN tam t USING (empresa_clave, anio) JOIN cortes_lote cl USING (anio) JOIN cortes_emp ce USING (anio)""") assert x("SELECT count(*) FROM ltam").fetchone()[0] == x("SELECT count(*) FROM lic").fetchone()[0] # unidades (regla de D-SIGMA 13_unidades: positivos y controles del mercado afectado en los años con positivos) x("CREATE TABLE ut (cartel VARCHAR, id_canonical VARCHAR, y INTEGER)") for c, pc_, mc_ in [('alimentos', 'pos_alim', 'm_alim'), ('militar', 'pos_mil', 'm_mil'), ('carreteras', 'pos_carr', 'm_carr')]: x(f"""INSERT INTO ut SELECT '{c}', id_canonical, CASE WHEN {pc_} = 1 THEN 1 ELSE 0 END FROM lic WHERE {pc_} = 1 OR ({mc_} AND NOT coalesce(excluido, false) AND anio IN (SELECT DISTINCT anio FROM lic WHERE {pc_} = 1))""") x("""INSERT INTO ut SELECT 'cantabria', id_canonical, CASE WHEN pos_cant = 1 THEN 1 ELSE 0 END FROM lic WHERE anio BETWEEN 2019 AND 2025 AND (pos_cant = 1 OR (m_obras AND NOT coalesce(en_cantabria, false) AND NOT coalesce(excluido, false)))""") # cuota definida (adj > 0) sin leer su valor; otra presencia del proveedor (para D-b) x("""CREATE TABLE otros AS SELECT empresa_clave, count(DISTINCT organo_clave) n_org FROM lic WHERE adj > 0 GROUP BY 1""") x("""CREATE TABLE u AS SELECT ut.*, l.organo_clave, l.anio, l.merc, l.adj > 0 AS def_cuota, (o.n_org >= 2) AS def_rel, t.dec_l, t.dec_e, t.cen_l FROM ut JOIN lic l USING (id_canonical) JOIN ltam t USING (id_canonical) LEFT JOIN otros o ON o.empresa_clave = l.empresa_clave""") log('u', x("SELECT cartel, count(*), sum(y) FROM u GROUP BY 1 ORDER BY 1").fetchall()) show('Universo (regla de D-SIGMA): lotes, positivos, órganos, con cuota definida (adj > 0), con otra presencia para D-b', """ SELECT cartel, count(*) n, sum(y) pos, count(DISTINCT organo_clave) FILTER (WHERE y = 1) org_pos, count(DISTINCT organo_clave) org, sum(y) FILTER (WHERE def_cuota) pos_cuota, sum(1 - y) FILTER (WHERE def_cuota) ctl_cuota, sum(y) FILTER (WHERE def_cuota AND def_rel) pos_rel, sum(1 - y) FILTER (WHERE def_cuota AND def_rel) ctl_rel FROM u GROUP BY 1 ORDER BY 1""") show('Decil de tamaño (ponderado por lote) de los positivos y de los controles, % por cártel', """ SELECT cartel, y, count(*) n, """ + ', '.join(f"round(100.0 * avg((dec_l = {d})::INT), 1) d{d}" for d in range(1, 11)) + """ FROM u WHERE def_cuota GROUP BY 1, 2 ORDER BY 1, 2""") for nom, col in [('dec_l', 'decil por lote (principal)'), ('dec_e', 'decil por empresa (sensibilidad)'), ('cen_l', 'centil por lote (sensibilidad)')]: show(f'D-a · estratos cártel × mercado × {col}: positivos y controles que quedan emparejados', f""" WITH s AS (SELECT cartel, merc, {nom} k, sum(y) n1, sum(1 - y) n0 FROM u WHERE def_cuota GROUP BY ALL), v AS (SELECT * FROM s WHERE n1 > 0 AND n0 > 0) SELECT cartel, (SELECT sum(n1) FROM s s2 WHERE s2.cartel = v.cartel) pos_def, sum(n1) pos_emp, sum(n0) ctl_emp, count(*) estratos, sum(n1 * n0) pares, round(sum(n1 * n0) / sum(n1), 1) ctl_por_pos FROM v GROUP BY cartel ORDER BY 1""") x("""CREATE TABLE pot AS WITH s AS (SELECT cartel, merc, dec_l k, sum(y) n1, sum(1 - y) n0 FROM u WHERE def_cuota GROUP BY ALL) SELECT * FROM s WHERE n1 > 0 AND n0 > 0""") x("COPY pot TO '/w/interno/01_pot.parquet' (FORMAT PARQUET)") x("""COPY (SELECT cartel, y, organo_clave, merc, dec_l FROM u WHERE def_cuota) TO '/w/interno/01_upot.parquet' (FORMAT PARQUET)""") log('fin')