如何在R的SQL查询中计算统计结果比值？为何结果恒为0？

问题背景 #

需要计算两个SQL查询结果的比值：

• 分子：含brexit关键词且满足用户条件的推文总数

SELECT COUNT(*) AS number_tweets
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE text LIKE '%brexit%'
AND users.screen_name_in = '1'

• 分母：满足用户条件的所有推文总数

SELECT COUNT(*) AS number_tweets
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE users.screen_name_in = '1'

尝试用子查询计算比值时结果始终为0，原查询写法：

SELECT x.number / y.number 
FROM
(SELECT COUNT(*) AS number
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE text LIKE '%brexit%'
AND users.screen_name_in = '1') x
JOIN
(SELECT COUNT(*) AS number
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE users.screen_name_in = '1') y on 1=1

核心原因 #

结果为0是因为整数除法机制：数据库中两个整数相除时，会自动舍弃小数部分返回整数结果。如果分子（含brexit的推文数）小于分母（总推文数），计算结果就会被截断为0。

解决方案 #

方案1：强制浮点除法 #

通过将其中一个数值转换为浮点数，让数据库执行浮点运算：

SELECT CAST(x.number AS FLOAT) / y.number AS brexit_ratio
FROM
(SELECT COUNT(*) AS number
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE text LIKE '%brexit%'
AND users.screen_name_in = '1') x
CROSS JOIN
(SELECT COUNT(*) AS number
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE users.screen_name_in = '1') y

或者更简洁的写法，用*1.0自动转换类型：

SELECT 
  (SELECT COUNT(*)
   FROM tweets JOIN users
   ON tweets.user_id_str = users.user_id_str
   WHERE text LIKE '%brexit%'
   AND users.screen_name_in = '1') * 1.0 /
  (SELECT COUNT(*)
   FROM tweets JOIN users
   ON tweets.user_id_str = users.user_id_str
   WHERE users.screen_name_in = '1') AS brexit_ratio

方案2：处理分母为0的边界情况 #

如果分母可能为0（无符合条件的推文），添加CASE语句避免报错：

SELECT 
  CASE WHEN y.number = 0 THEN 0 
       ELSE CAST(x.number AS FLOAT)/y.number 
  END AS brexit_ratio
FROM
(SELECT COUNT(*) AS number
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE text LIKE '%brexit%'
AND users.screen_name_in = '1') x
CROSS JOIN
(SELECT COUNT(*) AS number
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE users.screen_name_in = '1') y

方案3：高效的单次扫描写法 #

不需要两次关联查询，用条件聚合一次扫描完成计算，性能更优：

SELECT 
  SUM(CASE WHEN text LIKE '%brexit%' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS brexit_ratio
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE users.screen_name_in = '1'

这个查询只遍历一次关联后的数据集，同时统计分子和分母，再执行除法运算，效率远高于两次子查询关联。

内容的提问来源于stack exchange，提问作者Rhea Ramtohul