"""TOT-H: segunda implementación de los conjuntos: en SQL, sin el JSON de la medida, salvo F1-F4, comprobados aparte con 0 diferencias (h1_chk2). Compara por id.""" import sys, re, csv, json, collections; sys.path.insert(0, "/lect") from comun import * c = con(); x = c.execute D = "/data/unified_20261103" R = json.load(open("/o/h1_filas.json")); no_adj = set(json.load(open("/o/tot_cyl.json"))) x("CREATE TEMP TABLE r (id VARCHAR, bn VARCHAR, F VARCHAR, h1 BOOLEAN, h3 BOOLEAN, fuera VARCHAR, base DOUBLE, tc VARCHAR, ob VARCHAR, ex VARCHAR, tram VARCHAR)") x("INSERT INTO r SELECT * FROM (SELECT unnest(?) id, unnest(?) bn, unnest(?) F, unnest(?) h1, unnest(?) h3, unnest(?) fuera, unnest(?) base, unnest(?) tc, unnest(?) ob, unnest(?) ex, unnest(?) tram)", [[r[k] for r in R] for k in ("id",)] + [[r["bn"] for r in R], [(r["F"] or "")[:10] for r in R], [bool(r["h1"]) for r in R], [bool(r["h3"]) for r in R], [r["fuera"] or "" for r in R], [r["base"] for r in R], [r["tc"] or "" for r in R], [r["objeto"] or "" for r in R], [r["exp"] or "" for r in R], [r["tram"] or "" for r in R]]) print("filas", x("SELECT count(*), sum(h1::INT), sum(h3::INT) FROM r").fetchone(), flush=True) x(r"""CREATE TEMP TABLE q AS SELECT *, regexp_replace(regexp_replace(strip_accents(lower(ob)), '[^\x00-\x7F]', '', 'g'), '[^a-z0-9]+', ' ', 'g') t, regexp_replace(regexp_replace(strip_accents(lower(ex)), '[^\x00-\x7F]', '', 'g'), '[^a-z0-9]+', ' ', 'g') te FROM r""") x(r"""CREATE TEMP TABLE q2 AS SELECT * FROM q WHERE left(fuera, 2) NOT IN ('F1','F2','F3','F4') AND NOT (tc = 'Patrimonial' OR regexp_matches(t, '(^| )(venta|ventas|enajenacion)( |$)|adquisicion de (un |una |el |la |los |las )?(inmueble|finca|solar|parcela|vivienda|local)')) AND NOT (coalesce(base, 2) <= 1 OR contains(t, 'conclusion de acuerdo marco')) AND NOT contains(t || ' ' || te, 'prorrog') AND NOT (tram = 'emergencia' OR regexp_matches(t, 'emergenc|covid|coronavirus|(^| )dana( |$)|paiporta|riada|inundaci|nevada|filomena|temporal de'))""") print("con los filtros de la segunda vuelta", x("SELECT sum(h1::INT), sum(h3::INT) FROM q2").fetchone(), flush=True) x(f"""CREATE TEMP TABLE u AS SELECT id_canonical id, rol_am, procedimiento FROM read_parquet('{D}/source=*/**/*.parquet', hive_partitioning=1, union_by_name=1) WHERE id_canonical IN (SELECT id FROM q2)""") x("CREATE TEMP TABLE q3 AS SELECT q2.* FROM q2 LEFT JOIN (SELECT id, any_value(rol_am) rol_am, any_value(procedimiento) procedimiento FROM u GROUP BY 1) u USING (id) WHERE NOT (coalesce(u.rol_am,'') IN ('basado','basado_sda') OR coalesce(u.procedimiento,'') = 'Acuerdo_marco')") csv.field_size_limit(10**9) x("CREATE TEMP TABLE ma AS SELECT empresa_norm, borme_norm FROM read_parquet('/ca/mapa_norm.parquet')") x("""CREATE TEMP TABLE ac AS SELECT m.borme_norm bn, a.fecha_borme::VARCHAR fb, a.actos_detalle d FROM read_csv('/ca/h1_actos.csv', header=true, all_varchar=true, max_line_size=20000000) a JOIN ma m ON m.empresa_norm = a.empresa_norm WHERE contains(a.actos_detalle, 'Situación concursal=')""") x(r"""CREATE TEMP TABLE fe AS SELECT bn, left(fb, 10) f FROM ac UNION ALL SELECT bn, f FROM (SELECT bn, unnest(regexp_extract_all(regexp_extract(d, 'Situación concursal=[^|]*'), '\((\d{4}-\d{2}-\d{2})\)', 1)) f FROM ac)""") x("""CREATE TEMP TABLE q4 AS SELECT q3.* FROM q3 WHERE (SELECT max(f) FROM fe WHERE fe.bn = q3.bn AND fe.f <= q3.F) >= (q3.F::DATE - INTERVAL 1095 DAY)::DATE::VARCHAR""") x("CREATE TEMP TABLE fin AS SELECT * FROM q4 WHERE id NOT IN (SELECT unnest(?))", [sorted(no_adj)]) print("final", x("SELECT sum(h1::INT), sum(h3::INT), count(DISTINCT bn) FILTER (WHERE h1), count(DISTINCT bn) FILTER (WHERE h3) FROM fin").fetchone(), flush=True) for h in ("h1", "h3"): a = {r["id"] for r in json.load(open(f"/o/tot_{h}_conjunto.json"))} - no_adj b = {r[0] for r in x(f"SELECT id FROM fin WHERE {h}").fetchall()} print(h, "primera", len(a), "segunda", len(b), "solo en la primera", len(a - b), "solo en la segunda", len(b - a), sorted(b - a)[:2], sorted(a - b)[:2], flush=True)