"""SUB1 · 31 · compara la principal (interno/ea.parquet) con la segunda (interno/impl2_ea.parquet). DEFINICIONES_PREVIAS §7c.""" import duckdb, json x = duckdb.connect().execute x("CREATE TABLE a AS SELECT ente, per::INT anio, n_emp, n_w, k, imp_c, imp_c_doble, r, e, zd, zd_nw, hecho, elig FROM '/w/interno/ea.parquet'") x("CREATE TABLE b AS SELECT ente, anio::INT anio, n_emp, n_w, k, imp_c, imp_c_doble, r, e, zd, zd_nw, hecho, elig FROM '/w/interno/impl2_ea.parquet'") R = {} R['filas'] = x("SELECT (SELECT count(*) FROM a), (SELECT count(*) FROM b), (SELECT count(*) FROM a JOIN b USING (ente, anio))").fetchone() R['n_k_distintos'] = x("SELECT count(*) FROM a JOIN b USING (ente, anio) WHERE a.n_emp<>b.n_emp OR a.n_w<>b.n_w OR a.k<>b.k").fetchone()[0] R['importes_distintos_rel_1e9'] = x("SELECT count(*) FROM a JOIN b USING (ente, anio) WHERE abs(a.imp_c-b.imp_c) > 1e-9*greatest(abs(a.imp_c),1) OR abs(coalesce(a.imp_c_doble,0)-coalesce(b.imp_c_doble,0)) > 1e-9*greatest(abs(a.imp_c),1)").fetchone()[0] R['r_rel_mas_0005'] = x("SELECT count(*) FROM a JOIN b USING (ente, anio) WHERE a.r IS NOT NULL AND abs(a.r-b.r) > 0.005*greatest(abs(a.r),1e-12)").fetchone()[0] R['e_rel_mas_0005'] = x("SELECT count(*), count(*) FILTER (WHERE (a.e IS NULL) <> (b.e IS NULL) OR abs(a.e-b.e) > 0.005*a.e) FROM a JOIN b USING (ente, anio) WHERE a.e IS NOT NULL OR b.e IS NOT NULL").fetchone() R['zd_abs_mas_001'] = x("SELECT count(*) FILTER (WHERE abs(a.zd-b.zd) > 0.01), count(*) FILTER (WHERE abs(a.zd_nw-b.zd_nw) > 0.01), max(abs(a.zd_nw-b.zd_nw)) FROM a JOIN b USING (ente, anio) WHERE a.zd IS NOT NULL").fetchone() R['elig_distinto'] = x("SELECT count(*) FROM a JOIN b USING (ente, anio) WHERE a.elig<>b.elig").fetchone()[0] nel = x("SELECT count(*) FROM a WHERE elig").fetchone()[0] nh = x("SELECT count(*) FROM a JOIN b USING (ente, anio) WHERE a.elig AND a.hecho<>b.hecho").fetchone()[0] R['hecho_distinto'] = [nh, nel, nh / nel] R['hechos'] = x("SELECT (SELECT count(*) FROM a WHERE hecho), (SELECT count(*) FROM b WHERE hecho)").fetchone() ok = R['n_k_distintos'] == 0 and R['importes_distintos_rel_1e9'] == 0 and R['r_rel_mas_0005'] == 0 and R['e_rel_mas_0005'][1] == 0 and nh / nel <= 0.005 R['veredicto'] = 'pasa' if ok else 'no pasa' json.dump(R, open('/w/salidas/31_cotejo_impl2.json', 'w'), ensure_ascii=False, indent=1, default=str); print(R)