"""MUNI-1 · A4 · lo que cambia en el paso 3b (indicadores) entre main (tmp/m) y la rama (tmp/r) sobre la construcción publicada.""" import duckdb, json c = duckdb.connect(); x = c.execute m, r = '/w/tmp/m/indicadores/', '/w/tmp/r/indicadores/' R = {} for t in ('municipios_organos', 'organos', 'municipios_anio', 'municipios_fuentes_anio', 'organos_web', 'descriptores_organo_anio'): R[f'filas_{t}'] = x(f"SELECT (SELECT count(*) FROM '{m}{t}.parquet'), (SELECT count(*) FROM '{r}{t}.parquet')").fetchone() R['mo_por_via'] = {k: x(f"SELECT via, fuente IS NULL, count(*) FROM '{p}municipios_organos.parquet' GROUP BY ALL ORDER BY ALL").fetchall() for k, p in (('m', m), ('r', r))} x(f"CREATE VIEW a AS SELECT organo_clave, municipio_ine FROM '{m}municipios_organos.parquet' WHERE fuente IS NULL") x(f"CREATE VIEW b AS SELECT organo_clave, municipio_ine FROM '{r}municipios_organos.parquet' WHERE fuente IS NULL") R['mo_general'] = dict(zip(('igual', 'distinto', 'solo_m', 'solo_r'), x("""SELECT count(*) FILTER (WHERE a.municipio_ine = b.municipio_ine), count(*) FILTER (WHERE a.municipio_ine <> b.municipio_ine), count(*) FILTER (WHERE b.organo_clave IS NULL), count(*) FILTER (WHERE a.organo_clave IS NULL) FROM a FULL JOIN b USING (organo_clave)""").fetchone())) R['mo_por_fuente_igual'] = x(f"""SELECT (SELECT count(*) FROM (SELECT * FROM '{m}municipios_organos.parquet' WHERE fuente IS NOT NULL EXCEPT SELECT * FROM '{r}municipios_organos.parquet' WHERE fuente IS NOT NULL)), (SELECT count(*) FROM (SELECT * FROM '{r}municipios_organos.parquet' WHERE fuente IS NOT NULL EXCEPT SELECT * FROM '{m}municipios_organos.parquet' WHERE fuente IS NOT NULL))""").fetchone() R['organos_municipio_ine'] = dict(zip(('igual', 'distinto', 'solo_m', 'solo_r'), x(f"""SELECT count(*) FILTER (WHERE a.municipio_ine = b.municipio_ine), count(*) FILTER (WHERE a.municipio_ine <> b.municipio_ine), count(*) FILTER (WHERE a.municipio_ine IS NOT NULL AND b.municipio_ine IS NULL), count(*) FILTER (WHERE a.municipio_ine IS NULL AND b.municipio_ine IS NOT NULL) FROM '{m}organos.parquet' a JOIN '{r}organos.parquet' b USING (organo_clave)""").fetchone())) for t, k in (('municipios_anio', 'municipio_ine, anio'), ('municipios_fuentes_anio', 'municipio_ine, fuente, anio')): cols = [d[0] for d in x(f"SELECT * FROM '{m}{t}.parquet' LIMIT 0").description] R[f'{t}_claves'] = x(f"""SELECT (SELECT count(*) FROM (SELECT {k} FROM '{m}{t}.parquet' EXCEPT SELECT {k} FROM '{r}{t}.parquet')), (SELECT count(*) FROM (SELECT {k} FROM '{r}{t}.parquet' EXCEPT SELECT {k} FROM '{m}{t}.parquet'))""").fetchone() R[f'{t}_filas_distintas'] = x(f"""SELECT count(*) FROM (SELECT * FROM '{r}{t}.parquet' EXCEPT SELECT * FROM '{m}{t}.parquet')""").fetchone()[0] R[f'{t}_2024_menores'] = x(f"""SELECT (SELECT sum({'menores' if t == 'municipios_anio' else 'menores'}) FROM '{m}{t}.parquet' WHERE anio = 2024), (SELECT sum(menores) FROM '{r}{t}.parquet' WHERE anio = 2024)""").fetchone() # las demás tablas del 3b no deben cambiar for t in ('descriptores_organo_anio', 'm3_organo_anio', 'c1_organo_anio', 'c2_organo_anio', 'organos_web', 'menores_fuentes_organo_anio'): R[f'igual_{t}'] = x(f"""SELECT (SELECT count(*) FROM (SELECT * FROM '{m}{t}.parquet' EXCEPT SELECT * FROM '{r}{t}.parquet')), (SELECT count(*) FROM (SELECT * FROM '{r}{t}.parquet' EXCEPT SELECT * FROM '{m}{t}.parquet'))""").fetchone() json.dump(R, open('/w/salidas/45_comparar_3b.json', 'w'), indent=1, default=str) print(json.dumps(R, default=str))