Самосоединение? Были ли сотрудники, работавшие на предыдущей неделе, активными 3 недели назад — MYSQL

#mysql #sql #datetime #date-arithmetic #domo

#mysql #sql #дата и время #дата-арифметика #domo

Вопрос:

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

 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 (неделя) может быть ошибочным? Это то, с чем я играл до сих пор.

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

Комментарии:

1. Просто покажите нам свой код

2. Конечно, я немало покопался и попытался, прежде чем опубликовать. Когда я искал способ правильно задать вопрос, казалось, что предоставление выборочных данных было лучшим методом, я не уверен, что еще вы хотели бы для MRE. Я не предоставил свои попытки кода, поскольку считаю, что они далеки. Вы хотите, чтобы я записал образцы данных в виде ТАБЛИЦЫ СОЗДАНИЯ и ВСТАВИЛ В, иначе я не уверен, что могу уточнить.

3. Как это «ВОСПРОИЗВОДИМО»?

Ответ №1:

Если вы используете 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
  

Комментарии:

1. Привет, извините за неясность. Что я имел в виду, работая тремя неделями ранее, так это то, были ли у этого providerId какие-либо даты обслуживания, отработанные в DOS (неделя) за три недели до текущей строки. Итак, в таблице примеров первые две строки будут отображаться как «да», потому что поставщик работал на неделе 10/11, в то время как третья строка будет отображаться как «нет», потому что поставщик начал работу на неделе 10/11 и, следовательно, не работал тремя неделями ранее. Я попробую ваше предложение, спасибо!

2. К сожалению, я делаю это в DOMO, который из-за использования MySQL 5.6, похоже, не поддерживает предложение OVER…

3. Это кажется идеальным, большое вам спасибо! Теперь мне любопытно, как это решение так далеко от того, что я находил — какие поисковые запросы могли указывать ближе к этому направлению? Все, что я искал относительно сравнения строк в одном и том же наборе данных, указывало на самосоединения. Жаль, что я не видел ничего, ссылающегося на ТО, ЧТО СУЩЕСТВУЕТ в комбинации с логикой SELECT 1 и WHERE. Еще раз спасибо.