-- 03. M2, M6 y M4 por variante-órgano-año sobre `pobv` (01). Copia de p3/sql/03_m2_m6_m4.sql (guardada como -- sql/_p3_03_m2_m6_m4.sql) con `var` en cada agrupación y sin las réplicas de la prueba B y de DECISION (ya hechas en P3). -- Definiciones idénticas a P3 (PLAN §2): M2 serv./sum. [14.900, 15.000) sobre [5.000, 15.000) (investigación [49.900, 50.000) -- sobre [20.000, 50.000)); obras [39.000, 40.000) sobre [20.000, 40.000); robustez 250 y 500 € (serv./sum.). M6 = % con -- num_ofertas = 1. M4 = cuota del primer proveedor y HHI en órgano-años con ≥ 50 menores y ≥ 200.000 €. CREATE OR REPLACE TABLE m2b AS SELECT var, organo_clave, anio, tipo, base, CASE WHEN tipo_contrato = 'Obras' THEN 'obras' WHEN tipo = 'investigacion' THEN 'serv_sum_investigacion' ELSE 'serv_sum' END AS grupo_m2, umbral, CASE WHEN tipo_contrato = 'Obras' THEN 20000 WHEN tipo = 'investigacion' THEN 20000 ELSE 5000 END AS desde FROM pobv WHERE tipo_contrato IN ('Servicios', 'Suministros', 'Obras'); CREATE OR REPLACE TABLE m2_oa AS SELECT var, organo_clave, anio, count(*) FILTER (WHERE grupo_m2 IN ('serv_sum', 'serv_sum_investigacion') AND base >= desde AND base < umbral) AS m2_ss_n, count(*) FILTER (WHERE grupo_m2 IN ('serv_sum', 'serv_sum_investigacion') AND base >= umbral - 100 AND base < umbral) AS m2_ss_borde100, count(*) FILTER (WHERE grupo_m2 IN ('serv_sum', 'serv_sum_investigacion') AND base >= umbral - 250 AND base < umbral) AS m2_ss_borde250, count(*) FILTER (WHERE grupo_m2 IN ('serv_sum', 'serv_sum_investigacion') AND base >= umbral - 500 AND base < umbral) AS m2_ss_borde500, count(*) FILTER (WHERE grupo_m2 IN ('serv_sum', 'serv_sum_investigacion') AND base >= umbral - 1000 AND base < umbral) AS m2_ss_ultimos1000, any_value(umbral) FILTER (WHERE grupo_m2 IN ('serv_sum', 'serv_sum_investigacion')) AS m2_ss_umbral, count(*) FILTER (WHERE grupo_m2 = 'obras' AND base >= desde AND base < umbral) AS m2_ob_n, count(*) FILTER (WHERE grupo_m2 = 'obras' AND base >= umbral - 1000 AND base < umbral) AS m2_ob_borde1000 FROM m2b GROUP BY ALL; -- @ M2 global, variante 'sin' y 'con', 2019-2025 (P3 población común: 10,28 % serv./sum., 26,97 % obras) SELECT var, grupo_m2, count(*) FILTER (WHERE base >= desde AND base < umbral) n_ventana, round(100.0 * count(*) FILTER (WHERE base >= umbral - (CASE WHEN grupo_m2 = 'obras' THEN 1000 ELSE 100 END) AND base < umbral) / count(*) FILTER (WHERE base >= desde AND base < umbral), 2) pct FROM m2b GROUP BY ALL ORDER BY 1, 2; CREATE OR REPLACE TABLE m6_oa AS SELECT var, organo_clave, anio, count(num_ofertas) AS m6_n, count(*) FILTER (WHERE num_ofertas = 1) AS m6_n1, count(*) FILTER (WHERE num_ofertas = 2) AS m6_n2, count(*) FILTER (WHERE num_ofertas >= 3) AS m6_n3mas FROM pobv GROUP BY ALL; CREATE OR REPLACE TABLE m4_oe AS SELECT var, organo_clave, anio, empresa_clave, sum(base) AS imp, count(*) AS n FROM pobv GROUP BY ALL; CREATE OR REPLACE TABLE m4_oa AS WITH t AS (SELECT var, organo_clave, anio, sum(imp) tot, sum(n) n, count(*) proveedores FROM m4_oe GROUP BY ALL), r AS (SELECT e.*, t.tot, t.n n_org, t.proveedores, row_number() OVER (PARTITION BY e.var, e.organo_clave, e.anio ORDER BY e.imp DESC, e.empresa_clave) rk FROM m4_oe e JOIN t USING (var, organo_clave, anio)) SELECT var, organo_clave, anio, any_value(n_org) AS m4_n, any_value(tot) AS m4_importe, any_value(proveedores) AS m4_proveedores, max(imp) FILTER (WHERE rk = 1) / any_value(tot) AS m4_cuota_top1, sum((imp / tot) * (imp / tot)) AS m4_hhi, (any_value(n_org) >= 50 AND any_value(tot) >= 200000) AS m4_elegible FROM r GROUP BY var, organo_clave, anio; -- @ M6 y M4, variante 'sin', 2024 (P3: M6 83,0 % con 1; M4 2024 1.136 elegibles, cuota p50 8,8 %, p90 18,3 %) SELECT (SELECT round(100.0 * sum(m6_n1) / sum(m6_n), 1) FROM m6_oa WHERE var = 'sin' AND anio = 2024) m6_pct_1_2024, (SELECT count(*) FROM m4_oa WHERE var = 'sin' AND anio = 2024 AND m4_elegible) m4_elegibles_2024, (SELECT round(100 * quantile_cont(m4_cuota_top1, 0.5), 1) FROM m4_oa WHERE var = 'sin' AND anio = 2024 AND m4_elegible) m4_p50, (SELECT round(100 * quantile_cont(m4_cuota_top1, 0.9), 1) FROM m4_oa WHERE var = 'sin' AND anio = 2024 AND m4_elegible) m4_p90;