# E-M1b impl2 · extracción (DuckDB 1.1.x, imagen buscalicitaciones-etl:v2). Código propio, escrito de cero. import duckdb, time, sys t0=time.time() TMP='/w/tmp/impl2' OUT='/w/salidas/impl2' con=duckdb.connect(f'{TMP}/i2.duckdb') con.execute(f"SET memory_limit='7GB'; SET threads=4; SET temp_directory='{TMP}/duck'; SET preserve_insertion_order=false") U="read_parquet('/d/unified_20261018/source=PLACSP/*/*.parquet', hive_partitioning=true, union_by_name=true)" I='/d/unified_20261018.identidad' log=open(f'{OUT}/p1_extraer.log','w') def P(*a): s=' '.join(str(x) for x in a); print(s); log.write(s+'\n'); log.flush() P('duckdb', duckdb.__version__) # §2.1 filas, §2.2 feed con.execute(f""" CREATE OR REPLACE TABLE filas AS SELECT CASE WHEN tabla_origen='resultados_licitaciones' OR (tabla_origen='licitaciones_sin_resultado' AND url_origen LIKE 'https://contrataciondelestado.es%') THEN 'propio' ELSE 'agregacion' END AS feed, licitacion_id, valor_estimado_eur_expediente AS ve_f, imp_lic_eur_expediente AS pbl_f, tipo_contrato, procedimiento_native, cpv_division, ccaa, sara, objeto, organo_nif, organo_nombre, dir3, fecha_pub, fecha_fin_ofertas, fecha_adj FROM {U} WHERE source='PLACSP' AND vigente AND es_contrato AND NOT coalesce(es_menor, FALSE) AND tabla_origen IN ('resultados_licitaciones','resultados_agregacion','licitaciones_sin_resultado') AND unidad IN ('adjudicacion','desierto','licitacion:sin_resultado','licitacion:adjudicada_sin_detalle') AND NOT coalesce(anulado_en_origen, FALSE) AND copia_de IS NULL AND coalesce(rol_am,'') NOT IN ('basado','basado_sda') AND coalesce(procedimiento_native,'') NOT IN ('Derivado de acuerdo marco','Basado en sistema dinámico de adquisición') AND coalesce(tramitacion,'') <> 'emergencia' """) P('filas', con.execute("select feed,count(*),count(distinct licitacion_id) from filas group by 1 order by 1").fetchall()) P('licitacion_id nulo', con.execute("select count(*) from filas where licitacion_id is null").fetchall()) # §2.3 unidad licitación con.execute(f""" CREATE OR REPLACE TABLE lic0 AS SELECT f.feed, f.licitacion_id, max(ve_f) AS ve, max(pbl_f) AS pbl, min(tipo_contrato) AS tipo_contrato, min(procedimiento_native) AS proc, min(cpv_division) AS cpv_division, min(ccaa) AS ccaa, bool_or(sara) AS sara, min(o.organo_clave) AS organo_clave, min(NULLIF(trim(regexp_replace(regexp_replace(strip_accents(lower(objeto)),'[^a-z ]',' ','g'),' +',' ','g')),'')) AS objeto_norm, least(min(fecha_pub), min(fecha_fin_ofertas), min(fecha_adj)) AS fecha_ref FROM filas f LEFT JOIN '{I}/organos_filas.parquet' o ON f.organo_nif IS NOT DISTINCT FROM o.organo_nif AND f.organo_nombre IS NOT DISTINCT FROM o.organo_nombre AND f.dir3 IS NOT DISTINCT FROM o.dir3 GROUP BY 1,2 """) con.execute(f""" CREATE OR REPLACE TABLE lic AS SELECT l.*, coalesce(t.tipo,'sin_tipo') AS tipo_org, (objeto_norm IS NOT NULL AND length(objeto_norm) >= 10) AS obj_ok FROM lic0 l LEFT JOIN '{I}/organos_tipo.parquet' t USING (organo_clave) WHERE ve > 0 AND fecha_ref >= DATE '2022-01-01' AND fecha_ref <= DATE '2026-09-30' """) P('lic', con.execute("select feed,count(*),count(organo_clave),count(cpv_division),sum(obj_ok::int) from lic group by 1 order by 1").fetchall()) P('lic0 sin ve>0', con.execute("select feed,count(*) filter (where not coalesce(ve>0,false)), count(*) filter (where fecha_ref is null) from lic0 group by 1 order by 1").fetchall()) # §5.1 n_cpv y n_obj (ambos feeds, cualquier clase, por año de fecha_ref) con.execute(""" CREATE OR REPLACE TABLE lic2 AS SELECT *, year(fecha_ref) AS anio, CASE WHEN organo_clave IS NOT NULL AND cpv_division IS NOT NULL THEN count(*) OVER (PARTITION BY organo_clave, cpv_division, year(fecha_ref)) END AS n_cpv, CASE WHEN organo_clave IS NOT NULL AND obj_ok THEN count(*) OVER (PARTITION BY organo_clave, objeto_norm, year(fecha_ref)) END AS n_obj, CASE WHEN tipo_contrato IN ('Servicios','Suministros') THEN 'ss' END AS c_ss, tipo_contrato='Obras' AS c_obras, tipo_contrato IN ('Obras','Concesion_obras','Concesion_servicios') AS c_oc, (tipo_org='age') AS age, coalesce(ccaa,'')='NA' AS es_na, CAST(year(fecha_ref) AS VARCHAR)||'T'||CAST(quarter(fecha_ref) AS VARCHAR) AS trimestre FROM lic """) # exporta la tabla de licitaciones relevante (solo columnas necesarias; organo como entero opaco) con.execute(""" CREATE OR REPLACE TABLE orgid AS SELECT organo_clave, row_number() OVER (ORDER BY organo_clave) AS oid FROM (SELECT DISTINCT organo_clave FROM lic2 WHERE organo_clave IS NOT NULL) """) con.execute(f""" COPY (SELECT feed, ve, fecha_ref, trimestre, anio, tipo_contrato, age, es_na, o.oid, (cpv_division IS NOT NULL) AS tiene_cpv, n_cpv, obj_ok, n_obj FROM lic2 LEFT JOIN orgid o USING (organo_clave) WHERE tipo_contrato IN ('Servicios','Suministros','Obras','Concesion_obras','Concesion_servicios')) TO '{TMP}/lic.csv' (HEADER, DELIMITER ',') """) P('exportado', con.execute(f"select count(*) from read_csv_auto('{TMP}/lic.csv')").fetchall()) P('segundos', round(time.time()-t0,1))