mysql - 获取测试运行的计数及其开始时间

Question

我有一个表 test_data 看起来像这样

test_id | test_timestamp                 | test_value
123     | 2016-05-27 14:23:57.634119     | 45
123     | 2016-05-27 14:23:57.634119     | 11
123     | 2016-05-27 14:23:57.634119     | 12
123     | 2016-05-27 14:23:57.634119     | 13
123     | 2016-05-27 14:33:59.634121     | 46
123     | 2016-05-27 14:33:59.634121     | 50
456     | 2016-05-27 11:03:00.000000     | 14
456     | 2016-05-27 11:13:00.000000     | 15
456     | 2016-05-27 11:23:00.000000     | 16
456     | 2016-05-27 11:33:00.000000     | 17
123     | 2016-05-27 14:43:59.634121     | 47
123     | 2016-05-27 14:53:59.634121     | 48
123     | 2016-05-27 15:03:59.634121     | 49
123     | 2016-05-27 15:13:59.634121     | 46

每个测试都会在给定的时间戳收集一堆结果。我希望我的结果是 test_id，测试的开始时间，test_id 的计数，

像这样

test_id | test_timestamp                 | count
123     | 2016-05-27 14:23:57.634119     | 10
456     | 2016-05-27 11:03:00.000000     | 4

目前我的查询给了我两个不同的输出

SELECT test_id, COUNT(*) FROM test_data group by test_id;

此查询提供了所有唯一 test_id 的计数以及它们的计数，但没有提供开始收集结果的时间。我正在寻找测试开始收集结果的第一时间。

test_id | count
123     | 10
456     | 4

第二个查询

SELECT test_id, COUNT(*), test_timestamp FROM test_data group by test_id ,test_timestamp order by test_timestamp asc;

此查询为我提供了每次运行的 test_id 计数。

test_id | test_timestamp                 | count
456     | 2016-05-27 11:03:00.000000     | 1
456     | 2016-05-27 11:13:00.000000     | 1
456     | 2016-05-27 11:23:00.000000     | 1
456     | 2016-05-27 11:33:00.000000     | 1
123     | 2016-05-27 14:23:57.634119     | 4
123     | 2016-05-27 14:33:59.634121     | 2
123     | 2016-05-27 14:43:59.634121     | 1
123     | 2016-05-27 14:53:59.634121     | 1
123     | 2016-05-27 15:03:59.634121     | 1
123     | 2016-05-27 15:13:59.634121     | 1

我可能可以合并两个 sql 的输出，但如果我可以在单个 psql 查询中获得结果，那就太好了。表格大小为数百万行。

Answer 1

选择 MIN() 时间戳应该有效，这将针对每个组:

SELECT test_id, min(test_timestamp) as "start time", COUNT(*) FROM test_data group by test_id;

Here is a functional example与您的数据

在开始时间旁边为 Duration 添加一列会很有趣:

SELECT 
 test_id
 , min(test_timestamp ) as "start time"
 ,timestampdiff(MINUTE,min(test_timestamp ),max(test_timestamp )) as "Duration (Min)"
 ,COUNT(*) 
 FROM test_data group by test_id;

Sample