识别准可变时间窗口中的最大移动

我有 table （track_t，Postgresql 9.5）的时间戳和足球运动员的速度和距离测量表 . 时间戳在比赛开始前大约30分钟，并在比赛结束后最多30分钟 . 我有一个查询可以确定哪个45分钟范围有最大的移动 - 平均（速度）和总和（距离） - 因此可能是游戏正在播放的时间 .

SELECT
    tt.start_time
    , tt.game_id
    , tt.start_time AT TIME ZONE 'UTC' AT TIME ZONE 'CEST' + interval '45 minute' as end_time_lz
    , sum(aa.distance) as distance
    , avg(aa.speed) as speed
FROM
(
    SELECT
        game_id
        , GENERATE_SERIES(MIN(date_trunc('hour',ts)),max(date_trunc('minute',ts)),'1m') as start_time
    FROM track_t
        GROUP BY game_id
) tt
INNER JOIN
(
    SELECT
        game_id
        , avg(speed) as speed
        , sum(distance) as distance
        , date_trunc('minute',ts) as timestamp
    FROM track_t
    GROUP BY 
        game_id
        , date_trunc('minute',ts)
) aa
ON aa.game_id = tt.game_id
AND aa.timestamp >= tt.start_time 
AND aa.timestamp < tt.start_time + interval '45 minute'

GROUP BY
    tt.start_time
    , tt.game_id

ORDER BY
    SUM(aa.distance) desc
    , AVG(aa.speed) desc
;

第一部分根据每分钟的时间戳创建具有GENERATE_SERIES()功能的所有45分钟窗口 . 因此，如果游戏在10:00开始，数据从09:30开始，那么我在09：30,09：31,09：32等处创建45分钟的窗口 .

第二部分计算该45分钟窗口的平均速度和总距离 .

然后，为了确定最可能的45分钟窗口，我可以按总和（距离）和平均（速度）排序给我开始时间 .

结果看起来像这样（限制10个有序行）

start_time; game_id; end_time; sum(distance); avg(speed)
*"2016-09-03 17:03:00";"a20160903";"2016-09-03 17:48:00";47935.0703622001;1.06124213271675*
"2016-09-03 17:04:00";"a20160903";"2016-09-03 17:49:00";47761.7538393264;1.0572395112247
"2016-09-03 17:02:00";"a20160903";"2016-09-03 17:47:00";47642.3581425403;1.05482864669178
"2016-09-03 17:05:00";"a20160903";"2016-09-03 17:50:00";46949.1297795754;1.03702742158256
"2016-09-03 17:01:00";"a20160903";"2016-09-03 17:46:00";46868.0351728161;1.03604043683709
*"2016-09-03 17:58:00";"a20160903";"2016-09-03 18:43:00";46481.3160240327;1.03470232279402*
"2016-09-03 17:00:00";"a20160903";"2016-09-03 17:45:00";46454.0167265013;1.02557019844115
"2016-09-03 17:59:00";"a20160903";"2016-09-03 18:44:00";46183.0355093333;1.02985443158215
"2016-09-03 17:57:00";"a20160903";"2016-09-03 18:42:00";46059.2319184038;1.02059235291926
"2016-09-03 18:00:00";"a20160903";"2016-09-03 18:45:00";45984.1387791433;1.02661395680708

并且每一半的两个可能的开始时间用*标记（例如17:03和17:58） .

这可以很好地创建一个可排序的列表，但 how can I automatically select the 1st and 2nd half start times?

此外，由于受伤时间，半场比赛通常超过45分钟，每半场可以在0到5分钟之间 . How can I automatically scan time-windows that include possible injury time, and are between 45 and 50 minutes, and select the "maximum movement window" from these combinations?

如果你在x轴上绘制start_time，在y轴上绘制sum（距离），你会看到一个很好的双峰distrubition-所以我正在考虑一些分析功能来识别半开始时间和伤害时间 .

（由于UTC /本地时间转换，小时数与上述数据不匹配）

注意：我更喜欢用SQL（Postgres 9.5）来做这个，但我确实有一个可以使用的python包装器，这可能会提供更多的机器学习库 .