Gaps y islas, edición de división completa

02 Jun, 2023 Programación 0

Estoy tratando de implementar el problema de lagunas e islas sobre un conjunto de números, aquí hay un ejemplo:
0 0 3 4 5 6 6 7 7 8 11 12 18 22
Eso es una suma acumulativa, por lo que los números no pueden disminuir. Lo que necesito hacer es separar los registros respectivos en grupos según estas reglas:
1. El primer (y más pequeño) número en un grupo es un número “líder”
2. Un número puede estar en el mismo grupo que su número líder solo si es más grande en no más de cierto número (digamos 7 para este ejemplo)
3. El primer número que exceda número líder + 7 es un número líder para el siguiente grupo

Entonces, con el ejemplo mostrado anteriormente, los grupos serían:
0 0 3 4 5 6 6 7 7
8 11 12
18 22

Es casi como dividir un número por la brecha y obtener un número de grupo de esa manera, pero dado que la brecha entre el último número en un grupo y el número líder en el siguiente grupo puede ser cualquier número positivo, esto se volvería cada vez más incorrecto a medida que la secuencia crece. Intenté case when sum(...) over(...) > 7 then sum(...) else 0 end pero afecta a cada número que no está en el primer grupo, así que ya no estoy seguro de cómo abordar esto. ¡Cualquier ayuda sería apreciada! En caso de que sea importante, la tabla está en Snowflake.

WITH numbered AS ( SELECT val, ROW_NUMBER() OVER (ORDER BY val) rn FROM my_table ), islands AS ( SELECT val, rn, val - ROW_NUMBER() OVER (ORDER BY val) * 7 grp FROM numbered ) SELECT MIN(val) min_val, MAX(val) max_val FROM islands GROUP BY rn, grp ORDER BY min_val;

Answer

davy.ai

June 2, 2023 at 2:23 pm
Para implementar el problema de gaps e islands en un conjunto de números, debemos seguir las reglas definidas:
1. El primer número (y el más pequeño) en un grupo es un número “líder”.
2. Un número puede estar en el mismo grupo que su número líder solo si es mayor en no más de un cierto número (digamos 7 para este ejemplo).
3. El primer número que excede líder + 7 es un número líder para el próximo grupo.
Podemos usar una combinación de funciones de agregación y funciones de ventana para lograr el resultado deseado en SQL. Aquí hay una consulta de ejemplo para implementar esto:
```
WITH numbered AS (
   SELECT val,
          ROW_NUMBER() OVER (ORDER BY val) rn
   FROM   my_table
),
islands AS (
   SELECT val,
          rn,
          val - ROW_NUMBER() OVER (ORDER BY val) * 7 grp
   FROM numbered
)
SELECT MIN(val) min_val,
       MAX(val) max_val
FROM   islands
GROUP BY rn, grp
ORDER BY min_val;
```
En esta consulta, primero asignamos un número de fila a cada valor en la tabla utilizando la función ROW_NUMBER(). Luego usamos una función de ventana para calcular el número de grupo para cada valor basado en las reglas definidas. El número de grupo se calcula como la diferencia entre el valor y el número de fila multiplicado por 7 (la diferencia máxima permitida entre valores en el mismo grupo).

Finalmente, agrupamos los valores por su número de fila y número de grupo, y seleccionamos el valor mínimo y máximo para cada grupo para obtener el resultado deseado.

Esta consulta debería funcionar en Snowflake o en cualquier otra base de datos SQL que admita funciones de ventana.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Gaps y islas, edición de división completa

Answer

davy.ai