Get the average of values in every specific epoch ranges in unix timestamp which returns -1 in specific condition in MySQL

Question

I have a MySQL table which has some records as follows:

unix_timestamp           value

1001                     2
1003                     3
1012                     1
1025                     5
1040                     0
1101                     3
1105                     4
1130                     0
...

I want to compute the average for every 10 epochs to see the following results:

unix_timestamp_range         avg_value

1001-1010                    2.5
1011-1020                    1
1021-1030                    5
1031-1040                    0
1041-1050                   -1
1051-1060                   -1
1061-1070                   -1
1071-1080                   -1
1081-1090                   -1
1091-1100                   -1
1101-1110                    3.5
1111-1120                   -1
1121-1130                    0
...

I saw some similar answers like enter link description here and enter link description here and enter link description here but these answers are not a solution for my specific question. How can I get the above results?

Did you see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Feb 29 '20 at 08:49
The most difficult part is to compute the intervals, once you get that, you use group by — Cristofor, Feb 29 '20 at 08:51
@Strawberry My table fields are too many, so I don't mentioned the table creation statement. — morteza ali ahmadi, Feb 29 '20 at 08:56
@Cristian what about -1 value when a range has no value? Is it easy? — morteza ali ahmadi, Feb 29 '20 at 08:57

score 1 · Accepted Answer · answered Feb 29 '20 at 08:57

1

The easiest way to do this is to use a calendar table. Consider this approach:

SELECT
    CONCAT(CAST(cal.ts AS CHAR(50)), '-', CAST(cal.ts + 9 AS CHAR(50))) AS unix_timestamp_range,
    CASE WHEN COUNT(t.value) > 0 THEN AVG(t.value) ELSE -1 END AS avg_value
FROM
(
    SELECT 1001 AS ts UNION ALL
    SELECT 1011 UNION ALL
    SELECT 1021 UNION ALL
    ...
) cal
LEFT JOIN yourTable t
    ON t.unix_timestamp BETWEEN cal.ts AND cal.ts + 9
GROUP BY
    cal.ts
ORDER BY
    cal.ts;

In practice, if you have the need to do this sort of query often, instead of the inline subquery labelled as cal above, you might want to have a full dedicated table representing all timestamp ranges.

answered Feb 29 '20 at 08:57

Tim Biegeleisen

502,043
27
286
360

Thanks, but my ranges number are not static and can be changed when my max unix_timestamp is large. – morteza ali ahmadi Feb 29 '20 at 09:05
Also this query does not produce -1 value – morteza ali ahmadi Feb 29 '20 at 09:08
You need to maintain a table containing all the ranges which might appear. And it does return -1 for ranges having no data. – Tim Biegeleisen Feb 29 '20 at 09:09
Why when I add a `where` on `t` (`where t.node in (3,4,6,8)`) clause before `Group by`, the ranges with -1 value disappears? – morteza ali ahmadi Feb 29 '20 at 10:43
What is the `WHERE` clause? – Tim Biegeleisen Feb 29 '20 at 10:45
1

Move that logic to the `ON` clause of the left join. That is, use this: `LEFT JOIN yourTable t ON t.unix_timestamp BETWEEN cal.ts AND cal.ts + 9 AND t.node in (3,4,6,8)` – Tim Biegeleisen Feb 29 '20 at 10:49
1

Thank you very much. That was the solution. – morteza ali ahmadi Feb 29 '20 at 10:51

Get the average of values in every specific epoch ranges in unix timestamp which returns -1 in specific condition in MySQL

1 Answers1