Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Среднее значение каждых n-сторок

Автор: bernex 5.9.2010, 16:02

Есть таблица MySQL:

Код


ID <int>
TYPE <int>
DATE <timestamp>
VALUE <int>

Каждую секунду кидаются значения.
В одной такой таблице 20 миллионов записей.

Задача:
Выбрать набор значений одного типа (TYPE = 1), с усредненными значениями за час или минуту. Также в строке должен остаться DATE для определения минуты или часа?

Прошу помочь... спасибо...

Автор: Anark1 5.9.2010, 20:13

Можно, например написать функцию извлекающую из DATE часы или минуты, то есть HOUR(DATE) или MIN(DATE),
соответствующие запросы:

Код


SELECT Sum(VALUE)/COUNT(*) AS Frequency, HOUR(DATE) AS Hours FROM MyTable WHERE TYPE=1 GROUP BY HOUR(DATE)

SELECT Sum(VALUE)/COUNT(*) AS Frequency, MIN(DATE) AS Mins FROM MyTable WHERE TYPE=1 GROUP BY MIN(DATE)

Придумал навскидку, не проверял, но идея такова.

Автор: bernex 5.9.2010, 20:35

data там int в ввиде цифры timestamp в формате int
будет работать HOUR?

Автор: Zloxa 5.9.2010, 20:45

Цитата(Anark1 @ 5.9.2010, 20:13

)

Sum(VALUE)/COUNT(*)

rtfm AVG

Цитата(Anark1 @ 5.9.2010, 20:13

)

GROUP BY HOUR(DATE)

одинаковые часы разных суток схлопнутся в одну группу - ничо так?

Автор: Anark1 5.9.2010, 22:06

Цитата(Zloxa @ 5.9.2010, 20:45

)

одинаковые часы разных суток схлопнутся в одну группу - ничо так?

Цитата(Anark1 @ 5.9.2010, 20:13

)

Придумал навскидку, не проверял, но идея такова.

Добавить функцию DAY и делать группировку по двум значениям или придумать что-нибудь более изощренное, то что выполняло бы нужную группировку. У меня нет под рукой mysql, но можно попробовать что-нибудь вроде:

Код


SELECT Sum(VALUE)/COUNT(*) AS Frequency, EXTRACT(YEAR_MONTH_DAY_HOUR FROM CAST(DATE AS DATETIME)) AS UniqDate FROM MyTable WHERE TYPE=1 GROUP BY  EXTRACT(YEAR_MONTH_DAY_HOUR FROM CAST(DATE AS DATETIME))

CAST(TIMESTAMP AS DATETIME) работает нормально на MS SQL, наверное на mysql тоже.
Вообще тут полно функций по работе с датой и временем.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract

Цитата(bernex @ 5.9.2010, 20:35

)

data там int в ввиде цифры timestamp в формате int
будет работать HOUR?

Нужно определить функцию HOUR. Такое решение подразумевает определение UDF. Читайте про user-defined functions. Или см. выше.

P.S. Кстати, по поводу AVERAGE... так может быть автор хотя бы поймет о чем речь. ;)

Автор: Zloxa 5.9.2010, 22:58

оп... проглядел.

Цитата(Anark1 @ 5.9.2010, 20:13

)

GROUP BY MIN(DATE)

Цитата(Anark1 @ 5.9.2010, 22:06

)

CAST(TIMESTAMP AS DATETIME) работает нормально на MS SQL

ничетак, что timestamp в MS Sql и в MySQL суть разные вещи и в MS таймстамп в дату кастовать - абсурд?

Цитата(Anark1 @ 5.9.2010, 22:06

)

Нужно определить функцию HOUR. Такое решение подразумевает определение UDF. Читайте про user-defined functions

Ты ведь сам привел ссылку с фунциями даты, не ужели не удосужился сам ее и прочитать. Тогда, быть может ты понял бы о чем толкует ТС.

Anark1, дружище, ты вот если сам не в теме, других с понтолыку не сбивай пожалуйста.

Автор: Anark1 5.9.2010, 23:12

Zloxa,
если у человека возникают такие вопросы когда он работает с timestamp с базой mysql, не знает функций и не знает как составить структуру запроса (иначе он, наверное, формулировал бы вопрос по-другому), то по-моему нет ничего плохого в моем желании ему помочь. Накидал человеку идей, ссылок, у него есть возможность почитать и разобраться.
Кстати, спасибо за замечания, сам учту это, хоть наверное и не скоро придется что-нибудь делать с mysql.

Цитата(Zloxa @ 5.9.2010, 22:58

)

MS таймстамп в дату кастовать - абсурд?

Буду рад прочитать пояснение.

Да и есть такой момент, что ты сам пока что ничего не предложил для автора. Или это у тебя такой стиль комментариев? Недаром же столько в "Флейме" набил smile

И манера общения интересная, способ самовыражения что-ли? smile

P.S.
Не забудь поставить "-3" этому моему посту smile

честное слово, всегда умиляли такие интернет-вояки.

Автор: Zloxa 5.9.2010, 23:34

Цитата(Anark1 @ 5.9.2010, 23:12

)

ты сам пока что ничего не предложил для автора

У меня не хватает компетенций помочь автору. Потому и молчу.
Однако у меня хватает компетенций понять что ты, как и я не в теме. И вижу что тебя не гнушает выдавать за цимес отрыжку так и не переваренного тобой продукта. Я не могу оценить способен ли ТС самостоятельно определить качество твоих советов, потому помогаю ему, комментируя и оценивая твои посты.

Цитата(Anark1 @ 5.9.2010, 23:12

)

Буду рад прочитать пояснение.

да да, обязательно почитай в MSDN про timestamp. Или мне потрудиться тебе сцылочку подогнать?

Автор: djamshud 6.9.2010, 12:15

бррр, неправильно понял ТС.

Автор: Zloxa 6.9.2010, 12:32

djamshud, ему надо сгруппироваться с гранулярностью по часу или минуты и посчитать среднее.

если бы вместо таймстампа было бы просто целое число, обозначающее количество секунд с начала чего та это было бы чтото вроде:

Код


select trunc(date/60/*/60*/) date, avg(value) from table where type = 1 group by trunc(date/60/*/60*/)

но я не знаю можно ли так работать с таймстампом.

Еще как вариант - привести к строке по формату вроде 'DDMMYYYYHHMM' и группироваться по нему.

Но что есть реальное тру - я хз. Функции работы с датами в разных диалектах столь специфичны.....

Автор: Anark1 6.9.2010, 14:30

Zloxa, товарищ, да ты тот еще шутник smile

. Может быть тебе стоит ник поменять на "оценивающий чужие комментарии", ладно или хотя бы подпись?
Начнем с того, что я нигде не писал, что варианты, которые я предлагаю гарантированно будут работать, ладно ты такой классный утер мне нос, но сам ты ничем не лучше. Хотя бы даже процитирую тебя