-- impl2 · 05 · Licitaciones (procedimientos no menores) de todas las fuentes (§4.1) CREATE OR REPLACE VIEW u AS SELECT * FROM read_parquet('/d/unified_20261018/**/*.parquet', hive_partitioning=true, union_by_name=true); CREATE OR REPLACE VIEW ofi AS SELECT organo_nif, organo_nombre, dir3, organo_clave, entidad_clave FROM read_parquet('/d/unified_20261018.identidad/organos_filas.parquet'); CREATE OR REPLACE TABLE lic AS SELECT u.source, coalesce(u.licitacion_id, u.id_canonical) AS lic_key, u.id_canonical, u.anio_ref, u.fecha_pub, u.fecha_adj, u.procedimiento, u.unidad, u.expediente, u.imp_lic_eur, u.url_origen, u.objeto, o.organo_clave, o.entidad_clave, trim(regexp_replace(lower(strip_accents(coalesce(u.objeto, ''))), '[^a-z]+', ' ', 'g')) AS objeto_norm, CASE WHEN regexp_matches(u.cpv_code, '^[0-9]{8}$') AND u.cpv_code <> '00000000' THEN u.cpv_code END AS cpv8 FROM u JOIN ofi 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.copia_de IS NULL AND u.correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND NOT coalesce(u.es_menor, FALSE) AND u.procedimiento IS DISTINCT FROM 'Menor' AND u.unidad IN ('adjudicacion', 'desierto', 'licitacion:sin_resultado', 'licitacion:adjudicada_sin_detalle') AND u.tramitacion IS DISTINCT FROM 'emergencia' AND NOT coalesce(u.anulado_en_origen, FALSE) AND o.organo_clave IS NOT NULL AND u.anio_ref BETWEEN 2019 AND 2025; ANALYZE lic; -- @ licitaciones por fuente (filas y claves) y año SELECT source, count(*) filas, count(DISTINCT lic_key) licitaciones, count(cpv8) con_cpv, min(anio_ref), max(anio_ref) FROM lic GROUP BY 1 ORDER BY 2 DESC; SELECT anio_ref, count(*) filas, count(DISTINCT (source, lic_key)) licitaciones, count(DISTINCT organo_clave) organos FROM lic GROUP BY 1 ORDER BY 1; -- @ claves con filas de varios anio_ref (la etiqueta usa el anio_ref de cada fila) SELECT count(*) claves, count(*) FILTER (WHERE na > 1) varios_anios FROM (SELECT source, lic_key, count(DISTINCT anio_ref) na FROM lic GROUP BY 1, 2); -- @ claves con filas de varios órganos SELECT count(*) FILTER (WHERE n_org > 1) varios_organos FROM (SELECT source, lic_key, count(DISTINCT organo_clave) n_org FROM lic GROUP BY 1, 2);