Совокупная функция в течение заданного интервала времени

Мой SQL немного ржавый, и у меня довольно много проблем с этой проблемой. Предположим, что у меня есть таблица с столбцом Timestamp и столбцом Number. Цель состоит в том, чтобы вернуть набор результатов, содержащий среднее значение для некоторого произвольно выбранного регулярного интервала.

Так, например, если бы у меня были следующие исходные данные, результат с 5-минутным интервалом был бы следующим:

time value ------------------------------- ----- 06-JUN-12 12.40.00.000000000 PM 2 06-JUN-12 12.41.35.000000000 PM 3 06-JUN-12 12.43.22.000000000 PM 4 06-JUN-12 12.47.55.000000000 PM 5 06-JUN-12 12.52.00.000000000 PM 2 06-JUN-12 12.54.59.000000000 PM 3 06-JUN-12 12.56.01.000000000 PM 4 OUTPUT: start_time avg_value ------------------------------- --------- 06-JUN-12 12.40.00.000000000 PM 3 06-JUN-12 12.45.00.000000000 PM 5 06-JUN-12 12.50.00.000000000 PM 2.5 06-JUN-12 12.55.00.000000000 PM 4 

Обратите внимание, что это база данных Oracle, поэтому решения, ориентированные на Oracle, будут работать нормально. Это, конечно, можно было бы сделать с помощью хранимой процедуры, но я надеялся выполнить задачу в одном запросе.

 CREATE TABLE tt (time TIMESTAMP, value NUMBER); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.40.00.000000000 PM', 2); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.41.35.000000000 PM', 3); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.43.22.000000000 PM', 4); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.47.55.000000000 PM', 5); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.52.00.000000000 PM', 2); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.54.59.000000000 PM', 3); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.56.01.000000000 PM', 4); WITH tmin AS ( SELECT MIN(time) t FROM tt ), tmax AS ( SELECT MAX(time) t FROM tt ) SELECT ranges.inf, ranges.sup, AVG(tt.value) FROM ( SELECT 5*(level-1)*(1/24/60) + tmin.t as inf, 5*(level)*(1/24/60) + tmin.t as sup FROM tmin, tmax CONNECT BY (5*(level-1)*(1/24/60) + tmin.t) < tmax.t ) ranges JOIN tt ON tt.time BETWEEN ranges.inf AND ranges.sup GROUP BY ranges.inf, ranges.sup ORDER BY ranges.inf 

скрипка: http://sqlfiddle.com/#!4/9e314/11

Редактировать: избили Джастин, как обычно … 🙂

Что-то вроде

 with st as (SELECT to_timestamp( '2012-06-06 12:40:00', 'yyyy-mm-dd hh24:mi:ss') + numtodsinterval((level-1)*5, 'MINUTE') start_time, to_timestamp( '2012-06-06 12:40:00', 'yyyy-mm-dd hh24:mi:ss') + numtodsinterval(level*5, 'MINUTE') end_time from dual connect by level <= 10) SELECT st.start_time, avg( yt.value ) FROM your_table yt, st WHERE yt.time between st.start_time and st.end_time 

должно сработать. Вместо того, чтобы генерировать 10 интервалов и жестко кодировать самый низкий интервал, вы можете улучшить запрос, чтобы получить начальную точку и количество строк из MIN(time) и MAX(time) в таблице.

Ответы Джастина и Себаса могут быть расширены ЛЕВЫМ ПРИСОЕДИНЕНИЕМ, чтобы устранить «пробелы», что часто желательно.

Если это не обязательно, в качестве альтернативы, мы можем пойти в арифметику старой школы Oracle DATE …

 SELECT TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400 AS time , AVG(t.value) AS avg_value FROM foo t WHERE t.time IS NOT NULL GROUP BY TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400 ORDER BY TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400 

Давайте распакуем это немного. Мы можем отделить компоненты даты и времени, используя TRUNC, чтобы получить часть даты, и с помощью TO_CHAR вернуть число секунд с полуночи. Мы знаем, что 5 минут – 300 секунд, и мы знаем, что есть 86400 секунд в день. Таким образом, мы можем разделить количество секунд на 300 и взять FLOOR этого (только целую часть), который округляет нас до ближайшей границы в 5 минут. Мы умножим это назад (на 300), чтобы снова получить секунды, а затем разделим это на количество секунд в день (86400), и мы можем добавить это обратно к части (сокращенной) дате.

Больно, да. Но невероятно быстро.

ПРИМЕЧАНИЕ: это возвращает значение округленного времени как DATE , это может быть возвращено к отметке времени, если это необходимо, но даже на 5-минутных границах DATE имеет достаточное разрешение.

Как преимущество этого подхода, для большой таблицы мы можем повысить производительность запроса, добавив индекс покрытия для этого запроса:

 CREATE INDEX foo_FBX1 ON foo (TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400,value); 

ДОПОЛНЕНИЕ:

MiMo предоставил ответ для SQL Server, предполагая, что он будет адаптирован для Oracle. Вот адаптация этого подхода в Oracle. Обратите внимание, что Oracle не предоставляет эквивалентов для функций DATEDIFF и DATEADD. Вместо этого Oracle использует простую арифметику.

 SELECT TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288 AS time , AVG(t.value) AS avg_value FROM foo t WHERE t.time IS NOT NULL GROUP BY TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288 ORDER BY TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288 

Выбор 1 января 0001 года в качестве базовой даты является произвольным, но я не хотел связываться с отрицательными значениями и выяснять, будет ли FLOOR правильным, или нам нужно использовать CEIL с отрицательными числами. (Волшебное число 288 – результат 1440 минут в день, разделенный на 5). В этом случае мы занимаем дробный день, умножаясь на 1440 и деля на 5, и берем целую часть этого, а затем возвращаем его в дробные дни.

Заманчиво вытащить эту «базовую дату» из пакета PL / SQL или получить ее из подзапроса, но любое из них может помешать этому выражению быть детерминированным. И мы действительно хотели бы открыть возможность создания индекса, основанного на функциях.

Мое предпочтение заключается в том, чтобы избежать необходимости включать «базовую дату» в расчет.

Это решение для SQL Server:

 declare @startDate datetime = '2000-01-01T00:00:00' declare @interval int = 5 select DATEADD(mi, DATEDIFF(mi, @startDate, time)/@interval, @startDate), AVG(value) from table group by DATEDIFF(mi, @startDate, s_modifiedDate)/@interval order by DATEDIFF(mi, @startDate, s_modifiedDate)/@interval 

Дата начала произвола. Идея состоит в том, что вы вычисляете количество минут с даты начала, а затем группируетесь по этому числу, разделенному интервалом.

Он должен легко адаптироваться к Oracle, используя эквивалент DATEADD и DATEDIFF