-- impl2 · 03 · M1r (§3.2), series marcadas y cadenas de control (§3.4) -- Algoritmo propio, sin funciones de ventana para la lógica: los contratos se agregan por día y grupo; las ventanas de -- 365 días salen de una autounión por banda de fechas; la marca de cada día, de un ASOF JOIN al siguiente día con ventana -- activa; las cadenas, de dos ASOF JOIN (el día anterior del grupo y el último inicio de cadena). CREATE OR REPLACE TABLE pop AS SELECT fuente, id_canonical, organo_clave, entidad_clave, empresa_clave, clase, umbral, objeto_norm, fecha_adj, base, cpv8 FROM pob0 UNION ALL SELECT fuente, id_canonical, organo_clave, entidad_clave, empresa_clave, clase, umbral, objeto_norm, fecha_adj, base, cpv8 FROM pob2; ANALYZE pop; -- Grupos (fuente, órgano, empresa, clase, objeto_norm de 5 caracteres o más) con 2 menores o más CREATE OR REPLACE TABLE grp AS SELECT md5(fuente || '|' || organo_clave || '|' || empresa_clave || '|' || clase || '|' || objeto_norm) AS gid, fuente, organo_clave, empresa_clave, clase, objeto_norm, min(umbral) AS umbral, max(umbral) AS umbral_max, count(*) AS n FROM pop WHERE length(objeto_norm) >= 5 GROUP BY fuente, organo_clave, empresa_clave, clase, objeto_norm HAVING count(*) >= 2; ANALYZE grp; -- @ comprobación: umbral constante en cada grupo (debe ser 0) SELECT count(*) FROM grp WHERE umbral <> umbral_max; -- Contratos de esos grupos y sus días CREATE OR REPLACE TABLE gc AS SELECT g.gid, p.fuente, p.id_canonical, p.fecha_adj, p.base, p.cpv8, p.entidad_clave FROM pop p JOIN grp g ON g.fuente = p.fuente AND g.organo_clave = p.organo_clave AND g.empresa_clave = p.empresa_clave AND g.clase = p.clase AND g.objeto_norm = p.objeto_norm; ANALYZE gc; CREATE OR REPLACE TABLE dd AS SELECT gid, fecha_adj AS fecha, count(*) AS n_d, sum(base) AS s_d FROM gc GROUP BY 1, 2; ANALYZE dd; -- @ tamaño: días por grupo SELECT count(*) dias, count(DISTINCT gid) grupos, max(c) max_dias_grupo FROM (SELECT gid, count(*) c FROM dd GROUP BY 1); -- Ventana de 365 días que acaba en cada día con contrato: autounión por banda (fecha − 364 → fecha) CREATE OR REPLACE TABLE win AS SELECT a.gid, a.fecha, sum(b.n_d) AS n12, sum(b.s_d) AS s12 FROM dd a JOIN dd b ON b.gid = a.gid AND b.fecha BETWEEN a.fecha - 364 AND a.fecha GROUP BY a.gid, a.fecha; ANALYZE win; CREATE OR REPLACE TABLE act AS SELECT w.gid, w.fecha FROM win w JOIN grp g USING (gid) WHERE w.n12 >= 2 AND w.s12 > g.umbral; ANALYZE act; -- Un día está en M1r si hay una ventana activa que acaba entre ese día y 364 días después: el siguiente día activo (ASOF) CREATE OR REPLACE TABLE ddm AS SELECT d.gid, d.fecha, d.n_d, d.s_d, (a.fecha IS NOT NULL AND a.fecha - d.fecha <= 364) AS marcado FROM dd d ASOF LEFT JOIN act a ON a.gid = d.gid AND d.fecha <= a.fecha; ANALYZE ddm; -- Series: días marcados encadenados (364 días o menos entre consecutivos). Inicio = día marcado sin otro marcado en los -- 364 días anteriores (el marcado anterior, por ASOF); cada día marcado va con el último inicio de su grupo (ASOF) CREATE OR REPLACE TABLE mk AS SELECT gid, fecha, n_d, s_d FROM ddm WHERE marcado; ANALYZE mk; CREATE OR REPLACE TABLE mk_ini AS SELECT m.gid, m.fecha FROM mk m ASOF LEFT JOIN mk p ON p.gid = m.gid AND m.fecha > p.fecha WHERE p.fecha IS NULL OR m.fecha - p.fecha > 364; ANALYZE mk_ini; CREATE OR REPLACE TABLE mk_s AS SELECT m.gid, m.fecha, m.n_d, m.s_d, i.fecha AS ini FROM mk m ASOF JOIN mk_ini i ON i.gid = m.gid AND m.fecha >= i.fecha; ANALYZE mk_s; -- Cadenas (todos los días del grupo, mismo corte) para el control CREATE OR REPLACE TABLE ch_ini AS SELECT d.gid, d.fecha FROM ddm d ASOF LEFT JOIN ddm p ON p.gid = d.gid AND d.fecha > p.fecha WHERE p.fecha IS NULL OR d.fecha - p.fecha > 364; ANALYZE ch_ini; CREATE OR REPLACE TABLE ch_s AS SELECT d.gid, d.fecha, d.n_d, d.s_d, d.marcado, i.fecha AS ini FROM ddm d ASOF JOIN ch_ini i ON i.gid = d.gid AND d.fecha >= i.fecha; ANALYZE ch_s; -- Unidades: series marcadas (todas) y cadenas no marcadas con 2 menores o más CREATE OR REPLACE TABLE uni AS SELECT md5('S|' || gid || '|' || ini::VARCHAR) AS uid, gid, TRUE AS marcada, ini AS fecha_ini, max(fecha) AS fecha_fin, sum(n_d)::BIGINT AS n, sum(s_d) AS suma FROM mk_s GROUP BY gid, ini UNION ALL SELECT md5('C|' || gid || '|' || ini::VARCHAR), gid, FALSE, ini, max(fecha), sum(n_d)::BIGINT, sum(s_d) FROM ch_s GROUP BY gid, ini HAVING NOT bool_or(marcado) AND sum(n_d) >= 2; ANALYZE uni; CREATE OR REPLACE TABLE uni_x AS SELECT u.*, year(u.fecha_fin) AS anio_fin, g.fuente, g.organo_clave, g.empresa_clave, g.clase, g.objeto_norm, g.umbral FROM uni u JOIN grp g USING (gid); ANALYZE uni_x; -- Contratos de cada unidad (para el CPV y la entidad) CREATE OR REPLACE TABLE uni_c AS SELECT x.uid, c.id_canonical, c.cpv8, c.entidad_clave, c.fecha_adj FROM uni_x x JOIN gc c ON c.gid = x.gid AND c.fecha_adj BETWEEN x.fecha_ini AND x.fecha_fin JOIN ddm d ON d.gid = c.gid AND d.fecha = c.fecha_adj WHERE d.marcado = x.marcada; ANALYZE uni_c; -- @ comprobaciones: series con n < 2 o suma <= umbral (0); n de las unidades = contratos asignados (0 diferencias) SELECT (SELECT count(*) FROM uni_x WHERE marcada AND (n < 2 OR suma <= umbral)) series_mal, (SELECT count(*) FROM (SELECT x.uid, x.n, count(c.id_canonical) k FROM uni_x x LEFT JOIN uni_c c USING (uid) GROUP BY 1, 2) WHERE n <> k) n_distinto, (SELECT count(*) - count(DISTINCT id_canonical || fuente) FROM uni_c JOIN uni_x USING (uid)) contratos_repetidos; -- @ comprobación: ninguna cadena de control tiene una ventana que pase del umbral SELECT count(*) FROM uni_x x JOIN win w ON w.gid = x.gid AND w.fecha BETWEEN x.fecha_ini AND x.fecha_fin WHERE NOT x.marcada AND w.n12 >= 2 AND w.s12 > x.umbral; -- @ series marcadas y cadenas de control por fuente y año de fin SELECT fuente, anio_fin, count(*) FILTER (WHERE marcada) series, count(*) FILTER (WHERE NOT marcada) control FROM uni_x GROUP BY 1, 2 ORDER BY 1, 2;