ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выбор данных для построения 2 графиков
24 сообщений из 24, страница 1 из 1
Выбор данных для построения 2 графиков
    #38720882
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
Помогите, плиз, реализовать в MySQL следующее:
Есть 2 похожие таблицы: Дата_Время; Показатель
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Table1
DateTime;                 Val
-----------------------------
2014-08-01 12:45;      10
2014-08-01 12:51;      12
2014-08-01 12:58;      11

Table2
DateTime;                 Val
-----------------------------
2014-08-01 12:44;       8
2014-08-01 12:50;       9
2014-08-01 12:59;      10


Мне необходимо выбирать данные и строить график (два в одном), но так как метки времени не совпадают, при построении графиков есть проблемы.
В качестве решения хочу добавить таблицу с эталонными временными интервалами (с 00:00 по 23:30, через 30 мин) и строить графики опираясь на них, а значения из таблиц выбирать и рассчитывать среднее или скользящее среднее.
Хотел написать соответствующую хранимую функцию или с использованием вложенных подзапросов, но запутался в синтаксисе для mysql (привык к ms sql).
...
Рейтинг: 0 / 0
15.08.2014, 07:28:03
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38720913
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Код: sql
1.
2.
3.
4.
select t0.dt, t1.val, t2.val
from (select dt from table1 union select dt from table2) t0
left join table1 t1 on t0.dt=t1.dt
left join table2 t2 on t0.dt=t2.dt
...
Рейтинг: 0 / 0
15.08.2014, 08:55:18
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38720961
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
Спасибо, но это не подходит, т.к. сначала выводит все значения первой таблицы, потом второй.
А мне надо привязать к единой временной шкале и желательно взять усредненные значения.
В таблицах фактически лежат показания с датчиков температуры в разных точках их надо отобразить на общем графике для наглядности.
Как я и говорил - добавил таблицу 'timemaster', там лежат метки времени: 00:00, 00:30, 01:00, 01:30 и т.д.
Я пишу примерно следующее:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT  *
FROM (
		select  val id from timemaster
	) t
JOIN (
		select id, t from data
	)	d on TIME_FORMAT(t.id,'%H:%i') = TIME_FORMAT(d.id,'%H:%i')
	
WHERE d.ID > DATE_SUB(CURDATE(),INTERVAL 23 HOUR) 
AND d.ID < DATE_ADD(CURDATE(), INTERVAL 1 HOUR)


Естественно, что это выдает только те строки, где время полностью совпало. Поэтому я и хочу изменить условия так, что бы на каждую запись таблицы 'timemaster' выбиралось 3 ближайших значения таблицы 'data', а значение температуры при этом бралось среднее.
В ms sql я бы написал функцию и вставил бы в селект, тут у меня это не получается, скорее всего из за того что не могу разобраться в синтаксисе.
...
Рейтинг: 0 / 0
15.08.2014, 10:17:18
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38721086
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
AlexanderVSсначала выводит все значения первой таблицы, потом второй.Ну членовню-то не пори, да...

AlexanderVSжелательно взять усредненные значения.Тогда только дополнительная опорная таблица, возможно динамическая.
...
Рейтинг: 0 / 0
15.08.2014, 11:49:32
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38721314
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
Можно хоть краткий пример?
...
Рейтинг: 0 / 0
15.08.2014, 14:48:11
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38721388
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Нет, друг мой, это с тебя сначала DDL таблиц и DML из наполнения данными. А потом будут тебе примеры - причём вот прям на твоих же таблицах и данных.
Да, кириллицу нахрен, равно как и лишнюю шелужу в DDL-ках.
...
Рейтинг: 0 / 0
15.08.2014, 15:51:54
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38721628
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
В аттаче скриншот того, как выглядят графики сейчас и 3 таблички:
Код: sql
1.
2.
3.
timemaster - эталонное время
data - используем поля id, t2
cottage - используем поля id, t
...
Рейтинг: 0 / 0
15.08.2014, 22:26:18
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38721644
alex564657498765453
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alex564657498765453
Участник
лично меня учытель арыфметики учил так бить на интервалы

t1(dt,val) t2(dt,val)


SELECT IFNULL(r1.period,r2.period),ifnull(r1.val,'none'),ifnull(r2.val,'none')
FROM
(
select
time(floor((unix_timestamp(dt)-unix_timestamp(date(dt)))/60/30)*60*30) as 'period',avg(val)
from t1 group by `period`) r1

FULL OUTER JOIN

(
select
time(floor((unix_timestamp(dt)-unix_timestamp(date(dt)))/60/30)*60*30) as 'period',avg(val)
from t2 group by `period`) r2 ON(r1.period = r2.period)
...
Рейтинг: 0 / 0
15.08.2014, 23:23:31
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38721659
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
AlexanderVS,


1. в таблице еталонного времени сделайте интервалы:

