mysql: difference between values in one column

101
February 28, 2018, at 11:22 AM

this board helped me a few times in the past.

My challange: I want to get the difference between the values within one column.

The table looks like this:

id  |  channel_id  |  timestamp   |  value
4515|    7         |1519771680000 |  7777
4518|    8         |1519772160000 |  6666
4520|    7         |1519772340000 |  8888
  • id: Internal ID from Datasource. In some cases it's ordered, in other cases not. We cannot thrust this order.
  • channel_id: Different data sources.
  • timestamp: unix timestamp.
  • value: measured value.

What I want to do:

Filter (e.g. channel_id = 7). Calculate the difference between one timestamp and the next one. In this example: 8888-7777

I found an solution on another database but I cannot transfer it to mysql as the windows functions are very limited. Has somebody of you an idea how to get a solution which can be used in select statements?

Thx and KR Holger

Answer 1

You can get the two rows to compare (ie subtract) by joining the table to itself:

SELECT
    a.channel_id,
    a.timestamp,
    b.timestamp,
    a.value - b.value as `difference`
FROM table a
JOIN table b
ON a.channel_id = b.channel_id and a.timestamp <> b.timestamp and a.value > b.value
GROUP BY a.channel_id
ORDER BY a.channel_id
Answer 2

You can use a "correlated subquery" for this as seen below (also see this demo). When MySQL implements window functions such a LEAD() you could use those instead.

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`id` int, `channel_id` int, `timestamp` bigint, `value` int)
;
INSERT INTO Table1
    (`id`, `channel_id`, `timestamp`, `value`)
VALUES
    (4515, 7, 1519771680000, 7777),
    (4518, 8, 1519772160000, 6666),
    (4520, 7, 1519772340000, 8888)
;

Query 1:

select
      id
    , channel_id
    , timestamp
    , value
    , nxt_value
    , nxt_value - value as diff
from (
    select
          t1.id
        , t1.channel_id
        , t1.timestamp
        , t1.value
        , (select value from table1 as t2 
           where t2.channel_id = t1.channel_id
           and t2.timestamp > t1.timestamp
           order by t2.timestamp
           limit 1) nxt_value
    from table1 as t1
    ) as d

Results:

|   id | channel_id |     timestamp | value | nxt_value |   diff |
|------|------------|---------------|-------|-----------|--------|
| 4515 |          7 | 1519771680000 |  7777 |      8888 |   1111 |
| 4518 |          8 | 1519772160000 |  6666 |    (null) | (null) |
| 4520 |          7 | 1519772340000 |  8888 |    (null) | (null) |
READ ALSO
Why is this Union Query not working

Why is this Union Query not working

I am trying to access multiple tables from mySQL via my php in wordpressMy code looks like this

62
explode results then implode php

explode results then implode php

I'm getting the contents of a column which has contents stored as 3,2 then I'm using the explode function to separate themThe values separated are then used to query another table to bring names

107
Delete a row from the table

Delete a row from the table

I created a schema in mysql MyISAM with the help of the followingsql:

75