mysql - SQL 查询以确定群组中发送 1 个或多个邀请的用户百分比

Question

我有以下表格:

USERS: id, created_at, onboarding_started_at
INVITATIONS: id, user_id

我正在编写一个查询，显示发送 1 次或多次邀请的新用户的百分比。这是我到目前为止所拥有的:

SELECT floor(datediff(u.created_at, curdate()) / 7) AS weeks_ago,
       count(DISTINCT u.id) AS "New Users in Cohort", 
       count(DISTINCT i.id) AS "Total Invites in Cohort",
       count(DISTINCT i.user_id) AS "Uniq Users who invited Cohort",
count(DISTINCT u.id) / count(DISTINCT i.user_id) AS "% who invite"
FROM users u 
LEFT JOIN invitations i
     ON u.id = i.user_id
WHERE u.onboarding_started_at IS NOT NULL
GROUP BY weeks_ago;

目前这是错误地输出“% who invite”。示例数据:

weeks_ago | New Users in Cohort | Uniq Users who Invited Cohort | % who invite
-32 | 12 |  557 |  12 1.0000

在上面的例子中，% who invite 显示 1.0000

我应该怎么做才能修复“% who invite”？我计算的 Uniq Users who invited Cohort 有误吗？

谢谢

Answer 1

您是否尝试过将值转换为 DECIMAL？

CAST(count(DISTINCT u.id) AS DECIMAL(7,2))/CAST(count(DISTINCT i.user_id) AS DECIMAL(7,2))