d_start, d_end
00:00:00, 00:30:00
00:30:00, 01:00:00
01:00:00, 01:30:00
.........

2. сам запрос будет

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select
   t.d_start,
   t.d_end,
   ifnull(avg(t1.data_value),'пива нет')  data_avg
from 
  time_etalon t
  left join t1 on t1.td between t.d_start and t.d_end 
group by t.d_start



3. вторую таблицу в одном запросе можно подсоединить
несколькими способами. но проше сделать второй запрос.

4. "Сглаживаюшее" "бегущее" среднее по соседним точкам --
возможно на переменных -- довольно громоздко и точно
не в Пятницу вечером...
...
Рейтинг: 0 / 0
16.08.2014, 00:18:16
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38722185
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
javajdbc,
Спасибо, не плохой вариант.
Но все же если есть возможность, примерчик со скользящим средним мне б сильно помог :)
...
Рейтинг: 0 / 0
18.08.2014, 09:18:06
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38722895
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
Akina, Я выложил структуру и данные, можно надеяться на помощь?
...
Рейтинг: 0 / 0
18.08.2014, 19:14:39
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38722963
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
AlexanderVSAkina, Я выложил структуру и данные, можно надеяться на помощь?
Что касается задачи без опорной таблицы - достаточно адаптировать текст моего запроса, подставив соотв. имена полей и таблиц.
Код: sql
1.
2.
3.
4.
select t0.id, t1.t2, t2.t
from (select id from data union select id from cottage) t0
left join data t1 on t0.id=t1.id
left join cottage t2 on t0.id=t2.id;


Ну, если очень хочется, мжно добавить сортировку конечного набора по полю даты-времени
Код: sql
1.
2.
3.
4.
5.
select t0.id, t1.t2, t2.t
from (select id from data union select id from cottage) t0
left join data t1 on t0.id=t1.id
left join cottage t2 on t0.id=t2.id
order by t0.id;


Если добавить опорную таблицу времён и гарантировать её "30-минутную дискретность", то получится
Код: sql
1.
2.
3.
4.
5.
6.
select tm.val, avg(t1.t2), avg(t2.t)
from timemaster tm
left join (select id from data union select id from cottage) t0 on time(t0.id) between tm.val and tm.val + interval 30 minute
left join data t1 on t0.id=t1.id
left join cottage t2 on t0.id=t2.id
group by tm.val;


А для произвольной таблицы опорных времён сам в подзапросе трансформируешь её в таблицу вида start-end и перепишешь соотв. образом условия связывания.
...
Рейтинг: 0 / 0
18.08.2014, 21:15:49
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723037
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
AlexanderVSjavajdbc,
Спасибо, не плохой вариант.
Но все же если есть возможность, примерчик со скользящим средним мне б сильно помог :)


Ну например:

http://www.onlamp.com/pub/a/mysql/2007/04/12/emulating-analytic-aka-ranking-functions-with-mysql.html?page=3

Было еще пару статеек -- не смог быстро найти -- но в результате
легче поставить Оракле Експресс :-)

Идея такая: если надо бегушее окно из 3 записей,то
задается 3 переменные и в них FILO хороводом записывается
следуюшee значение и усредняется, потом на следуюшей записи
последнее выкидывается и усредняется 2 предпоследних и текушая
запись... хоровод переменных -- занудство и легко ошибится.
Если надо окно из 5 -- надо тянуть 5 переменных, итд.
...
Рейтинг: 0 / 0
19.08.2014, 03:38:27
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723049
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
Akina, javajdbc

Спасибо, буду разбираться.
...
Рейтинг: 0 / 0
19.08.2014, 06:46:11
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723051
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
javajdbcAlexanderVSjavajdbc,
Спасибо, не плохой вариант.
Но все же если есть возможность, примерчик со скользящим средним мне б сильно помог :)

Было еще пару статеек -- не смог быстро найти -- но в результате
легче поставить Оракле Експресс :-)


А с этим никак, потому как и web server и mysql крутятся на железяке QNAP-219P, и служат для сбора информации с домашних и дачных датчиков температуры, влажности, давления.
...
Рейтинг: 0 / 0
19.08.2014, 06:50:35
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723063
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
AlexanderVS,

вот тут попроше идея:
селф-жоинт с условиями связки в качестве окна.

Т.е. если нужно конкретно временое окно --
данная точка плюс-минус один час, то

Код: sql
1.
2.
3.
4.
5.
6.
select t.id, t.datex, avg(t2.value) avg_value
from t join t as t2 
on t2.datex between dateadd(t.datex, interval 1 hour) 
                         and  dateadd(t.datex, interval -1 hour)  
group by t.id
, t.datex  -- это чтоб ScareCrow не ругался



если надо по соседним точкам, то счанала надо
пронумеровать все точке последовательно, а потом
аналогичный СКЛ:

