1

Topic: Grouping and the miscalculation of a difference with the previous value

Kind time of days.
There is a table item0001
---------------------------------------
time timestamp (3) - write time
value double - value
---------------------------------------
I want to count a difference value values in relation to the previous value in the field value.
I try such variant

SET @var = 0;
SELECT time, value, value-@var, @var: = value
FROM item0001
GROUP BY day (time)
ORDER BY time ASC

But for some reason given construction does not work.
I receive unexpected result (attached an answer example).
[img=http://softtime.ru/forum/files/97637-20171221005019.PNG]
There are ideas as it is possible to make?
PS: if something is not clear, ask, I will specify
Thankful in advance.

2

Re: Grouping and the miscalculation of a difference with the previous value

SELECT time, value-@tmp delta, @tmp: = value value
FROM (SELECT @tmp: = 0) dummy, item0001
ORDER BY time ASC

?

3

Re: Grouping and the miscalculation of a difference with the previous value

wrote:

in the given variant it is considered that the data in a heap is allocated in ascending order time

And ORDER BY there type for appearance?

4

Re: Grouping and the miscalculation of a difference with the previous value

[spoiler]

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)

[/spoiler]

5

Re: Grouping and the miscalculation of a difference with the previous value

All super, here only  grouping.
GROUP BY day (time).
If to try to use grouping that all on the former does not work

Akina wrote:

[spoiler]

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)

[/spoiler]

6

Re: Grouping and the miscalculation of a difference with the previous value

646826 wrote:

All super, here only  grouping.
GROUP BY day (time).

  here grouping??? As about it and in an initial question there is no also a hint.

7

Re: Grouping and the miscalculation of a difference with the previous value

Akina;
In this case grouping it is necessary for obtaining of values on days
Initially I gave an example with grouping and title I about it too specified

SET @var = 0;
SELECT time, value, value-@var, @var: = value
FROM item0001
GROUP BY day (time)
ORDER BY time ASC

8

Re: Grouping and the miscalculation of a difference with the previous value

Akina;
I think here it is possible to try to count the total
For example so

SELECT time, sum (value-@tmp) delta, @tmp: = value
FROM (SELECT @tmp: = 0) dummy, item0001
GROUP BY day (time)
ORDER BY time ASC Limit 10

It is not absolutely assured yet of obtaining  values

9

Re: Grouping and the miscalculation of a difference with the previous value

646826 wrote:

Akina;
I think here it is possible to try to count the total
For example so

SELECT time, sum (value-@tmp) delta, @tmp: = value
FROM (SELECT @tmp: = 0) dummy, item0001
GROUP BY day (time)
ORDER BY time ASC Limit 10

It is not absolutely assured yet of obtaining  values

Yes I hastened with outputs, other decision which is necessary allows to receive values at grouping usage

10

Re: Grouping and the miscalculation of a difference with the previous value

If you group in days - that what of values value for group you intend to take? Them there a little, and they, suddenly, the different...
If be defined - that at first fulfill grouping and selection of these necessary values, and then received subquery use as data source of the request shown by me.

11

Re: Grouping and the miscalculation of a difference with the previous value

Akina, thanks.
I likely itself was tangled.
It turns out that as a result, I should receive number which will display a difference between  fixed in the end of day.
, the table contains a certain counter which registers in value Value, for example every minute (this value will be or more or equally previous) and I need to pull out a difference of this counter per every day.
PS: I will try to think as it is all to display in request.

12

Re: Grouping and the miscalculation of a difference with the previous value

wrote:

that sorting is applied already after variables are calculated?

At first ORDER BY, then calculation of output expressions, then LIMIT.

13

Re: Grouping and the miscalculation of a difference with the previous value

wrote:

that can influence that such order is broken?

Grouping. From you not expressions of an output dial-up, and expression of implicit group function "to take casual record of group".

14

Re: Grouping and the miscalculation of a difference with the previous value

wrote:

in what a profit of it?

Code portability. Correspondence to the standard.

15

Re: Grouping and the miscalculation of a difference with the previous value

Akina;
Something turned out, while works and looks not ideally

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

In general the task consists in making sampling for creation of the schedule of the expenditure of the electric power without usage of additional tables in which suggest to save specific records of daily and hourly values (a source - https://geektimes.ru/company/plarium/blog/288206/)
[spoiler] [img=https://habrastorage.org/files/0fc/e9c/c0c/0fce9cc0c4cd43fbbf7ac36745309426.png] [/spoiler]
If there will be sentences how to generate request more correctly, will be grateful.
PS: the schedule I form in
[spoiler] [img=https://discourse-cdn-sjc1.com/standard3/uploads/grafana/original/2X/7/7e805e448f59de9494167505ec7cb74246187b23.png] [/spoiler]

16

Re: Grouping and the miscalculation of a difference with the previous value

Unfortunately did not find as here to edit a post.
Made changes to a script that the given samplings corresponded to the data saved in other table (the lower schedule is under construction through request).
[img=https://community.grafana.com/uploads/grafana/original/2X/e/e29e94c6345929f8a4e5349b14ce95d12316b2f9.png]