Блог

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

 RowID | ProviderID | ClientID |     DOS    |   DOS (Week)  | Hours 
  1   | 1111111111 | 22222222 | 11/2/2020  |   11/1/2020   | 2.5 
  2   | 1111111111 | 33333333 | 11/5/2020  |   11/1/2020   | 1 
  3   | 1111111111 | 44444444 | 10/13/2020 |   10/11/2020  | 3 
  

Я пытаюсь получить дополнительный столбец «Активный за 3 недели до» с y / n или 1/0 для значений. Для приведенной выше таблицы предположим, что поставщик начал работу 13.10/20. В идеале новый столбец будет заполняться следующим образом:

 RowID | ProviderID | ClientID |     DOS    |   DOS (Week)  | Hours | Active 3 weeks Prior 
  1   | 1111111111 | 22222222 | 11/2/2020  |   11/1/2020   | 2.5   |   Yes              
  2   | 1111111111 | 33333333 | 11/5/2020  |   11/1/2020   | 1     |   Yes
  3   | 1111111111 | 44444444 | 10/13/2020 |   10/11/2020  | 3     |   No
  

Пара дополнительных лакомых кусочков: наша организация использует воскресенье в качестве начала недели, поэтому DOS (неделя) — это воскресенье, предшествующее дате обслуживания. Из того, что я читал до сих пор, похоже, что решение здесь — это своего рода самосоединение, где базовые производственные записи объединяются в еженедельные часы и сравниваются с записями того же providerId для DOS (неделя) — 21.

Проблема, с которой я сталкиваюсь, заключается в следующем: нахожусь ли я на правильном пути в первую очередь с самосоединением и как я буду генерировать значения y / n на основе успеха или неудачи в поиске соответствующего значения. Кроме того, я подозреваю, что объединение на основе объединения providerId и DOS (неделя) может быть ошибочным? Это то, с чем я играл до сих пор.

Пожалуйста, дайте мне знать, могу ли я вообще прояснить вопрос или я упускаю что-то очень очевидное. Я действительно ценю любую помощь, так как уже несколько дней пытаюсь найти правильные условия поиска, чтобы получить ключ к ответу.

Если вы используете MySQL 8.0, вы можете использовать оконные функции и range спецификацию:

 select t.*,
    (
        max(providerid) over(
            partition by providerid 
            order by dos
            range between interval 3 week preceding and interval 3 week preceding
        ) is not null
    ) as active_3_weeks_before
from mytable t
  

Из ваших объяснений и данных не совсем понятно, что вы подразумеваете под тем, что также работало тремя неделями ранее. Для каждой строки запрос проверяет, существует ли другая строка с тем же поставщиком, и dos это ровно за 3 недели до dos текущей строки. Это можно легко адаптировать для некоторых других требований.

Редактировать: если вы хотите проверить наличие какой-либо записи за последние 3 недели, вам следует изменить диапазон окон на:

 range between interval 3 week preceding and interval 1 day preceding
  

И если вы хотите это в MySQL <8.0, где оконные функции недоступны, тогда вы должны использовать коррелированный подзапрос:

 select t.*,
    exists (
        select 1
        from mytable t1
        where 
            t1.providerid = t.provider_id
            and t1.dos >= t.dos - interval 3 week
            and t1.dos <  t.dos
    ) as active_3_weeks_before
from mytable t