"""PH1 · 10 · base SIN cruces entre sociedades (antes del sellado): universo de adjudicaciones (menores y no menores, 2012-2025, NOT es_copia), sociedades de la celda del casado, tramos de cargo (como RS1) y de domicilio, hechos de salida (BORME), constituciones (regla E-M7) y sancionadas (D4c + P8, como RS1). Escribe /w/tmp/ph1.duckdb.""" import sys, os, re, json, unicodedata; sys.path.insert(0, '/w/py') from comun import * os.makedirs('/w/tmp/duck', exist_ok=True) if os.path.exists('/w/tmp/ph1.duckdb'): os.remove('/w/tmp/ph1.duckdb') con = abrir(); x = con.execute D = '/d/unified_20261018'; I = D + '.identidad/' # 1. universo de adjudicaciones x(f"""CREATE TABLE l AS SELECT u.source, u.id_canonical, u.organo_nif, u.organo_nombre, u.dir3, u.cpv_division cpv2, u.anio_ref anio, coalesce(u.es_menor, FALSE) es_menor, coalesce(u.fecha_adj, u.fecha_formalizacion) F, CASE WHEN coalesce(u.correccion_imp_adj, '') IN ('repetido', 'inverosimil') THEN NULL ELSE coalesce(u.imp_adj_eur_comparable, u.imp_adj_eur_sin_iva, u.imp_adj_eur) END imp, u.adjudicatario_nif_raw, u.adjudicatario_nif, u.adjudicatario_nombre FROM read_parquet('{D}/*/*/*.parquet', hive_partitioning=1, union_by_name=1) u LEFT JOIN read_parquet('{D}.vinculos/filas.parquet') v ON v.source = u.source AND v.id_canonical = u.id_canonical WHERE u.unidad = 'adjudicacion' AND NOT coalesce(u.es_menor_dudoso, FALSE) AND u.copia_de IS NULL AND coalesce(u.correccion_imp_adj, '') <> 'copia_otra_fuente' AND NOT coalesce(u.anulado_en_origen, FALSE) AND coalesce(v.cuenta, TRUE) AND u.anio_ref BETWEEN 2012 AND 2025""") x("CHECKPOINT") x(f"""CREATE TABLE l2 AS SELECT l.source, l.id_canonical, o.organo_clave, l.cpv2, l.anio, l.es_menor, l.F, l.imp, a.empresa_clave, a.pf FROM l LEFT JOIN (SELECT organo_nif, organo_nombre, dir3, any_value(organo_clave) organo_clave FROM read_parquet('{I}organos_filas.parquet') GROUP BY ALL) o ON o.organo_nif IS NOT DISTINCT FROM l.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM l.organo_nombre AND o.dir3 IS NOT DISTINCT FROM l.dir3 LEFT JOIN (SELECT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, any_value(empresa_clave) empresa_clave, bool_or(coalesce(persona_fisica, FALSE)) pf FROM read_parquet('{I}adjudicatarios_filas.parquet') GROUP BY ALL) a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM l.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM l.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM l.adjudicatario_nombre""") x(f"""CREATE TEMP TABLE us AS SELECT DISTINCT ute_clave, socio_nif FROM read_parquet('{I}ute_socios.parquet') WHERE socio_nif IS NOT NULL AND NOT coalesce(socio_persona_fisica, FALSE)""") x("CREATE TEMP TABLE usn AS SELECT ute_clave, count(*) n_socios FROM us GROUP BY 1") x("""CREATE TABLE u AS SELECT DISTINCT l2.source, l2.id_canonical, l2.organo_clave, l2.cpv2, l2.anio, l2.es_menor, l2.F, l2.imp / coalesce(usn.n_socios, 1) imp, coalesce(us.socio_nif, l2.empresa_clave) emp, us.ute_clave IS NOT NULL via_ute FROM l2 LEFT JOIN us ON us.ute_clave = l2.empresa_clave LEFT JOIN usn ON usn.ute_clave = l2.empresa_clave WHERE l2.empresa_clave IS NOT NULL AND NOT coalesce(l2.pf, FALSE) AND l2.organo_clave IS NOT NULL""") x("DROP TABLE l; DROP TABLE l2; CHECKPOINT") log('u', x("SELECT count(*), count(DISTINCT source||'|'||id_canonical), count(DISTINCT emp), sum(es_menor::INT), count(F), count(cpv2) FROM u").fetchone()) # 2. sociedades de la celda x("""CREATE TABLE sat AS SELECT s.*, e.etiqueta em5 FROM '/et/em3/salidas/unified_20261018.borme/empresas.parquet' s LEFT JOIN '/et/em5/salidas/17_etiquetas_INTERNO.parquet' e USING (empresa_clave)""") x("""CREATE TABLE soc AS SELECT empresa_clave emp, borme_norm bn, via, em5, fecha_extincion, provincia FROM sat WHERE via IN ('v1_exacto','v2_historico') AND utilizable AND coalesce(em5,'') <> 'R'""") x("CREATE TABLE bnset AS SELECT DISTINCT bn FROM soc") log('soc', x("SELECT count(*), count(DISTINCT bn) FROM soc").fetchone()) # 3. cargos y tramos (idéntico a RS1 10_base §3-4) x(f"""CREATE TABLE car0 AS SELECT clave_borme(empresa) bn, CAST(fecha_borme AS DATE) d, tipo_acto, persona_hash h, pdf_filename, num_entrada, code, {CLASE_SQL} clase FROM (SELECT *, regexp_replace(upper(strip_accents(cargo)),'[^A-Z0-9]','','g') code FROM '/b/parse/borme_cargos.parquet' WHERE persona_hash IS NOT NULL) WHERE {CLASE_SQL} IS NOT NULL""") x("CREATE TABLE nemp AS SELECT h, count(DISTINCT bn) n_emp FROM car0 GROUP BY 1") x("CREATE TABLE car AS SELECT c.* FROM car0 c JOIN bnset USING (bn)") x("DROP TABLE car0") log('car (celda)', x("SELECT count(*), count(DISTINCT h), count(DISTINCT bn) FROM car").fetchone()) x("""CREATE TABLE ev AS SELECT bn, h, clase, d, bool_or(tipo_acto IN ('nombramiento','reeleccion')) op, bool_or(tipo_acto IN ('cese','revocacion','cancelacion')) cl FROM car GROUP BY ALL""") x(f"""CREATE TABLE tramo AS WITH e AS (SELECT *, CASE WHEN op THEN 1 ELSE 0 END st FROM ev), p AS (SELECT *, lag(st) OVER (PARTITION BY bn, h, clase ORDER BY d) prev FROM e), s AS (SELECT *, CASE WHEN st = 1 AND coalesce(prev, 0) = 0 THEN 1 WHEN st = 0 AND prev IS NULL THEN 1 ELSE 0 END ini FROM p), g AS (SELECT *, sum(ini) OVER (PARTITION BY bn, h, clase ORDER BY d ROWS UNBOUNDED PRECEDING) gid FROM s), a AS (SELECT bn, h, clase, gid, arg_min(st, d) st0, min(d) d0, min(d) FILTER (WHERE st = 0) dcl, max(d) FILTER (WHERE st = 1) dop_ult FROM g WHERE gid > 0 GROUP BY ALL) SELECT a.bn, a.h, a.clase, CASE WHEN st0 = 0 THEN DATE '{INI}' ELSE d0 END desde, CASE WHEN st0 = 0 THEN d0 WHEN dcl IS NOT NULL THEN dcl ELSE least(coalesce(x.fext, DATE '{FIN_CARGOS}'), DATE '{FIN_CARGOS}') END hasta, (st0 = 0) censura_izq, (st0 = 1 AND dcl IS NULL) abierto, dop_ult FROM a LEFT JOIN (SELECT bn, min(fecha_extincion) fext FROM soc GROUP BY 1) x USING (bn)""") x("""CREATE TABLE perd AS SELECT clave_borme(empresa) bn, CAST(fecha_borme AS DATE) d FROM '/b/parse/borme_empresas.parquet' WHERE actos ILIKE '%rdida del car%cter de unipersonalidad%' AND clave_borme(empresa) IN (SELECT bn FROM bnset)""") x("""UPDATE tramo t SET hasta = least(t.hasta, coalesce((SELECT min(t2.desde) FROM tramo t2 WHERE t2.bn = t.bn AND t2.clase = 'soc' AND t2.h <> t.h AND t2.desde > t.desde), t.hasta), coalesce((SELECT min(p.d) FROM perd p WHERE p.bn = t.bn AND p.d > t.desde), t.hasta)), abierto = abierto AND hasta = t.hasta WHERE t.clase = 'soc'""") log('tramos', x("SELECT clase, count(*), sum(censura_izq::INT), sum(abierto::INT), count(DISTINCT bn) FROM tramo GROUP BY 1").fetchall()) # 4. BORME de empresas de la celda: domicilios, salidas, sucesiones, constituciones x("""CREATE TABLE be AS SELECT clave_borme(empresa) bn, CAST(fecha_borme AS DATE) d, '|' || coalesce(actos, '') || '|' a, domicilio, fecha_constitucion, pdf_filename, num_entrada FROM '/b/parse/borme_empresas.parquet' WHERE clave_borme(empresa) IN (SELECT bn FROM bnset)""") x("""CREATE TABLE dom0 AS SELECT clave_borme(empresa) bn, CAST(fecha_borme AS DATE) d, regexp_replace(upper(strip_accents(domicilio)),'[^A-Z0-9]','','g') k FROM '/b/parse/borme_empresas.parquet' WHERE coalesce(domicilio,'') <> ''""") x("CREATE TABLE domn AS SELECT k, count(DISTINCT bn) n_soc FROM dom0 WHERE length(k) >= 8 GROUP BY 1") x("CREATE TABLE dom1 AS SELECT d.bn, d.d, max(d.k) k FROM dom0 d JOIN bnset USING (bn) GROUP BY 1, 2") x("DROP TABLE dom0") x(f"""CREATE TABLE dtramo AS WITH o AS (SELECT *, lag(k) OVER (PARTITION BY bn ORDER BY d) kprev FROM dom1), s AS (SELECT *, sum(CASE WHEN kprev IS NULL OR kprev <> k THEN 1 ELSE 0 END) OVER (PARTITION BY bn ORDER BY d ROWS UNBOUNDED PRECEDING) gid FROM o), a AS (SELECT bn, gid, any_value(k) k, min(d) desde FROM s GROUP BY 1, 2) SELECT a.bn, a.k, a.desde, coalesce(lead(a.desde) OVER (PARTITION BY a.bn ORDER BY a.desde), least(coalesce(x.fext, DATE '{FIN_BORME}'), DATE '{FIN_BORME}')) hasta FROM a LEFT JOIN (SELECT bn, min(fecha_extincion) fext FROM soc GROUP BY 1) x USING (bn)""") log('dtramos', x("SELECT count(*), count(DISTINCT bn) FROM dtramo").fetchone()) x("""CREATE TABLE salida_ev AS SELECT bn, d, pdf_filename, num_entrada, a LIKE '%|Disolución|%' dis, a LIKE '%|Extinción|%' ext, a LIKE '%|Auto de apertura de la fase de liquidación|%' liq, (a LIKE '%|Fusión por absorción|%' OR a LIKE '%|Fusión|%' OR a LIKE '%|Escisión|%') suc FROM be WHERE a LIKE '%|Disolución|%' OR a LIKE '%|Extinción|%' OR a LIKE '%|Auto de apertura de la fase de liquidación|%' OR a LIKE '%|Fusión por absorción|%' OR a LIKE '%|Fusión|%' OR a LIKE '%|Escisión|%'""") # hecho de salida BORME de cada sociedad: primera publicación de disolución, extinción o apertura de liquidación x("""CREATE TABLE salida AS WITH f AS (SELECT bn, min(d) FILTER (WHERE dis OR ext OR liq) D FROM salida_ev GROUP BY 1) SELECT f.bn, f.D, (SELECT string_agg(DISTINCT t, '+' ORDER BY t) FROM (SELECT CASE WHEN e.dis THEN 'dis' END t FROM salida_ev e WHERE e.bn = f.bn AND e.d = f.D UNION ALL SELECT CASE WHEN e.ext THEN 'ext' END FROM salida_ev e WHERE e.bn = f.bn AND e.d = f.D UNION ALL SELECT CASE WHEN e.liq THEN 'liq' END FROM salida_ev e WHERE e.bn = f.bn AND e.d = f.D) WHERE t IS NOT NULL) tipo, EXISTS (SELECT 1 FROM salida_ev e WHERE e.bn = f.bn AND e.suc AND e.d BETWEEN f.D - 365 AND f.D + 365) sucesion_declarada FROM f WHERE f.D IS NOT NULL""") log('salida', x("SELECT count(*), sum(sucesion_declarada::INT), count(*) FILTER (WHERE D BETWEEN DATE '2012-01-01' AND DATE '2025-12-31') FROM salida").fetchone()) # constituciones (regla E-M7 02_base) x("""CREATE TABLE cf AS SELECT bn, d d_pub, regexp_extract(fecha_constitucion, '^\\s*(\\d{1,2})\\.(\\d{1,2})\\.(\\d{2,4})', ['d','m','y']) p, pdf_filename, num_entrada FROM be WHERE a LIKE '%|Constitución|%'""") x("""CREATE TABLE cf2 AS SELECT bn, d_pub, pdf_filename, num_entrada, CASE WHEN p.d <> '' THEN CAST(try_strptime(printf('%04d-%02d-%02d', CASE WHEN length(p.y)=2 THEN 2000 + CAST(p.y AS INT) ELSE CAST(p.y AS INT) END, CAST(p.m AS INT), CAST(p.d AS INT)), '%Y-%m-%d') AS DATE) END fc FROM cf""") x("""CREATE TABLE cf3 AS SELECT *, CASE WHEN fc IS NOT NULL AND fc <= d_pub AND d_pub - fc <= 365 THEN fc ELSE d_pub END f_const FROM cf2""") x("""CREATE TABLE constit AS SELECT bn, min(f_const) f_const, (max(f_const) - min(f_const) > 60) ambigua, arg_min(pdf_filename, f_const) pdf_c, arg_min(num_entrada, f_const) num_c FROM cf3 GROUP BY 1""") log('constit', x("SELECT count(*), sum(ambigua::INT) FROM constit").fetchone()) # 5. sancionadas (idéntico a RS1 12_sanc.py) + fecha de resolución def sa(s): return ''.join(ch for ch in unicodedata.normalize('NFKD', s or '') if not unicodedata.combining(ch)).lower() def nn(s): return re.sub(r'[^0-9a-z]+', ' ', sa(s)).strip() FJRX = r'( (s a u|s a|s l u|s l|s c a|s coop|sociedad anonima|sociedad limitada|unipersonal|sau|slu|sa|sl|sca))+$' def nemp(s): return re.sub(FJRX, '', nn(s)).strip() fallo = json.load(open('/et/colusion/etiquetas/casado/interno/c1_fallo.json')) fe = [] for e, v in fallo.items(): for n in v['empresas']: n2 = re.sub(r'^(?:[A-Z]{2,5}\.\s*)?\d+\.\s*', '', n).strip() for cand in {n2, n2.split(', ')[-1]}: k = nemp(cand) if len(k) >= 4: fe.append((e, n2, k)) x("CREATE TEMP TABLE fe(exp VARCHAR, nombre VARCHAR, k VARCHAR)"); con.executemany("INSERT INTO fe VALUES (?,?,?)", fe) x(f"""CREATE TEMP TABLE femp AS SELECT DISTINCT fe.exp, fe.nombre, af.empresa_clave FROM fe JOIN ( SELECT DISTINCT empresa_clave, regexp_replace(regexp_replace(lower(strip_accents(adjudicatario_nombre)), '[^0-9a-z]+', ' ', 'g'), '{FJRX}', '') k0 FROM '{I}adjudicatarios_filas.parquet' WHERE NOT coalesce(persona_fisica, false) AND empresa_clave IS NOT NULL) af ON trim(af.k0) = fe.k""") src = open('/et/p8/sql/c06_carteles.py', encoding='utf-8').read() p8 = re.findall(r"\('(S/00(?:13/19|08/21|16/21))', '\w', '([A-Z0-9]{9})', '(\d{4}-\d\d-\d\d)', '(\d{4}-\d\d-\d\d)'", src) x("CREATE TEMP TABLE p8emp(exp VARCHAR, k VARCHAR, desde DATE, hasta DATE)"); con.executemany("INSERT INTO p8emp VALUES (?,?,?,?)", p8) x("""CREATE TABLE sanc AS SELECT DISTINCT exp, empresa_clave emp FROM femp WHERE NOT exp LIKE 'AVC%' UNION SELECT DISTINCT exp, k FROM p8emp""") x("CREATE TABLE per(exp VARCHAR, a0 INT, a1 INT)") con.executemany("INSERT INTO per VALUES (?,?,?)", [(e, max(2012, int(a[:4])), min(2025, int(b[:4]))) for e, (a, b) in PER.items()]) M = {'Ene':1,'Feb':2,'Mar':3,'Abr':4,'May':5,'Jun':6,'Jul':7,'Ago':8,'Sep':9,'Oct':10,'Nov':11,'Dic':12} res = {} for f in json.load(open('/et/colusion/etiquetas/interno/fichas_cnmc.json')): ex = f['titulo'].split(' - ')[0] rs = [] for e in f['eventos']: if e['acto'].startswith('Resolución del Consejo'): dd, mm, yy = e['fecha'].split(); rs.append(f"{yy}-{M[mm]:02d}-{int(dd):02d}") if rs: res[ex] = min(rs) res['ACCO 101/2018'] = '2024-07-01' # año 2024 (casado D4c); día no visto: mitad de año (declarado) res['CGC S 8/2021'] = '2024-11-15' x("CREATE TABLE resol(exp VARCHAR, R DATE)"); con.executemany("INSERT INTO resol VALUES (?,?)", sorted(res.items())) x("CREATE TABLE lab AS SELECT * FROM read_csv('/et/colusion/etiquetas/casado/interno/lotes_cartel_v2_ids.csv', header=true, all_varchar=true)") x("""CREATE TABLE labu AS SELECT DISTINCT lab.exp, u.source, u.id_canonical, u.cpv2 FROM lab JOIN u ON u.source = lab.source AND u.id_canonical = lab.id_canonical WHERE NOT lab.exp LIKE 'AVC%' AND NOT u.es_menor""") x("""CREATE TABLE merc AS WITH c AS (SELECT exp, cpv2, count(*) n, sum(count(*)) OVER (PARTITION BY exp) t FROM labu WHERE cpv2 IS NOT NULL GROUP BY 1, 2) SELECT exp, cpv2, n, t FROM c WHERE n >= 5 AND n >= 0.10 * t""") log('sanc', x("SELECT s.exp, count(DISTINCT s.emp), any_value(r.R) FROM sanc s LEFT JOIN resol r USING (exp) GROUP BY 1 ORDER BY 1").fetchall()) log('merc', x("SELECT exp, string_agg(cpv2, ' ') FROM merc GROUP BY 1 ORDER BY 1").fetchall()) x("ANALYZE"); x("CHECKPOINT"); log('fin')