-- 01. Base de P6 (en /w/tmp/p6.duckdb). Copia de p3/sql/01_base.sql (guardada como sql/_p3_01_base.sql) apuntada a la -- construcción de P1b, con dos cambios: -- (1) se quitan las columnas que solo servían a la conciliación de P3 con la prueba B (tipo31, dudoso_pb) y su lectura de -- /pb, y la marca borrado_en_origen leída del fichero de borrados: ahora viene en la fila (anulado_en_origen, -- anulado_motivo, P1b §1); -- (2) la población se construye en TRES variantes (DEFINICIONES_PREVIAS D1), en una sola tabla `pobv` con columna `var`: -- 'con' = con anulados (P3 tal cual con los datos de P1b) -- 'sin' = sin anulado_en_origen = TRUE <- PRINCIPAL -- 'sinsm' = sin anulado_en_origen ni anulado_motivo = 'sin_motivo' -- Las anulaciones se quitan ANTES de quitar publicaciones dobles (un doble anulado deja de tapar al bueno). -- Resto idéntico a P3: vista única, 2019-2025 (año de referencia y fecha de adjudicación), base sin IVA > 0, sin persona -- física, sin es_menor_dudoso, con organo_clave y empresa_clave, sin DA 9.ª, sin publicaciones dobles; clase obras/resto; -- umbral 40.000 / 15.000 € (50.000 € en servicios y suministros de órganos de tipo «investigacion», como P3). CREATE OR REPLACE TABLE pm AS WITH b AS ( SELECT u.id_canonical, u.fecha_adj, u.fecha_pub, u.anio_ref, u.expediente, u.objeto, u.tipo_contrato, u.tramitacion, u.rol_am, u.iva_base, u.imp_adj_eur, u.imp_adj_eur_comparable, u.num_ofertas, u.url_origen, u.organo_nombre, u.organo_nif, u.dir3, u.adjudicatario_nombre, u.adjudicatario_nif, u.adjudicatario_nif_raw, u.ccaa, u.cpv_code, u.copia_de, u.correccion_imp_adj, u.es_menor_dudoso, u.menor_dudoso_motivo, u.supera_umbral_menor, u.anulado_en_origen, u.anulado_motivo, v.cuenta, a.persona_fisica, a.empresa_clave, a.clase_nif, a.entidad_de_personas, o.organo_clave, lower(strip_accents(coalesce(u.objeto, ''))) AS objeto_low FROM u LEFT JOIN vf v ON v.source = u.source AND v.id_canonical = u.id_canonical 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 LEFT 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.source = 'PLACSP' AND u.es_menor AND u.unidad = 'adjudicacion') SELECT b.*, coalesce(t.tipo, 'sin_tipo') AS tipo, (coalesce(b.cuenta, TRUE) AND b.copia_de IS NULL AND b.correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente') AS unica, CASE WHEN b.tipo_contrato = 'Obras' THEN 'obras' ELSE 'resto' END AS clase, trim(regexp_replace(regexp_replace(b.objeto_low, '[^a-z ]', ' ', 'g'), '\s+', ' ', 'g')) AS objeto_norm, (regexp_matches(b.objeto_low, 'suscrip|subscrip|harpidetza|bases? de datos') AND NOT regexp_matches(b.objeto_low, 'poliza|seguro|mantenimiento|software|mysql|oracle|\bsql\b|servidor|licencias? de (uso|software)|gestor')) AS da9 FROM b LEFT JOIN otipo t USING (organo_clave); ALTER TABLE pm ADD COLUMN umbral INTEGER; UPDATE pm SET umbral = CASE WHEN clase = 'obras' THEN 40000 WHEN tipo = 'investigacion' THEN 50000 ELSE 15000 END; ANALYZE pm; -- @ pm: filas, ids, uniones con la identidad y anulaciones (P1b) SELECT count(*) filas, count(DISTINCT id_canonical) ids, count(*) FILTER (WHERE unica) unicas, count(*) FILTER (WHERE persona_fisica IS NULL) sin_union_empresa, count(*) FILTER (WHERE organo_clave IS NULL) sin_organo_clave, count(*) FILTER (WHERE tipo = 'sin_tipo') sin_tipo_v32, count(*) FILTER (WHERE es_menor_dudoso) dudosos_p1b, count(*) FILTER (WHERE anulado_en_origen) anulados, count(*) FILTER (WHERE anulado_motivo = 'sin_motivo') sin_motivo, count(*) FILTER (WHERE anulado_motivo = 'cerrada') cerradas, count(*) FILTER (WHERE anulado_en_origen IS NULL) anulado_nulo FROM pm; CREATE OR REPLACE TABLE pobv AS WITH f AS ( SELECT id_canonical, organo_clave, empresa_clave, clase, tipo, umbral, objeto_norm, tipo_contrato, num_ofertas, ccaa, expediente, anulado_en_origen, anulado_motivo, fecha_pub, imp_adj_eur_comparable AS base, fecha_adj, year(fecha_adj) AS anio, (tipo IN ('salud', 'investigacion')) AS sector_aparte FROM pm WHERE unica AND anio_ref BETWEEN 2019 AND 2025 AND fecha_adj BETWEEN DATE '2019-01-01' AND DATE '2025-12-31' AND iva_base IN ('sin', 'sin_medido') AND imp_adj_eur_comparable > 0 AND persona_fisica = FALSE AND NOT coalesce(es_menor_dudoso, FALSE) AND organo_clave IS NOT NULL AND empresa_clave IS NOT NULL AND NOT da9), v AS ( SELECT 'con' AS var, * FROM f UNION ALL SELECT 'sin', * FROM f WHERE NOT coalesce(anulado_en_origen, FALSE) UNION ALL SELECT 'sinsm', * FROM f WHERE NOT coalesce(anulado_en_origen, FALSE) AND anulado_motivo IS DISTINCT FROM 'sin_motivo'), d AS (SELECT *, row_number() OVER (PARTITION BY var, organo_clave, empresa_clave, base, fecha_adj, objeto_norm, coalesce(expediente, '') ORDER BY id_canonical) > 1 AS dup FROM v) SELECT * EXCLUDE (dup, expediente) FROM d WHERE NOT dup; ANALYZE pobv; -- @ Población por variante (P3: 2.316.057 menores, 9.840,4 M€; principal 1.789.245; salud e investigación 526.812) SELECT var, count(*) menores, round(sum(base) / 1e6, 1) meur, count(*) FILTER (WHERE NOT sector_aparte) principal, count(*) FILTER (WHERE sector_aparte) salud_investigacion, count(*) FILTER (WHERE anulado_en_origen) anulados, count(*) FILTER (WHERE anulado_motivo = 'sin_motivo') sin_motivo, count(DISTINCT organo_clave) organos FROM pobv GROUP BY 1 ORDER BY 1; -- @ Población 'sin' por año SELECT anio, count(*) menores, round(sum(base) / 1e6, 1) meur, count(DISTINCT organo_clave) organos FROM pobv WHERE var = 'sin' GROUP BY 1 ORDER BY 1;