# impl2 · §2 población común (vista única de menores) -> tmp/impl2/pob4.parquet (§4) y tmp/impl2/R6.parquet (§6) import duckdb, time t0=time.time() con = duckdb.connect() con.execute("SET memory_limit='7GB'; SET threads=6; SET temp_directory='/w/tmp/duck'; SET preserve_insertion_order=false") print('duckdb', duckdb.__version__) D='/d/unified_20261018'; I='/d/unified_20261018.identidad' con.execute(f""" CREATE VIEW pob AS SELECT u.source, u.id_canonical, CASE WHEN u.iva_base IN ('sin','sin_medido') THEN 'sin' WHEN u.iva_base IN ('con','con_medido') THEN 'con' ELSE 'no_consta' END AS base, u.tipo_contrato, u.imp_adj_eur_corregido AS imp, u.fecha_adj, u.anio_ref, u.ccaa, u.objeto, ofi.organo_clave, af.empresa_clave, af.persona_fisica, CASE WHEN ot.organo_clave IS NULL THEN 'sin_tipo' ELSE ot.tipo END AS tipo_org, ot.agente_secti FROM read_parquet('{D}/**/*.parquet', hive_partitioning=true, union_by_name=true) u LEFT JOIN read_parquet('/d/unified_20261018.vinculos/filas.parquet') v ON v.source = u.source AND v.id_canonical = u.id_canonical LEFT JOIN read_parquet('{I}/organos_filas.parquet') ofi ON ofi.organo_nif IS NOT DISTINCT FROM u.organo_nif AND ofi.organo_nombre IS NOT DISTINCT FROM u.organo_nombre AND ofi.dir3 IS NOT DISTINCT FROM u.dir3 LEFT JOIN read_parquet('{I}/adjudicatarios_filas.parquet') af ON af.adjudicatario_nif_raw IS NOT DISTINCT FROM u.adjudicatario_nif_raw AND af.adjudicatario_nif IS NOT DISTINCT FROM u.adjudicatario_nif AND af.adjudicatario_nombre IS NOT DISTINCT FROM u.adjudicatario_nombre LEFT JOIN read_parquet('{I}/organos_tipo.parquet') ot ON ot.organo_clave = ofi.organo_clave WHERE u.es_menor AND 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) """) t=time.time() con.execute("""COPY (SELECT source, base, CASE WHEN tipo_contrato='Obras' THEN 'obras' ELSE 'resto' END AS clase, imp, fecha_adj FROM pob WHERE fecha_adj BETWEEN DATE '2017-01-01' AND DATE '2019-12-31') TO '/w/tmp/pob4.parquet' (FORMAT parquet)""") print('pob4 escrito %.1fs'%(time.time()-t)) t=time.time() con.execute("""COPY (SELECT source, base, tipo_contrato, imp, anio_ref, ccaa, organo_clave, empresa_clave, tipo_org, agente_secti, trim(regexp_replace(regexp_replace(strip_accents(lower(objeto)), '[^a-z ]', ' ', 'g'), ' +', ' ', 'g')) AS objeto_norm FROM pob WHERE anio_ref BETWEEN 2019 AND 2025 AND organo_clave IS NOT NULL AND empresa_clave IS NOT NULL AND NOT coalesce(persona_fisica, FALSE)) TO '/w/tmp/R6.parquet' (FORMAT parquet)""") print('R6 escrito %.1fs'%(time.time()-t)) # controles for s in ["SELECT count(*) FROM read_parquet('/w/tmp/pob4.parquet')", "SELECT count(*) FROM read_parquet('/w/tmp/R6.parquet')", "SELECT count(*) FROM pob", "SELECT count(*) FILTER (WHERE anio_ref BETWEEN 2019 AND 2025), count(*) FILTER (WHERE anio_ref BETWEEN 2019 AND 2025 AND organo_clave IS NOT NULL), count(*) FILTER (WHERE anio_ref BETWEEN 2019 AND 2025 AND empresa_clave IS NOT NULL), count(*) FILTER (WHERE anio_ref BETWEEN 2019 AND 2025 AND coalesce(persona_fisica,FALSE)) FROM pob"]: print(s[:90], con.execute(s).fetchall()) print('total %.1fs'%(time.time()-t0))