Clever Geek Handbook

Group by hour in mysql, but include the first value from hour + 1 as well

Let's say you have data like this:

  time  value
  10:00   5
  10:15   12
  10:30   15
  10:45   27
  11:00   29

And a request like this:

SELECT MAX(value) - MIN(value), HOUR(time) FROM mytable GROUP BY HOUR(time);

You'll get:

  value  time
    22    10

But I will need to include the value at 11:00, so the result will be 24 (29-5), not 22. Is there a way to do this in SQL or do I have no choice but to do it manually at the code level (so , no grouping, just extract data and manually subtract).

+3


source to share


3 answers


Depending on how consistent your data is, you can do this with self-attach, for example:

SELECT HOUR(a.`time`) AS grouper,
GREATEST(MAX(a.value),IFNULL(MIN(b.value),0)) - MIN(a.value) AS diff
FROM mytable a
LEFT JOIN mytable b ON IF(HOUR(a.time) <= 23, HOUR(a.time)+1, 0) = HOUR(b.time)
GROUP BY grouper


LEFT JOIN

in the same table allows you to get the value of the next hour for comparison.

http://sqlfiddle.com/#!9/fe72a/16

+3


source


Try:

SELECT hr, 
       max( value ) - min( value )
FROM mytable m
JOIN (
  SELECT  HOUR(`time`) hr, 
          cast(Date_Format(min(`time`),'%Y-%m-%d %H:00:00') as time) mt
  FROM mytable 
  GROUP BY HOUR(`time`)
) q
ON m.`time` >= q.mt 
AND m.`time` <= q.mt + interval 1 hour 
GROUP BY hr


Demo: http://sqlfiddle.com/#!9/012c7/9

+1


source


Since you want 11:00 to be part of HOUR = 10 and HOUR = 11, I would use this UNION ALL query:

SELECT `time`, MAX(value) - MIN(value)
FROM (
  SELECT
    HOUR(`time`) AS `time`, value
  FROM
    mytable

  UNION ALL

  SELECT
    CASE WHEN HOUR(`time`) > 0 THEN HOUR(`time`)-1 ELSE 23 END, value
  FROM
    mytable
  WHERE
    MINUTE(`time`)=0
) s
GROUP BY `time`

If you have a lot of data, I would try to rethink the structure of the database, as queries like this cannot be optimized.

0


source






More articles:


All Articles