"""LOB1 · 61 · cifras de referencia (DuckDB) para comparar con la segunda implementación.""" import sys, json; sys.path.insert(0, '/w/py'); from comun import * con = abrir(ro=True); x = con.execute REG = {'GENCAT': 'CAT-R', 'AYTO_MAD': 'MAD-R', 'CAM': 'CAM-R'}; TER = {'GENCAT': 'CT', 'AYTO_MAD': 'MD', 'CAM': 'MD'} out = {'casado': {}, 'c': {}, 'a': {}} for R in REG.values(): out['casado'][R] = sorted(r[0] for r in x(f"SELECT DISTINCT nif FROM casado WHERE registro = '{R}'").fetchall()) for X, R in REG.items(): out['c'][X] = x(f"""SELECT count(DISTINCT emp), count(DISTINCT emp) FILTER (WHERE emp IN (SELECT nif FROM casado WHERE registro='{R}')), count(*) FROM lot WHERE x='{X}' AND NOT menor""").fetchone() out['a'][X] = x(f"""WITH f AS (SELECT emp, count(*) n, count(*) FILTER (WHERE x='{X}') k FROM lot WHERE ccaa='{TER[X]}' AND NOT menor GROUP BY 1 HAVING count(*) >= 3) SELECT emp IN (SELECT nif FROM casado WHERE registro='{R}') r, count(*), sum(k), sum(n) FROM f GROUP BY 1 ORDER BY 1""").fetchall() b = json.load(open('/w/interno/40_res.json'))['CAT_primaria']; out['b'] = [b['pre'], b['post'], b['ctrl_pre'], b['ctrl_post'], b['pares']] out['n_lot'] = x("SELECT ccaa, count(*) FROM lot GROUP BY 1 ORDER BY 1").fetchall() json.dump(out, open('/w/interno/61_ref.json', 'w'), default=list); log({k: v for k, v in out.items() if k != 'casado'}, {k: len(v) for k, v in out['casado'].items()})