时间戳

首页 > 解决方案 > SQL 查询以确定同类群组中发送 1 个或多个邀请的用户百分比

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

问题描述

我有以下表格：

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;

目前这是错误地输出“邀请的百分比”。示例数据：

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

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

我应该怎么做才能修复“邀请的百分比”？我计算Uniq Users who invited Cohort不正确吗？

谢谢

标签： mysqlsql

解决方案

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

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

推荐阅读