PostgreSQL整数转float异常：计算接受率始终得1的问题排查

你的计算结果一直为1，核心有三个问题：

• JOIN条件写错：ON子句里的b.user_id_receiver = b.user_id_receiver是恒成立的无效条件，相当于没限制接收者匹配，导致所有发送请求都能和接受请求关联上，COUNT(b.user_id_receiver)和COUNT(a.user_id_sender)数值完全相等，除法结果自然是1。正确的应该是关联双方的接收者：b.user_id_receiver = a.user_id_receiver。
• JOIN类型错误：用JOIN（内连接）会过滤掉发送后没被接受的请求，导致你统计的只是有接受记录的请求，接受率自然是100%。应该用LEFT JOIN保留所有发送请求，没被接受的记录中b表字段会是NULL，COUNT时会被计为0。
• 浮点转换可优化：虽然你加了::float，但最好把其中一个计数先转成浮点型，避免整数除法的截断问题（这不是此次结果为1的原因，但优化后更严谨）。

修正后的SQL代码 #

SELECT
    a.date,
    COUNT(b.user_id_receiver)::float / COUNT(a.user_id_sender) AS acceptance_rate
FROM (
    SELECT 
        date,
        user_id_sender,
        user_id_receiver,
        action
    FROM fb_friend_requests
    WHERE action = 'sent'
) a
LEFT JOIN (
    SELECT 
        date,
        user_id_sender,
        user_id_receiver,
        action
    FROM fb_friend_requests
    WHERE action = 'accepted'
) b 
    ON b.user_id_sender = a.user_id_sender
    AND b.user_id_receiver = a.user_id_receiver  -- 修正关联条件
GROUP BY a.date

更简洁的写法（无需子查询） #

SELECT
    date,
    SUM(CASE WHEN action = 'accepted' THEN 1 ELSE 0 END)::float / 
    SUM(CASE WHEN action = 'sent' THEN 1 ELSE 0 END) AS acceptance_rate
FROM fb_friend_requests
GROUP BY date

直接按日期分组，统计每个日期的接受数和发送数再计算比率，逻辑更清晰，也避免了JOIN带来的问题。

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