-- impl2 · 01 · Base de menores de todas las fuentes con la identidad y los vínculos (DEFINICIONES_PREVIAS §2 y §3.1) -- Una sola lectura de unified; las poblaciones V0 (PLACSP) y V2 (demás) salen de aquí en 02. 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 vf AS SELECT source, id_canonical, cuenta FROM read_parquet('/d/unified_20261018.vinculos/filas.parquet'); CREATE OR REPLACE VIEW af AS SELECT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, persona_fisica, varias_empresas, entidad_de_personas, clase_nif, empresa_clave FROM read_parquet('/d/unified_20261018.identidad/adjudicatarios_filas.parquet'); 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 otipo AS SELECT organo_clave, coalesce(tipo, 'sin_tipo') AS tipo FROM read_parquet('/d/unified_20261018.identidad/organos_tipo.parquet'); -- Categorías por fuente: las de producción (poblacion = 'menores') y las de P9 (clase = 'categoria'), comparadas con objeto_norm CREATE OR REPLACE TABLE cat AS SELECT DISTINCT source, objeto_base AS objeto_norm FROM ( SELECT source, objeto_base FROM read_csv('/datos/c2_objetos_categoria_main.csv', header=true, all_varchar=true) WHERE poblacion = 'menores' UNION ALL SELECT source, objeto_base FROM read_csv('/datos/categorias_p9_20261020.csv', header=true, all_varchar=true) WHERE clase = 'categoria'); -- @ categorías por fuente SELECT source, count(*) FROM cat GROUP BY 1 ORDER BY 1; -- Menores adjudicados, publicación propia, con algún año 2019-2025 (superconjunto de V0 y de V2) CREATE OR REPLACE TABLE mb AS WITH x AS ( SELECT u.source, u.id_canonical, u.fecha_adj, u.anio_ref, u.anio_precision, u.expediente, u.tipo_contrato, u.iva_base, u.imp_adj_eur_comparable, u.imp_adj_eur_corregido, u.es_menor_dudoso, u.anulado_en_origen, u.cpv_code, u.adjudicatario_nif_raw, u.adjudicatario_nif, u.adjudicatario_nombre, u.organo_nif, u.organo_nombre, u.dir3, length(trim(coalesce(u.objeto, ''))) AS obj_len, lower(strip_accents(coalesce(u.objeto, ''))) AS objeto_low FROM u WHERE u.es_menor AND u.unidad = 'adjudicacion' AND u.copia_de IS NULL AND u.correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND (u.anio_ref BETWEEN 2019 AND 2025 OR year(u.fecha_adj) BETWEEN 2019 AND 2025)) SELECT x.source, x.id_canonical, x.fecha_adj, x.anio_ref, x.anio_precision, x.expediente, x.tipo_contrato, x.iva_base, x.imp_adj_eur_comparable, x.imp_adj_eur_corregido, x.es_menor_dudoso, x.anulado_en_origen, x.obj_len, CASE WHEN regexp_matches(x.cpv_code, '^[0-9]{8}$') AND x.cpv_code <> '00000000' THEN x.cpv_code END AS cpv8, v.cuenta, a.persona_fisica, a.varias_empresas, a.empresa_clave, o.organo_clave, o.entidad_clave, coalesce(t.tipo, 'sin_tipo') AS tipo, trim(regexp_replace(x.objeto_low, '[^a-z]+', ' ', 'g')) AS objeto_norm, (regexp_matches(x.objeto_low, 'suscrip|subscrip|harpidetza|bases? de datos') AND NOT regexp_matches(x.objeto_low, 'poliza|seguro|mantenimiento|software|mysql|oracle|\bsql\b|servidor|licencias? de (uso|software)|gestor')) AS da9 FROM x LEFT JOIN vf v ON v.source = x.source AND v.id_canonical = x.id_canonical LEFT JOIN af a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM x.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM x.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM x.adjudicatario_nombre LEFT JOIN ofi o ON o.organo_nif IS NOT DISTINCT FROM x.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM x.organo_nombre AND o.dir3 IS NOT DISTINCT FROM x.dir3 LEFT JOIN otipo t ON t.organo_clave = o.organo_clave; ANALYZE mb; -- @ mb por fuente SELECT source, count(*) n, count(organo_clave) con_organo, count(empresa_clave) con_empresa, count(*) FILTER (WHERE persona_fisica) pf, count(*) FILTER (WHERE cuenta = FALSE) no_cuenta FROM mb GROUP BY 1 ORDER BY 1; -- @ control: filas de mb frente a unified (sin multiplicar por las uniones) SELECT (SELECT count(*) FROM mb) mb, (SELECT count(*) FROM u WHERE es_menor AND unidad = 'adjudicacion' AND copia_de IS NULL AND correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND (anio_ref BETWEEN 2019 AND 2025 OR year(fecha_adj) BETWEEN 2019 AND 2025)) u;