Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Группировка и просчет разницы с предыдущим значением / 20 сообщений из 20, страница 1 из 1
21.12.2017, 09:44
    #39573296
646826
646826
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Доброго времени суток.

Есть таблица item0001
---------------------------------------
time timestamp(3) - время записи
value double - значение
---------------------------------------

Хочу посчитать разницу value значения по отношению к предыдущему значению в поле value.
Пробую такой вариант

Код: sql
1.
2.
3.
4.
5.
SET @var = 0; 
SELECT time, value, value-@var, @var:=value 
FROM item0001 
GROUP BY day(time) 
ORDER BY time ASC



Но почему-то данная конструкция не работает.
Получаю неожиданный результат (прикрепил пример ответа).


Есть идеи как можно сделать?

PS: если что-то не понятно, спрашивайте, уточню

Заранее благодарен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 12:12
    #39573429
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Код: sql
1.
2.
3.
SELECT time, value-@tmp delta, @tmp:=value value
FROM (SELECT @tmp:=0) dummy, item0001
ORDER BY time ASC


?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 15:08
    #39573675
вадя
вадя
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Akina
Код: sql
1.
2.
3.
SELECT time, value-@tmp delta, @tmp:=value value
FROM (SELECT @tmp:=0) dummy, item0001
ORDER BY time ASC



?в данном варианте учитывается что данные в куче расположены в порядке возрастания времени.и выбор из кучи идет в порядке изменения времени. но если отвлечься от времени , то предыдущее значение из кучи не есть предыдущее по логике данных. Поэтому надо сначала отсортировать данные т.е.сделать выбор и сортировку в подзапросе , а потом уже value-@tmp delta, @tmp:=value value ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 15:14
    #39573684
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
вадяв данном варианте учитывается что данные в куче расположены в порядке возрастания времениА ORDER BY там типа для красоты?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 15:18
    #39573694
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
mysql> create table item0001 (time int, value int);
Query OK, 0 rows affected (0.34 sec)

mysql> insert into item0001 (time, value)
    -> select 1,1
    -> union all select 10,20
    -> union all select 3,8
    -> union all select 13,28
    -> union all select 7,15
    -> union all select 17,35
    -> union all select 5,12;
