Por favor, eche un vistazo a este violín.

https://www.db-fiddle.com/f/71CxYHKkzwmXJnovzpFheV/7

Estoy tratando de lograr 2 cosas:

  1. ¿Cómo obtengo la duración y la fecha de la ÚLTIMA RACHA DE ADIVINACIONES CORRECTAS (es decir, Resultado = Adivinar) sin omitir ninguna fecha? En este caso, serían 4, comenzando desde 2021-01-05 hasta 2021-01-08. (Aunque 2021-01-03 es correcto, debido a que no hubo conjeturas sobre 2021-01-04, no debería incluirse).

  2. ¿Cómo obtengo la duración y la fecha de la RACHA MÁS LARGA DE PERSONAS CORRECTAS DE TODOS LOS TIEMPOS? Nuevamente significa Resultado = Adivinar, pero puede estar en cualquier lugar de la tabla. Digamos que son 10 de hace 3 meses.

Para complicar aún más las cosas, varios usuarios pueden hacer conjeturas Y habrá varios resultados (para diferentes categorías de juegos, por ejemplo) el mismo día. Entonces, la tabla anterior es para un usuario y una categoría de juego. Creo que puedo manejar esto si puedo obtener alguna orientación sobre los objetivos anteriores.

Esto está más allá de mi comprensión. Se agradecería cualquier ayuda.

EDITAR: He cambiado la tabla para mostrar que la fecha no siempre es secuencial. Además, se me informó que debería usar MySQL 8.0 para esta tarea, ya que el uso de variables no es una buena práctica para este problema.

Editar: usando las funciones de la ventana, comenzando a llegar a alguna parte:

Por favor revise el violín. Está bastante cerca de lo que estoy tratando de llegar, pero el '4' en la columna total debería ser un 1. En otras palabras, la "suma" debería reiniciarse. No estoy seguro de cómo lograr esto, porque está claro que la función de ventana se agrupará en función de las condiciones, rompiendo el orden y, por lo tanto, la racha.

Actualizado: he actualizado el violín según la petición de @The Impaler. La tabla aquí es más representativa de lo que realmente estoy trabajando (todavía no es exacta, pero mucho más cercana).

Dado que este nuevo violín es más representativo, también explicaré mi objetivo final. También me gustaría obtener la racha para cada game_type. La forma en que he estado comparando el resultado de game_type en un día determinado con la conjetura de la "comunidad" (básicamente todos los usuarios) es sumando todos los 0 y 1 para cada game_type en esa fecha de todos los usuarios y luego usando el valor mayor como ' adivinar'. De esta manera, puedo ver cómo le está yendo a la "comunidad" en su conjunto. Esto funciona para citas individuales, pero para hacer una racha, no estoy seguro.

Actualización 2 Así que esto es todo lo que tengo:

https://www.db-fiddle.com/f/71CxYHKkzwmXJnovzpFheV/11

Intenté hacer una función de ventana anidada pero no está permitido. Tengo las agrupaciones y la columna adecuadas para cuando adivine = resultado. Ahora necesito ayuda para descifrar la racha dentro de los grupos.

1
MikelG 23 feb. 2021 a las 03:48

2 respuestas

La mejor respuesta

Entonces, tomó un tiempo, pero gracias a @The Impaler que me proporcionó la base y el enlace a continuación, pude resolver el problema.

https://www.red-gate.com/simple-talk/sql/t-sql-programming/efficient-solutions-to-gaps-and-islands-challenges/

Aquí está la solución completa:

with GAME_LOG as (
  select 
    *,
    guess = result as correct,
    lag(case when (guess = result) then 1 else 0 end) over(partition by user_id, game_type) as previous_game_result,
    lead(case when (guess = result) then 1 else 0 end) over(partition by user_id, game_type) as next_game_result,
    row_number() over(partition by user_id, game_type order by dayt DESC) as ilocation
  from mytable
),
  
CTE_ISLAND_START as (
  select
    *,
    row_number() over(partition by user_id, game_type order by dayt DESC) as inumber,
    dayt as island_start_time,
    ilocation as island_start_location
  from GAME_LOG
  where correct = 1 AND
    (previous_game_result <> 1 OR previous_game_result is null)
),

CTE_ISLAND_END as (
  select
    *,
    row_number() over(partition by user_id, game_type order by dayt DESC) as inumber,
    dayt as island_end_time,
    ilocation as island_end_location
  from GAME_LOG
  where correct = 1 AND
    (next_game_result <> 1 OR next_game_result is null)
)

select
  CTE_ISLAND_START.user_id,
  CTE_ISLAND_START.game_type,
  CTE_ISLAND_START.island_start_time as streak_end,
  CTE_ISLAND_END.island_end_time as streak_start,
  cast(CTE_ISLAND_END.island_end_location as signed) - 
   cast(CTE_ISLAND_START.island_start_location as signed) + 1 as streak
from CTE_ISLAND_START
inner join CTE_ISLAND_END
on CTE_ISLAND_START.inumber = CTE_ISLAND_END.inumber AND
  CTE_ISLAND_START.user_id = CTE_ISLAND_END.user_id AND
  CTE_ISLAND_START.game_type = CTE_ISLAND_END.game_type

Esto le dará todas las rachas para cada user_id, cada game_type, así como las fechas de inicio y finalización de la racha.

Simplemente puede agregar una cláusula WHERE para filtrar por game_type y user_id.

Aquí está el violín con un conjunto de datos ligeramente actualizado.

Violín

1
MikelG 11 mar. 2021 a las 00:13

Este es un problema típico de "Brechas e islas". Una vez que ensamblas las islas, la consulta se vuelve fácil.

Por ejemplo, para un solo usuario, como se indica en el violín, puede obtener la RACHA MÁS LARGA haciendo:

with 
i as (
  select
    min(dayt) as starting_day,
    max(dayt) as ending_day,
    count(*) as streak_length
  from (
    select *, sum(beach) over(order by dayt) as island
    from (
      select *,
        guess = result as inland,
        case when (guess = result) <> (
          lag(guess) over(order by dayt) = lag(result) over(order by dayt))
          then 1 else 0 end as beach
      from mytable
    ) x
    where inland = 1
  ) y
  group by island
)
select *
from i
order by streak_length desc
limit 1;

Resultado:

starting_day  ending_day  streak_length 
------------- ----------- ------------- 
2021-01-06    2021-01-08  3             

Para obtener la RACHA MÁS RECIENTE, solo necesita cambiar la cláusula ORDER BY al final como se muestra a continuación:

with 
i as (
  select
    min(dayt) as starting_day,
    max(dayt) as ending_day,
    count(*) as streak_length
  from (
    select *, sum(beach) over(order by dayt) as island
    from (
      select *,
        guess = result as inland,
        case when (guess = result) <> (
          lag(guess) over(order by dayt) = lag(result) over(order by dayt))
          then 1 else 0 end as beach
      from mytable
    ) x
    where inland = 1
  ) y
  group by island
)
select *
from i
order by ending_day desc
limit 1;

Resultado (el mismo resultado que antes):

starting_day  ending_day  streak_length 
------------- ----------- ------------- 
2021-01-06    2021-01-08  3             

Vea el ejemplo de ejecución en DB Fiddle.

Nota : puede eliminar la cláusula LIMIT al final para ver todas las islas, no solo la seleccionada.

Para múltiples usuarios, es solo una cuestión de modificar las ventanas (agregar particiones) y el resto de la consulta permanece igual. Si proporciona un violín para múltiples usuarios, también puedo agregar la solución.

1
The Impaler 8 mar. 2021 a las 02:18