-- 04. Indicadores por órgano, fuente y año (definiciones §4-§6) sobre la tabla p de 03. Fuera de la PLACSP (la PLACSP -- es la de P4). Decisión por fuente y año con las reglas del §3, base exacta o con cota por fila, umbral por clase. SET threads = 4; -- Decisión por fuente y año CREATE OR REPLACE TABLE dec AS WITH c AS (SELECT * FROM crit), dup AS (SELECT source, anio, dup_fuente, count(*) n FROM p WHERE en_pob AND copia_fuera GROUP BY source, anio, dup_fuente QUALIFY row_number() OVER (PARTITION BY source, anio ORDER BY count(*) DESC, dup_fuente) = 1), fc AS (SELECT source, anio, string_agg(strftime(fecha_adj, '%d-%m-%Y') || ' (' || replace(pct::VARCHAR, '.', ',') || ' %)', ', ' ORDER BY fecha_adj) fechas FROM fconv GROUP BY ALL) SELECT c.*, dup.dup_fuente AS fuente_principal_copias, fc.fechas AS fechas_convencionales, (c.pct_copia_fuera >= 50) AS espejo, (c.source = 'CATALUNYA_PSCP' AND c.anio < 2023) AS pscp_no_comparable, (c.pct_copia_fuera < 50 AND NOT (c.source = 'CATALUNYA_PSCP' AND c.anio < 2023) AND c.source <> 'PLACSP') AS calc, (c.pct_objeto_vacio < 50) AS ok_c2_objeto, (c.pct_nif_publicado >= 50) AS ok_c2_adj, (c.pct_fecha_ok >= 90 AND c.pct_empresa >= 60 AND c.pct_fecha_conv <= 50) AS ok_m1, (c.pct_fecha_ok >= 90 AND c.pct_empresa >= 60 AND c.pct_fecha_conv <= 50 AND c.pct_objeto_info >= 50) AS ok_m1r, (c.pct_sin_iva >= 50) AS ok_m2, (c.pct_num_ofertas >= 20) AS ok_m6_cobertura, -- M6 solo donde el cotejo confirma que es el dato publicado (§3): la Comunidad de Madrid (20 de 20 fichas) (c.pct_num_ofertas >= 20 AND c.source IN ('MADRID_CAM')) AS ok_m6, (c.pct_empresa >= 60) AS ok_m4, -- Cambio tras el cotejo (§7.4): la API de Euskadi publica en 2021 parte de los importes multiplicados por 100 (7 de 22 -- menores M3 cotejados con su ficha; 0 de 48 candidatos de los demás años): sin indicadores de importe ese año NOT (c.source = 'EUSKADI' AND c.anio = 2021) AS importe_fiable FROM c LEFT JOIN dup USING (source, anio) LEFT JOIN fc USING (source, anio); -- Población de M3 y de los descriptores (fuentes y años con cálculo): sin varios adjudicatarios ni filas que pueden ser -- de una persona (sin NIF y sin empresa identificada); base exacta o con cota; umbral por clase CREATE OR REPLACE TABLE q AS SELECT p.source AS fuente, p.id_canonical, p.organo_clave, p.anio, p.fecha_adj, p.fecha_ok, coalesce(p.fecha_conv, FALSE) AS fecha_conv, p.empresa_clave, p.tipo_contrato, p.tipo_conocido, p.obras, coalesce(p.tipo, 'sin_tipo') AS tipo, coalesce(p.agente_secti, FALSE) AS agente, p.iva_sin, p.iva_base, p.objeto_info, p.objeto_base AS objeto_norm, p.num_ofertas, p.supera_umbral_menor, p.copia_fuera, p.objeto, p.url_origen, p.expediente, p.adjudicatario_nombre, p.imp_adj_eur, p.imp_adj_eur_corregido, p.menor_dudoso_motivo, CASE WHEN p.iva_sin THEN p.imp_adj_eur_comparable ELSE p.imp_adj_eur_corregido / 1.21 END AS base_d, -- exacta: base sin IVA, tipo de contrato conocido y, en servicios y suministros, tipo de órgano clasificado (si no, -- no se sabe si es agente de la DA 54.ª y M3 usa el umbral mayor: cambio tras medir, salidas/05c) (p.iva_sin AND p.tipo_conocido AND NOT (coalesce(p.tipo, 'sin_tipo') IN ('otro', 'sin_tipo') AND coalesce(p.tipo_contrato IN ('Servicios', 'Suministros'), FALSE))) AS exacta, (coalesce(p.tipo, 'sin_tipo') IN ('otro', 'sin_tipo')) AS tipo_organo_sin_clasificar, CASE WHEN NOT p.tipo_conocido THEN 'sin_tipo' WHEN p.obras THEN 'obras' ELSE 'resto' END AS clase FROM p JOIN dec d ON d.source = p.source AND d.anio = p.anio WHERE p.en_pob AND d.calc AND d.importe_fiable AND NOT p.varias AND NOT p.puede_ser_persona AND (CASE WHEN p.iva_sin THEN p.imp_adj_eur_comparable ELSE p.imp_adj_eur_corregido END) > 0; ALTER TABLE q ADD COLUMN base DECIMAL(18,2); UPDATE q SET base = base_d::DECIMAL(18,2); -- Umbral de M3 (art. 118; DA 54.ª para agentes, servicios y suministros con fecha desde el 5-7-2018 o año ≥ 2019; tipo -- desconocido: el mayor posible) y de los descriptores (como P4: 50.000 € solo para el tipo de órgano «investigacion») ALTER TABLE q ADD COLUMN umbral_m3 INTEGER; UPDATE q SET umbral_m3 = CASE WHEN clase = 'obras' THEN 40000 WHEN clase = 'resto' AND (agente OR tipo_organo_sin_clasificar) AND tipo_contrato IN ('Servicios', 'Suministros') AND coalesce(fecha_adj >= DATE '2018-07-05', fecha_adj IS NULL AND anio >= 2019, FALSE) THEN 50000 WHEN clase = 'resto' THEN 15000 WHEN agente OR tipo_organo_sin_clasificar THEN 50000 ELSE 40000 END; ALTER TABLE q ADD COLUMN umbral INTEGER; UPDATE q SET umbral = CASE WHEN clase = 'obras' THEN 40000 WHEN tipo = 'investigacion' THEN 50000 WHEN clase = 'resto' THEN 15000 ELSE 40000 END; ANALYZE q;