Поиск нескольких последовательных дат (datetime) в Ruby on Rails/Postgresql
Как мы можем найти X последовательные даты (используя hour), которые удовлетворяют условию?
Обновлено: вот рабочий пример SQL http://sqlfiddle.com/#!17/44928/1
Пример:
Найдите 3 последовательных даты where aa < 2 и bb < 6 и cc < 7
Учитывая эту таблицу под названием weather:
Ответ должен вернуть 3 записи из 02:00, 03:00, 04:00.
Как мы можем сделать это в Ruby on Rails или напрямую в SQL, если это лучше?
Я начал работать над методом, основанным на этом ответе: Обнаружение последовательных диапазонов дат с помощью SQL
def consecutive_dates
the_query = "WITH t AS (
SELECT timestamp d,ROW_NUMBER() OVER(ORDER BY timestamp) i
FROM @d
GROUP BY timestamp
)
SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(hour,i,d)"
ActiveRecord::Base.connection.execute(the_query)
end
Но я не смог заставить его работать.
Ответы 2
) Это проблема пробелов и островов. Острова — это соседние записи, соответствующие условию, и вам нужны острова, длина которых составляет не менее 3 записей.
Вот один из подходов, использующий счетчик окон, который увеличивается каждый раз, когда значение, не соответствующее условию, выполняется для определения групп. Затем мы можем подсчитать количество строк в каждой группе и использовать эту информацию для фильтрации.
select *
from (
select t.*, count(*) over(partition by a, grp) cnt
from (
select t.*,
count(*) filter(where b <= 4) over(partition by a order by timestamp) grp
from mytable t
) t
) t
where cnt >= 3
@viktorsmari: вы бы просто изменили предложение filter функции внутреннего окна. Обратите внимание, однако, что вы не должны существенно изменять вопрос после публикации ответов. Это делает ответ недействительным, что может привести к отрицательным отзывам.
Да, извините, я просто сделал это, чтобы сделать это немного яснее, и я прокомментировал, чтобы вы знали, чтобы вы могли изменить свои as на aas. Приносим извинения за неудобства. Итак, чтобы уточнить, чтобы найти cc - я мог бы изменить фильтр на: filter(where b <= 4 AND c < 7)?
Я просто сбит с толку как ответом, так и плюсами. В нем нет условий фильтрации.
Предполагая, что у вас есть одна строка каждый час, простой способ получить первый час, когда это происходит, использует lead():
select t.*
from (select t.*,
lead(timestamp, 2) over (order by timestamp) as timestamp_2
from t
where aa < 2 and bb < 6 and cc < 7
) t
where timestamp_2 = timestamp + interval '2 hour';
Это фильтрует условия и просматривает строки на две строки вперед. Если на два часа вперед, то три ряда подряд соответствуют условиям. Примечание. Приведенное выше вернет как 2020-01-01 02:00, так и 2020-01-01 03:00.
Судя по вашему вопросу, вы хотите только самого раннего. Чтобы справиться с этим, также используйте lag():
select t.*
from (select t.*,
lag(timestamp) over (order by timestamp) as prev_timestamp
lead(timestamp, 2) over (order by timestamp) as timestamp_2
from t
where aa < 2 and bb < 6 and cc < 7
) t
where timestamp_2 = timestamp + interval '2 hour' and
(prev_timestamp is null or prev_timestamp < timestamp - interval '1' hour);
Вы можете сгенерировать дополнительные часы использования generate_series(), если вам действительно нужны исходные строки:
select t.timestamp + n.n * interval '1 hour', aa, bb, cc
from (select t.*,
lead(timestamp, 2) over (order by timestamp) as timestamp_2
from t
where aa < 2 and bb < 6 and cc < 7
) t cross join lateral
generate_series(0, 2) n
where timestamp_2 = timestamp + interval '2 hour';
Ваши данные, похоже, имеют точные временные метки, основанные на вопросе, поэтому равенство временных меток будет работать. Если реальные данные имеют большую нечеткость, то запросы можно настроить, чтобы учесть это.
Спасибо Гордон! Я добавил sql fiddle, если вы хотите протестировать свой код, я пытался, но получил ошибки. Может имя таблицы отсутствует?
Плохо, у меня был скриптовый движок SQL на MySQL, а не на PostgreSQL, обновляю здесь
Нам нужен только timestamp последней последовательной точки. Мы хотим выполнять «действия», которые занимают, например, 3 часа, и нам нужно 3 часа подряд, когда выполняются условия (aa, bb, cc).
@виктормари . . . Ваш вопрос никоим образом не предполагает, что вам нужна последняя точка, которая будет 05:00 и не будет в вашем наборе результатов. Это специально обрабатывает случай трех соседних строк, в которых выполняются условия.
Вы правы, извините, я обновлю вопрос, чтобы сделать его более ясным. Первоначально я думал, что нам нужны 3 записи, но последней последовательной метки времени достаточно. Вопрос: Если бы нам потребовалось 6 последовательных записей, нам нужно было бы изменить только ваши примеры, где указано от 2 до 5? Итак, если мы используем переменную consecutive_hours - 1?
@виктормари . . . Не обновляйте вопрос. У вас есть два ответа на вопрос как есть, и это, вероятно, сделает ответы недействительными. Задайте новый вопрос, который будет четким (и подчеркнет именно то, что вы хотите, чтобы он не был закрыт как дубликат этого вопроса).
Хорошо, Гордон! Задача состоит в том, чтобы найти X последовательные даты. Используя ваш первый ответ (и мой предыдущий комментарий), будет ли это правильным способом обработки X последовательных дат? Например, если нам потребуется 6 последовательных дат вместо 3, какие части вашего первого примера нам нужно будет заменить?
Просто замените «2» на «5».
Давайте продолжим обсуждение в чате.
Спасибо, этот код действительно работает! Я переименовал переменную
aвaa, чтобы не ошибиться с синтаксисом SQL. Я также добавил третью переменную,cc, чтобы помочь мне понять синтаксис. Как бы мы добавили третью переменнуюcc?