"""E-M1b · 05. POSTERIOR (no pre-registrado; escrito tras ver 02_migracion): el mismo tramo en tres regímenes. La regla del pico de §3.4 compara la banda vieja con las 3 anchuras de debajo. En G1 y G3 esa zona contiene el umbral de 2022-2023 (140.000 / 215.000) y su amontonamiento persistente, y la regla no se cumple (1,78 y 1,64 < 2). Medida alternativa, descriptiva: la densidad de cada banda en 2022-2023, 2024-2025 y 2026T1-T3, con el mismo normalizador. Una banda que solo es «justo bajo el umbral» en 2024-2025 debería estar alta en 2024-2025 y baja en los otros dos. IC 95 % por bootstrap de órganos (1.000 réplicas, semilla 20261021) del cociente de densidades. Salidas: salidas/05_posterior.md, 05_posterior.csv, 05_histograma.csv. Uso: sql/ejecutar.sh 05_posterior.py """ import csv import time import duckdb import numpy as np T0 = time.time() con = duckdb.connect("/w/tmp/em1b.duckdb", read_only=True) con.execute("SET threads=6; SET memory_limit='7GB'") REG = {"r22": ("2022T1", "2023T4"), "r24": ("2024T1", "2025T4"), "r26": ("2026T1", "2026T3")} BANDAS = [ # celda, filtro, etiqueta, condición, anchura, unidad, normalizador ("G1 AGE", "clase='ss' AND age", "[137.000, 140.000) (bajo 140.000: 2022-23 y 2026)", "ve >= 137000 AND ve < 140000", 3000, 1000, (150000, 200000)), ("G1 AGE", "clase='ss' AND age", "(140.000, 143.000) (bajo 143.000: 2024-25)", "ve > 140000 AND ve < 143000", 3000, 1000, (150000, 200000)), ("G2 resto sin NA", "clase='ss' AND NOT age AND coalesce(ccaa,'')<>'NA'", "[137.000, 140.000)", "ve >= 137000 AND ve < 140000", 3000, 1000, (150000, 200000)), ("G2 resto sin NA", "clase='ss' AND NOT age AND coalesce(ccaa,'')<>'NA'", "(140.000, 143.000)", "ve > 140000 AND ve < 143000", 3000, 1000, (150000, 200000)), ("G3 resto", "clase='ss' AND NOT age", "[209.000, 215.000) (bajo 215.000: 2022-23)", "ve >= 209000 AND ve < 215000", 6000, 1000, (150000, 200000)), ("G3 resto", "clase='ss' AND NOT age", "[211.000, 216.000) (bajo 216.000: 2026)", "ve >= 211000 AND ve < 216000", 5000, 1000, (150000, 200000)), ("G3 resto", "clase='ss' AND NOT age", "(216.000, 221.000) (bajo 221.000: 2024-25)", "ve > 216000 AND ve < 221000", 5000, 1000, (150000, 200000)), ("G4 obras y conc.", "clase IN ('obras','conc')", "(5.404.000, 5.538.000) (bajo 5.538.000: 2024-25)", "ve > 5404000 AND ve < 5538000", 134000, 100000, (3200000, 5000000)), ] filas = [] for feed in ("propio", "agregacion"): for celda, filtro, etq, cond, w, U, (lo, hi) in BANDAS: g = con.execute(f"""SELECT organo_clave, CASE WHEN trimestre BETWEEN '2022T1' AND '2023T4' THEN 0 WHEN trimestre BETWEEN '2024T1' AND '2025T4' THEN 1 WHEN trimestre BETWEEN '2026T1' AND '2026T3' THEN 2 END r, count(*) FILTER (WHERE {cond}) nb, count(*) FILTER (WHERE ve >= {lo} AND ve < {hi}) nn FROM lic WHERE feed='{feed}' AND ve > 0 AND {filtro} AND trimestre BETWEEN '2022T1' AND '2026T3' GROUP BY ALL""").fetchnumpy() g = {k: np.asarray(v) for k, v in g.items()} orgs = np.unique(g["organo_clave"]); oi = np.searchsorted(orgs, g["organo_clave"]) Hb = np.zeros((len(orgs), 3)); Hn = np.zeros((len(orgs), 3)) np.add.at(Hb, (oi, g["r"]), g["nb"]); np.add.at(Hn, (oi, g["r"]), g["nn"]) W = np.random.default_rng(20261021).multinomial(len(orgs), np.full(len(orgs), 1 / len(orgs)), size=1000).astype(float) dens = lambda b, n: 1000.0 * b / n * U / w with np.errstate(divide="ignore", invalid="ignore"): D = dens(Hb.sum(0), Hn.sum(0)); Dr = dens(W @ Hb, W @ Hn) q1, q2 = D[1] / D[0], D[2] / D[1] c1 = np.nanpercentile(Dr[:, 1] / Dr[:, 0], [2.5, 97.5]); c2 = np.nanpercentile(Dr[:, 2] / Dr[:, 1], [2.5, 97.5]) filas.append(dict(feed=feed, celda=celda, banda=etq, n22=int(Hb[:, 0].sum()), n24=int(Hb[:, 1].sum()), n26=int(Hb[:, 2].sum()), norm22=int(Hn[:, 0].sum()), norm24=int(Hn[:, 1].sum()), norm26=int(Hn[:, 2].sum()), d22=D[0], d24=D[1], d26=D[2], r24_22=q1, r24_22_lo=c1[0], r24_22_hi=c1[1], r26_24=q2, r26_24_lo=c2[0], r26_24_hi=c2[1])) # histograma para la figura: VE 100.000-240.000 por 1.000 €, sum./serv., feed propio, por régimen y grupo h = con.execute("""SELECT CASE WHEN age THEN 'AGE' ELSE 'resto' END grupo, CASE WHEN trimestre BETWEEN '2022T1' AND '2023T4' THEN '2022-2023' WHEN trimestre BETWEEN '2024T1' AND '2025T4' THEN '2024-2025' ELSE '2026T1-T3' END regimen, CAST(floor(ve / 1000) * 1000 AS INTEGER) tramo, count(*) n FROM lic WHERE feed='propio' AND clase='ss' AND ve >= 100000 AND ve < 240000 AND trimestre BETWEEN '2022T1' AND '2026T3' GROUP BY ALL ORDER BY 1, 2, 3""").fetchall() nn = dict(((r[0], r[1]), r[2]) for r in con.execute("""SELECT CASE WHEN age THEN 'AGE' ELSE 'resto' END, CASE WHEN trimestre BETWEEN '2022T1' AND '2023T4' THEN '2022-2023' WHEN trimestre BETWEEN '2024T1' AND '2025T4' THEN '2024-2025' ELSE '2026T1-T3' END, count(*) FROM lic WHERE feed='propio' AND clase='ss' AND ve >= 150000 AND ve < 200000 AND trimestre BETWEEN '2022T1' AND '2026T3' GROUP BY ALL""").fetchall()) with open("/w/salidas/05_histograma.csv", "w", newline="") as fh: wr = csv.writer(fh); wr.writerow(["grupo", "regimen", "tramo", "n", "n_norm", "por_mil_norm"]) for g_, r_, t_, n_ in h: wr.writerow([g_, r_, t_, n_, nn[(g_, r_)], round(1000.0 * n_ / nn[(g_, r_)], 3)]) with open("/w/salidas/05_posterior.csv", "w", newline="") as fh: wr = csv.DictWriter(fh, fieldnames=list(filas[0].keys())); wr.writeheader() for f in filas: wr.writerow({k: (f"{v:.6g}" if isinstance(v, (float, np.floating)) else v) for k, v in f.items()}) d = lambda v, k=1: "∅" if not np.isfinite(v) else f"{v:.{k}f}".replace(".", ",") md = [f"# Resultado de `05_posterior.py` (POSTERIOR, NO PRE-REGISTRADO)\n\nDuckDB {duckdb.__version__} · {time.strftime('%Y-%m-%d %H:%M:%S %Z')}\n\n", "Densidad = ‰ del normalizador (VE [150.000, 200.000) en sum./serv.; [3.200.000, 5.000.000) en obras) por 1.000 € (o 100.000 €) de anchura.\n\n", "| feed | celda | banda | n 2022-23 / 2024-25 / 2026 | d 2022-23 | d 2024-25 | d 2026 | 2024-25 ÷ 2022-23 [IC] | 2026 ÷ 2024-25 [IC] |\n|---|---|---|---|---|---|---|---|---|\n"] for f in filas: md.append(f"| {f['feed']} | {f['celda']} | {f['banda']} | {f['n22']} / {f['n24']} / {f['n26']} | {d(f['d22'])} | {d(f['d24'])} | {d(f['d26'])} | " f"{d(f['r24_22'], 2)} [{d(f['r24_22_lo'], 2)}; {d(f['r24_22_hi'], 2)}] | {d(f['r26_24'], 2)} [{d(f['r26_24_lo'], 2)}; {d(f['r26_24_hi'], 2)}] |\n") md.append(f"\n_Tiempo total: {time.time()-T0:.0f} s_\n") open("/w/salidas/05_posterior.md", "w").write("".join(md))