Time series data - question for daily aggregation query Announcing the arrival of Valued...
Would it be easier to apply for a UK visa if there is a host family to sponsor for you in going there?
What order were files/directories output in dir?
Crossing US/Canada Border for less than 24 hours
Belief In God or Knowledge Of God. Which is better?
Antipodal Land Area Calculation
What was the first language to use conditional keywords?
Most bit efficient text communication method?
How to unroll a parameter pack from right to left
How were pictures turned from film to a big picture in a picture frame before digital scanning?
Maximum summed subsequences with non-adjacent items
Why is there Net Work Done on a Pressure/Volume Cycle?
An adverb for when you're not exaggerating
How can I prevent/balance waiting and turtling as a response to cooldown mechanics
QGIS virtual layer functionality does not seem to support memory layers
What is the home of drows in Flanaess?
Central Vacuuming: Is it worth it, and how does it compare to normal vacuuming?
What would you call this weird metallic apparatus that allows you to lift people?
In musical terms, what properties are varied by the human voice to produce different words / syllables?
How to draw/optimize this graph with tikz
Random body shuffle every night—can we still function?
What initially awakened the Balrog?
AppleTVs create a chatty alternate WiFi network
What is "gratricide"?
Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?
Time series data - question for daily aggregation query
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)Time series data for ad platformOptimizing indexesWhy does IN (subquery) perform bad when = (subquery) is blazing fast?Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance LimitMySQL: Why queries with subqueries are much faster than single query?Mysql join not workingIf I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?How can I use MySQL variables in subqueries?Slow SELECT examining whole tableSpeeding Up Query on a Time Series Table. It's Slow to get (MAX - MIN) Aggregation Value
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
1
I'm trying to query a MySQL database with hourly timeseries data but I'm stuck with (daily) aggregation.
My raw data:
mysql> SELECT * FROM emoncms.custom_registers
-> WHERE timestamp >= '2016-08-24' AND timestamp < '2016-08-25';
+-----+------+---------------------+----------------+
| id | node | timestamp | register_value |
+-----+------+---------------------+----------------+
| 299 | 10 | 2016-08-24 00:00:03 | 869.458 |
| 300 | 10 | 2016-08-24 01:00:00 | 869.825 |
| 301 | 10 | 2016-08-24 02:00:03 | 870.202 |
| 302 | 10 | 2016-08-24 03:00:01 | 870.616 |
| 303 | 10 | 2016-08-24 04:00:05 | 870.977 |
| 304 | 10 | 2016-08-24 05:00:03 | 871.376 |
| 305 | 10 | 2016-08-24 06:00:01 | 871.759 |
| 306 | 10 | 2016-08-24 07:00:00 | 872.156 |
| 307 | 10 | 2016-08-24 08:00:04 | 872.629 |
| 308 | 10 | 2016-08-24 09:00:02 | 873.038 |
| 309 | 10 | 2016-08-24 10:00:01 | 873.512 |
| 310 | 10 | 2016-08-24 11:00:01 | 874.009 |
| 311 | 10 | 2016-08-24 12:00:01 | 874.429 |
| 312 | 10 | 2016-08-24 13:00:00 | 874.894 |
| 313 | 10 | 2016-08-24 14:00:04 | 875.331 |
| 314 | 10 | 2016-08-24 15:00:04 | 876.715 |
| 315 | 10 | 2016-08-24 16:00:04 | 877.249 |
| 316 | 10 | 2016-08-24 17:00:04 | 878.672 |
| 317 | 10 | 2016-08-24 18:00:04 | 879.19 |
| 318 | 10 | 2016-08-24 19:00:04 | 879.603 |
| 319 | 10 | 2016-08-24 20:00:04 | 880.027 |
| 320 | 10 | 2016-08-24 21:00:04 | 880.386 |
| 321 | 10 | 2016-08-24 22:00:05 | 880.787 |
| 322 | 10 | 2016-08-24 23:00:00 | 881.218 |
+-----+------+---------------------+----------------+
24 rows in set (0.00 sec)
When querying for interval differences:
mysql> SELECT t2.node,
-> ROUND(MIN(t2.register_value - t1.register_value),3) AS register_diff,
-> t1.timestamp AS start_interval, t2.timestamp AS stop_interval
-> FROM custom_registers AS t1 INNER JOIN custom_registers AS t2
-> on t1.timestamp < t2.timestamp WHERE t1.node = t2.node
-> AND t1.timestamp >= '2016-08-24 00:00:00'
-> AND t1.timestamp < '2016-08-25 00:00:00'
-> GROUP BY t1.id ORDER BY t1.timestamp;
+------+---------------+---------------------+---------------------+
| node | register_diff | start_interval | stop_interval |
+------+---------------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 0.371 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
+------+---------------+---------------------+---------------------+
24 rows in set (0.00 sec)
Which totals to 12.131. Unfortunately, my query for aggregation only sums 23 intervals, instead of 24. It misses the 0.371 value. What's wrong with my query?
mysql> SELECT t1.timestamp AS stop_interval, t2.register_min,
-> t2.register_max, (register_max - register_min) AS register_diff
-> FROM emoncms.custom_registers AS t1,
-> (SELECT timestamp, ROUND(MIN(register_value),3) AS register_min,
-> ROUND(MAX(register_value),3) AS register_max
-> FROM emoncms.custom_registers WHERE timestamp >= '2016-08-24'
-> AND timestamp <= ('2016-08-25'+ INTERVAL 1 MINUTE)
-> GROUP BY YEAR(timestamp), WEEK(timestamp), DAY(timestamp)
-> ) AS t2 WHERE t1.timestamp = t2.timestamp;
+---------------------+--------------+--------------+---------------+
| stop_interval | register_min | register_max | register_diff |
+---------------------+--------------+--------------+---------------+
| 2016-08-24 00:00:03 | 869.458 | 881.218 | 11.760 |
| 2016-08-25 00:00:01 | 881.589 | 881.589 | 0.000 |
+---------------------+--------------+--------------+---------------+
2 rows in set (0.00 sec)
mysql aggregate
edited Aug 25 '16 at 15:01
mustaccio
10.2k72241
asked Aug 25 '16 at 14:34
JMB240517JMB240517
62
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
1
I'm trying to query a MySQL database with hourly timeseries data but I'm stuck with (daily) aggregation.
My raw data:
mysql> SELECT * FROM emoncms.custom_registers
-> WHERE timestamp >= '2016-08-24' AND timestamp < '2016-08-25';
+-----+------+---------------------+----------------+
| id | node | timestamp | register_value |
+-----+------+---------------------+----------------+
| 299 | 10 | 2016-08-24 00:00:03 | 869.458 |
| 300 | 10 | 2016-08-24 01:00:00 | 869.825 |
| 301 | 10 | 2016-08-24 02:00:03 | 870.202 |
| 302 | 10 | 2016-08-24 03:00:01 | 870.616 |
| 303 | 10 | 2016-08-24 04:00:05 | 870.977 |
| 304 | 10 | 2016-08-24 05:00:03 | 871.376 |
| 305 | 10 | 2016-08-24 06:00:01 | 871.759 |
| 306 | 10 | 2016-08-24 07:00:00 | 872.156 |
| 307 | 10 | 2016-08-24 08:00:04 | 872.629 |
| 308 | 10 | 2016-08-24 09:00:02 | 873.038 |
| 309 | 10 | 2016-08-24 10:00:01 | 873.512 |
| 310 | 10 | 2016-08-24 11:00:01 | 874.009 |
| 311 | 10 | 2016-08-24 12:00:01 | 874.429 |
| 312 | 10 | 2016-08-24 13:00:00 | 874.894 |
| 313 | 10 | 2016-08-24 14:00:04 | 875.331 |
| 314 | 10 | 2016-08-24 15:00:04 | 876.715 |
| 315 | 10 | 2016-08-24 16:00:04 | 877.249 |
| 316 | 10 | 2016-08-24 17:00:04 | 878.672 |
| 317 | 10 | 2016-08-24 18:00:04 | 879.19 |
| 318 | 10 | 2016-08-24 19:00:04 | 879.603 |
| 319 | 10 | 2016-08-24 20:00:04 | 880.027 |
| 320 | 10 | 2016-08-24 21:00:04 | 880.386 |
| 321 | 10 | 2016-08-24 22:00:05 | 880.787 |
| 322 | 10 | 2016-08-24 23:00:00 | 881.218 |
+-----+------+---------------------+----------------+
24 rows in set (0.00 sec)
When querying for interval differences:
mysql> SELECT t2.node,
-> ROUND(MIN(t2.register_value - t1.register_value),3) AS register_diff,
-> t1.timestamp AS start_interval, t2.timestamp AS stop_interval
-> FROM custom_registers AS t1 INNER JOIN custom_registers AS t2
-> on t1.timestamp < t2.timestamp WHERE t1.node = t2.node
-> AND t1.timestamp >= '2016-08-24 00:00:00'
-> AND t1.timestamp < '2016-08-25 00:00:00'
-> GROUP BY t1.id ORDER BY t1.timestamp;
+------+---------------+---------------------+---------------------+
| node | register_diff | start_interval | stop_interval |
+------+---------------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 0.371 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
+------+---------------+---------------------+---------------------+
24 rows in set (0.00 sec)
Which totals to 12.131. Unfortunately, my query for aggregation only sums 23 intervals, instead of 24. It misses the 0.371 value. What's wrong with my query?
mysql> SELECT t1.timestamp AS stop_interval, t2.register_min,
-> t2.register_max, (register_max - register_min) AS register_diff
-> FROM emoncms.custom_registers AS t1,
-> (SELECT timestamp, ROUND(MIN(register_value),3) AS register_min,
-> ROUND(MAX(register_value),3) AS register_max
-> FROM emoncms.custom_registers WHERE timestamp >= '2016-08-24'
-> AND timestamp <= ('2016-08-25'+ INTERVAL 1 MINUTE)
-> GROUP BY YEAR(timestamp), WEEK(timestamp), DAY(timestamp)
-> ) AS t2 WHERE t1.timestamp = t2.timestamp;
+---------------------+--------------+--------------+---------------+
| stop_interval | register_min | register_max | register_diff |
+---------------------+--------------+--------------+---------------+
| 2016-08-24 00:00:03 | 869.458 | 881.218 | 11.760 |
| 2016-08-25 00:00:01 | 881.589 | 881.589 | 0.000 |
+---------------------+--------------+--------------+---------------+
2 rows in set (0.00 sec)
mysql aggregate
edited Aug 25 '16 at 15:01
mustaccio
10.2k72241
asked Aug 25 '16 at 14:34
JMB240517JMB240517
62
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Why are you grouping by t1.id?
– Lennart
Apr 29 '18 at 11:18
add a comment |
1
1
1
I'm trying to query a MySQL database with hourly timeseries data but I'm stuck with (daily) aggregation.
My raw data:
mysql> SELECT * FROM emoncms.custom_registers
-> WHERE timestamp >= '2016-08-24' AND timestamp < '2016-08-25';
+-----+------+---------------------+----------------+
| id | node | timestamp | register_value |
+-----+------+---------------------+----------------+
| 299 | 10 | 2016-08-24 00:00:03 | 869.458 |
| 300 | 10 | 2016-08-24 01:00:00 | 869.825 |
| 301 | 10 | 2016-08-24 02:00:03 | 870.202 |
| 302 | 10 | 2016-08-24 03:00:01 | 870.616 |
| 303 | 10 | 2016-08-24 04:00:05 | 870.977 |
| 304 | 10 | 2016-08-24 05:00:03 | 871.376 |
| 305 | 10 | 2016-08-24 06:00:01 | 871.759 |
| 306 | 10 | 2016-08-24 07:00:00 | 872.156 |
| 307 | 10 | 2016-08-24 08:00:04 | 872.629 |
| 308 | 10 | 2016-08-24 09:00:02 | 873.038 |
| 309 | 10 | 2016-08-24 10:00:01 | 873.512 |
| 310 | 10 | 2016-08-24 11:00:01 | 874.009 |
| 311 | 10 | 2016-08-24 12:00:01 | 874.429 |
| 312 | 10 | 2016-08-24 13:00:00 | 874.894 |
| 313 | 10 | 2016-08-24 14:00:04 | 875.331 |
| 314 | 10 | 2016-08-24 15:00:04 | 876.715 |
| 315 | 10 | 2016-08-24 16:00:04 | 877.249 |
| 316 | 10 | 2016-08-24 17:00:04 | 878.672 |
| 317 | 10 | 2016-08-24 18:00:04 | 879.19 |
| 318 | 10 | 2016-08-24 19:00:04 | 879.603 |
| 319 | 10 | 2016-08-24 20:00:04 | 880.027 |
| 320 | 10 | 2016-08-24 21:00:04 | 880.386 |
| 321 | 10 | 2016-08-24 22:00:05 | 880.787 |
| 322 | 10 | 2016-08-24 23:00:00 | 881.218 |
+-----+------+---------------------+----------------+
24 rows in set (0.00 sec)
When querying for interval differences:
mysql> SELECT t2.node,
-> ROUND(MIN(t2.register_value - t1.register_value),3) AS register_diff,
-> t1.timestamp AS start_interval, t2.timestamp AS stop_interval
-> FROM custom_registers AS t1 INNER JOIN custom_registers AS t2
-> on t1.timestamp < t2.timestamp WHERE t1.node = t2.node
-> AND t1.timestamp >= '2016-08-24 00:00:00'
-> AND t1.timestamp < '2016-08-25 00:00:00'
-> GROUP BY t1.id ORDER BY t1.timestamp;
+------+---------------+---------------------+---------------------+
| node | register_diff | start_interval | stop_interval |
+------+---------------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 0.371 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
+------+---------------+---------------------+---------------------+
24 rows in set (0.00 sec)
Which totals to 12.131. Unfortunately, my query for aggregation only sums 23 intervals, instead of 24. It misses the 0.371 value. What's wrong with my query?
mysql> SELECT t1.timestamp AS stop_interval, t2.register_min,
-> t2.register_max, (register_max - register_min) AS register_diff
-> FROM emoncms.custom_registers AS t1,
-> (SELECT timestamp, ROUND(MIN(register_value),3) AS register_min,
-> ROUND(MAX(register_value),3) AS register_max
-> FROM emoncms.custom_registers WHERE timestamp >= '2016-08-24'
-> AND timestamp <= ('2016-08-25'+ INTERVAL 1 MINUTE)
-> GROUP BY YEAR(timestamp), WEEK(timestamp), DAY(timestamp)
-> ) AS t2 WHERE t1.timestamp = t2.timestamp;
+---------------------+--------------+--------------+---------------+
| stop_interval | register_min | register_max | register_diff |
+---------------------+--------------+--------------+---------------+
| 2016-08-24 00:00:03 | 869.458 | 881.218 | 11.760 |
| 2016-08-25 00:00:01 | 881.589 | 881.589 | 0.000 |
+---------------------+--------------+--------------+---------------+
2 rows in set (0.00 sec)
mysql aggregate
edited Aug 25 '16 at 15:01
mustaccio
10.2k72241
asked Aug 25 '16 at 14:34
JMB240517JMB240517
62
I'm trying to query a MySQL database with hourly timeseries data but I'm stuck with (daily) aggregation.
My raw data:
mysql> SELECT * FROM emoncms.custom_registers
-> WHERE timestamp >= '2016-08-24' AND timestamp < '2016-08-25';
+-----+------+---------------------+----------------+
| id | node | timestamp | register_value |
+-----+------+---------------------+----------------+
| 299 | 10 | 2016-08-24 00:00:03 | 869.458 |
| 300 | 10 | 2016-08-24 01:00:00 | 869.825 |
| 301 | 10 | 2016-08-24 02:00:03 | 870.202 |
| 302 | 10 | 2016-08-24 03:00:01 | 870.616 |
| 303 | 10 | 2016-08-24 04:00:05 | 870.977 |
| 304 | 10 | 2016-08-24 05:00:03 | 871.376 |
| 305 | 10 | 2016-08-24 06:00:01 | 871.759 |
| 306 | 10 | 2016-08-24 07:00:00 | 872.156 |
| 307 | 10 | 2016-08-24 08:00:04 | 872.629 |
| 308 | 10 | 2016-08-24 09:00:02 | 873.038 |
| 309 | 10 | 2016-08-24 10:00:01 | 873.512 |
| 310 | 10 | 2016-08-24 11:00:01 | 874.009 |
| 311 | 10 | 2016-08-24 12:00:01 | 874.429 |
| 312 | 10 | 2016-08-24 13:00:00 | 874.894 |
| 313 | 10 | 2016-08-24 14:00:04 | 875.331 |
| 314 | 10 | 2016-08-24 15:00:04 | 876.715 |
| 315 | 10 | 2016-08-24 16:00:04 | 877.249 |
| 316 | 10 | 2016-08-24 17:00:04 | 878.672 |
| 317 | 10 | 2016-08-24 18:00:04 | 879.19 |
| 318 | 10 | 2016-08-24 19:00:04 | 879.603 |
| 319 | 10 | 2016-08-24 20:00:04 | 880.027 |
| 320 | 10 | 2016-08-24 21:00:04 | 880.386 |
| 321 | 10 | 2016-08-24 22:00:05 | 880.787 |
| 322 | 10 | 2016-08-24 23:00:00 | 881.218 |
+-----+------+---------------------+----------------+
24 rows in set (0.00 sec)
When querying for interval differences:
mysql> SELECT t2.node,
-> ROUND(MIN(t2.register_value - t1.register_value),3) AS register_diff,
-> t1.timestamp AS start_interval, t2.timestamp AS stop_interval
-> FROM custom_registers AS t1 INNER JOIN custom_registers AS t2
-> on t1.timestamp < t2.timestamp WHERE t1.node = t2.node
-> AND t1.timestamp >= '2016-08-24 00:00:00'
-> AND t1.timestamp < '2016-08-25 00:00:00'
-> GROUP BY t1.id ORDER BY t1.timestamp;
+------+---------------+---------------------+---------------------+
| node | register_diff | start_interval | stop_interval |
+------+---------------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 0.371 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
+------+---------------+---------------------+---------------------+
24 rows in set (0.00 sec)
Which totals to 12.131. Unfortunately, my query for aggregation only sums 23 intervals, instead of 24. It misses the 0.371 value. What's wrong with my query?
mysql> SELECT t1.timestamp AS stop_interval, t2.register_min,
-> t2.register_max, (register_max - register_min) AS register_diff
-> FROM emoncms.custom_registers AS t1,
-> (SELECT timestamp, ROUND(MIN(register_value),3) AS register_min,
-> ROUND(MAX(register_value),3) AS register_max
-> FROM emoncms.custom_registers WHERE timestamp >= '2016-08-24'
-> AND timestamp <= ('2016-08-25'+ INTERVAL 1 MINUTE)
-> GROUP BY YEAR(timestamp), WEEK(timestamp), DAY(timestamp)
-> ) AS t2 WHERE t1.timestamp = t2.timestamp;
+---------------------+--------------+--------------+---------------+
| stop_interval | register_min | register_max | register_diff |
+---------------------+--------------+--------------+---------------+
| 2016-08-24 00:00:03 | 869.458 | 881.218 | 11.760 |
| 2016-08-25 00:00:01 | 881.589 | 881.589 | 0.000 |
+---------------------+--------------+--------------+---------------+
2 rows in set (0.00 sec)
mysql aggregate
mysql aggregate
edited Aug 25 '16 at 15:01
mustaccio
10.2k72241
asked Aug 25 '16 at 14:34
JMB240517JMB240517
62
edited Aug 25 '16 at 15:01
mustaccio
10.2k72241
asked Aug 25 '16 at 14:34
JMB240517JMB240517
62
edited Aug 25 '16 at 15:01
mustaccio
10.2k72241
edited Aug 25 '16 at 15:01
mustaccio
10.2k72241
edited Aug 25 '16 at 15:01
mustaccio
10.2k72241
10.2k72241
asked Aug 25 '16 at 14:34
JMB240517JMB240517
62
asked Aug 25 '16 at 14:34
JMB240517JMB240517
62
asked Aug 25 '16 at 14:34
JMB240517JMB240517
62
62
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Why are you grouping by t1.id?
– Lennart
Apr 29 '18 at 11:18
add a comment |
Why are you grouping by t1.id?
– Lennart
Apr 29 '18 at 11:18
Why are you grouping by t1.id?
– Lennart
Apr 29 '18 at 11:18
Why are you grouping by t1.id?
– Lennart
Apr 29 '18 at 11:18
add a comment |
2 Answers
2
active
oldest
votes
0
Perhaps one of these?
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
AND timestamp < '2016-08-24' + INTERVAL 25 HOUR;
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
ORDER BY timestamp
LIMIT 25;
Note the use of 25, not 24.
edited Aug 26 '16 at 14:49
RLF
13k12440
answered Aug 26 '16 at 3:16
Rick JamesRick James
43.9k22360
add a comment |
0
Your query is invalid sql, it appears to work in MySQL (unless you add ONLY_FULL_GROUP_BY to @@sql_mode), but may produce in-deterministic results. If I understand your intention you want to get current and previous row per node. From what I understand window functions are soon to make it into MySQL, but are not yet there. It's a breeze using either LEAD or LAG to solve your problem, but you probably cant use these:
select node, timestamp ts1
, lead(register_value) over (partition by node order by timestamp) - register_value as diff
, lead(t1.timestamp) over (partition by node order by timestamp) ts2
from custom_registers
Without window functions first step is to find current and next row:
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp group by t1.node, t1.timestamp;
+------+---------------------+---------------------+
| node | ts1 | ts2 |
+------+---------------------+---------------------+
| 10 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 2016-08-24 23:00:00 | NULL |
+------+---------------------+---------------------+
24 rows in set (0.01 sec)
Note the null on the last row, it means there is no next row.
Now we can use this derived table and join it with the original table twice:
select x.node, abs(t3.register_value - t4.register_value) as diff
, x.ts1, x.ts2
from (
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node and x.ts2 = t4.timestamp
order by x.node, x.ts1;
This will produce 23 rows using you sample data, I'm not sure what you want to do with the last row since there is no next row there.
+------+-------+---------------------+---------------------+
| node | diff | ts1 | ts2 |
+------+-------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
+------+-------+---------------------+---------------------+
23 rows in set (0.01 sec)
If all you are interested in is date total, something like:
select node, dt, min_val, max_val, max_val-min_val as diff
from (
select node, date(timestamp) dt, min(register_value) as min_val, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
+------+------------+---------+---------+--------+
EDIT: I noticed now that there actually is a value for next day in your sample. Adding that:
insert into custom_registers values (323,10,'2016-08-25 00:00:01',881.588);
select x.node, abs(t3.register_value - t4.register_value) as diff, x.ts1, x.ts2
from (select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node
and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node
and x.ts2 = t4.timestamp
where ts1 >= '2016-08-24 00:00:00'
and ts1 < '2016-08-25 00:00:00'
order by x.node, x.ts1;
[...]
| 10 | 0.370 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
24 rows in set (0.01 sec)
Day aggregation becomes:
select node, dt, min_val, max_val, max_val-min_val as diff
from (select node, date(timestamp) dt, min(register_value) as min_val
, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
| 10 | 2016-08-25 | 881.588 | 881.588 | 0.000 |
+------+------------+---------+---------+--------+
2 rows in set (0.71 sec)
answered Apr 29 '18 at 13:41
LennartLennart
13.3k21243
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f147893%2ftime-series-data-question-for-daily-aggregation-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Perhaps one of these?
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
AND timestamp < '2016-08-24' + INTERVAL 25 HOUR;
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
ORDER BY timestamp
LIMIT 25;
Note the use of 25, not 24.
edited Aug 26 '16 at 14:49
RLF
13k12440
answered Aug 26 '16 at 3:16
Rick JamesRick James
43.9k22360
add a comment |
0
Perhaps one of these?
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
AND timestamp < '2016-08-24' + INTERVAL 25 HOUR;
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
ORDER BY timestamp
LIMIT 25;
Note the use of 25, not 24.
edited Aug 26 '16 at 14:49
RLF
13k12440
answered Aug 26 '16 at 3:16
Rick JamesRick James
43.9k22360
add a comment |
0
0
0
Perhaps one of these?
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
AND timestamp < '2016-08-24' + INTERVAL 25 HOUR;
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
ORDER BY timestamp
LIMIT 25;
Note the use of 25, not 24.
edited Aug 26 '16 at 14:49
RLF
13k12440
answered Aug 26 '16 at 3:16
Rick JamesRick James
43.9k22360
Perhaps one of these?
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
AND timestamp < '2016-08-24' + INTERVAL 25 HOUR;
SELECT * FROM emoncms.custom_registers
WHERE timestamp >= '2016-08-24'
ORDER BY timestamp
LIMIT 25;
Note the use of 25, not 24.
edited Aug 26 '16 at 14:49
RLF
13k12440
answered Aug 26 '16 at 3:16
Rick JamesRick James
43.9k22360
edited Aug 26 '16 at 14:49
RLF
13k12440
edited Aug 26 '16 at 14:49
RLF
13k12440
edited Aug 26 '16 at 14:49
RLF
13k12440
13k12440
answered Aug 26 '16 at 3:16
Rick JamesRick James
43.9k22360
answered Aug 26 '16 at 3:16
Rick JamesRick James
43.9k22360
answered Aug 26 '16 at 3:16
Rick JamesRick James
43.9k22360
43.9k22360
add a comment |
add a comment |
0
Your query is invalid sql, it appears to work in MySQL (unless you add ONLY_FULL_GROUP_BY to @@sql_mode), but may produce in-deterministic results. If I understand your intention you want to get current and previous row per node. From what I understand window functions are soon to make it into MySQL, but are not yet there. It's a breeze using either LEAD or LAG to solve your problem, but you probably cant use these:
select node, timestamp ts1
, lead(register_value) over (partition by node order by timestamp) - register_value as diff
, lead(t1.timestamp) over (partition by node order by timestamp) ts2
from custom_registers
Without window functions first step is to find current and next row:
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp group by t1.node, t1.timestamp;
+------+---------------------+---------------------+
| node | ts1 | ts2 |
+------+---------------------+---------------------+
| 10 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 2016-08-24 23:00:00 | NULL |
+------+---------------------+---------------------+
24 rows in set (0.01 sec)
Note the null on the last row, it means there is no next row.
Now we can use this derived table and join it with the original table twice:
select x.node, abs(t3.register_value - t4.register_value) as diff
, x.ts1, x.ts2
from (
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node and x.ts2 = t4.timestamp
order by x.node, x.ts1;
This will produce 23 rows using you sample data, I'm not sure what you want to do with the last row since there is no next row there.
+------+-------+---------------------+---------------------+
| node | diff | ts1 | ts2 |
+------+-------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
+------+-------+---------------------+---------------------+
23 rows in set (0.01 sec)
If all you are interested in is date total, something like:
select node, dt, min_val, max_val, max_val-min_val as diff
from (
select node, date(timestamp) dt, min(register_value) as min_val, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
+------+------------+---------+---------+--------+
EDIT: I noticed now that there actually is a value for next day in your sample. Adding that:
insert into custom_registers values (323,10,'2016-08-25 00:00:01',881.588);
select x.node, abs(t3.register_value - t4.register_value) as diff, x.ts1, x.ts2
from (select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node
and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node
and x.ts2 = t4.timestamp
where ts1 >= '2016-08-24 00:00:00'
and ts1 < '2016-08-25 00:00:00'
order by x.node, x.ts1;
[...]
| 10 | 0.370 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
24 rows in set (0.01 sec)
Day aggregation becomes:
select node, dt, min_val, max_val, max_val-min_val as diff
from (select node, date(timestamp) dt, min(register_value) as min_val
, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
| 10 | 2016-08-25 | 881.588 | 881.588 | 0.000 |
+------+------------+---------+---------+--------+
2 rows in set (0.71 sec)
answered Apr 29 '18 at 13:41
LennartLennart
13.3k21243
add a comment |
0
Your query is invalid sql, it appears to work in MySQL (unless you add ONLY_FULL_GROUP_BY to @@sql_mode), but may produce in-deterministic results. If I understand your intention you want to get current and previous row per node. From what I understand window functions are soon to make it into MySQL, but are not yet there. It's a breeze using either LEAD or LAG to solve your problem, but you probably cant use these:
select node, timestamp ts1
, lead(register_value) over (partition by node order by timestamp) - register_value as diff
, lead(t1.timestamp) over (partition by node order by timestamp) ts2
from custom_registers
Without window functions first step is to find current and next row:
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp group by t1.node, t1.timestamp;
+------+---------------------+---------------------+
| node | ts1 | ts2 |
+------+---------------------+---------------------+
| 10 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 2016-08-24 23:00:00 | NULL |
+------+---------------------+---------------------+
24 rows in set (0.01 sec)
Note the null on the last row, it means there is no next row.
Now we can use this derived table and join it with the original table twice:
select x.node, abs(t3.register_value - t4.register_value) as diff
, x.ts1, x.ts2
from (
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node and x.ts2 = t4.timestamp
order by x.node, x.ts1;
This will produce 23 rows using you sample data, I'm not sure what you want to do with the last row since there is no next row there.
+------+-------+---------------------+---------------------+
| node | diff | ts1 | ts2 |
+------+-------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
+------+-------+---------------------+---------------------+
23 rows in set (0.01 sec)
If all you are interested in is date total, something like:
select node, dt, min_val, max_val, max_val-min_val as diff
from (
select node, date(timestamp) dt, min(register_value) as min_val, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
+------+------------+---------+---------+--------+
EDIT: I noticed now that there actually is a value for next day in your sample. Adding that:
insert into custom_registers values (323,10,'2016-08-25 00:00:01',881.588);
select x.node, abs(t3.register_value - t4.register_value) as diff, x.ts1, x.ts2
from (select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node
and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node
and x.ts2 = t4.timestamp
where ts1 >= '2016-08-24 00:00:00'
and ts1 < '2016-08-25 00:00:00'
order by x.node, x.ts1;
[...]
| 10 | 0.370 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
24 rows in set (0.01 sec)
Day aggregation becomes:
select node, dt, min_val, max_val, max_val-min_val as diff
from (select node, date(timestamp) dt, min(register_value) as min_val
, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
| 10 | 2016-08-25 | 881.588 | 881.588 | 0.000 |
+------+------------+---------+---------+--------+
2 rows in set (0.71 sec)
answered Apr 29 '18 at 13:41
LennartLennart
13.3k21243
add a comment |
0
0
0
Your query is invalid sql, it appears to work in MySQL (unless you add ONLY_FULL_GROUP_BY to @@sql_mode), but may produce in-deterministic results. If I understand your intention you want to get current and previous row per node. From what I understand window functions are soon to make it into MySQL, but are not yet there. It's a breeze using either LEAD or LAG to solve your problem, but you probably cant use these:
select node, timestamp ts1
, lead(register_value) over (partition by node order by timestamp) - register_value as diff
, lead(t1.timestamp) over (partition by node order by timestamp) ts2
from custom_registers
Without window functions first step is to find current and next row:
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp group by t1.node, t1.timestamp;
+------+---------------------+---------------------+
| node | ts1 | ts2 |
+------+---------------------+---------------------+
| 10 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 2016-08-24 23:00:00 | NULL |
+------+---------------------+---------------------+
24 rows in set (0.01 sec)
Note the null on the last row, it means there is no next row.
Now we can use this derived table and join it with the original table twice:
select x.node, abs(t3.register_value - t4.register_value) as diff
, x.ts1, x.ts2
from (
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node and x.ts2 = t4.timestamp
order by x.node, x.ts1;
This will produce 23 rows using you sample data, I'm not sure what you want to do with the last row since there is no next row there.
+------+-------+---------------------+---------------------+
| node | diff | ts1 | ts2 |
+------+-------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
+------+-------+---------------------+---------------------+
23 rows in set (0.01 sec)
If all you are interested in is date total, something like:
select node, dt, min_val, max_val, max_val-min_val as diff
from (
select node, date(timestamp) dt, min(register_value) as min_val, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
+------+------------+---------+---------+--------+
EDIT: I noticed now that there actually is a value for next day in your sample. Adding that:
insert into custom_registers values (323,10,'2016-08-25 00:00:01',881.588);
select x.node, abs(t3.register_value - t4.register_value) as diff, x.ts1, x.ts2
from (select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node
and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node
and x.ts2 = t4.timestamp
where ts1 >= '2016-08-24 00:00:00'
and ts1 < '2016-08-25 00:00:00'
order by x.node, x.ts1;
[...]
| 10 | 0.370 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
24 rows in set (0.01 sec)
Day aggregation becomes:
select node, dt, min_val, max_val, max_val-min_val as diff
from (select node, date(timestamp) dt, min(register_value) as min_val
, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
| 10 | 2016-08-25 | 881.588 | 881.588 | 0.000 |
+------+------------+---------+---------+--------+
2 rows in set (0.71 sec)
answered Apr 29 '18 at 13:41
LennartLennart
13.3k21243
Your query is invalid sql, it appears to work in MySQL (unless you add ONLY_FULL_GROUP_BY to @@sql_mode), but may produce in-deterministic results. If I understand your intention you want to get current and previous row per node. From what I understand window functions are soon to make it into MySQL, but are not yet there. It's a breeze using either LEAD or LAG to solve your problem, but you probably cant use these:
select node, timestamp ts1
, lead(register_value) over (partition by node order by timestamp) - register_value as diff
, lead(t1.timestamp) over (partition by node order by timestamp) ts2
from custom_registers
Without window functions first step is to find current and next row:
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp group by t1.node, t1.timestamp;
+------+---------------------+---------------------+
| node | ts1 | ts2 |
+------+---------------------+---------------------+
| 10 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
| 10 | 2016-08-24 23:00:00 | NULL |
+------+---------------------+---------------------+
24 rows in set (0.01 sec)
Note the null on the last row, it means there is no next row.
Now we can use this derived table and join it with the original table twice:
select x.node, abs(t3.register_value - t4.register_value) as diff
, x.ts1, x.ts2
from (
select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node and x.ts2 = t4.timestamp
order by x.node, x.ts1;
This will produce 23 rows using you sample data, I'm not sure what you want to do with the last row since there is no next row there.
+------+-------+---------------------+---------------------+
| node | diff | ts1 | ts2 |
+------+-------+---------------------+---------------------+
| 10 | 0.367 | 2016-08-24 00:00:03 | 2016-08-24 01:00:00 |
| 10 | 0.377 | 2016-08-24 01:00:00 | 2016-08-24 02:00:03 |
| 10 | 0.414 | 2016-08-24 02:00:03 | 2016-08-24 03:00:01 |
| 10 | 0.361 | 2016-08-24 03:00:01 | 2016-08-24 04:00:05 |
| 10 | 0.399 | 2016-08-24 04:00:05 | 2016-08-24 05:00:03 |
| 10 | 0.383 | 2016-08-24 05:00:03 | 2016-08-24 06:00:01 |
| 10 | 0.397 | 2016-08-24 06:00:01 | 2016-08-24 07:00:00 |
| 10 | 0.473 | 2016-08-24 07:00:00 | 2016-08-24 08:00:04 |
| 10 | 0.409 | 2016-08-24 08:00:04 | 2016-08-24 09:00:02 |
| 10 | 0.474 | 2016-08-24 09:00:02 | 2016-08-24 10:00:01 |
| 10 | 0.497 | 2016-08-24 10:00:01 | 2016-08-24 11:00:01 |
| 10 | 0.420 | 2016-08-24 11:00:01 | 2016-08-24 12:00:01 |
| 10 | 0.465 | 2016-08-24 12:00:01 | 2016-08-24 13:00:00 |
| 10 | 0.437 | 2016-08-24 13:00:00 | 2016-08-24 14:00:04 |
| 10 | 1.384 | 2016-08-24 14:00:04 | 2016-08-24 15:00:04 |
| 10 | 0.534 | 2016-08-24 15:00:04 | 2016-08-24 16:00:04 |
| 10 | 1.423 | 2016-08-24 16:00:04 | 2016-08-24 17:00:04 |
| 10 | 0.518 | 2016-08-24 17:00:04 | 2016-08-24 18:00:04 |
| 10 | 0.413 | 2016-08-24 18:00:04 | 2016-08-24 19:00:04 |
| 10 | 0.424 | 2016-08-24 19:00:04 | 2016-08-24 20:00:04 |
| 10 | 0.359 | 2016-08-24 20:00:04 | 2016-08-24 21:00:04 |
| 10 | 0.401 | 2016-08-24 21:00:04 | 2016-08-24 22:00:05 |
| 10 | 0.431 | 2016-08-24 22:00:05 | 2016-08-24 23:00:00 |
+------+-------+---------------------+---------------------+
23 rows in set (0.01 sec)
If all you are interested in is date total, something like:
select node, dt, min_val, max_val, max_val-min_val as diff
from (
select node, date(timestamp) dt, min(register_value) as min_val, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
+------+------------+---------+---------+--------+
EDIT: I noticed now that there actually is a value for next day in your sample. Adding that:
insert into custom_registers values (323,10,'2016-08-25 00:00:01',881.588);
select x.node, abs(t3.register_value - t4.register_value) as diff, x.ts1, x.ts2
from (select t1.node, t1.timestamp ts1, min(t2.timestamp) ts2
from custom_registers t1
left join custom_registers t2
on t1.node = t2.node
and t1.timestamp < t2.timestamp
group by t1.node, t1.timestamp
) as x
join custom_registers t3
on x.node = t3.node
and x.ts1 = t3.timestamp
join custom_registers t4
on x.node = t4.node
and x.ts2 = t4.timestamp
where ts1 >= '2016-08-24 00:00:00'
and ts1 < '2016-08-25 00:00:00'
order by x.node, x.ts1;
[...]
| 10 | 0.370 | 2016-08-24 23:00:00 | 2016-08-25 00:00:01 |
24 rows in set (0.01 sec)
Day aggregation becomes:
select node, dt, min_val, max_val, max_val-min_val as diff
from (select node, date(timestamp) dt, min(register_value) as min_val
, max(register_value) as max_val
from custom_registers
group by node, date(timestamp)
) as t;
+------+------------+---------+---------+--------+
| node | dt | min_val | max_val | diff |
+------+------------+---------+---------+--------+
| 10 | 2016-08-24 | 869.458 | 881.218 | 11.760 |
| 10 | 2016-08-25 | 881.588 | 881.588 | 0.000 |
+------+------------+---------+---------+--------+
2 rows in set (0.71 sec)
answered Apr 29 '18 at 13:41
LennartLennart
13.3k21243
edited Apr 30 '18 at 3:58
answered Apr 29 '18 at 13:41
LennartLennart
13.3k21243
answered Apr 29 '18 at 13:41
LennartLennart
13.3k21243
answered Apr 29 '18 at 13:41
LennartLennart
13.3k21243
13.3k21243
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f147893%2ftime-series-data-question-for-daily-aggregation-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why are you grouping by t1.id?
– Lennart
Apr 29 '18 at 11:18