mysql - Min、Max、Average 和 Count 分组到 15 分钟的 bin 中

Question

我有一个 MySQL 查询，由另一个用户在另一个线程中提供，它以以下格式生成一天的 ANPR 旅程时间结果列表:

Plate   Date        Starttime   Endtime    Journey Time
YF10PXE 2014-06-18  10:00:32    10:03:22    00:02:50
KG55GOA 2014-06-18  10:00:39    10:03:25    00:02:46
N380LGN 2014-06-18  10:00:43    10:03:44    00:03:01
X557EFB 2014-06-18  10:01:05    10:03:31    00:02:26
AJ61AOS 2014-06-18  10:01:32    10:41:09    00:39:37
BG58ASO 2014-06-18  10:01:42    10:06:09    00:04:27
HT56ENL 2014-06-18  10:02:21    10:07:27    00:05:06
X449LGS 2014-06-18  10:02:51    11:51:42    01:48:51
KR07DWW 2014-06-18  10:03:19    11:05:45    01:02:26
NA59FKE 2014-06-18  10:03:35    10:44:17    00:40:42
HV13MXY 2014-06-18  10:03:54    10:52:12    00:48:18
HS03FUW 2014-06-18  10:04:35    10:06:19    00:01:44
HX53FKP 2014-06-18  10:06:09    10:06:47    00:00:38
HK11NJU 2014-06-18  10:07:25    10:08:06    00:00:41
HN12OAV 2014-06-18  10:07:56    10:08:51    00:00:55
MM08AZB 2014-06-18  10:08:10    10:08:53    00:00:43
BW57ENK 2014-06-18  10:08:35    10:09:19    00:00:44
AE55LHR 2014-06-18  10:08:35    10:09:18    00:00:43
RV03HMA 2014-06-18  10:09:13    11:07:45    00:58:32
JPO6JEM 2014-06-18  10:10:48    10:11:25    00:00:37
BV62WMP 2014-06-18  10:11:00    10:11:46    00:00:46
X647HBP 2014-06-18  10:12:01    10:18:06    00:06:05
HV13LSJ 2014-06-18  10:12:18    10:12:54    00:00:36
X553UYC 2014-06-18  10:13:29    10:17:51    00:04:22
Y208WGO 2014-06-18  10:13:56    10:23:03    00:09:07

SQL命令如下:

    SELECT 
    A.plate,
    a.date,
    a.time as 'start time',
    b.time as 'end time',
    timediff(B.time, A.time) as 'Journey time'
FROM
    (SELECT 
        x.plate,
            date(x.timestamp) as 'date',
            time(x.timestamp) as 'time',
            COUNT(*) rank
    FROM
        anpr_1 x
    JOIN anpr_1 y ON y.plate = x.plate
        AND ((date(y.timestamp) < date(x.timestamp))
        OR (date(y.timestamp) = date(x.timestamp)
        AND time(y.timestamp) <= time(x.timestamp)))
    GROUP BY x.plate , date(x.timestamp) , time(x.timestamp)) a
        JOIN
    (SELECT 
        x.plate,
            date(x.timestamp) as 'Date',
            time(x.timestamp) as 'time',
            COUNT(*) rank
    FROM
        anpr_2 x
    JOIN anpr_2 y ON y.plate = x.plate
        AND ((date(y.timestamp) < date(x.timestamp))
        OR (date(y.timestamp) = date(x.timestamp)
        AND time(y.timestamp) <= time(x.timestamp)))
    GROUP BY x.plate , date(x.timestamp) , time(x.timestamp)) b ON b.plate = a.plate AND b.rank = a.rank
where
    b.time > a.time
        and a.date = '2014-06-18'
        and b.date = '2014-06-18'
        and timediff(B.time, A.time) <= '03:00:00'
order by a.time;

虽然可能过于复杂，但此命令可以很好地满足我的需要。然而，我现在要做的是创建另一个查询，将这些结果聚合到 15 分钟的时间段中，具有中位数平均值、最小和最大旅程时间以及计数。因此，例如，上面显示的 15 分钟数据将显示为:

Timeslot    Median       Min           Max    Count
10:00:00    00:02:50    00:00:36    01:48:51    25
10:15:00    ??:??:??    ??:??:??    ??:??:??    ??

我已经尝试了 group by 和 UNIX_TIMESTAMPs 的各种组合，并查看了发布到该站点的类似问题，但我还没有取得太大的成功试图实现。您有什么建议可以提供帮助吗？

Answer 1

这是一个使用平均行程时间而不是中位数的解决方案:

 SELECT
      `Date`,
      FLOOR(
      ((60* 60 * HOUR(TIMEDIFF(Starttime, '10:00:00')))
      + (60 * MINUTE(TIMEDIFF(Starttime, '10:00:00')))
      + SECOND(TIMEDIFF(Starttime, '10:00:00')))/(15 * 60)) as intervalNo,
      min(Journey_Time), max(Journey_Time), 
      SEC_TO_TIME(AVG(TIME_TO_SEC(Journey_Time))) as avgTime, 
      count(*) as Journeys  
 FROM table
 GROUP BY 1,2

请注意，这是基于每天的 10:00:00。只需将其更改为任何其他开始时间。您可以使用 MySQL ADDTIME() 函数从 intervalNo 计算实际时间间隔(参见 here )。

EDID

找到一个带有 15 分钟时隙的紧凑版本:

 SELECT
    `Date`,
    FLOOR(TIME_TO_SEC(TIMEDIFF(Starttime, '10:00:00'))/(15 * 60)) as intervalNo,
    SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIMEDIFF(Starttime, '10:00:00'))/(15 * 60)) * 15 * 60) 
              as TimeInterval,

    min(Journey_Time), max(Journey_Time),
    SEC_TO_TIME(AVG(TIME_TO_SEC(Journey_Time))),
    count(*) as Journeys

FROM table
GROUP BY 1,2