Код: sql
1.
2.
3.
4.
5.
6.
select t.id, t.datex, avg(t2.value) avg_value
from t join t as t2 
on t2.num between t.num - 2  
                         and  t.num + 2  
group by t.id
, t.datex  -- это чтоб ScareCrow не ругался



https://jonlabelle.com/snippets/view/sql/mysql-moving-average

На переменных будет, скорее всего, быстрее, но путанее.

Вариант -- сглаживать в сторед-процедуре

Вариант -- самый простой -- сглаживать группировкой по часам --
просто прямолинейным

Код: sql
1.
2.
3.
4.
5.
select 
  date_format(t.datex,'%Y-%m-%d-%h') YMDH, 
  avg(t.value) avg_value
from t
group by 1
...
Рейтинг: 0 / 0
19.08.2014, 07:38:54
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723070
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
javajdbcAlexanderVS,

Вариант -- самый простой -- сглаживать группировкой по часам --
просто прямолинейным



Это я уже попробовал, самое грубое, но и самое простое решение :)
...
Рейтинг: 0 / 0
19.08.2014, 07:53:03
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723075
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
javajdbcAlexanderVS,
Вариант -- сглаживать в сторед-процедуре

Вообще я хотел сглаживать в хранимой функции с построчным вызовом в селекте (так бы я поступил в ms sql) - не получается, запутался в синтаксисе.
Сглаживать в ХП: чем будет отличаться от всех перечисленных примеров, только тем что код будет оформлен в виде ХП и храниться на сервере? Ну и вызов из PHP соответственно будет мной.
...
Рейтинг: 0 / 0
19.08.2014, 08:02:35
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723076
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
AlexanderVSСглаживать в ХП: чем будет отличаться от всех перечисленных примеров, только тем что код будет оформлен в виде ХП и храниться на сервере?В коде ХП можно использовать "настоящие" переменные. И курсоры. По крайней мере читаться будет гораздо лучше, чем многоэтажный монстр, который вы получите, пытаясь всю логику запихнуть в один запрос.
...
Рейтинг: 0 / 0
19.08.2014, 08:05:30
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723144
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
AkinaAlexanderVSAkina, Я выложил структуру и данные, можно надеяться на помощь?
Что касается задачи без опорной таблицы - достаточно адаптировать текст моего запроса, подставив соотв. имена полей и таблиц.
Если добавить опорную таблицу времён и гарантировать её "30-минутную дискретность", то получится
Код: sql
1.
2.
3.
4.
5.
6.
select tm.val, avg(t1.t2), avg(t2.t)
from timemaster tm
left join (select id from data union select id from cottage) t0 on time(t0.id) between tm.val and tm.val + interval 30 minute
left join data t1 on t0.id=t1.id
left join cottage t2 on t0.id=t2.id
group by tm.val;


А для произвольной таблицы опорных времён сам в подзапросе трансформируешь её в таблицу вида start-end и перепишешь соотв. образом условия связывания.

Почему
Код: sql
1.
tm.val + interval 30 minute


дает значение NULL, поле tm.val - это TIME
...
Рейтинг: 0 / 0
19.08.2014, 10:07:38
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723158
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
Вот так работает
Код: sql
1.
ADDTIME(val, '00:30:00')
...
Рейтинг: 0 / 0
19.08.2014, 10:18:17
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723187
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
AlexanderVSПочему
Код: sql
1.
tm.val + interval 30 minute


дает значение NULL, поле tm.val - это TIME
У меня никаких NULL нет. На твоих, между прочим, данных, без какой-либо модификации. Я проверял, прежде чем постить. Опять же синтаксически с учётом твойих данных - этому NULL взяться неоткуда.
Вывод простой - ищи косяк у себя.
...
Рейтинг: 0 / 0
19.08.2014, 10:44:48
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723210
AlexanderVS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexanderVS
Участник
Akina, версия сервера не может влиять? У меня: Версия сервера: 5.1.36-log - Source distribution
...
Рейтинг: 0 / 0
19.08.2014, 11:03:24
| Ответить | Цитировать | Написать  
Выбор данных для построения 2 графиков
    #38723445
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Версия - нет, в тексте запроса нет ничего версионно-специфичного.
Настройки - возможно. Я пробовал на инстансе 5.6.14-log MySQL Community Server (GPL), установленном под Windows x86 как девелоперский, и все настройки оставлены по умолчанию. За исключением дефолтной кодировки (изменена на 1251) - но она не может так повлиять на результат.
...
Рейтинг: 0 / 0
19.08.2014, 13:37:12
| Ответить | Цитировать | Написать  
24 сообщений из 24, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выбор данных для построения 2 графиков
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&gotonew=1&tid=1834348]:
 0ms
get settings:
 6ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 46ms
get topic data:
 9ms
get first new msg:
 6ms
get forum data:
 2ms
get page messages:
 58ms
get tp. blocked users:
 1ms
others: 238ms
total:  382ms
созд. / загр.: 382ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]