Query OK, 7 rows affected (0.11 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from item0001;
+------+-------+
| time | value |
+------+-------+
|    1 |     1 |
|   10 |    20 |
|    3 |     8 |
|   13 |    28 |
|    7 |    15 |
|   17 |    35 |
|    5 |    12 |
+------+-------+
7 rows in set (0.00 sec)

mysql> SELECT time, value-@tmp delta, @tmp:=value value
    -> FROM (SELECT @tmp:=0) dummy, item0001
    -> ORDER BY time ASC;
+------+-------+-------+
| time | delta | value |
+------+-------+-------+
|    1 |     1 |     1 |
|    3 |     7 |     8 |
|    5 |     4 |    12 |
|    7 |     3 |    15 |
|   10 |     5 |    20 |
|   13 |     8 |    28 |
|   17 |     7 |    35 |
+------+-------+-------+
7 rows in set (0.00 sec)

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 16:03
    #39573762
646826
646826
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Все супер, вот только упущенна группировка.
GROUP BY day(time).
Если попробовать использовать группировку то все по прежнему не работает

Akina
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
mysql> create table item0001 (time int, value int);
Query OK, 0 rows affected (0.34 sec)

mysql> insert into item0001 (time, value)
    -> select 1,1
    -> union all select 10,20
    -> union all select 3,8
    -> union all select 13,28
    -> union all select 7,15
    -> union all select 17,35
    -> union all select 5,12;
Query OK, 7 rows affected (0.11 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from item0001;
+------+-------+
| time | value |
+------+-------+
|    1 |     1 |
|   10 |    20 |
|    3 |     8 |
|   13 |    28 |
|    7 |    15 |
|   17 |    35 |
|    5 |    12 |
+------+-------+
7 rows in set (0.00 sec)

mysql> SELECT time, value-@tmp delta, @tmp:=value value
    -> FROM (SELECT @tmp:=0) dummy, item0001
    -> ORDER BY time ASC;
+------+-------+-------+
| time | delta | value |
+------+-------+-------+
|    1 |     1 |     1 |
|    3 |     7 |     8 |
|    5 |     4 |    12 |
|    7 |     3 |    15 |
|   10 |     5 |    20 |
|   13 |     8 |    28 |
|   17 |     7 |    35 |
+------+-------+-------+
7 rows in set (0.00 sec)

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 16:07
    #39573776
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
646826Все супер, вот только упущенна группировка.
GROUP BY day(time).НАХРЕНА тут группировка??? тем более что про неё и в исходном вопросе нет и намёка.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 16:17
    #39573792
646826
646826
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Akina,
в данном случае группировка нужно для получения значений по дням

Изначально я привел пример с группировкой и заголовке я об этом тоже указал
Код: sql
1.
2.
3.
4.
5.
6.
7.
SET @var = 0; 
SELECT time, value, value-@var, @var:=value 
FROM item0001 

GROUP BY day(time)

ORDER BY time ASC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 16:19
    #39573794
646826
646826
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Akina,

Я думаю тут можно попробовать посчитать сумму
например так
Код: sql
1.
2.
3.
4.
SELECT time, sum(value-@tmp) delta, @tmp:=value
FROM (SELECT @tmp:=0) dummy, item0001
GROUP BY day(time)
ORDER BY time ASC Limit 10



Пока не совсем уверен в получении правельных значений
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 16:22
    #39573804
646826
646826
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
646826Akina,

Я думаю тут можно попробовать посчитать сумму
например так
Код: sql
1.
2.
3.
4.
SELECT time, sum(value-@tmp) delta, @tmp:=value
FROM (SELECT @tmp:=0) dummy, item0001
GROUP BY day(time)
ORDER BY time ASC Limit 10



Пока не совсем уверен в получении правельных значений


Да я поспешил с выводами, нужно другое решение которое позволит получить значения при использовании группировки
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 16:40
    #39573826
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Если Вы группируете по дням - то какое из значений value для группы Вы намерены брать? их там несколько, и они, внезапно, разные...

Если определитесь - то сперва выполните группировку и отбор этих нужных значений, и потом полученный подзапрос используйте в качестве источника данных показанного мной запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 16:57
    #39573853
646826
646826
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Akina, спасибо.
Я наверно сам запутался.

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

Тоесть, таблица в значении Value содержит некий счетчик, который записывается, к примеру каждую минуту (это значение будет либо больше либо равно предыдущему) и мне нужно вытащить разницу этого счетчика за каждый день.

PS: попробую сообразить как это все отобразить в запросе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 17:47
    #39573913
вадя
вадя
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Akina,

да всё верно. но если есть не одна таблица, что может влиять на очерёдность выборки?
у меня есть примерно аналогичный запрос, состоящий из основной таблицы, и нескольких вьюх в которых эта таблица и ещё одна, во вьюхах задана сортировка по основной таблице. но в результирующем - сортировка нарушается, и соответственно правильного результата нет.. пришлось загнать в подзапрос в нем отсортировать , и только потом использовать переменные
в чем может быть причина? что сортировка применяется уже после того как вычислены переменные?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 18:38
    #39573966
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
вадячто сортировка применяется уже после того как вычислены переменные?Сначала ORDER BY, потом вычисление выходных выражений, затем LIMIT.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 18:41
    #39573970
вадя
вадя
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
AkinaСначала ORDER BY, потом вычисление выходных выражений, затем LIMIT.а что может влиять что такой порядок нарушается?
у меня сначала вычисления - потом сортировка
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 18:56
    #39573987
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
вадячто может влиять что такой порядок нарушается?Группировка. У тебя не выражения выходного набора, а выражения неявной групповой функции "взять случайную запись группы".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 19:11
    #39574003
вадя
вадя
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
AkinaГруппировка. У тебя не выражения выходного набора, а выражения неявной групповой функции "взять случайную запись группы".тогда в чем причина требования группировки в 5.7+ ?
в чем профит этого?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2017, 19:12
    #39574005
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
вадяв чем профит этого?Переносимость кода. Соответствие стандарту.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2017, 23:22
    #39574724
646826
646826
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
Akina,

Что-то получилось, пока работает и выглядит не идеально
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SELECT FLOOR(UNIX_TIMESTAMP(d.timed)) as time_sec, d.valued as value, "Day" as metric
FROM 
(
SELECT f.time as timed,  f.value - @tmp as valued, @tmp:=f.value as valuesss
FROM 
  (
    SELECT time as time, max(value) as value
		FROM `item0001` 
		WHERE time >= FROM_UNIXTIME(1513890000) AND time <= FROM_UNIXTIME(1513976399)
		GROUP BY hour(time)
		ORDER BY time ASC
  ) as f,
  (SELECT @tmp:=0) dummy
  ORDER BY time ASC
) as d
WHERE hour(d.timed) > '7' AND hour(d.timed) <= '23'
ORDER BY d.timed ASC



В общем задача состоит в том, чтоб сделать выборку для построения графика расхода электроэнергии без использования дополнительных таблиц в которых предлагают сохранять конкретные записи суточных и почасовых значений (источник - https://geektimes.ru/company/plarium/blog/288206/)
 


Если будут предложения как сформировать запрос более корректно, буду признателен.

PS: график формирую в графане
 
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2017, 23:45
    #39574733
646826
646826
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка и просчет разницы с предыдущим значением
К сожалению не нашел как тут редактировать пост.

Внес изменения в скрипт чтоб данные выборки соответствовали данным сохраненным в другой таблице (нижний график строится через запрос).

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Группировка и просчет разницы с предыдущим значением / 20 сообщений из 20, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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