-- 02. §4 del pre-registro: densidades por trimestre 2017-2019 por celda fuente × base × clase y migración del pico. -- Normalizador: [5.000, 14.500) (resto) o [10.000, 38.000) (obras), tal como se publica. Bandas en la base de la celda. -- Densidad = ‰ del normalizador; dens500 = ‰ por 500 € de anchura de banda. -- Interpretación para obras (no entra en A1): la zona de referencia del pico son los 3 anchos de banda inferiores, -- que en resto coincide con lo escrito (1.500 € bajo V/V2, 1.815 € bajo V1). CREATE OR REPLACE TABLE r17 AS SELECT source, base, clase, cuenta, fecha_adj, imp, organo_clave, CASE WHEN fecha_adj <= DATE '2018-03-08' THEN 'antes' ELSE 'despues' END AS periodo, (fecha_adj BETWEEN DATE '2018-04-01' AND DATE '2018-09-30') AS d2t, year(fecha_adj) || 'T' || quarter(fecha_adj) AS trimestre FROM men WHERE fecha_adj BETWEEN DATE '2017-01-01' AND DATE '2019-12-31' AND imp IS NOT NULL; ANALYZE r17; -- Recuentos por banda (resto y obras usan columnas distintas; se calculan todas y se leen las de su clase) CREATE OR REPLACE MACRO bandas_resto(i) AS STRUCT_PACK( norm := (i >= 5000 AND i < 14500), n5_25 := (i >= 5000 AND i < 25000), v := (i >= 17500 AND i < 18000), v1 := (i >= 21175 AND i < 21780), n := (i >= 14500 AND i < 15000), n1 := (i >= 18000 AND i <= 18150), ref_v := (i >= 16000 AND i < 17500), ref_v1 := (i >= 19360 AND i < 21175)); CREATE OR REPLACE MACRO bandas_obras(i) AS STRUCT_PACK( norm := (i >= 10000 AND i < 38000), n5_25 := (i >= 10000 AND i < 70000), v := (i >= 49000 AND i < 50000), v1 := (i >= 59290 AND i < 60500), n := (i >= 39000 AND i < 40000), n1 := (i >= 47190 AND i <= 48400), ref_v := (i >= 46000 AND i < 49000), ref_v1 := (i >= 55660 AND i < 59290)); CREATE OR REPLACE TABLE r17b AS SELECT *, CASE WHEN clase = 'obras' THEN bandas_obras(imp) ELSE bandas_resto(imp) END AS b FROM r17; -- Anchuras (€) de cada banda por clase CREATE OR REPLACE TABLE anchos AS SELECT * FROM (VALUES ('resto', 500.0, 605.0, 500.0, 150.0, 1500.0, 1815.0), ('obras', 1000.0, 1210.0, 1000.0, 1210.0, 3000.0, 3630.0)) t(clase, w_v, w_v1, w_n, w_n1, w_ref_v, w_ref_v1); -- Recuentos por trimestre (vista única y sin deduplicar) CREATE OR REPLACE TABLE trim AS SELECT source, base, clase, trimestre, dedup, count(*) FILTER (WHERE b.norm) n_norm, count(*) FILTER (WHERE b.n5_25) n_5_25, count(*) FILTER (WHERE b.v) n_v, count(*) FILTER (WHERE b.v1) n_v1, count(*) FILTER (WHERE b.n) n_n, count(*) FILTER (WHERE b.n1) n_n1, count(*) FILTER (WHERE b.ref_v) n_ref_v, count(*) FILTER (WHERE b.ref_v1) n_ref_v1 FROM (SELECT *, 'vista_unica' dedup FROM r17b WHERE cuenta UNION ALL SELECT *, 'sin_dedup' dedup FROM r17b) GROUP BY ALL; -- Recuentos por periodo (antes, después-2T, después) CREATE OR REPLACE TABLE per AS WITH x AS (SELECT *, 'vista_unica' dedup FROM r17b WHERE cuenta UNION ALL SELECT *, 'sin_dedup' dedup FROM r17b), y AS (SELECT *, periodo p FROM x UNION ALL SELECT *, 'despues2t' p FROM x WHERE d2t) SELECT source, base, clase, dedup, p periodo, count(*) FILTER (WHERE b.norm) n_norm, count(*) FILTER (WHERE b.n5_25) n_5_25, count(*) FILTER (WHERE b.v) n_v, count(*) FILTER (WHERE b.v1) n_v1, count(*) FILTER (WHERE b.n) n_n, count(*) FILTER (WHERE b.n1) n_n1, count(*) FILTER (WHERE b.ref_v) n_ref_v, count(*) FILTER (WHERE b.ref_v1) n_ref_v1 FROM y GROUP BY ALL; -- Celdas: densidades por 500 € y veredicto CREATE OR REPLACE TABLE celdas AS WITH a AS (SELECT * FROM per WHERE periodo = 'antes'), d AS (SELECT * FROM per WHERE periodo = 'despues2t'), dd AS (SELECT * FROM per WHERE periodo = 'despues'), j AS ( SELECT a.source, a.base, a.clase, a.dedup, a.n_norm n_norm_antes, coalesce(d.n_norm, 0) n_norm_d2t, coalesce(dd.n_norm, 0) n_norm_despues, a.n_v n_v_antes, a.n_v1 n_v1_antes, a.n_n n_n_antes, a.n_n1 n_n1_antes, a.n_ref_v, a.n_ref_v1, coalesce(d.n_v, 0) n_v_d2t, coalesce(d.n_v1, 0) n_v1_d2t, coalesce(d.n_n, 0) n_n_d2t, coalesce(d.n_n1, 0) n_n1_d2t, coalesce(dd.n_v, 0) n_v_despues, coalesce(dd.n_v1, 0) n_v1_despues, coalesce(dd.n_n, 0) n_n_despues, coalesce(dd.n_n1, 0) n_n1_despues, w.* FROM a LEFT JOIN d USING (source, base, clase, dedup) LEFT JOIN dd USING (source, base, clase, dedup) JOIN anchos w ON w.clase = a.clase), k AS ( SELECT *, 1000.0 * n_v_antes / nullif(n_norm_antes, 0) * 500 / w_v dv_antes, 1000.0 * n_v1_antes / nullif(n_norm_antes, 0) * 500 / w_v1 dv1_antes, 1000.0 * n_ref_v / nullif(n_norm_antes, 0) * 500 / w_ref_v dref_v, 1000.0 * n_ref_v1 / nullif(n_norm_antes, 0) * 500 / w_ref_v1 dref_v1, 1000.0 * n_v_d2t / nullif(n_norm_d2t, 0) * 500 / w_v dv_d2t, 1000.0 * n_v1_d2t / nullif(n_norm_d2t, 0) * 500 / w_v1 dv1_d2t, 1000.0 * n_n_antes / nullif(n_norm_antes, 0) * 500 / w_n dn_antes, 1000.0 * n_n1_antes / nullif(n_norm_antes, 0) * 500 / w_n1 dn1_antes, 1000.0 * n_n_d2t / nullif(n_norm_d2t, 0) * 500 / w_n dn_d2t, 1000.0 * n_n1_d2t / nullif(n_norm_d2t, 0) * 500 / w_n1 dn1_d2t FROM j), l AS ( SELECT *, CASE WHEN base = 'sin' THEN 'V' WHEN coalesce(dv1_antes, 0) > coalesce(dv_antes, 0) THEN 'V1' ELSE 'V2' END banda_vieja FROM k), m AS ( SELECT *, CASE WHEN banda_vieja = 'V1' THEN n_v1_antes ELSE n_v_antes END n_vieja_antes, CASE WHEN banda_vieja = 'V1' THEN dv1_antes ELSE dv_antes END d_vieja_antes, CASE WHEN banda_vieja = 'V1' THEN dref_v1 ELSE dref_v END d_ref_antes, CASE WHEN banda_vieja = 'V1' THEN dv1_d2t ELSE dv_d2t END d_vieja_d2t FROM l) SELECT *, (n_norm_antes >= 200 AND n_norm_d2t >= 100 AND n_vieja_antes >= 20 AND d_vieja_antes >= 2 * d_ref_antes) AS evaluable, (n_norm_antes >= 200 AND n_norm_d2t >= 100 AND n_vieja_antes >= 20 AND d_vieja_antes >= 2 * d_ref_antes AND d_vieja_d2t <= 0.5 * d_vieja_antes) AS migracion, round(1 - d_vieja_d2t / nullif(d_vieja_antes, 0), 3) AS caida_vieja FROM m; -- @max 200 -- @ Celdas resto (servicios/suministros), vista única: densidades por 500 € (‰ de [5.000, 14.500)) antes / después-2T y veredicto SELECT source, base, n_norm_antes, n_norm_d2t, banda_vieja, n_vieja_antes, round(d_ref_antes,1) d_ref, round(d_vieja_antes,1) d_vieja_antes, round(d_vieja_d2t,1) d_vieja_d2t, caida_vieja, round(dn_antes,1) dN_antes, round(dn_d2t,1) dN_d2t, round(dn1_antes,1) dN1_antes, round(dn1_d2t,1) dN1_d2t, evaluable, migracion FROM celdas WHERE clase = 'resto' AND dedup = 'vista_unica' ORDER BY source, base; -- @ Veredicto por fuente (resto, vista única): ¿alguna celda evaluable con migración? SELECT source, string_agg(base || ':' || CASE WHEN migracion THEN 'migra' WHEN evaluable THEN 'no_migra' ELSE 'no_evaluable' END, ', ' ORDER BY base) celdas, bool_or(evaluable) alguna_evaluable, bool_or(migracion) migracion_visible FROM celdas WHERE clase = 'resto' AND dedup = 'vista_unica' GROUP BY 1 ORDER BY migracion_visible DESC, source; -- @ A1: número de fuentes con migración visible (resto, vista única) y sin deduplicar (sensibilidad) SELECT dedup, count(DISTINCT source) FILTER (WHERE migracion) fuentes_con_migracion, count(DISTINCT source) FILTER (WHERE evaluable) fuentes_evaluables, string_agg(DISTINCT source, ', ') FILTER (WHERE migracion) cuales FROM celdas WHERE clase = 'resto' GROUP BY 1 ORDER BY 1; -- @max 200 -- @ Celdas obras, vista única (complementario) SELECT source, base, n_norm_antes, n_norm_d2t, banda_vieja, n_vieja_antes, round(d_ref_antes,1) d_ref, round(d_vieja_antes,1) d_vieja_antes, round(d_vieja_d2t,1) d_vieja_d2t, caida_vieja, round(dn_antes,1) dN_antes, round(dn_d2t,1) dN_d2t, evaluable, migracion FROM celdas WHERE clase = 'obras' AND dedup = 'vista_unica' AND n_norm_antes >= 50 ORDER BY source, base; -- @max 200 -- @ Sensibilidad sin deduplicar: celdas resto cuyo veredicto cambia respecto a la vista única SELECT a.source, a.base, a.evaluable ev_unica, a.migracion mig_unica, b.evaluable ev_sin_dedup, b.migracion mig_sin_dedup, a.n_norm_antes, b.n_norm_antes n_norm_antes_sd FROM celdas a JOIN celdas b USING (source, base, clase) WHERE a.dedup = 'vista_unica' AND b.dedup = 'sin_dedup' AND a.clase = 'resto' AND (a.evaluable IS DISTINCT FROM b.evaluable OR a.migracion IS DISTINCT FROM b.migracion); -- @max 400 -- @ Trimestres, resto, vista única: ‰ del normalizador [5.000,14.500) por banda (celdas con n_norm >= 30 en el trimestre) SELECT source, base, trimestre, n_norm, round(1000.0*n_v/n_norm,1) pm_17500_18000, round(1000.0*n_v1/n_norm,1) pm_21175_21780, round(1000.0*n_n/n_norm,1) pm_14500_15000, round(1000.0*n_n1/n_norm,1) pm_18000_18150, round(1000.0*n_v/nullif(n_5_25,0),1) pm5_25_17500, round(1000.0*n_n/nullif(n_5_25,0),1) pm5_25_14500 FROM trim WHERE clase = 'resto' AND dedup = 'vista_unica' AND n_norm >= 30 ORDER BY source, base, trimestre; -- @ Cotejo con Fable 06 (fuentes sin IVA de su «resto (comparable)», base sin, normalizador [5.000,25.000)): ‰ de [14.500,15.000) y [17.500,18.000) SELECT trimestre, sum(n_5_25) n_5_25, round(1000.0*sum(n_n)/sum(n_5_25),1) pm_14500, round(1000.0*sum(n_v)/sum(n_5_25),1) pm_17500 FROM trim WHERE clase = 'resto' AND dedup = 'vista_unica' AND base = 'sin' AND source IN ('ANDALUCIA','EUSKADI','VALENCIA','PLACSP','MURCIA','MADRID_AYTO','CATALUNYA_BCN_MENORS') GROUP BY 1 ORDER BY 1; COPY (SELECT * FROM trim ORDER BY ALL) TO '/w/salidas/02_trimestres_celdas.csv' (HEADER); COPY (SELECT * EXCLUDE (w_v, w_v1, w_n, w_n1, w_ref_v, w_ref_v1) FROM celdas ORDER BY clase, dedup, source, base) TO '/w/salidas/02_migracion_celdas.csv' (HEADER);