"""PH1 · 21 · concentración del importe a sucesoras (mismo órgano) y por estructura/personal. interno/21_importe.json (agregados).""" import sys, json; sys.path.insert(0, '/w/py') from comun import * con = abrir(ro=True); x = con.execute P = "(p.np20 > 0 OR p.dom)" x(f"""CREATE TEMP TABLE ti AS SELECT t.*, CASE WHEN p.consoc OR p.np20 >= 3 THEN 'estructura' WHEN p.np20 BETWEEN 1 AND 2 THEN 'personal' ELSE 'solo_domicilio' END clase FROM terna t JOIN par2 p USING (A, D, fuente, B) WHERE {P}""") R = {'total': x("SELECT sum(imp), sum(n_adj), count(*) FROM ti").fetchone(), 'top10_ternas_pct_importe': x("SELECT 100 * sum(imp) / (SELECT sum(imp) FROM ti) FROM (SELECT imp FROM ti ORDER BY imp DESC NULLS LAST LIMIT 10)").fetchone()[0], 'top1_pct': x("SELECT 100 * max(imp) / sum(imp) FROM ti").fetchone()[0], 'mediana_importe_terna': x("SELECT median(imp) FROM ti").fetchone()[0], 'por_clase': x("SELECT clase, count(DISTINCT (A,B)), sum(n_adj), sum(imp) FROM ti GROUP BY 1 ORDER BY 1").fetchall(), 'por_tipo_org': x("SELECT coalesce(o.tipo,'(sin tipo)'), count(DISTINCT t.O), sum(t.n_adj), sum(t.imp) FROM ti t LEFT JOIN (SELECT organo_clave, any_value(tipo) tipo FROM '/d/unified_20261018.identidad/organos_tipo.parquet' GROUP BY 1) o ON o.organo_clave = t.O GROUP BY 1 ORDER BY 3 DESC").fetchall(), 'menores_vs_no': x("SELECT f.es_menor, count(DISTINCT f.source||f.id_canonical), sum(f.imp) FROM bpost f JOIN ti t ON t.A=f.A AND t.D=f.D AND t.fuente=f.fuente AND t.B=f.B AND t.O=f.O GROUP BY 1").fetchall()} json.dump(R, open('/w/interno/21_importe.json', 'w'), indent=1, default=str); print(json.dumps(R, default=str))