"""02. Base de E-M7 (/w/tmp/em7.duckdb): adjudicaciones, casado (satélite E-M3 + etiquetas E-M5), constitución y concursal por sociedad desde el parse 2009-2026, y la COBERTURA por fuente y año. Nada por órgano, ninguna tasa de jóvenes.""" import sys; sys.path.insert(0, "/w/py") from util import Informe, abrir D = "/d/unified_20261018" con = abrir() r = Informe(con, "Base y cobertura de E-M7 (`02_base.py`)") r.x(f"""CREATE OR REPLACE TABLE adj AS SELECT u.id_canonical, u.source, u.anio_ref, u.fecha_adj, u.fecha_formalizacion, coalesce(u.es_menor, FALSE) es_menor, coalesce(u.fecha_adj, u.fecha_formalizacion) AS F, coalesce(u.imp_adj_eur_comparable, u.imp_adj_eur_sin_iva, u.imp_adj_eur) AS base_any, coalesce(u.cpv_division, '??') cpv_div, CASE WHEN u.tipo_contrato IN ('Obras','Servicios','Suministros') THEN u.tipo_contrato ELSE 'Otros' END tipo_c, u.url_origen, o.organo_clave, a.empresa_clave, coalesce(a.persona_fisica, FALSE) AS persona_fisica FROM read_parquet('{D}/**/*.parquet', hive_partitioning=true, union_by_name=true) u LEFT JOIN read_parquet('{D}.vinculos/filas.parquet') v ON v.source = u.source AND v.id_canonical = u.id_canonical LEFT JOIN read_parquet('{D}.identidad/adjudicatarios_filas.parquet') a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM u.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM u.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM u.adjudicatario_nombre LEFT JOIN read_parquet('{D}.identidad/organos_filas.parquet') o ON o.organo_nif IS NOT DISTINCT FROM u.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM u.organo_nombre AND o.dir3 IS NOT DISTINCT FROM u.dir3 WHERE u.unidad = 'adjudicacion' AND NOT coalesce(u.es_menor_dudoso, FALSE) AND NOT coalesce(u.anulado_en_origen, FALSE) AND u.copia_de IS NULL AND coalesce(u.correccion_imp_adj, '') NOT IN ('copia_otra_fuente', 'repetido', 'inverosimil') AND coalesce(v.cuenta, TRUE)""") r.x("CREATE OR REPLACE TABLE tipo_org AS SELECT organo_clave, any_value(tipo) tipo FROM read_parquet('/d/unified_20261018.identidad/organos_tipo.parquet') GROUP BY 1") r.x("""CREATE OR REPLACE TABLE sat AS SELECT s.empresa_clave, s.via, s.borme_norm, s.homonima, s.utilizable, s.empresa_tipo, e.etiqueta em5, n.crono em5_crono FROM read_parquet('/af/em3/salidas/unified_20261018.borme/empresas.parquet') s LEFT JOIN read_parquet('/af/em5/salidas/17_etiquetas_INTERNO.parquet') e USING (empresa_clave) LEFT JOIN read_parquet('/af/em5/salidas/niveles_INTERNO.parquet') n USING (empresa_clave)""") # Constituciones del parse 2009-2026 (todas las filas con el acto «Constitución») r.x("""CREATE OR REPLACE TABLE const_filas AS SELECT clave_borme(empresa) borme_norm, CAST(fecha_borme AS DATE) d_pub, fecha_constitucion fc_txt, pdf_filename, num_entrada, cod_provincia, hoja_registral, regexp_extract(fecha_constitucion, '^\\s*(\\d{1,2})\\.(\\d{1,2})\\.(\\d{2,4})', ['d','m','y']) p FROM read_parquet('/bh/parse/borme_empresas.parquet') WHERE actos LIKE '%Constitución%'""") r.x("""CREATE OR REPLACE TABLE const_filas2 AS SELECT * EXCLUDE (p), 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) fc FROM const_filas WHERE p.d <> ''""") r.x("""INSERT INTO const_filas2 SELECT * EXCLUDE (p), NULL FROM const_filas WHERE p.d = '' OR p.d IS NULL""") r.x("""CREATE OR REPLACE TABLE const_filas3 AS SELECT *, CASE WHEN fc IS NOT NULL AND fc <= d_pub AND d_pub - fc <= 365 THEN fc ELSE d_pub END AS f_const, (fc IS NOT NULL AND fc <= d_pub AND d_pub - fc <= 365) AS fc_valida FROM const_filas2 WHERE borme_norm IS NOT NULL""") r.x("""CREATE OR REPLACE TABLE const_soc AS SELECT borme_norm, count(*) n_filas, min(f_const) f_const, max(f_const) f_const_max, min(d_pub) d_pub, arg_min(fc_valida, f_const) fc_valida, count(DISTINCT coalesce(hoja_registral,'') || '#' || coalesce(cod_provincia,'')) n_hojas, (max(f_const) - min(f_const) > 60) ambigua FROM const_filas3 GROUP BY 1""") r.x("""CREATE OR REPLACE TABLE renom AS SELECT DISTINCT clave_borme(empresa) borme_norm FROM read_parquet('/bh/parse/borme_empresas.parquet') WHERE actos LIKE '%Cambio de denominación social%'""") r.x("""CREATE OR REPLACE TABLE conc AS SELECT clave_borme(empresa) borme_norm, CAST(fecha_borme AS DATE) d FROM read_parquet('/bh/parse/borme_empresas.parquet') WHERE lower(strip_accents(actos)) LIKE '%concurs%' AND clave_borme(empresa) IS NOT NULL""") r.x("CREATE OR REPLACE TABLE conc_soc AS SELECT borme_norm, list_sort(list_distinct(list(d))) ds, min(d) d0 FROM conc GROUP BY 1") # Primera adjudicación de cada empresa_clave (todas las fuentes y años) para la cronología r.x("CREATE OR REPLACE TABLE prim AS SELECT empresa_clave, min(F) F0 FROM adj WHERE empresa_clave IS NOT NULL AND F BETWEEN DATE '2000-01-01' AND DATE '2026-10-16' GROUP BY 1") r.x("""CREATE OR REPLACE TABLE pe AS SELECT a.*, coalesce(t.tipo, 'sin_tipo') tipo_org, s.via, s.borme_norm, s.homonima, coalesce(s.utilizable, FALSE) utilizable, s.em5, s.em5_crono, (s.via IN ('v1_exacto','v2_historico') AND s.homonima = FALSE) AS celda, c.f_const, c.f_const_max, c.d_pub, c.fc_valida, coalesce(c.ambigua, FALSE) ambigua, c.n_hojas, (rn.borme_norm IS NOT NULL) renombrada, p.F0, (c.f_const IS NOT NULL AND p.F0 < c.f_const - 30) AS refutada_crono FROM adj a LEFT JOIN tipo_org t USING (organo_clave) LEFT JOIN (SELECT * FROM sat WHERE via <> 'sin_casar') s ON s.empresa_clave = a.empresa_clave LEFT JOIN const_soc c ON c.borme_norm = s.borme_norm LEFT JOIN renom rn ON rn.borme_norm = s.borme_norm LEFT JOIN prim p ON p.empresa_clave = a.empresa_clave WHERE a.anio_ref BETWEEN 2015 AND 2025 AND a.organo_clave IS NOT NULL AND a.empresa_clave IS NOT NULL AND NOT a.persona_fisica""") r.x("ANALYZE") r.q("Constituciones del parse: filas, sociedades, fecha_constitucion válida, ambiguas", """ SELECT (SELECT count(*) FROM const_filas3) filas, (SELECT count(*) FILTER (WHERE fc_valida) FROM const_filas3) fc_valida, (SELECT count(*) FROM const_filas3 WHERE fc IS NOT NULL AND NOT fc_valida) fc_fuera_rango, count(*) sociedades, count(*) FILTER (WHERE n_filas > 1) varias_filas, count(*) FILTER (WHERE ambigua) ambiguas FROM const_soc""") r.q("Desfase publicación − fecha_constitucion (válidas), días", "SELECT quantile_cont(d_pub - fc, [0.05,0.25,0.5,0.75,0.95]) FROM const_filas3 WHERE fc_valida") COLS = """count(*) P, round(sum(base_any)/1e6) P_meur, count(*) FILTER (WHERE F IS NOT NULL) con_F, count(*) FILTER (WHERE via IS NOT NULL) casada, count(*) FILTER (WHERE celda) celda_v1v2_nohom, count(*) FILTER (WHERE celda AND F IS NOT NULL) celda_con_F, count(*) FILTER (WHERE celda AND f_const IS NOT NULL) celda_constit_2009, count(*) FILTER (WHERE celda AND ambigua) ambigua, count(*) FILTER (WHERE celda AND refutada_crono) refut_crono, count(*) FILTER (WHERE celda AND em5 = 'R') refut_em5, count(*) FILTER (WHERE celda AND f_const IS NULL AND renombrada) sin_const_renombrada, round(100.0 * count(*) FILTER (WHERE celda AND F IS NOT NULL) / count(*), 1) pct_celda_F, round(100.0 * sum(base_any) FILTER (WHERE celda AND F IS NOT NULL) / sum(base_any), 1) pct_imp_celda_F""" r.q("Cobertura por año", f"SELECT anio_ref, {COLS} FROM pe GROUP BY 1 ORDER BY 1") r.q("Cobertura por menor / no menor", f"SELECT es_menor, {COLS} FROM pe GROUP BY 1 ORDER BY 1") r.q("Cobertura por fuente", f"SELECT source, {COLS} FROM pe GROUP BY 1 ORDER BY 2 DESC") r.q("Cobertura por fuente y año (pct de filas en la celda con F)", """ PIVOT (SELECT source, anio_ref, round(100.0 * count(*) FILTER (WHERE celda AND F IS NOT NULL) / count(*), 1) pct FROM pe GROUP BY 1, 2) ON anio_ref USING first(pct) ORDER BY source""") r.q("Sociedades de la celda: con constitución 2009+, refutadas, E-M5", """ SELECT count(DISTINCT empresa_clave) emp, count(DISTINCT empresa_clave) FILTER (WHERE f_const IS NOT NULL) constit_2009, count(DISTINCT empresa_clave) FILTER (WHERE refutada_crono) refut_crono, count(DISTINCT empresa_clave) FILTER (WHERE em5='R') em5_R, count(DISTINCT empresa_clave) FILTER (WHERE ambigua) ambiguas FROM pe WHERE celda""") r.q("Año de constitución de las sociedades de la celda (constituidas 2009+), por sociedad", """ SELECT year(f_const) a, count(DISTINCT empresa_clave) emp FROM pe WHERE celda AND f_const IS NOT NULL GROUP BY 1 ORDER BY 1""") r.guardar("/w/salidas/02_cobertura.md")