-- 05. Casado banco ↔ indicadores (DEFINICIONES_PREVIAS D3-D5). Una fila por entidad-año del universo con menores en la -- PLACSP (298), variante e indicador. -- Etiquetas: positivo / negativo (con la marca sin_constancia) / discrepante (fuera de la AUC). -- Versiones: clm = algún informe 'cccm_*'; matriz = casos.casado_a_matriz = 1 en esa entidad-año o entidad_nombre_fiable -- = FALSE en la identidad de P1b. -- Agregación «pooled»: se suman numerador, denominador y n de elegibilidad de todos los órganos de la entidad ese año -- (M1x: importe en M1x / importe; M2: borde / ventana; M6: con 1 oferta / con dato; C1: > 90 días / n). M4 se recalcula -- sobre el importe de la entidad por empresa. Tipo de la entidad-año = tipo del órgano con más menores ese año (variante -- 'sin'); si no tiene menores, el del órgano con más filas en la identidad (marcado). -- Percentil (D5) = nº de órgano-años elegibles (P3) del mismo indicador, variante, tipo y año con valor < el de la -- entidad, / (N − 1). CREATE OR REPLACE TABLE ea AS WITH m AS (SELECT entidad_clave, anio, max(casado_a_matriz) matriz FROM casos GROUP BY ALL) SELECT u.entidad_clave, u.anio, u.nombre_nuestro, u.informes, split_part(u.informes, '|', 1) informe1, split_part(u.informes, '_', 1) oc, u.etiqueta, u.verificacion, (u.verificacion = 'negativo_confirmado_sin_constancia') sin_constancia, (u.informes LIKE '%cccm\_%' ESCAPE '\') clm, (coalesce(m.matriz, 0) = 1 OR coalesce(NOT e.entidad_nombre_fiable, FALSE)) matriz, coalesce(m.matriz, 0) = 1 matriz_casos, coalesce(NOT e.entidad_nombre_fiable, FALSE) nif_compartido, e.organos organos_identidad, u.n_menor_placsp FROM universo u LEFT JOIN m USING (entidad_clave, anio) LEFT JOIN ent_id e USING (entidad_clave) WHERE u.n_menor_placsp > 0; CREATE OR REPLACE TABLE eorg AS SELECT ea.entidad_clave, ea.anio, o.organo_clave FROM ea JOIN org_id o USING (entidad_clave); -- Tipo de la entidad-año CREATE OR REPLACE TABLE etipo AS WITH a AS (SELECT e.entidad_clave, e.anio, x.tipo, x.menores, x.organo_clave, row_number() OVER (PARTITION BY e.entidad_clave, e.anio ORDER BY x.menores DESC, x.organo_clave) rk FROM eorg e JOIN oa x ON x.var = 'sin' AND x.organo_clave = e.organo_clave AND x.anio = e.anio), b AS (SELECT e.entidad_clave, e.anio, coalesce(t.tipo, 'sin_tipo') tipo, row_number() OVER (PARTITION BY e.entidad_clave, e.anio ORDER BY o.filas DESC NULLS LAST, o.organo_clave) rk FROM eorg e JOIN org_id o USING (organo_clave) LEFT JOIN otipo t USING (organo_clave)) SELECT ea.entidad_clave, ea.anio, coalesce(a.tipo, b.tipo) tipo, (a.tipo IS NULL) tipo_sin_menores FROM ea LEFT JOIN a ON a.entidad_clave = ea.entidad_clave AND a.anio = ea.anio AND a.rk = 1 LEFT JOIN b ON b.entidad_clave = ea.entidad_clave AND b.anio = ea.anio AND b.rk = 1; -- Agregados por entidad-año (todas las variantes; M4 aparte) CREATE OR REPLACE TABLE epool AS SELECT e.entidad_clave, e.anio, l.var, l.indicador, sum(l.num) num, sum(l.den) den, sum(l.n_elig) n_elig, count(*) organos_con_dato FROM eorg e JOIN oal l ON l.organo_clave = e.organo_clave AND l.anio = e.anio WHERE l.indicador <> 'm4' GROUP BY ALL; INSERT INTO epool WITH x AS (SELECT e.entidad_clave, e.anio, m.var, m.empresa_clave, sum(m.imp) imp, sum(m.n) n FROM eorg e JOIN m4_oe m ON m.organo_clave = e.organo_clave AND m.anio = e.anio GROUP BY ALL), t AS (SELECT entidad_clave, anio, var, sum(imp) tot, sum(n) n, max(imp) top1 FROM x GROUP BY ALL) SELECT entidad_clave, anio, var, 'm4', top1, tot, n, NULL FROM t; CREATE OR REPLACE TABLE banco AS SELECT ea.*, et.tipo, et.tipo_sin_menores, p.var, p.indicador, p.num, p.den, p.n_elig, p.organos_con_dato, round(100.0 * p.num / p.den, 6) AS val, -- POSTERIOR (determinismo): mismo redondeo que 04 CASE WHEN p.indicador = 'm4' THEN (p.n_elig >= 50 AND p.den >= 200000) ELSE p.n_elig >= 1 END AS eleg1, CASE WHEN p.indicador = 'm4' THEN (p.n_elig >= 50 AND p.den >= 200000) ELSE p.n_elig >= 20 END AS eleg20 FROM ea JOIN etipo et USING (entidad_clave, anio) JOIN epool p USING (entidad_clave, anio) WHERE p.den > 0; ALTER TABLE banco ADD COLUMN pct DOUBLE; ALTER TABLE banco ADD COLUMN n_dist BIGINT; UPDATE banco SET pct = s.pct, n_dist = s.n FROM ( SELECT b.entidad_clave, b.anio, b.var, b.indicador, CASE WHEN count(d.val) > 1 THEN 100.0 * count(*) FILTER (WHERE d.val < b.val) / (count(d.val) - 1) END pct, count(d.val) n FROM banco b LEFT JOIN oal d ON d.var = b.var AND d.indicador = b.indicador AND d.tipo = b.tipo AND d.anio = b.anio AND d.elegible GROUP BY ALL) s WHERE s.entidad_clave = banco.entidad_clave AND s.anio = banco.anio AND s.var = banco.var AND s.indicador = banco.indicador; UPDATE banco SET pct = least(pct, 100); COPY (SELECT * FROM banco ORDER BY var, indicador, entidad_clave, anio) TO '/w/salidas/banco_indicadores.parquet' (FORMAT PARQUET); COPY (SELECT * FROM ea ORDER BY entidad_clave, anio) TO '/w/salidas/banco_entidades.parquet' (FORMAT PARQUET); -- @ Universo casado: entidades-año por etiqueta y versión (CLM, matriz), con y sin «sin constancia» SELECT etiqueta, count(*) n, count(*) FILTER (WHERE sin_constancia) sin_constancia, count(*) FILTER (WHERE clm) clm, count(*) FILTER (WHERE matriz) matriz, count(*) FILTER (WHERE matriz_casos) matriz_casos, count(*) FILTER (WHERE nif_compartido) nif_compartido, count(*) FILTER (WHERE NOT clm) v_b, count(*) FILTER (WHERE NOT matriz) v_c, count(*) FILTER (WHERE NOT clm AND NOT matriz) v_d FROM ea GROUP BY 1 ORDER BY 1; -- @ Nº de órganos de la entidad en la identidad y con menores en nuestra población ese año (variante 'sin') WITH x AS (SELECT e.entidad_clave, e.anio, count(DISTINCT e.organo_clave) organos, count(DISTINCT o.organo_clave) con_menores, coalesce(sum(o.menores), 0) menores FROM eorg e LEFT JOIN oa o ON o.var = 'sin' AND o.organo_clave = e.organo_clave AND o.anio = e.anio GROUP BY ALL) SELECT ea.etiqueta, CASE WHEN x.con_menores = 0 THEN '0 órganos con menores' WHEN x.con_menores = 1 THEN '1' WHEN x.con_menores <= 3 THEN '2-3' ELSE '4+' END organos_con_menores, count(*) n, quantile_cont(x.menores, 0.5) menores_p50, count(*) FILTER (WHERE x.menores >= 20) con_20_menores FROM ea JOIN x USING (entidad_clave, anio) GROUP BY ALL ORDER BY 1, 2; -- @ Entidades-año con dato por indicador (variante 'sin'), por etiqueta: elegibles (≥ 1) y con ≥ 20 SELECT indicador, etiqueta, count(*) FILTER (WHERE eleg1) eleg1, count(*) FILTER (WHERE eleg20) eleg20, count(*) FILTER (WHERE eleg1 AND pct IS NULL) sin_percentil FROM banco WHERE var = 'sin' GROUP BY ALL ORDER BY 1, 2; -- @ Tipo de la entidad-año por etiqueta SELECT etiqueta, tipo, count(*) n, count(*) FILTER (WHERE tipo_sin_menores) tipo_por_identidad FROM ea JOIN etipo USING (entidad_clave, anio) GROUP BY ALL ORDER BY 1, 3 DESC;