-- V-INT · 02 · impl. 1 (DuckDB): banco de etiquetas, puesta en común por entidad-año y puntuaciones S1, S2, V. -- DEFINICIONES_PREVIAS §1-§4 (md5 fe6aa69c…). Salidas: interno/i1_scores.csv (entidad-año) e interno/i1_organos.csv. ATTACH '/id2/interno/id2.duckdb' AS id2 (READ_ONLY); --; CREATE OR REPLACE TABLE cas AS SELECT tipo, lugar, nif FROM id2.cas_oir2 WHERE nif IS NOT NULL AND tipo <> 'CCAA'; --; DETACH id2; --; CREATE OR REPLACE TABLE t4 AS SELECT tipo, lugar, ejercicio::INT ejercicio, irregularidad, valor::DOUBLE valor FROM read_csv('/id2/salidas/15_t4_irregularidades_k.csv', header=true, all_varchar=true) WHERE entidad_texto <> 'Total'; --; CREATE OR REPLACE TABLE t4g AS SELECT tipo, lugar, ejercicio, grupo, max(valor) valor FROM ( SELECT *, CASE WHEN irregularidad ILIKE 'Incumplimiento de las obligaciones de publicidad%' THEN 'BB' WHEN irregularidad ILIKE 'Contratación menor: se fracciona%' THEN 'I' WHEN irregularidad ILIKE 'Contratación menor: se factura por encima%' THEN 'I1' WHEN irregularidad ILIKE 'No tramitación del expediente en contratación menor%' THEN 'SE' WHEN irregularidad ILIKE 'No solicitud de al menos tres ofertas%' THEN 'S3' WHEN irregularidad ILIKE 'Adjudicación directa%' THEN 'V' WHEN irregularidad ILIKE 'Prestaciones sin cobertura contractual%' THEN 'CC' WHEN irregularidad ILIKE 'Resto de contratos no menores: se fracciona%' THEN 'FNM' END grupo FROM t4) WHERE grupo IS NOT NULL GROUP BY ALL; --; CREATE OR REPLACE TABLE m2 AS SELECT tipo, lugar, ejercicio::INT ejercicio, TRY_CAST(NULLIF(razon_menores, '') AS DOUBLE) rz, TRY_CAST(NULLIF(razon_con_nombre, '') AS DOUBLE) rn FROM read_csv('/id2/salidas/44_m2_con_nombre.csv', header=true, all_varchar=true); --; CREATE OR REPLACE TABLE t3 AS SELECT tipo, lugar, ejercicio::INT ejercicio, tabla, valor::DOUBLE valor FROM read_csv('/id2/salidas/14_t3_omision_ei_k.csv', header=true, all_varchar=true) WHERE tipo <> 'CCAA'; --; -- tamaño: log(1 + total declarado en T2); sin T2 o 0, la mediana de los otros ejercicios (> 0) de la entidad CREATE OR REPLACE TABLE t2 AS SELECT tipo, lugar, ejercicio::INT ejercicio, max(total::DOUBLE) total FROM read_csv('/id2/salidas/12_t2_procedimientos_k.csv', header=true, all_varchar=true) WHERE tipo <> 'CCAA' GROUP BY ALL; --; -- banco largo: etiqueta, entidad-año, y (1/0); fuera = no entra CREATE OR REPLACE TABLE np AS SELECT COALESCE(m.tipo, b.tipo) tipo, COALESCE(m.lugar, b.lugar) lugar, COALESCE(m.ejercicio, b.ejercicio) ejercicio, b.valor bb, m.rz, m.rn, COALESCE(b.valor > 0, FALSE) bb_pos, COALESCE(m.rz < 0.5 AND m.rn < 0.5, FALSE) cand, COALESCE(m.rz < 0.5 AND m.rn >= 0.5, FALSE) hueco FROM m2 m FULL JOIN (SELECT * FROM t4g WHERE grupo = 'BB') b USING (tipo, lugar, ejercicio); --; CREATE OR REPLACE TABLE np2 AS SELECT *, CASE WHEN bb_pos THEN 1 WHEN cand THEN 1 WHEN hueco THEN NULL WHEN rz IS NULL AND bb IS NULL THEN NULL ELSE 0 END y FROM np; --; CREATE OR REPLACE TABLE banco AS SELECT 'NP' etiqueta, tipo, lugar, ejercicio, y, NULL::DOUBLE pct FROM np2 WHERE y IS NOT NULL UNION ALL SELECT 'NP_FIRME', tipo, lugar, ejercicio, y, NULL FROM np2 WHERE y IS NOT NULL AND NOT (y = 1 AND NOT bb_pos) UNION ALL SELECT 'NP_BB', tipo, lugar, ejercicio, CASE WHEN bb > 0 THEN 1 ELSE 0 END, bb FROM np2 WHERE bb IS NOT NULL UNION ALL SELECT 'NP_CAND', tipo, lugar, ejercicio, y, NULL FROM np2 WHERE y IS NOT NULL AND NOT bb_pos UNION ALL SELECT 'MEN', tipo, lugar, ejercicio, CASE WHEN max(valor) > 0 THEN 1 ELSE 0 END, max(valor) FROM t4g WHERE grupo IN ('I', 'I1', 'SE', 'S3') GROUP BY ALL UNION ALL SELECT 'FRAC', tipo, lugar, ejercicio, CASE WHEN valor > 0 THEN 1 ELSE 0 END, valor FROM t4g WHERE grupo = 'I' UNION ALL SELECT 'UMB', tipo, lugar, ejercicio, CASE WHEN valor > 0 THEN 1 ELSE 0 END, valor FROM t4g WHERE grupo = 'I1' UNION ALL SELECT 'S3', tipo, lugar, ejercicio, CASE WHEN valor > 0 THEN 1 ELSE 0 END, valor FROM t4g WHERE grupo = 'S3' UNION ALL SELECT 'SP', tipo, lugar, ejercicio, CASE WHEN max(v) > 0 THEN 1 ELSE 0 END, NULL FROM ( SELECT tipo, lugar, ejercicio, valor v FROM t3 UNION ALL SELECT tipo, lugar, ejercicio, valor FROM t4g WHERE grupo IN ('CC', 'V', 'FNM')) GROUP BY ALL; --; CREATE OR REPLACE TABLE banco_n AS SELECT b.*, c.nif, CASE WHEN b.tipo = 'AY' THEN 'AY' WHEN b.tipo IN ('DP', 'DF') THEN 'DP' ELSE 'insular' END clase, ln(1 + COALESCE(NULLIF(t.total, 0), (SELECT median(t2b.total) FROM t2 t2b WHERE t2b.tipo = b.tipo AND t2b.lugar = b.lugar AND t2b.total > 0))) tamano FROM banco b LEFT JOIN cas c USING (tipo, lugar) LEFT JOIN t2 t USING (tipo, lugar, ejercicio); --; -- @ B1 · banco por etiqueta (sin indicadores): entidades-año, positivas, negativas, sin NIF, sin tamaño SELECT etiqueta, count(*) n, sum(y) pos, count(*) - sum(y) neg, count(DISTINCT nif) FILTER (WHERE y = 1) ent_pos, count(DISTINCT nif) FILTER (WHERE y = 0) ent_neg, count(*) FILTER (WHERE nif IS NULL) sin_nif, count(*) FILTER (WHERE tamano IS NULL) sin_tamano FROM banco_n GROUP BY 1 ORDER BY 1; --; -- indicadores a nivel órgano-año(-fuente): num, den, n_el (base de elegibilidad), dirección CREATE OR REPLACE TABLE oa AS WITH d AS (SELECT * FROM ind_descriptores_organo_anio) SELECT organo_clave, anio, 'PLACSP' fuente, tipo, 'M1r' ind, m1r_importe::DOUBLE num, importe::DOUBLE den, menores n_el FROM d UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'M1e', m1e_importe::DOUBLE, importe::DOUBLE, menores FROM d UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'M1a', m1a_importe::DOUBLE, importe::DOUBLE, menores FROM d UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'M2', m2_ss_borde100, m2_ss_n, m2_ss_n FROM d UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'M6', m6_n - m6_n3mas, m6_n, m6_n FROM d UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'M6_1', m6_n1, m6_n, m6_n FROM d UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'M4', m4_cuota_top1_pct * importe::DOUBLE, importe::DOUBLE, menores FROM d WHERE m4_elegible AND m4_cuota_top1_pct IS NOT NULL UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'M3', m3_n, menores, menores FROM ind_m3_organo_anio UNION ALL SELECT organo_clave, anio, fuente, tipo, 'C1', n_fuera_trim_sig, n, n FROM ind_c1_organo_anio WHERE no_comparable IS NULL UNION ALL SELECT organo_clave, anio, fuente, tipo, 'C2', n_incompleta, n, n FROM ind_c2_organo_anio WHERE poblacion = 'menores' UNION ALL SELECT organo_clave, anio, fuente, tipo, 'C2L', n_incompleta, n, n FROM ind_c2_organo_anio WHERE fuente = 'PLACSP_LICITADOS_PERFIL' UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'DOC4', pcap_n + memoria_n + aprobacion_n + actas_n, 4 * licitaciones, licitaciones FROM ind_documentos_organo_anio UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'PCAP', pcap_n, licitaciones, licitaciones FROM ind_documentos_organo_anio UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'MEMORIA', memoria_n, licitaciones, licitaciones FROM ind_documentos_organo_anio UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'APROB', aprobacion_n, licitaciones, licitaciones FROM ind_documentos_organo_anio UNION ALL SELECT organo_clave, anio, 'PLACSP', tipo, 'ACTAS', actas_n, licitaciones, licitaciones FROM ind_documentos_organo_anio UNION ALL SELECT organo_clave, anio, fuente, tipo, 'SERIES', series_pasaron, series, series FROM ind_series_licitadas_organo_anio; --; CREATE OR REPLACE TABLE oa2 AS SELECT oa.*, o.entidad_clave, CASE WHEN ind IN ('DOC4', 'PCAP', 'MEMORIA', 'APROB', 'ACTAS') THEN -1 ELSE 1 END dir, CASE WHEN den > 0 THEN round(num / den, 9) END v FROM oa JOIN ind_organos o USING (organo_clave) WHERE oa.den IS NOT NULL; --; -- referencia de S1: órgano-años(-fuente) elegibles (n_el >= 20) del mismo tipo, año, fuente e indicador CREATE OR REPLACE TABLE ref1 AS SELECT ind, fuente, tipo, anio, v FROM oa2 WHERE n_el >= 20 AND v IS NOT NULL AND ind <> 'SERIES'; --; CREATE OR REPLACE TABLE ref1n AS SELECT ind, fuente, tipo, anio, count(*) nref FROM ref1 GROUP BY ALL; --; -- entidad-año-fuente puesta en común (todas las entidades: también son los pares de S2) CREATE OR REPLACE TABLE ef AS SELECT entidad_clave, anio, ind, fuente, any_value(dir) dir, sum(num) num, sum(den) den, sum(n_el) n_el, arg_max(tipo, n_el::DOUBLE * 1e6 + (CASE tipo WHEN 'ayuntamiento' THEN 9 WHEN 'local_supramunicipal' THEN 8 ELSE 1 END)) tipo, CASE WHEN sum(den) > 0 THEN round(sum(num) / sum(den), 9) END v FROM oa2 WHERE entidad_clave IS NOT NULL GROUP BY ALL; --; CREATE OR REPLACE TABLE objetivo AS SELECT DISTINCT nif entidad_clave, ejercicio anio FROM banco_n WHERE nif IS NOT NULL; --; -- S1 por parte (entidad-año-fuente) de las entidades del banco CREATE OR REPLACE TABLE s1f AS SELECT e.entidad_clave, e.anio, e.ind, e.fuente, e.n_el, e.v, e.dir, n.nref, 100.0 * (count(*) FILTER (WHERE r.v < e.v) + 0.5 * count(*) FILTER (WHERE r.v = e.v)) / n.nref s1 FROM ef e JOIN objetivo o USING (entidad_clave, anio) JOIN ref1n n ON n.ind = e.ind AND n.fuente = e.fuente AND n.tipo = e.tipo AND n.anio = e.anio AND n.nref >= 20 JOIN ref1 r ON r.ind = e.ind AND r.fuente = e.fuente AND r.tipo = e.tipo AND r.anio = e.anio WHERE e.v IS NOT NULL AND e.n_el >= 1 GROUP BY e.entidad_clave, e.anio, e.ind, e.fuente, e.n_el, e.v, e.dir, n.nref; --; -- S2 por parte: las 50 entidades-año más cercanas en log(n_el), mismo tipo, año, fuente e indicador, n_el >= 20, sin sí misma CREATE OR REPLACE TABLE s2f AS WITH t AS (SELECT e.* FROM ef e JOIN objetivo o USING (entidad_clave, anio) WHERE e.v IS NOT NULL AND e.n_el >= 1 AND e.ind <> 'SERIES'), p AS (SELECT * FROM ef WHERE n_el >= 20 AND v IS NOT NULL AND ind <> 'SERIES'), c AS (SELECT t.entidad_clave, t.anio, t.ind, t.fuente, t.v tv, p.v pv, row_number() OVER (PARTITION BY t.entidad_clave, t.anio, t.ind, t.fuente ORDER BY abs(ln(p.n_el) - ln(t.n_el)), p.entidad_clave) rk FROM t JOIN p ON p.ind = t.ind AND p.fuente = t.fuente AND p.tipo = t.tipo AND p.anio = t.anio AND p.entidad_clave <> t.entidad_clave) SELECT entidad_clave, anio, ind, fuente, count(*) npares, 100.0 * (count(*) FILTER (WHERE pv < tv) + 0.5 * count(*) FILTER (WHERE pv = tv)) / count(*) s2 FROM c WHERE rk <= 50 GROUP BY ALL HAVING count(*) >= 20; --; -- entidad-año: V puesto en común sobre todas las fuentes; S1 y S2 = media de las partes ponderada por n_el; dirección aplicada CREATE OR REPLACE TABLE ea AS SELECT e.entidad_clave, e.anio, e.ind, any_value(e.dir) dir, sum(e.n_el) n_el, CASE WHEN sum(e.den) > 0 THEN round(sum(e.num) / sum(e.den), 9) END v, sum(s.s1 * e.n_el) / NULLIF(sum(e.n_el) FILTER (WHERE s.s1 IS NOT NULL), 0) s1, sum(q.s2 * e.n_el) / NULLIF(sum(e.n_el) FILTER (WHERE q.s2 IS NOT NULL), 0) s2 FROM ef e JOIN objetivo o USING (entidad_clave, anio) LEFT JOIN s1f s USING (entidad_clave, anio, ind, fuente) LEFT JOIN s2f q USING (entidad_clave, anio, ind, fuente) GROUP BY ALL; --; CREATE OR REPLACE TABLE scores AS SELECT b.etiqueta, b.tipo, b.lugar, b.nif, b.clase, b.ejercicio, b.y, b.tamano, b.pct, i.ind, i.n_el, i.dir * i.v v_dir, CASE WHEN i.dir = 1 THEN i.s1 ELSE 100 - i.s1 END s1_dir, CASE WHEN i.dir = 1 THEN i.s2 ELSE 100 - i.s2 END s2_dir FROM banco_n b JOIN ea i ON i.entidad_clave = b.nif AND i.anio = b.ejercicio; --; COPY (SELECT * FROM scores ORDER BY etiqueta, ind, nif, ejercicio) TO '/c/interno/i1_scores.csv' (HEADER); --; COPY (SELECT * FROM banco_n ORDER BY etiqueta, tipo, lugar, ejercicio) TO '/c/interno/i1_banco.csv' (HEADER); --; -- órgano-año (secundaria): S1 propio de cada órgano de las entidades del banco (n_el >= 20 en el órgano), media por fuentes CREATE OR REPLACE TABLE o1 AS SELECT x.organo_clave, x.entidad_clave, x.anio, x.ind, x.dir, x.n_el, 100.0 * (count(*) FILTER (WHERE r.v < x.v) + 0.5 * count(*) FILTER (WHERE r.v = x.v)) / n.nref s1 FROM oa2 x JOIN objetivo o USING (entidad_clave, anio) JOIN ref1n n ON n.ind = x.ind AND n.fuente = x.fuente AND n.tipo = x.tipo AND n.anio = x.anio AND n.nref >= 20 JOIN ref1 r ON r.ind = x.ind AND r.fuente = x.fuente AND r.tipo = x.tipo AND r.anio = x.anio WHERE x.n_el >= 20 AND x.v IS NOT NULL GROUP BY x.organo_clave, x.entidad_clave, x.anio, x.ind, x.dir, x.n_el, x.fuente, n.nref, x.v; --; CREATE OR REPLACE TABLE o2 AS SELECT organo_clave, entidad_clave, anio, ind, any_value(dir) dir, sum(s1 * n_el) / sum(n_el) s1 FROM o1 GROUP BY ALL; --; COPY (SELECT b.etiqueta, b.nif, b.ejercicio, b.y, o.organo_clave, o.ind, CASE WHEN o.dir = 1 THEN o.s1 ELSE 100 - o.s1 END s1_dir FROM banco_n b JOIN o2 o ON o.entidad_clave = b.nif AND o.anio = b.ejercicio ORDER BY 1, 6, 2, 3, 5) TO '/c/interno/i1_organos.csv' (HEADER); --; -- @ B2 · cobertura: entidades-año del banco con S1 y n_el >= 20, por etiqueta e indicador (clases juntas) SELECT etiqueta, ind, count(*) FILTER (WHERE s1_dir IS NOT NULL AND n_el >= 20) con_dato, count(*) filas FROM scores GROUP BY ALL ORDER BY 1, 2;