"""E-M2 · las dos implementaciones, al dato: resumen por fuente y año (E0 y E4) y serie a serie (E0, y E0b si está). Uso: python3 /w/sql/comparar.py [e0b] (lee /w/tmp/medir.duckdb y /w/comparar/impl2_*/)""" import sys import duckdb E0B = len(sys.argv) > 1 and sys.argv[1] == "e0b" con = duckdb.connect("/w/tmp/medir.duckdb", read_only=True) con.execute("SET memory_limit='6GB'; SET threads=4; SET temp_directory='/w/tmp/duckdb_tmp'") out = ["# E-M2 · comparación de las dos implementaciones\n"] def tabla(t, sql): r = con.execute(sql); cols = [d[0] for d in r.description]; f = r.fetchall() out.append(f"\n## {t}\n\n| " + " | ".join(cols) + " |\n|" + "|".join("---" for _ in cols) + "|\n") out.extend("| " + " | ".join("∅" if v is None else str(v) for v in x) + " |\n" for x in f) return f for et, mio, suyo in (("E0", "p_lift", "/w/comparar/impl2_e0/resumen.csv"),) + ((("E0b", "p_lift_e0b", "/w/comparar/impl2_e0b/resumen_e0b.csv"),) if E0B else ()): tabla(f"Resumen {et} por fuente y año: diferencias (vacío = ninguna)", f""" WITH a AS (SELECT fuente, anio, series, series_pasaron, control, control_pasaron FROM {mio} WHERE anio IS NOT NULL AND series + control > 0), b AS (SELECT * FROM read_csv('{suyo}', header = true)) SELECT coalesce(a.fuente, b.fuente) fuente, coalesce(a.anio, b.anio) anio, a.series, b.series s2, a.series_pasaron, b.series_pasaron sp2, a.control, b.control c2, a.control_pasaron, b.control_pasaron cp2 FROM a FULL JOIN b ON a.fuente = b.fuente AND a.anio = b.anio WHERE a.series IS DISTINCT FROM b.series OR a.series_pasaron IS DISTINCT FROM b.series_pasaron OR a.control IS DISTINCT FROM b.control OR a.control_pasaron IS DISTINCT FROM b.control_pasaron""") tabla(f"Resumen {et}: filas comparadas", f"SELECT count(*) fuente_anios, sum(series) series, sum(series_pasaron) pasaron, sum(control) control, sum(control_pasaron) control_pasaron FROM {mio} WHERE anio IS NOT NULL") tabla("Resumen E4: diferencias", """ WITH a AS (SELECT fuente, anio, count(*) FILTER (WHERE tipo_unidad = 'serie') series, count(*) FILTER (WHERE tipo_unidad = 'serie' AND e4) series_pasaron, count(*) FILTER (WHERE tipo_unidad = 'control') control, count(*) FILTER (WHERE tipo_unidad = 'control' AND e4) control_pasaron FROM p_var GROUP BY 1, 2), b AS (SELECT * FROM read_csv('/w/comparar/impl2_e0/resumen_e4.csv', header = true)) SELECT coalesce(a.fuente, b.fuente) fuente, coalesce(a.anio, b.anio) anio, a.series_pasaron, b.series_pasaron sp2, a.control_pasaron, b.control_pasaron cp2 FROM a FULL JOIN b ON a.fuente = b.fuente AND a.anio = b.anio WHERE (a.series IS DISTINCT FROM b.series OR a.series_pasaron IS DISTINCT FROM b.series_pasaron OR a.control IS DISTINCT FROM b.control OR a.control_pasaron IS DISTINCT FROM b.control_pasaron) AND coalesce(a.series, 0) + coalesce(a.control, 0) > 0""") # Serie a serie: la clave de impl2 seudonimiza los órganos con NIF de persona ('PF:' + 16 de md5) con.execute("""CREATE TEMP TABLE b AS SELECT * FROM read_parquet('/w/comparar/impl2_e0/series.parquet') WHERE year(fecha_fin) <= 2024""") con.execute("""CREATE TEMP TABLE a AS SELECT u.fuente, CASE WHEN 'PF:' || left(md5(u.organo_clave), 16) IN (SELECT organo_clave FROM b) THEN 'PF:' || left(md5(u.organo_clave), 16) ELSE u.organo_clave END organo_clave, u.empresa_clave, u.clase, u.objeto_norm, u.fecha_ini, u.fecha_fin, u.tipo_unidad = 'serie' marcada, e.pasa, e.rama FROM p_u u JOIN p_et e USING (tipo_unidad, unidad)""") k = "fuente, organo_clave, empresa_clave, clase, objeto_norm, fecha_ini, fecha_fin, marcada" tabla("Serie a serie (E0): unidades y diferencias", f""" SELECT (SELECT count(*) FROM a) unidades_1, (SELECT count(*) FROM b) unidades_2, (SELECT count(*) FROM a ANTI JOIN b USING ({k})) solo_en_1, (SELECT count(*) FROM b ANTI JOIN a USING ({k})) solo_en_2, (SELECT count(*) FROM a JOIN b USING ({k}) WHERE a.pasa IS DISTINCT FROM b.pasa) pasa_distinta, (SELECT count(*) FROM a JOIN b USING ({k}) WHERE a.pasa AND a.rama IS DISTINCT FROM b.rama) rama_distinta""") tabla("Serie a serie (E0): las primeras diferencias de rama", f""" SELECT a.fuente, a.objeto_norm, a.rama, b.rama rama_2 FROM a JOIN b USING ({k}) WHERE a.pasa AND a.rama IS DISTINCT FROM b.rama LIMIT 10""") if E0B: con.execute("CREATE TEMP TABLE b2 AS SELECT * FROM read_parquet('/w/comparar/impl2_e0b/series_e0b.parquet') WHERE year(fecha_fin) <= 2024") con.execute("""CREATE TEMP TABLE a2 AS SELECT u.fuente, CASE WHEN 'PF:' || left(md5(u.organo_clave), 16) IN (SELECT organo_clave FROM b2) THEN 'PF:' || left(md5(u.organo_clave), 16) ELSE u.organo_clave END organo_clave, u.empresa_clave, u.clase, u.objeto_norm, u.fecha_ini, u.fecha_fin, u.tipo_unidad = 'serie' marcada, e.pasa, e.rama FROM p_u u JOIN p_et_e0b e USING (tipo_unidad, unidad)""") tabla("Serie a serie (E0b): unidades y diferencias", f""" SELECT (SELECT count(*) FROM a2) unidades_1, (SELECT count(*) FROM b2) unidades_2, (SELECT count(*) FROM a2 JOIN b2 USING ({k}) WHERE a2.pasa IS DISTINCT FROM b2.pasa) pasa_distinta, (SELECT count(*) FROM a2 JOIN b2 USING ({k}) WHERE a2.pasa AND a2.rama IS DISTINCT FROM b2.rama) rama_distinta""") open("/w/salidas/40_comparar.md", "w").write("".join(out)) print("".join(out))