#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. Еще раз спасибо.