MySQL在时间轴上选择最小值、最大值

Question

是否可以根据时间轴获取最小和最大时间戳？我将在 parking 场示例中进行解释。

+---------------------+------+--------+-------+------------+
| ts                  | pos  | posidx | car   | carowner   |
+---------------------+------+--------+-------+------------+
| 2016-02-16 20:15:02 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-16 20:30:02 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-16 20:35:01 | Lev2 |      2 | Volvo | Mr Johnson |
| 2016-02-16 22:20:01 | Lev2 |      2 | Volvo | Mr Johnson |
| 2016-02-16 22:25:02 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-16 22:30:01 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-17 00:30:01 | Lev1 |      3 | GM    | Mr Johnson |
| 2016-02-17 00:35:02 | Lev1 |      3 | GM    | Mr Johnson |

假设 pos 是 parking 位，posidx 是 parking 位编号。我需要时间线中的车主和他的每辆车的报告，它应该如下所示:

+---------------------+---------------------+------+--------+-------+------------+
| min(ts)             | max(ts)             | pos  | posidx | car   | carowner   |
+---------------------+---------------------+------+--------+-------+------------+
| 2016-02-16 20:15:02 | 2016-02-16 20:30:01 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-16 20:35:01 | 2016-02-16 22:20:01 | Lev2 |      2 | Volvo | Mr Johnson |
| 2016-02-16 22:25:02 | 2016-02-17 00:25:01 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-17 00:30:01 | 2016-02-17 00:35:02 | Lev1 |      3 | GM    | Mr Johnson |

我试过:

SELECT min(ts), max(ts), pos, posidx, car, carowner
from parking
where carowner = 'Mr Johnson'
group by pos, posidx, car

但由于 posidx 分组，它不显示上述报告的第 3 行，而是在第一行显示其最大 ts。

你能帮帮我吗？

Answer 1

您需要分配一个分组标识符。一种思考方式是，它是一个计数器，当某些值发生变化时会递增。有了这个概念，您可以使用变量来分配分组然后聚合:

select min(ts), max(ts), pos, posidx, car, carowner
from (select t.*, 
             (@grp := if(@pcc = concat_ws(':', posidx, car, carowner), @grp,
                         if(@pcc := concat_ws(':', posidx, car, carowner), @grp+1, @grp+1)
                        )
             ) grp
      from t cross join
           (select @pcc := '', @grp := 0) params
      order by ts
     ) t
group by pos, posidx, car, carowner, grp;