"""ETIQ-3 · impl. 1 · base de adjudicaciones de la web (NOT es_copia) con órgano, entidad y empresa (DuckDB, /c/tmp/etiq3.duckdb). DEFINICIONES_PREVIAS_E9 §2 y PR1BIS §3. Uso (etiq3-base): python3 -I base_u.py""" import duckdb, os, time D = '/d/unified_20261025' con = duckdb.connect('/c/tmp/etiq3.duckdb') con.execute("SET memory_limit='8GB'; SET threads=3; SET temp_directory='/c/tmp/duck'; SET max_temp_directory_size='5GB'; SET preserve_insertion_order=false") con.execute(f"CREATE OR REPLACE TABLE vf AS SELECT DISTINCT id_canonical FROM read_parquet('/d/aggregations_20261025/vinculos_filas.parquet') WHERE NOT cuenta") con.execute(f"CREATE OR REPLACE TABLE of AS SELECT organo_nif, organo_nombre, dir3, organo_clave, entidad_clave FROM read_parquet('{D}.identidad/organos_filas.parquet')") con.execute(f"""CREATE OR REPLACE TABLE af AS SELECT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, empresa_clave, empresa_tipo, coalesce(persona_fisica, FALSE) persona_fisica FROM read_parquet('{D}.identidad/adjudicatarios_filas.parquet')""") con.execute("DROP TABLE IF EXISTS ub") t0 = time.time() for src in sorted(os.listdir(D)): S = src.split('=')[1] sql = f"""SELECT '{S}' AS source, u.id_canonical, o.organo_clave, o.entidad_clave, coalesce(year(coalesce(u.fecha_formalizacion, u.fecha_adj)), u.anio_ref) AS anio_e9, coalesce(u.fecha_adj, u.fecha_formalizacion) AS f_pr, coalesce(u.es_menor, FALSE) es_menor, coalesce(u.es_menor_dudoso, FALSE) es_menor_dudoso, coalesce(u.imp_adj_eur_comparable, u.imp_adj_eur_sin_iva) importe, a.empresa_clave, u.adjudicatario_nif, coalesce(a.persona_fisica, FALSE) persona_fisica, u.cpv_code FROM read_parquet('{D}/{src}/**/*.parquet', hive_partitioning=true, union_by_name=true) u LEFT JOIN of 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 LEFT JOIN af 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 WHERE u.unidad = 'adjudicacion' AND NOT coalesce(u.anulado_en_origen, FALSE) AND u.copia_de IS NULL AND coalesce(u.correccion_imp_adj, '') <> 'copia_otra_fuente' AND u.id_canonical NOT IN (SELECT id_canonical FROM vf)""" if not con.sql("SELECT count(*) FROM information_schema.tables WHERE table_name='ub'").fetchone()[0]: con.execute("CREATE TABLE ub AS " + sql) else: con.execute("INSERT INTO ub " + sql) print(S, con.sql("SELECT count(*) FROM ub").fetchone()[0], f'{time.time()-t0:.0f}s', flush=True) con.execute("ANALYZE") print(con.sql("""SELECT count(*) filas, count(organo_clave) con_organo, count(*) FILTER (WHERE entidad_clave LIKE 'P%') entidad_P, count(*) FILTER (WHERE entidad_clave LIKE 'N:%') entidad_nombre, count(DISTINCT id_canonical) ids FROM ub""").fetchall())