我有一个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 byUNIX_TIMESTAMPs的组合,并查看了发布到这个网站上的类似问题,但在我试图实现的目标方面还没有取得多大成功。你有什么建议可以帮忙的吗?

最佳答案

下面是一个用平均行程时间代替中值的解决方案:

 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)。
电子数据交换
找到一个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

08-05 06:58