如何将时间列分为5分钟间隔和最大/最小值分别为SQL？

I have a database with a Datetime column containing intervals of +/- 30 seconds and a Value column containing random numbers between 10 and 100. My table looks like this:

我有一个数据库,其日期时间列包含+/- 30秒的间隔和一个包含10到100之间随机数的值列。我的表如下所示:

datetime               value
----------------------------
2016-05-04 20:47:20    12
2016-05-04 20:47:40    44
2016-05-04 20:48:30    56
2016-05-04 20:48:40    25
2016-05-04 20:49:30    92
2016-05-04 20:49:40    61
2016-05-04 20:50:00    79
2016-05-04 20:51:20    76
2016-05-04 20:51:30    10
2016-05-04 20:51:40    47
2016-05-04 20:52:40    23
2016-05-04 20:54:00    40
2016-05-04 20:54:10    18
2016-05-04 20:54:50    12
2016-05-04 20:56:00    55

What I want the following output:

我想要以下输出:

datetime               max_val    min_val
-----------------------------------------
2016-05-04 20:45:00    92         12
2016-05-04 20:50:00    79         10
2016-05-04 20:55:00    55         55

Before I can even continue getting the maximum value and the minimum value, I first have to GROUP the datetime column into 5 minute intervals. According to my research I came up with this:

在我甚至可以继续获得最大值和最小值之前,我首先必须将datetime列分组为5分钟。根据我的研究,我想出了这个:

SELECT
  time,
  value
FROM random_number_minute
GROUP BY
  UNIX_TIMESTAMP(time) DIV 300

Which actually GROUPS the datetime column into 5 minute intervals like this:

实际上将datetime列组合成5分钟的间隔,如下所示:

datetime
-------------------
2016-05-04 20:47:20
2016-05-04 20:50:00
2016-05-04 20:56:00

This comes very close as it takes the next closest datetime to, in this case, 20:45:00, 20:50:00, etc. I would like to rounddown the datetime to the nearest 5 minutes regardless of the seconds, for instance if the minutes are:

这是非常接近的,因为它需要下一个最接近的日期时间,在这种情况下,20:45:00,20:50:00等。我想将日期时间舍入到最接近的5分钟,无论秒,例如如果分钟是:

minutes    rounddown
--------------------
10         10
11         10
12         10
13         10
14         10
15         15
16         15
17         15
18         15
19         15
20         20

The time could be 14:59 and I would like to rounddown to 10:00. I also tried using this after hours of research:

时间可能是14:59,我想向下舍入到10:00。经过几个小时的研究,我也试过用这个:

SELECT
    time,
    time_rounded =
    dateadd(mi,(datepart(mi,dateadd(mi,1,time))/5)*5,dateadd(hh,datediff(hh,0,dateadd(mi,1,time)),0))

But sadly this did not work. I get this error:

但遗憾的是,这没有用。我收到此错误:

Incorrect parameter count in the call to native function 'datediff'

调用本机函数'datediff'时参数计数不正确

I tried this too:

我也试过这个:

SELECT  
    time, CASE  
          WHEN  DATEDIFF(second, DATEADD(second, DATEDIFF(second, 0, time_out) / 300 * 300, 0), time) >= 240
            THEN    DATEADD(second, (DATEDIFF(second, 0, time) / 300 * 300) + 300, 0)
            ELSE    DATEADD(second, DATEDIFF(second, 0, time) / 300 * 300, 0)
          END

Returning the same error.

返回相同的错误。

How can I do this? And after the datetime is grouped, how can I get the max and min value of the data grouping?

我怎样才能做到这一点?在对日期时间进行分组后,如何获取数据分组的最大值和最小值?

3 个解决方案

#1

Sorry if I'm repeating another answer. I'll delete if I am..

对不起,如果我重复另一个答案。如果我是,我会删除..

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(datetime)/300)*300) x
     , MIN(value) min_value
     , MAX(value) max_value 
  FROM my_table 
 GROUP 
    BY x;

#2

Use various date partition functions inside a GROUP BY.

在GROUP BY中使用各种日期分区功能。

Code:

SELECT from_unixtime(300 * round(unix_timestamp(r.datetime)/300)) AS 5datetime,
MAX(r.value) AS max_value, 
MIN(r.value) As min_value,
(SELECT r.value FROM random_number_minute ra WHERE ra.datetime = r.datetime order by ra.datetime desc LIMIT 1) as first_val
FROM random_number_minute r
GROUP BY UNIX_TIMESTAMP(r.datetime) DIV 300

Output:

5datetime               max_value   min_value   first_val
May, 04 2016 20:45:00   92          12          12
May, 04 2016 20:50:00   79          10          79
May, 04 2016 20:55:00   55          55          55

SQL Fiddle: http://sqlfiddle.com/#!9/e16b1/17/0

SQL小提琴:http://sqlfiddle.com/#!9 / e16b1 / 17/0

#3

SELECT 
    timestamp(concat(date(time), ' ', hour(time), ':', minute(time) div 5 * 5)) as floor_time,
    min(value),
    max(value)
FROM random_number_minute
GROUP BY date(time), hour(time), minute(time) div 5 * 5

http://sqlfiddle.com/#!9/91212f/5

#1