Блог

Пожалуйста, взгляните на эту скрипку.

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

Я пытаюсь выполнить 2 вещи:

1. Как мне получить длину и дату ПОСЛЕДНЕЙ СЕРИИ ПРАВИЛЬНЫХ ПРЕДПОЛОЖЕНИЙ (что означает Результат = Предположение) без каких-либо пропущенных дат? В этом случае это будет 4, начиная с 2021-01-05 по 2021-01-08. (Хотя 2021-01-03 является правильным, поскольку в 2021-01-04 не было предположения, его не следует включать).
2. Как мне получить длину и дату самой ДЛИННОЙ СЕРИИ ПРАВИЛЬНЫХ ДОГАДОК ЗА ВСЕ ВРЕМЯ? Снова означает результат = Предположение, но может быть где угодно в таблице. Допустим, это 10 с 3 месяцев назад.

Чтобы еще больше усложнить ситуацию, предположения могут быть сделаны несколькими пользователями, и в один и тот же день будет несколько результатов (например, для разных категорий игр). Таким образом, приведенная выше таблица предназначена для одного пользователя и одной категории игр. Я думаю, что смогу справиться с этим, если смогу получить некоторые рекомендации по вышеуказанным целям.

Это выходит за рамки моего понимания. Будем признательны за любую помощь.

РЕДАКТИРОВАТЬ: я изменил таблицу, чтобы показать, что дата не всегда последовательна. Кроме того, мне сообщили, что я должен использовать MySQL 8.0 для этой задачи, поскольку использование переменных не является хорошей практикой для этой проблемы.

Редактировать: используя оконные функции, начинаю куда-то добираться:

Пожалуйста, проверьте скрипку. Это довольно близко к тому, к чему я пытаюсь добраться, но «4» в общем столбце должно быть равно 1. Другими словами, «сумма» должна перезапуститься. Не уверен, как этого добиться, потому что ясно, что функция window будет группироваться на основе условий, нарушая порядок и, следовательно, последовательность.

Обновлено: я обновил скрипку по запросу @The Impaler. Приведенная здесь таблица более репрезентативна для того, с чем я на самом деле работаю (все еще не точная, но гораздо ближе).

Поскольку эта новая скрипка более репрезентативна, я также объясню свою конечную цель. Я также хотел бы получить строку для каждого game_type. Способ, которым я сравнивал результат game_type в определенный день с предположением «сообщества» (в основном всех пользователей), заключается в суммировании всех 0 и 1 для каждого game_type на эту дату от всех пользователей, а затем в зависимости от того, что больше, в качестве «предположения». Таким образом, я могу понять, как работает «сообщество» в целом. Это работает для отдельных дат, но для выполнения строки я не уверен.

Обновление 2 Итак, это все, что у меня есть:

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

Я пытался выполнить функцию вложенного окна, но это запрещено. У меня есть правильные группировки и столбцы для того, когда угадать = результат. Теперь мне нужна помощь в определении последовательности внутри групп.

Это типичная проблема «пробелов и островов». Как только вы соберете острова, запрос станет простым.

Например, для одного пользователя, как указано в скрипке, вы можете получить самую ДЛИННУЮ СТРОКУ, выполнив:

 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;
 

Результат:

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

Чтобы получить ПОСЛЕДНЮЮ СТРОКУ, вам просто нужно изменить ORDER BY предложение в конце, как показано ниже:

 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;
 

Результат (тот же результат, что и раньше):

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

Смотрите Пример выполнения в DB Fiddle.

Примечание: вы можете удалить LIMIT предложение в конце, чтобы увидеть все острова, а не только выбранный.

Для многопользовательских пользователей это просто вопрос изменения окон (добавления разделения), а остальная часть запроса остается прежней. Если вы предоставляете скрипку для многопользовательских пользователей, я также могу добавить решение.

Итак, это заняло некоторое время, но благодаря @The Impaler, предоставившему мне основу и ссылку ниже, я смог решить проблему.

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

Вот полное решение:

 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
 

Это даст все строки для каждого идентификатора пользователя, каждого game_type, а также даты начала и окончания строки.

Вы можете просто добавить WHERE предложение для фильтрации по game_type и user_id.

Вот скрипка с немного обновленным набором данных.

Скрипка