-- 03. Población por fuente (definiciones §2) con las columnas derivadas, y los criterios del §3 medidos por fuente y año. -- Tabla p (tmp/p9.duckdb): todas las filas de b que no son agregadas de la PSCP, con marcas; la población es -- p WHERE en_pob. La PLACSP va con la publicación propia aquí (solo para los criterios); sus indicadores son los de P4. CREATE OR REPLACE TABLE cat AS SELECT source, objeto_base FROM read_csv('/w/salidas/02b_categorias.csv', header = true, all_varchar = true) WHERE clase = 'categoria'; -- Órganos genéricos: clave solo de nombre que es un cargo o un órgano colegiado sin la unidad (definiciones §2; la -- primera regla medida, «el nombre lo publican otras claves con NIF o DIR3», marcaba nombres propios: salidas/03a) CREATE OR REPLACE MACRO generico(c) AS regexp_matches(c, '^N:(EL |LA )?(ALCALDIA|ALCALDE|ALCALDESA|ALCALDE PRESIDENTE|PLENO|JUNTA DE GOBIERNO( LOCAL)?|PRESIDENCIA|PRESIDENTE|PRESIDENTA|DIRECTOR|DIRECTORA|DIRECTOR A|DIRECTOR A GENERAL|DIRECTOR A GERENTE|DIRECTOR GENERAL|DIRECTOR GERENTE|DIRECCION|DIRECCION GENERAL|DIRECCION GERENCIA|GERENTE|GERENCIA|SECRETARIA|SECRETARIA GENERAL|ORGANO DE CONTRATACION|CONSEJO DE ADMINISTRACION|RECTOR|RECTORADO|SSCC|SERVICIOS CENTRALES|CONSEJERO DELEGADO|ADMINISTRADOR UNICO)$'); CREATE OR REPLACE TABLE p0 AS WITH x AS ( SELECT b.*, (copia_de IS NULL AND correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente') AS propia, CASE WHEN anio_precision = 'exacto' AND fecha_adj IS NOT NULL THEN year(fecha_adj) ELSE anio_ref END AS anio, (anio_precision = 'exacto' AND fecha_adj IS NOT NULL AND fecha_adj_es IN ('adjudicacion', 'adjudicacion_o_formalizacion', 'formalizacion', 'contrato', 'aprobacion_gasto')) AS fecha_ok, lower(strip_accents(coalesce(objeto, ''))) AS objeto_low, trim(regexp_replace(lower(strip_accents(coalesce(objeto, ''))), '[^a-z]+', ' ', 'g')) AS objeto_base, coalesce(iva_base IN ('sin', 'sin_medido'), FALSE) AS iva_sin, -- sin iva_base: con cota (tipo_contrato IS NOT NULL AND NOT regexp_matches(lower(tipo_contrato), '^otro')) AS tipo_conocido, coalesce(tipo_contrato IN ('Obras', 'Concesion_obras'), FALSE) AS obras, (NOT coalesce(cuenta, TRUE) AND dup_fuente IS DISTINCT FROM source) AS copia_fuera, coalesce(persona_fisica, FALSE) AS pf, (coalesce(clase_nif, 'sin_nif') NOT IN ('valido', 'control_ko', 'enmascarado', 'extranjero', 'lista', 'lista_con_lider', 'id_provisional_ute', 'cif_sin_control') AND empresa_clave IS NULL) AS puede_ser_persona, (coalesce(clase_nif, 'sin_nif') IN ('valido', 'control_ko', 'enmascarado', 'extranjero', 'lista', 'lista_con_lider', 'id_provisional_ute', 'cif_sin_control')) AS nif_publicado, (coalesce(varias_empresas, FALSE) OR coalesce(clase_nif IN ('lista', 'lista_con_lider'), FALSE) OR coalesce(empresa_clave LIKE 'L:%', FALSE)) AS varias, generico(coalesce(organo_clave, '')) AS org_generico FROM b WHERE NOT agregada_pscp AND es_menor), y AS ( SELECT x.*, t.tipo, t.agente_secti, (regexp_matches(objeto_low, 'suscrip|subscrip|harpidetza|bases? de datos') AND NOT regexp_matches(objeto_low, 'poliza|seguro|mantenimiento|software|mysql|oracle|\bsql\b|servidor|licencias? de (uso|software)|gestor')) AS da9, (length(trim(coalesce(objeto, ''))) >= 20 AND trim(coalesce(objeto, '')) <> '(sin objeto)' AND c.objeto_base IS NULL) AS objeto_info FROM x LEFT JOIN otipo t USING (organo_clave) LEFT JOIN cat c ON c.source = x.source AND c.objeto_base = x.objeto_base) SELECT y.*, (propia AND anio BETWEEN 2019 AND 2025 AND NOT coalesce(es_menor_dudoso, FALSE) AND NOT da9 AND organo_clave IS NOT NULL AND NOT org_generico AND NOT pf) AS pre FROM y; -- Publicación doble (P4): mismo órgano, empresa (o nombre), importe publicado, fecha (o año), objeto y expediente; en una -- pasada estrecha (la ventana sobre la tabla ancha no cabía en 7 GB) CREATE OR REPLACE TABLE dbl AS SELECT source, id_canonical, row_number() OVER (PARTITION BY source, organo_clave, coalesce(empresa_clave, adjudicatario_nombre), imp_adj_eur, coalesce(fecha_adj::VARCHAR, 'a' || anio::VARCHAR), objeto_base, coalesce(expediente, '') ORDER BY id_canonical) > 1 AS doble FROM p0 WHERE pre; CREATE OR REPLACE TABLE p AS SELECT p0.*, coalesce(d.doble, FALSE) AS doble, (p0.pre AND NOT coalesce(d.doble, FALSE)) AS en_pob FROM p0 LEFT JOIN dbl d USING (source, id_canonical); DROP TABLE p0; DROP TABLE dbl; ANALYZE p; -- @max 40 -- @ Embudo por fuente (2019-2025): propias → población SELECT source, count(*) FILTER (WHERE anio BETWEEN 2019 AND 2025) filas, count(*) FILTER (WHERE propia AND anio BETWEEN 2019 AND 2025) propias, count(*) FILTER (WHERE propia AND anio BETWEEN 2019 AND 2025 AND coalesce(es_menor_dudoso, FALSE)) dudosas, count(*) FILTER (WHERE propia AND anio BETWEEN 2019 AND 2025 AND da9) da9, count(*) FILTER (WHERE propia AND anio BETWEEN 2019 AND 2025 AND (organo_clave IS NULL OR org_generico)) organo_nulo_o_generico, count(*) FILTER (WHERE propia AND anio BETWEEN 2019 AND 2025 AND pf) persona_fisica, count(*) FILTER (WHERE doble) dobles, count(*) FILTER (WHERE en_pob) poblacion, count(*) FILTER (WHERE en_pob AND puede_ser_persona) pob_puede_ser_persona, count(*) FILTER (WHERE en_pob AND varias) pob_varias_adjudicatarios FROM p GROUP BY ALL ORDER BY poblacion DESC; -- @max 60 -- @ Órganos genéricos que quedan fuera, por fuente SELECT source, organo_clave, count(*) filas FROM p WHERE org_generico AND propia AND anio BETWEEN 2019 AND 2025 GROUP BY ALL ORDER BY filas DESC LIMIT 60; -- Fechas convencionales por fuente y año (§3): fechas con ≥ 1 % de las filas con fecha del año y ≥ 10 × la mediana diaria CREATE OR REPLACE TABLE fconv AS WITH d AS (SELECT source, anio, fecha_adj, count(*) n FROM p WHERE en_pob AND fecha_ok GROUP BY ALL), t AS (SELECT source, anio, sum(n) tot, median(n) med FROM d GROUP BY ALL) SELECT d.source, d.anio, d.fecha_adj, d.n, t.tot, t.med, round(100.0 * d.n / t.tot, 2) pct FROM d JOIN t USING (source, anio) WHERE d.n >= 0.01 * t.tot AND d.n >= 10 * t.med; -- @max 80 -- @ Fechas convencionales detectadas SELECT * FROM fconv ORDER BY source, anio, fecha_adj; ALTER TABLE p ADD COLUMN fecha_conv BOOLEAN; UPDATE p SET fecha_conv = (p.fecha_ok AND EXISTS (SELECT 1 FROM fconv f WHERE f.source = p.source AND f.anio = p.anio AND f.fecha_adj = p.fecha_adj)); -- Criterios por fuente y año, sobre la población CREATE OR REPLACE TABLE crit AS SELECT source, anio, count(*) n, count(DISTINCT organo_clave) organos, round(100.0 * avg(iva_sin::INT), 1) pct_sin_iva, round(100.0 * avg((iva_base IN ('con', 'con_medido'))::INT), 1) pct_con_iva, round(100.0 * avg((iva_base = 'no_consta' OR iva_base IS NULL)::INT), 1) pct_iva_no_consta, round(100.0 * avg(fecha_ok::INT), 1) pct_fecha_ok, round(100.0 * avg(coalesce(fecha_conv, FALSE)::INT), 1) pct_fecha_conv, round(100.0 * avg(objeto_info::INT), 1) pct_objeto_info, round(100.0 * avg((trim(coalesce(objeto, '')) IN ('', '(sin objeto)'))::INT), 1) pct_objeto_vacio, round(100.0 * avg(tipo_conocido::INT), 1) pct_tipo, round(100.0 * avg(nif_publicado::INT), 1) pct_nif_publicado, round(100.0 * avg((empresa_clave IS NOT NULL)::INT), 1) pct_empresa, round(100.0 * avg(puede_ser_persona::INT), 1) pct_puede_ser_persona, round(100.0 * avg(varias::INT), 1) pct_varias, round(100.0 * avg(copia_fuera::INT), 1) pct_copia_fuera, round(100.0 * avg(coalesce(num_ofertas >= 1, FALSE)::INT), 1) pct_num_ofertas, round(100.0 * count(*) FILTER (WHERE num_ofertas = 1) / nullif(count(*) FILTER (WHERE num_ofertas >= 1), 0), 1) pct_1_oferta, round(100.0 * avg((url_origen IS NOT NULL AND url_origen <> '')::INT), 1) pct_url, round(100.0 * avg((coalesce(tipo, 'sin_tipo') = 'sin_tipo')::INT), 1) pct_organo_sin_tipo, round(100.0 * avg(coalesce(agente_secti, FALSE)::INT), 1) pct_agente_secti FROM p WHERE en_pob GROUP BY ALL; -- @max 200 -- @ Criterios por fuente y año (población) SELECT * FROM crit ORDER BY source, anio; -- @max 40 -- @ Criterios por fuente (2019-2025) SELECT source, sum(n) n, round(sum(pct_sin_iva * n) / sum(n), 1) pct_sin_iva, round(sum(pct_fecha_ok * n) / sum(n), 1) pct_fecha_ok, round(sum(pct_fecha_conv * n) / sum(n), 1) pct_fecha_conv, round(sum(pct_objeto_info * n) / sum(n), 1) pct_objeto_info, round(sum(pct_tipo * n) / sum(n), 1) pct_tipo, round(sum(pct_nif_publicado * n) / sum(n), 1) pct_nif, round(sum(pct_empresa * n) / sum(n), 1) pct_empresa, round(sum(pct_copia_fuera * n) / sum(n), 1) pct_copia_fuera, round(sum(pct_num_ofertas * n) / sum(n), 1) pct_num_ofertas, round(sum(pct_url * n) / sum(n), 1) pct_url FROM crit GROUP BY ALL ORDER BY n DESC; -- @max 80 -- @ Fuente que cuenta de las copias (dup_fuente), por fuente SELECT source, dup_fuente, count(*) FROM p WHERE en_pob AND copia_fuera GROUP BY ALL ORDER BY 1, 3 DESC; -- @max 120 -- @ Tipo de contrato por fuente (población) SELECT source, tipo_contrato, count(*) FROM p WHERE en_pob GROUP BY ALL ORDER BY 1, 3 DESC; -- @max 80 -- @ fecha_adj_es y anio_precision por fuente (población) SELECT source, fecha_adj_es, anio_precision, count(*) FROM p WHERE en_pob GROUP BY ALL ORDER BY 1, 4 DESC; -- @max 80 -- @ Tipo de órgano por fuente (población, filas) SELECT source, coalesce(tipo, 'sin_tipo') tipo, count(*) filas, count(DISTINCT organo_clave) organos FROM p WHERE en_pob GROUP BY ALL ORDER BY 1, 3 DESC; -- @max 80 -- @ Vía de la clave del órgano por fuente (población) SELECT source, organo_via, count(*) filas, count(DISTINCT organo_clave) organos FROM p WHERE en_pob GROUP BY ALL ORDER BY 1, 3 DESC;