-- ── C2 (menores) para las fuentes y años con cálculo ────────────────────────────────────────────────────────────── CREATE OR REPLACE TABLE c2_oa AS WITH b0 AS ( SELECT p.source AS fuente, p.organo_clave, p.anio, d.ok_c2_objeto, d.ok_c2_adj, CASE WHEN d.ok_c2_objeto THEN NOT p.objeto_info END AS falta_objeto, (greatest(coalesce(p.imp_adj_eur, 0), coalesce(p.imp_adj_eur_con_iva, 0), coalesce(p.imp_adj_eur_sin_iva, 0)) <= 0) AS falta_importe, CASE WHEN d.ok_c2_adj THEN NOT (coalesce(p.clase_nif IN ('valido', 'extranjero'), FALSE) OR coalesce(p.adjudicatario_nif_valid, FALSE) OR regexp_matches(lower(strip_accents(coalesce(p.adjudicatario_nombre, ''))), '(\b(s\.?\s?a\.?\s?u?|s\.?\s?l\.?\s?[lunp]?|s\.?\s?coop[a-z\. ]*|sccl|s\.?\s?c\.?\s?[cpal]?|c\.?\s?b|s\.?\s?a\.?\s?t|a\.?\s?i\.?\s?e|u\.?\s?t\.?\s?e|gmbh|ltd|limited|inc|llc|plc|b\.?\s?v|n\.?\s?v|ag|sarl|sas|s\.?\s?p\.?\s?a|s\.?\s?r\.?\s?l|lda|oy|aps|kg)\.?\s*\)?$)|sociedad|societat|sociedade|cooperativ|fundaci|asociaci|associaci|elkartea|union temporal|agrupacion|ayuntamiento|ajuntament|concello|udala|universi|consorci|diputaci|mancomun|colegio|federaci|confederaci|camara|institut|agencia|ministerio|comunidad de|servicio|empresa|entidad|organismo|corporaci|limitada|anonima|\bs\.?\s?a\.?\b|\bs\.?\s?l\.?\b|\bu\.?\s?t\.?\s?e\b|sucursal|\b(llc|l\.l\.c|inc|incorporated|ltd|limited|gmbh|corp|corporation|plc|sarl|s\.a\.r\.l|srl|s\.r\.l|s\.p\.a|b\.v|n\.v)\b')) END AS falta_adj FROM p JOIN dec d ON d.source = p.source AND d.anio = p.anio WHERE p.en_pob AND d.calc AND NOT p.puede_ser_persona) SELECT fuente, organo_clave, anio, count(*) AS c2_n, any_value(ok_c2_objeto) AS c2_con_objeto, any_value(ok_c2_adj) AS c2_con_adjudicatario, round(100.0 * avg(falta_objeto::INT), 6) AS c2_pct_objeto, round(100.0 * avg(falta_importe::INT), 6) AS c2_pct_importe, round(100.0 * avg(falta_adj::INT), 6) AS c2_pct_adjudicatario, count(*) FILTER (WHERE coalesce(falta_objeto, FALSE) OR falta_importe OR coalesce(falta_adj, FALSE)) AS c2_n_incompleta FROM b0 GROUP BY ALL; -- ── Tabla órgano × fuente × año ──────────────────────────────────────────────────────────────────────────────────── CREATE OR REPLACE TABLE ofa_v AS WITH k AS (SELECT fuente, organo_clave, anio FROM m3_oa UNION SELECT fuente, organo_clave, anio FROM c2_oa) SELECT k.fuente, k.organo_clave, k.anio, coalesce(m3.tipo, t.tipo, 'sin_tipo') AS tipo, CASE WHEN et.exacto THEN 'exacta' WHEN et.exacto IS NOT NULL THEN 'cota' END AS base_etiqueta, et.pct_filas_cota, et.pct_en_otra_fuente, m3.menores, m3.filas_cota, m3.m3_n, m3.m3_n_igual, m3.m3_n_supera, m3.m3_n_cota, m3.m3_importe, m3.m3_pct, m1.m1_menores, m1.m1_importe, m1.m1a_menores, m1.m1a_importe, round(100.0 * m1.m1a_importe::DOUBLE / nullif(m1.m1_importe::DOUBLE, 0), 6) AS m1a_pct_importe, m1.m1r_series, m1.m1r_contratos, m1.m1r_importe, round(100.0 * m1.m1r_importe::DOUBLE / nullif(m1.m1_importe::DOUBLE, 0), 6) AS m1r_pct_importe, m1.m1e_dias, m1.m1e_contratos, m1.m1e_importe, round(100.0 * m1.m1e_importe::DOUBLE / nullif(m1.m1_importe::DOUBLE, 0), 6) AS m1e_pct_importe, m1.m1e_dias_relacion_continua, m1.m1e_fuera_fecha_convencional, m2.m2_ss_n, m2.m2_ss_borde100, round(100.0 * m2.m2_ss_borde100 / nullif(m2.m2_ss_n, 0), 6) AS m2_ss_pct, round(100.0 * m2.m2_ss_borde250 / nullif(m2.m2_ss_n, 0), 6) AS m2_ss_pct_250, round(100.0 * m2.m2_ss_borde500 / nullif(m2.m2_ss_n, 0), 6) AS m2_ss_pct_500, m2.m2_ob_n, m2.m2_ob_borde1000, round(100.0 * m2.m2_ob_borde1000 / nullif(m2.m2_ob_n, 0), 6) AS m2_ob_pct, m6.m6_n, m6.m6_n1, m6.m6_n3mas, round(100.0 * m6.m6_n1 / nullif(m6.m6_n, 0), 6) AS m6_pct_1, round(100.0 * m6.m6_n3mas / nullif(m6.m6_n, 0), 6) AS m6_pct_3mas, m4.m4_n, m4.m4_importe, m4.m4_proveedores, m4.m4_cuota_top1_pct, m4.m4_hhi, coalesce(m4.m4_elegible, FALSE) AS m4_elegible, c2.c2_n, c2.c2_con_objeto, c2.c2_con_adjudicatario, c2.c2_pct_objeto, c2.c2_pct_importe, c2.c2_pct_adjudicatario, c2.c2_n_incompleta, round(100.0 * c2.c2_n_incompleta / nullif(c2.c2_n, 0), 6) AS c2_pct_incompleta, (coalesce(m3.menores, 0) >= 20) AS m3_elegible, (coalesce(m1.m1_menores, 0) >= 20) AS m1_elegible, (coalesce(m2.m2_ss_n, 0) >= 20) AS m2_ss_elegible, (coalesce(m2.m2_ob_n, 0) >= 20) AS m2_ob_elegible, (coalesce(m6.m6_n, 0) >= 20) AS m6_elegible, (coalesce(c2.c2_n, 0) >= 20) AS c2_elegible FROM k LEFT JOIN m3_oa m3 USING (fuente, organo_clave, anio) 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) LEFT JOIN c2_oa c2 USING (fuente, organo_clave, anio) LEFT JOIN et_oa et USING (fuente, organo_clave, anio) LEFT JOIN otipo t USING (organo_clave); -- Percentil y mediana en los pares: misma fuente, tipo, año y etiqueta de base (≥ 20 elegibles) CREATE OR REPLACE TABLE ofa AS SELECT *, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m3_elegible AND count(*) OVER g3 >= 20 THEN round(100 * percent_rank() OVER (PARTITION BY fuente, tipo, anio, base_etiqueta, m3_elegible ORDER BY round(m3_pct, 6)), 1) END AS m3_percentil, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m3_elegible AND count(*) OVER g3 >= 20 THEN round(median(round(m3_pct, 6)) OVER g3, 2) END AS m3_mediana_pares, CASE WHEN m3_elegible THEN count(*) OVER g3 END AS m3_n_pares, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m1_elegible AND count(*) OVER g1 >= 20 THEN round(100 * percent_rank() OVER (PARTITION BY fuente, tipo, anio, base_etiqueta, m1_elegible ORDER BY round(m1a_pct_importe, 6)), 1) END AS m1a_percentil, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m1_elegible AND count(*) OVER g1 >= 20 THEN round(median(round(m1a_pct_importe, 6)) OVER g1, 2) END AS m1a_mediana_pares, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m1_elegible AND count(*) OVER g1 >= 20 THEN round(100 * percent_rank() OVER (PARTITION BY fuente, tipo, anio, base_etiqueta, m1_elegible ORDER BY round(m1e_pct_importe, 6)), 1) END AS m1e_percentil, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m1_elegible AND count(*) OVER g1 >= 20 THEN round(median(round(m1e_pct_importe, 6)) OVER g1, 2) END AS m1e_mediana_pares, CASE WHEN m1_elegible THEN count(*) OVER g1 END AS m1_n_pares, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m2_ss_elegible AND count(*) OVER g2 >= 20 THEN round(100 * percent_rank() OVER (PARTITION BY fuente, tipo, anio, m2_ss_elegible ORDER BY round(m2_ss_pct, 6)), 1) END AS m2_ss_percentil, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m2_ss_elegible AND count(*) OVER g2 >= 20 THEN round(median(round(m2_ss_pct, 6)) OVER g2, 2) END AS m2_ss_mediana_pares, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m4_elegible AND count(*) OVER g4 >= 20 THEN round(100 * percent_rank() OVER (PARTITION BY fuente, tipo, anio, base_etiqueta, m4_elegible ORDER BY round(m4_cuota_top1_pct, 6)), 1) END AS m4_percentil, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND m4_elegible AND count(*) OVER g4 >= 20 THEN round(median(round(m4_cuota_top1_pct, 6)) OVER g4, 2) END AS m4_mediana_pares, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND c2_elegible AND count(*) OVER gc >= 20 THEN round(100 * percent_rank() OVER (PARTITION BY fuente, tipo, anio, c2_elegible ORDER BY round(c2_pct_incompleta, 6)), 1) END AS c2_percentil, CASE WHEN tipo NOT IN ('otro', 'sin_tipo') AND c2_elegible AND count(*) OVER gc >= 20 THEN round(median(round(c2_pct_incompleta, 6)) OVER gc, 2) END AS c2_mediana_pares FROM ofa_v WINDOW g3 AS (PARTITION BY fuente, tipo, anio, base_etiqueta, m3_elegible), g1 AS (PARTITION BY fuente, tipo, anio, base_etiqueta, m1_elegible), g2 AS (PARTITION BY fuente, tipo, anio, m2_ss_elegible), g4 AS (PARTITION BY fuente, tipo, anio, base_etiqueta, m4_elegible), gc AS (PARTITION BY fuente, tipo, anio, c2_elegible); COPY (SELECT * FROM ofa ORDER BY fuente, organo_clave, anio) TO '/w/salidas/04_organo_fuente_anio.parquet' (FORMAT PARQUET, COMPRESSION ZSTD); COPY (SELECT * FROM dec ORDER BY source, anio) TO '/w/salidas/04_decision_fuente_anio.parquet' (FORMAT PARQUET, COMPRESSION ZSTD); COPY (SELECT * FROM dec ORDER BY source, anio) TO '/w/salidas/04_decision_fuente_anio.csv' (HEADER, DELIMITER ';'); -- @max 200 -- @ Decisión por fuente y año SELECT source, anio, n, espejo, pscp_no_comparable, calc, ok_c2_objeto, ok_c2_adj, ok_m1, ok_m1r, ok_m2, ok_m6_cobertura, ok_m4, pct_copia_fuera, fuente_principal_copias FROM dec ORDER BY source, anio; -- @max 200 -- @ Globales por fuente y año (órgano-años, menores, M3, M1, M2, M6, M4, C2) SELECT fuente, anio, count(*) organo_anios, sum(menores) menores, sum(filas_cota) filas_cota, sum(m3_n) m3_n, sum(m3_n_igual) m3_igual, sum(m3_n_supera) m3_supera, sum(m3_n_cota) m3_cota, round(sum(m3_importe)::DOUBLE / 1e6, 2) m3_meur, sum(m1_menores) m1_menores, sum(m1a_menores) m1a_menores, round(100 * sum(m1a_importe)::DOUBLE / nullif(sum(m1_importe)::DOUBLE, 0), 1) m1a_pct, sum(m1r_series) m1r_series, sum(m1r_contratos) m1r_contratos, sum(m1e_dias) m1e_dias, sum(m1e_contratos) m1e_contratos, sum(m1e_fuera_fecha_convencional) m1e_fuera_conv, sum(m2_ss_n) m2_ss_n, round(100.0 * sum(m2_ss_borde100) / nullif(sum(m2_ss_n), 0), 2) m2_ss_pct, sum(m6_n) m6_n, round(100.0 * sum(m6_n1) / nullif(sum(m6_n), 0), 1) m6_pct1, count(*) FILTER (WHERE m4_elegible) m4_elegibles, sum(c2_n) c2_n, round(100.0 * sum(c2_n_incompleta) / nullif(sum(c2_n), 0), 2) c2_pct FROM ofa GROUP BY ALL ORDER BY fuente, anio; -- Globales por fuente y año para la segunda implementación (comparar/segunda.py) COPY (SELECT fuente, anio, count(*) organo_anios, sum(menores) menores, sum(filas_cota) filas_cota, sum(m3_n) m3_n, sum(m3_n_igual) m3_igual, sum(m3_n_supera) m3_supera, sum(m3_n_cota) m3_cota, sum(m3_importe) m3_importe, sum(m1_menores) m1_menores, sum(m1a_menores) m1a_menores, sum(m1a_importe) m1a_importe, sum(m1r_series) m1r_series, sum(m1r_contratos) m1r_contratos, sum(m1e_dias) m1e_dias, sum(m1e_contratos) m1e_contratos, sum(m1e_importe) m1e_importe, sum(m1e_fuera_fecha_convencional) m1e_fuera_conv, sum(m2_ss_n) m2_ss_n, sum(m2_ss_borde100) m2_ss_borde100, sum(m2_ob_n) m2_ob_n, sum(m2_ob_borde1000) m2_ob_borde1000, sum(m6_n) m6_n, sum(m6_n1) m6_n1, sum(m6_n3mas) m6_n3mas, count(*) FILTER (WHERE m4_elegible) m4_elegibles, sum(m4_proveedores) m4_proveedores, sum(c2_n) c2_n, sum(c2_n_incompleta) c2_n_incompleta, sum(round(c2_pct_objeto * c2_n / 100)) c2_n_objeto, sum(round(c2_pct_adjudicatario * c2_n / 100)) c2_n_adj FROM ofa GROUP BY ALL ORDER BY fuente, anio) TO '/w/salidas/04_globales.csv' (HEADER, DELIMITER ',');