假设我有下表:
users(id INT, timestamp TIMESTAMP, speed DOUBLE PRECISION);
INSERT INTO users(id, timestamp, speed)
VALUES (658,'2016-04-01 00:50:43.673+01',0.93),
(658,'2016-04-01 00:50:45.677+01',0.94),
(658,'2016-04-01 00:50:56.813+01',0.94),
(658,'2016-04-01 00:51:13.332+01', 0),
(658,'2016-04-01 00:51:18.337+01',0),
(658,'2016-04-01 00:51:23.427+01',0),
(658,'2016-04-01 00:51:28.584+01',0),
(658,'2016-04-01 00:51:33.574+01',0),
(658,'2016-04-01 00:51:38.686+01',0),
(658,'2016-04-01 00:51:43.719+01',0)
我有6000多个不同的id
s。如何计算50%的行速度等于0的id
s的数量?
此查询应该有效:
SELECT id FROM users GROUP BY id HAVING SUM(CASE speed WHEN 0 THEN 1 ELSE 0 END) * 2 >= COUNT(speed)
- 使用CCD_ 3按用户id分组
- 为了计算一个特殊值,我使用
case
函数将该值映射为1,将其他值映射为0,并取映射值的sum
。现在我们有了每个id的搜索值的计数 - 我使用
HAVING
关键字只取id,其中搜索值的数量是行的50%
较短版本:
SELECT id FROM users GROUP BY id HAVING SUM(CASE speed WHEN 0 THEN 2 ELSE 0 END) >= COUNT(speed)