-- 04. Tabla órgano-año de P6 en formato largo (`oal`): una fila por variante, órgano, año e indicador, con numerador, -- denominador, n de elegibilidad, valor (%), elegible y percentil en su tipo y año (percent_rank × 100 entre los elegibles -- de la misma variante, indicador, tipo v3.2 y año, como P3). Elegibilidad como P3: M1x y M6 con ≥ 20 menores (M6: con -- dato), M2 con ≥ 20 en la ventana, C1 con n ≥ 20, M4 con ≥ 50 menores y ≥ 200.000 €. -- Indicadores: m1a, m1r, m1e (% del importe), m1a_nat y m1r_nat (año natural, solo 'sin'), m2 (100 €), m2_250, m2_500, -- m6 (% con 1 oferta), m4 (cuota del primer proveedor), c1 (P2, fuente PLACSP, % > 90 días) y c1_p3 (P3, todas sus -- fuentes). C1 no depende de la variante de anulaciones (viene de P2/P3): se repite igual en las tres. CREATE OR REPLACE TABLE oa AS SELECT x.var, x.organo_clave, x.anio, any_value(x.tipo) tipo, any_value(x.sector_aparte) sector_aparte, count(*) menores, sum(x.base) importe, coalesce(sum(x.base) FILTER (WHERE x.en_m1a), 0) m1a_imp, coalesce(sum(x.base) FILTER (WHERE x.en_m1r), 0) m1r_imp, coalesce(sum(x.base) FILTER (WHERE x.en_m1e), 0) m1e_imp, count(DISTINCT x.dia_m1e) m1e_dias, count(*) FILTER (WHERE x.en_m1e) m1e_contratos, count(*) FILTER (WHERE x.en_m1r) m1r_contratos, coalesce(sum(x.base) FILTER (WHERE x.en_m1a_nat), 0) m1a_nat_imp, coalesce(sum(x.base) FILTER (WHERE x.en_m1r_nat), 0) m1r_nat_imp FROM m1x x GROUP BY x.var, x.organo_clave, x.anio; CREATE OR REPLACE TABLE c1src AS SELECT organo_clave, anio, sum(n) c1_n, sum(n_mas_90) c1_num FROM read_parquet('/p2/c1_organo_anio.parquet') WHERE fuente = 'PLACSP' GROUP BY ALL; CREATE OR REPLACE TABLE c1p3src AS SELECT organo_clave, anio, c1_n, c1_n_mas_90d c1_num FROM read_parquet('/p3/c1_organo_anio_p3.parquet'); CREATE OR REPLACE TABLE oal AS WITH l AS ( SELECT var, organo_clave, anio, 'm1a' indicador, m1a_imp num, importe den, menores n_elig, NULL::BOOLEAN el4 FROM oa UNION ALL SELECT var, organo_clave, anio, 'm1r', m1r_imp, importe, menores, NULL FROM oa UNION ALL SELECT var, organo_clave, anio, 'm1e', m1e_imp, importe, menores, NULL FROM oa UNION ALL SELECT var, organo_clave, anio, 'm1a_nat', m1a_nat_imp, importe, menores, NULL FROM oa WHERE var = 'sin' UNION ALL SELECT var, organo_clave, anio, 'm1r_nat', m1r_nat_imp, importe, menores, NULL FROM oa WHERE var = 'sin' UNION ALL SELECT var, organo_clave, anio, 'm2', m2_ss_borde100, m2_ss_n, m2_ss_n, NULL FROM m2_oa WHERE m2_ss_n > 0 UNION ALL SELECT var, organo_clave, anio, 'm2_250', m2_ss_borde250, m2_ss_n, m2_ss_n, NULL FROM m2_oa WHERE m2_ss_n > 0 UNION ALL SELECT var, organo_clave, anio, 'm2_500', m2_ss_borde500, m2_ss_n, m2_ss_n, NULL FROM m2_oa WHERE m2_ss_n > 0 UNION ALL SELECT var, organo_clave, anio, 'm6', m6_n1, m6_n, m6_n, NULL FROM m6_oa WHERE m6_n > 0 UNION ALL SELECT var, organo_clave, anio, 'm4', m4_cuota_top1 * m4_importe, m4_importe, m4_n, m4_elegible FROM m4_oa UNION ALL SELECT v.var, c.organo_clave, c.anio, 'c1', c.c1_num, c.c1_n, c.c1_n, NULL FROM c1src c CROSS JOIN (VALUES ('con'), ('sin'), ('sinsm')) v(var) WHERE c.c1_n > 0 UNION ALL SELECT v.var, c.organo_clave, c.anio, 'c1_p3', c.c1_num, c.c1_n, c.c1_n, NULL FROM c1p3src c CROSS JOIN (VALUES ('con'), ('sin'), ('sinsm')) v(var) WHERE c.c1_n > 0), -- POSTERIOR (determinismo): el valor se redondea a 6 decimales; sin redondeo, el último bit de las sumas en paralelo -- cambiaba de una ejecución a otra y movía empates (M4 entre dos ejecuciones). t AS (SELECT l.*, coalesce(o.tipo, 'sin_tipo') tipo, round(100.0 * l.num / l.den, 6) AS val, CASE WHEN l.indicador = 'm4' THEN coalesce(l.el4, FALSE) ELSE l.n_elig >= 20 END AS elegible FROM l LEFT JOIN otipo o USING (organo_clave)) SELECT t.*, CASE WHEN elegible THEN 100 * percent_rank() OVER (PARTITION BY var, indicador, tipo, anio, elegible ORDER BY val) END AS pct_tipo, CASE WHEN elegible THEN count(*) OVER (PARTITION BY var, indicador, tipo, anio, elegible) END AS n_tipo FROM t; ANALYZE oal; COPY (SELECT * FROM oal ORDER BY var, indicador, anio, organo_clave) TO '/w/salidas/indicadores_organo_anio_p6.parquet' (FORMAT PARQUET); COPY (SELECT oa.*, o.nombre organo_nombre FROM oa JOIN org_id o USING (organo_clave) WHERE var = 'sin' ORDER BY anio, organo_clave) TO '/w/salidas/oa_sin_p6.parquet' (FORMAT PARQUET); -- @ Comprobación: una fila por variante-órgano-año-indicador; percentiles en [0, 100]; sumas de menores = pobv SELECT (SELECT count(*) FROM oal) - (SELECT count(DISTINCT (var, organo_clave, anio, indicador)) FROM oal) repetidas, (SELECT count(*) FROM oal WHERE pct_tipo NOT BETWEEN 0 AND 100) pct_fuera, (SELECT sum(menores) FROM oa) - (SELECT count(*) FROM pobv) dif_menores; -- @ Órgano-años elegibles por indicador, variante 'sin' y año SELECT indicador, anio, count(*) organo_anios, count(*) FILTER (WHERE elegible) elegibles, round(median(val) FILTER (WHERE elegible), 2) mediana_val, count(*) FILTER (WHERE elegible AND pct_tipo >= 90) decil_superior FROM oal WHERE var = 'sin' GROUP BY ALL ORDER BY 1, 2;