SQL:一列中相对于另一列的值计数

Question

我有下表

id      date      time_stamp  licenseid   storeid    deviceid    value
1     2015-06-12   17:36:15   lic0001       1         0add      52
2     2015-06-12   17:36:15   lic0002       1         0add      54
3     2015-06-12   17:36:15   lic0003       1         0add      53
4     2015-06-12   17:36:21   lic0001       1         0add      54
5     2015-06-12   17:36:21   lic0002       1         0add      59
6     2015-06-12   17:36:21   lic0003       1         0add      62
7     2015-06-12   17:36:21   lic0004       1         0add      55
8     2015-06-12   17:36:15   lic0001       1         0bdd      53
9     2015-06-12   17:36:15   lic0002       1         0bdd      52
10    2015-06-12   17:36:15   lic0003       1         0bdd      52

我需要基于它出现的时间戳数的 deviceid 计数。所以输出将是这样的: 0add 出现在 2 个时间戳中，因此计数为 2 而 0bdd 出现在一个时间戳中，因此 0bdd 的计数为 1 . 计数时不考虑每个时间戳的设备对应的许可证数量。

date        deviceid count
2015-06-12   0add     2
2015-06-12   0bdd     1

我正在尝试使用下面的这个查询，但无法验证它是否有效，因为该查询已经执行了很长一段时间并且没有显示任何结果:

select date, deviceid, count(deviceid) from my_table group by deviceid, time_stamp

请注意，我运行此查询的行数是 2,000,000

上面的查询是否适合我的输出

如果是这样，我如何优化它以针对我的表大小快速运行

编辑:标有 time_stamp 的列是 TIME类型。

Answer 1

我认为您需要在这里考虑以下几点:

如果您想要每个日期每个设备的时间戳数，您应该按设备和日期分组，而不是按设备和时间戳分组。

您有设备 ID 具有相同日期和时间戳的行，因此您可能需要考虑在每个日期中查找不同的时间戳。

第一个的修复是不言自明的，对于第二个，您可以将聚合更改为 COUNT(DISTINCT timestamp) .试试这个查询:

SELECT device_id, date, COUNT(DISTINCT timestamp) AS numRows
FROM myTable
GROUP BY device_id, date;

这是一个 SQL Fiddle使用您的示例数据的示例。还值得注意的是，如果此查询对您来说仍然很慢，则在 device_id 和 date 列上放置索引可能有助于此查询运行得更快。有关此问题的更多讨论，请参阅评论。