-- 06. Control §7.1: el motor de P9 (04b) sobre la PLACSP con la población de P4 (vista única; etl/indicadores_organo.py, -- placsp_menores + poblacion + m3) frente a las tablas de producción (aggregations_{F}/indicadores). Base de datos aparte -- (tmp/ctrl.duckdb) con la de P9 adjunta en solo lectura. ATTACH '/w/tmp/p9.duckdb' AS m (READ_ONLY); CREATE OR REPLACE TABLE otipo AS SELECT * FROM m.otipo; CREATE OR REPLACE TABLE dec AS SELECT 'PLACSP' AS source, a AS anio, TRUE ok_m1, TRUE ok_m1r, TRUE ok_m2, TRUE ok_m6_cobertura, TRUE ok_m6, TRUE ok_m4 FROM range(2019, 2026) t(a); CREATE OR REPLACE TABLE q AS WITH f AS ( SELECT b.*, lower(strip_accents(coalesce(b.objeto, ''))) AS objeto_low, coalesce(t.tipo, 'sin_tipo') AS tipo, t.agente_secti FROM m.b b LEFT JOIN m.otipo t USING (organo_clave) WHERE b.source = 'PLACSP' AND b.es_menor AND (coalesce(b.cuenta, TRUE) AND b.copia_de IS NULL AND b.correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente') AND b.anio_ref BETWEEN 2019 AND 2025 AND b.fecha_adj BETWEEN DATE '2019-01-01' AND DATE '2025-12-31' AND b.iva_base IN ('sin', 'sin_medido') AND b.imp_adj_eur_comparable > 0 AND b.persona_fisica = FALSE AND NOT coalesce(b.es_menor_dudoso, FALSE) AND b.organo_clave IS NOT NULL AND NOT coalesce(b.anulado_en_origen, FALSE)), g AS ( SELECT *, trim(regexp_replace(objeto_low, '[^a-z]+', ' ', 'g')) AS objeto_norm, (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 FROM f), d AS (SELECT *, row_number() OVER (PARTITION BY organo_clave, coalesce(empresa_clave, ''), imp_adj_eur_comparable, fecha_adj, objeto_norm, coalesce(expediente, '') ORDER BY id_canonical) > 1 AS dup FROM g WHERE NOT da9) SELECT 'PLACSP' AS fuente, id_canonical, organo_clave, year(fecha_adj) AS anio, fecha_adj, TRUE AS fecha_ok, FALSE AS fecha_conv, empresa_clave, tipo_contrato, TRUE AS tipo_conocido, coalesce(tipo_contrato IN ('Obras', 'Concesion_obras'), FALSE) AS obras, tipo, coalesce(agente_secti, FALSE) AS agente, TRUE AS iva_sin, iva_base, TRUE AS objeto_info, objeto_norm, num_ofertas, supera_umbral_menor, FALSE AS copia_fuera, objeto, url_origen, expediente, adjudicatario_nombre, imp_adj_eur, imp_adj_eur_corregido, menor_dudoso_motivo, imp_adj_eur_comparable AS base_d, TRUE AS exacta, FALSE AS tipo_organo_sin_clasificar, CASE WHEN tipo_contrato = 'Obras' THEN 'obras' ELSE 'resto' END AS clase FROM d WHERE NOT dup; ALTER TABLE q ADD COLUMN base DECIMAL(18,2); UPDATE q SET base = base_d::DECIMAL(18,2); -- Umbral de M3 como P4 (umbral_m3: obras y concesiones de obras 40.000; agente con servicios o suministros 50.000) y de -- los descriptores como P4 (obras 40.000; tipo investigación 50.000; resto 15.000) ALTER TABLE q ADD COLUMN umbral_m3 INTEGER; UPDATE q SET umbral_m3 = CASE WHEN tipo_contrato IN ('Obras', 'Concesion_obras') THEN 40000 WHEN agente AND tipo_contrato IN ('Servicios', 'Suministros') THEN 50000 ELSE 15000 END; ALTER TABLE q ADD COLUMN umbral INTEGER; UPDATE q SET umbral = CASE WHEN tipo_contrato = 'Obras' THEN 40000 WHEN tipo = 'investigacion' THEN 50000 ELSE 15000 END; ANALYZE q; -- @include /w/sql/04b_motor.sql -- Producción (las 15 claves seudónimas «PF:» no se pueden casar: fuera de la comparación) CREATE OR REPLACE VIEW prod_d AS SELECT * FROM read_parquet('/d/aggregations_{F}/indicadores/descriptores_organo_anio.parquet') WHERE organo_clave NOT LIKE 'PF:%'; CREATE OR REPLACE VIEW prod_m3 AS SELECT * FROM read_parquet('/d/aggregations_{F}/indicadores/m3_organo_anio.parquet') WHERE organo_clave NOT LIKE 'PF:%'; CREATE OR REPLACE TABLE mia AS SELECT m3.organo_clave, m3.anio, m3.menores AS m3_menores, m3.m3_n, m3.m3_n_igual, m3.m3_n_supera, m3.m3_importe, m1.m1_menores AS menores, m1.m1_importe AS importe, m1.m1a_menores, m1.m1a_importe, m1.m1r_series, m1.m1r_contratos, m1.m1r_importe, m1.m1e_dias, m1.m1e_contratos, m1.m1e_importe, m1.m1e_dias_relacion_continua, m2.m2_ss_n, m2.m2_ss_borde100, m2.m2_ob_n, m2.m2_ob_borde1000, m6.m6_n, m6.m6_n1, m6.m6_n3mas, m4.m4_proveedores, m4.m4_cuota_top1_pct, m4.m4_hhi, coalesce(m4.m4_elegible, FALSE) m4_elegible FROM m3_oa m3 LEFT JOIN m1_oa m1 USING (fuente, organo_clave, anio) LEFT JOIN m2_oa m2 USING (fuente, organo_clave, anio) LEFT JOIN m6_oa m6 USING (fuente, organo_clave, anio) LEFT JOIN m4_oa m4 USING (fuente, organo_clave, anio) WHERE organo_clave NOT IN (SELECT organo_clave FROM m.b WHERE regexp_matches(organo_clave, '^([0-9]{8}[A-Z]|[XYZKLM][0-9]{7}[A-Z])(\||$)')); -- @ num_ofertas = 0 en la población de los descriptores (P4 cuenta count(num_ofertas); el motor, los ≥ 1) SELECT count(*) FILTER (WHERE num_ofertas = 0) cero, count(num_ofertas) con_dato FROM q WHERE empresa_clave IS NOT NULL; -- @ M3: órgano-años y diferencias frente a producción SELECT (SELECT count(*) FROM prod_m3) prod, (SELECT count(*) FROM mia) mia, (SELECT count(*) FROM prod_m3 p FULL JOIN mia USING (organo_clave, anio) WHERE p.organo_clave IS NULL OR mia.m3_menores IS NULL) sin_pareja, (SELECT count(*) FROM prod_m3 p JOIN mia USING (organo_clave, anio) WHERE p.menores <> mia.m3_menores) menores_distintos, (SELECT count(*) FROM prod_m3 p JOIN mia USING (organo_clave, anio) WHERE p.m3_n <> mia.m3_n OR p.m3_n_igual <> mia.m3_n_igual OR p.m3_n_supera <> mia.m3_n_supera OR p.m3_importe::DECIMAL(18,2) <> mia.m3_importe) m3_distintos, (SELECT sum(m3_n) FROM prod_m3) m3_prod, (SELECT sum(m3_n) FROM mia) m3_mia; -- @ Descriptores: órgano-años y diferencias frente a producción (valor a valor) WITH j AS (SELECT p.*, mia.menores mm, mia.importe mi, mia.m1a_menores a1, mia.m1a_importe a2, mia.m1r_series r1, mia.m1r_contratos r2, mia.m1r_importe r3, mia.m1e_dias e1, mia.m1e_contratos e2, mia.m1e_importe e3, mia.m1e_dias_relacion_continua e4, mia.m2_ss_n s1, mia.m2_ss_borde100 s2, mia.m2_ob_n o1, mia.m2_ob_borde1000 o2, mia.m6_n x1, mia.m6_n1 x2, mia.m6_n3mas x3, mia.m4_proveedores h1, mia.m4_cuota_top1_pct h2, mia.m4_hhi h3, mia.m4_elegible h4 FROM prod_d p LEFT JOIN mia USING (organo_clave, anio)) SELECT count(*) prod, count(mm) con_pareja, count(*) FILTER (WHERE menores IS DISTINCT FROM mm OR importe::DECIMAL(18,2) IS DISTINCT FROM mi) menores_importe, count(*) FILTER (WHERE m1a_menores IS DISTINCT FROM a1 OR m1a_importe::DECIMAL(18,2) IS DISTINCT FROM a2) m1a, count(*) FILTER (WHERE m1r_series IS DISTINCT FROM r1 OR m1r_contratos IS DISTINCT FROM r2 OR m1r_importe::DECIMAL(18,2) IS DISTINCT FROM r3) m1r, count(*) FILTER (WHERE m1e_dias IS DISTINCT FROM e1 OR m1e_contratos IS DISTINCT FROM e2 OR m1e_importe::DECIMAL(18,2) IS DISTINCT FROM e3 OR m1e_dias_relacion_continua IS DISTINCT FROM e4) m1e, count(*) FILTER (WHERE m2_ss_n IS DISTINCT FROM coalesce(s1, 0) OR m2_ss_borde100 IS DISTINCT FROM coalesce(s2, 0) OR m2_ob_n IS DISTINCT FROM coalesce(o1, 0) OR m2_ob_borde1000 IS DISTINCT FROM coalesce(o2, 0)) m2, count(*) FILTER (WHERE coalesce(m6_n, 0) IS DISTINCT FROM coalesce(x1, 0) OR coalesce(m6_n1, 0) IS DISTINCT FROM coalesce(x2, 0) OR coalesce(m6_n3mas, 0) IS DISTINCT FROM coalesce(x3, 0)) m6, count(*) FILTER (WHERE m4_proveedores IS DISTINCT FROM h1 OR abs(m4_cuota_top1_pct - h2) > 1e-6 OR abs(m4_hhi - h3) > 1e-6 OR m4_elegible IS DISTINCT FROM h4) m4 FROM j; -- @ Las primeras diferencias de M1e o M2 (si las hay) SELECT p.organo_clave, p.anio, p.m1e_dias, mia.m1e_dias, p.m1e_contratos, mia.m1e_contratos, p.m2_ss_n, mia.m2_ss_n FROM prod_d p JOIN mia USING (organo_clave, anio) WHERE p.m1e_dias IS DISTINCT FROM mia.m1e_dias OR p.m2_ss_n IS DISTINCT FROM coalesce(mia.m2_ss_n, 0) LIMIT 10;