SQL技术问询：users表中speed为0的占比及特定id数量计算

表结构与测试数据 #

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)

问题1：存在0速记录的id占总id的百分比 #

直接统计有过0速记录的唯一id数，除以总唯一id数再转成百分比：

SELECT 
  ROUND(
    (COUNT(DISTINCT CASE WHEN speed = 0 THEN id END)::NUMERIC / COUNT(DISTINCT id)) * 100,
    2
  ) AS zero_speed_id_percentage
FROM users;

• COUNT(DISTINCT CASE WHEN speed=0 THEN id END)：统计至少有一条0速记录的唯一id数量
• COUNT(DISTINCT id)：统计所有唯一id的总数
• 转成NUMERIC是为了避免整数除法导致结果失真，ROUND用来保留两位小数

问题2：统计自身50%以上记录为0速的id数量 #

先按id分组计算每个id的总记录数和0速记录数，再筛选出0速占比≥50%的id，最后统计这些id的数量：

SELECT COUNT(*) AS qualified_id_count
FROM (
  SELECT 
    id,
    COUNT(*) AS total_records,
    SUM(CASE WHEN speed = 0 THEN 1 ELSE 0 END) AS zero_speed_records
  FROM users
  GROUP BY id
  HAVING SUM(CASE WHEN speed = 0 THEN 1 ELSE 0 END)::NUMERIC / COUNT(*) >= 0.5
) AS subquery;

• 子查询按id分组，计算每个id的总记录数和0速记录数
• HAVING条件判断0速记录数占总记录数的比例是否≥50%
• 外层查询统计符合条件的id总数

内容的提问来源于stack exchange，提问作者Amina Umar