-- ── M3 ───────────────────────────────────────────────────────────────────────────────────────────────────────────── CREATE OR REPLACE TABLE m3f AS -- DA 54.ª: «inferior o igual a 50.000 euros» (50.000,00 € exactos es menor); art. 118: «inferior a» (el umbral exacto es 'igual') SELECT *, CASE WHEN exacta AND umbral_m3 <> 50000 AND base = umbral_m3 THEN 'igual' WHEN exacta AND base > umbral_m3 THEN 'supera' WHEN NOT exacta AND base > umbral_m3 THEN 'supera' END AS m3 FROM q; CREATE OR REPLACE TABLE m3_oa AS SELECT fuente, organo_clave, anio, any_value(tipo) tipo, count(*) AS menores, count(*) FILTER (WHERE NOT exacta) AS filas_cota, count(m3) AS m3_n, count(*) FILTER (WHERE m3 = 'igual') AS m3_n_igual, count(*) FILTER (WHERE m3 = 'supera') AS m3_n_supera, count(*) FILTER (WHERE m3 IS NOT NULL AND NOT exacta) AS m3_n_cota, coalesce(sum(base) FILTER (WHERE m3 IS NOT NULL), 0) AS m3_importe, round(100.0 * count(m3) / count(*), 6) AS m3_pct, count(*) FILTER (WHERE copia_fuera) AS en_otra_fuente FROM m3f GROUP BY fuente, organo_clave, anio; -- ── Descriptores: población (empresa identificada) ───────────────────────────────────────────────────────────────── CREATE OR REPLACE TABLE pd AS SELECT q.* FROM q WHERE empresa_clave IS NOT NULL; ANALYZE pd; CREATE OR REPLACE TABLE pm1 AS SELECT pd.* FROM pd JOIN dec d ON d.source = pd.fuente AND d.anio = pd.anio WHERE d.ok_m1 AND pd.fecha_ok; ANALYZE pm1; -- M1a (ventana de 365 días por par órgano-empresa-clase, como P4) CREATE OR REPLACE TABLE m1a AS WITH w AS ( SELECT id_canonical, fuente, organo_clave, empresa_clave, clase, fecha_adj, base, umbral, count(*) OVER v AS n12, sum(base) OVER v AS s12 FROM pm1 WINDOW v AS (PARTITION BY fuente, organo_clave, empresa_clave, clase ORDER BY fecha_adj RANGE BETWEEN INTERVAL 364 DAYS PRECEDING AND CURRENT ROW)), a AS (SELECT *, (n12 >= 2 AND s12 > umbral) AS activa FROM w) SELECT fuente, id_canonical, bool_or(activa) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase ORDER BY fecha_adj RANGE BETWEEN CURRENT ROW AND INTERVAL 364 DAYS FOLLOWING) AS en_m1a FROM a; -- M1r (solo filas con objeto informativo, en fuentes-años con ok_m1r) CREATE OR REPLACE TABLE m1r AS WITH b0 AS (SELECT pm1.* FROM pm1 JOIN dec d ON d.source = pm1.fuente AND d.anio = pm1.anio WHERE d.ok_m1r AND pm1.objeto_info AND length(pm1.objeto_norm) >= 5), w AS ( SELECT id_canonical, fuente, organo_clave, empresa_clave, clase, objeto_norm, fecha_adj, base, umbral, count(*) OVER v AS n12, sum(base) OVER v AS s12 FROM b0 WINDOW v AS (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN INTERVAL 364 DAYS PRECEDING AND CURRENT ROW)), a AS (SELECT *, (n12 >= 2 AND s12 > umbral) AS activa FROM w), m AS (SELECT *, bool_or(activa) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN CURRENT ROW AND INTERVAL 364 DAYS FOLLOWING) AS en_m1r FROM a), s AS (SELECT *, lag(fecha_adj) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj, id_canonical) AS prev FROM m WHERE en_m1r), k AS (SELECT *, sum(CASE WHEN prev IS NULL OR fecha_adj - prev > 364 THEN 1 ELSE 0 END) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj, id_canonical ROWS UNBOUNDED PRECEDING) AS k FROM s) SELECT fuente, id_canonical, md5(fuente || '|' || organo_clave || '|' || empresa_clave || '|' || clase || '|' || objeto_norm || '|' || k::VARCHAR) AS serie_m1r FROM k; -- M1e (sin las filas de fechas convencionales) CREATE OR REPLACE TABLE m1e_d AS SELECT fuente, organo_clave, empresa_clave, clase, fecha_adj, any_value(umbral) AS umbral, count(*) AS n_dia, sum(base) AS suma_dia, md5(fuente || '|' || organo_clave || '|' || empresa_clave || '|' || clase || '|' || fecha_adj::VARCHAR) AS dia_m1e FROM pm1 WHERE NOT fecha_conv GROUP BY fuente, organo_clave, empresa_clave, clase, fecha_adj HAVING count(*) >= 2 AND sum(base) > any_value(umbral); CREATE OR REPLACE TABLE par_anio AS SELECT fuente, organo_clave, empresa_clave, clase, anio, count(*) AS n_par_anio FROM pm1 GROUP BY ALL; CREATE OR REPLACE TABLE m1x AS SELECT p.fuente, p.id_canonical, p.organo_clave, p.anio, p.base, p.exacta, p.fecha_conv, a.en_m1a, (r.serie_m1r IS NOT NULL) AS en_m1r, r.serie_m1r, (d.dia_m1e IS NOT NULL) AS en_m1e, d.dia_m1e, coalesce(pa.n_par_anio >= 10 AND d.dia_m1e IS NOT NULL, FALSE) AS m1e_relacion_continua FROM pm1 p JOIN m1a a USING (fuente, id_canonical) LEFT JOIN m1r r USING (fuente, id_canonical) LEFT JOIN m1e_d d ON d.fuente = p.fuente AND d.organo_clave = p.organo_clave AND d.empresa_clave = p.empresa_clave AND d.clase = p.clase AND d.fecha_adj = p.fecha_adj LEFT JOIN par_anio pa ON pa.fuente = p.fuente AND pa.organo_clave = p.organo_clave AND pa.empresa_clave = p.empresa_clave AND pa.clase = p.clase AND pa.anio = p.anio; -- @ Comprobaciones de M1 (deben ser 0): series mal, filas de más, M1e/M1r fuera de M1a WITH sr AS (SELECT r.fuente, r.serie_m1r, count(*) n, sum(p.base) s, any_value(p.umbral) umbral FROM m1r r JOIN pm1 p USING (fuente, id_canonical) GROUP BY 1, 2) SELECT (SELECT count(*) FROM sr WHERE n < 2 OR s <= umbral) series_mal, (SELECT count(*) FROM m1x) - (SELECT count(*) FROM pm1) filas_de_mas, (SELECT count(*) FROM m1x WHERE (en_m1e OR en_m1r) AND NOT en_m1a) fuera_de_m1a; CREATE OR REPLACE TABLE m1_oa AS SELECT fuente, organo_clave, anio, count(*) AS m1_menores, sum(base) AS m1_importe, count(*) FILTER (WHERE en_m1a) AS m1a_menores, coalesce(sum(base) FILTER (WHERE en_m1a), 0) AS m1a_importe, count(DISTINCT serie_m1r) AS m1r_series, count(*) FILTER (WHERE en_m1r) AS m1r_contratos, coalesce(sum(base) FILTER (WHERE en_m1r), 0) AS m1r_importe, count(DISTINCT dia_m1e) AS m1e_dias, count(*) FILTER (WHERE en_m1e) AS m1e_contratos, coalesce(sum(base) FILTER (WHERE en_m1e), 0) AS m1e_importe, count(DISTINCT dia_m1e) FILTER (WHERE m1e_relacion_continua) AS m1e_dias_relacion_continua, count(*) FILTER (WHERE fecha_conv) AS m1e_fuera_fecha_convencional, count(*) FILTER (WHERE NOT exacta) AS m1_filas_cota FROM m1x GROUP BY ALL; -- ── M2, M6, M4 ───────────────────────────────────────────────────────────────────────────────────────────────────── CREATE OR REPLACE TABLE m2_oa AS WITH b0 AS (SELECT pd.organo_clave, pd.fuente, pd.anio, pd.base, pd.umbral, CASE WHEN pd.tipo_contrato = 'Obras' THEN 'obras' ELSE 'ss' END AS g, CASE WHEN pd.tipo_contrato = 'Obras' OR pd.tipo = 'investigacion' THEN 20000 ELSE 5000 END AS desde FROM pd JOIN dec d ON d.source = pd.fuente AND d.anio = pd.anio WHERE d.ok_m2 AND pd.iva_sin AND pd.tipo_contrato IN ('Servicios', 'Suministros', 'Obras')) SELECT fuente, organo_clave, anio, count(*) FILTER (WHERE g = 'ss' AND base >= desde AND base < umbral) AS m2_ss_n, count(*) FILTER (WHERE g = 'ss' AND base >= umbral - 100 AND base < umbral) AS m2_ss_borde100, count(*) FILTER (WHERE g = 'ss' AND base >= umbral - 250 AND base < umbral) AS m2_ss_borde250, count(*) FILTER (WHERE g = 'ss' AND base >= umbral - 500 AND base < umbral) AS m2_ss_borde500, count(*) FILTER (WHERE g = 'obras' AND base >= desde AND base < umbral) AS m2_ob_n, count(*) FILTER (WHERE g = 'obras' AND base >= umbral - 1000 AND base < umbral) AS m2_ob_borde1000 FROM b0 GROUP BY ALL; CREATE OR REPLACE TABLE m6_oa AS SELECT pd.fuente, pd.organo_clave, pd.anio, count(pd.num_ofertas) FILTER (WHERE pd.num_ofertas >= 1) AS m6_n, count(*) FILTER (WHERE pd.num_ofertas = 1) AS m6_n1, count(*) FILTER (WHERE pd.num_ofertas >= 3) AS m6_n3mas FROM pd JOIN dec d ON d.source = pd.fuente AND d.anio = pd.anio WHERE d.ok_m6 GROUP BY ALL; CREATE OR REPLACE TABLE m4_oa AS WITH e AS (SELECT pd.fuente, pd.organo_clave, pd.anio, pd.empresa_clave, sum(pd.base) imp, count(*) n FROM pd JOIN dec d ON d.source = pd.fuente AND d.anio = pd.anio WHERE d.ok_m4 GROUP BY ALL), t AS (SELECT fuente, organo_clave, anio, sum(imp) tot, sum(n) n, count(*) proveedores FROM e GROUP BY ALL), r AS (SELECT e.*, t.tot, t.n n_org, t.proveedores, row_number() OVER (PARTITION BY e.fuente, e.organo_clave, e.anio ORDER BY e.imp DESC, e.empresa_clave) rk FROM e JOIN t USING (fuente, organo_clave, anio)) SELECT fuente, organo_clave, anio, any_value(n_org) AS m4_n, any_value(tot) AS m4_importe, any_value(proveedores) AS m4_proveedores, round(100 * (max(imp) FILTER (WHERE rk = 1))::DOUBLE / any_value(tot)::DOUBLE, 6) AS m4_cuota_top1_pct, round(sum((imp::DOUBLE / tot::DOUBLE) * (imp::DOUBLE / tot::DOUBLE)), 6) AS m4_hhi, (any_value(n_org) >= 50 AND any_value(tot) >= 200000) AS m4_elegible FROM r GROUP BY fuente, organo_clave, anio; -- Etiqueta del órgano-año (exacta si todas sus filas de q lo son) y % en otra fuente CREATE OR REPLACE TABLE et_oa AS SELECT fuente, organo_clave, anio, bool_and(exacta) AS exacto, round(100.0 * avg((NOT exacta)::INT), 2) AS pct_filas_cota, round(100.0 * avg(copia_fuera::INT), 2) AS pct_en_otra_fuente FROM q GROUP BY ALL;