MYSQL - 如何获取按月分组的连接用户的百分比

Question

我正在尝试编写一个高效的查询，以获取按月份和 user_group 分组的已连接用户的百分比，给定以下 2 个表:

• connections(id, connection _date, user_id)
• 用户(用户 ID、用户组、创建日期)

我正在使用 MySQL。

提前谢谢你。

Answer 1

我假设 connections.connection _date 和 users.creation_date 属于日期时间。

要获取一个月的连接用户百分比相对于该月的总用户数，请使用:

SELECT u.user_group,  DATE_FORMAT(`c`.`connection​_date`, "%M %Y") AS month,
COUNT(DISTINCT u.`user_id`) / (SELECT COUNT(`user_id`) FROM users WHERE users.creation_date <= adddate(last_day(`c`.`connection​_date`), 1) AND users.user_group = u.user_group) AS percentage,
COUNT(DISTINCT u.`user_id`) as loggedThisMonth,
(SELECT COUNT(`user_id`) FROM users WHERE users.creation_date <= adddate(last_day(`c`.`connection​_date`), 1) AND users.user_group = u.user_group) AS totalRegisteredToMonth
FROM connections c LEFT JOIN users u ON c.`user_id` = u.`user_id`
GROUP BY u.user_group, DATE_FORMAT(`c`.`connection​_date`, "%M %Y")
ORDER BY DATE_FORMAT(`c`.`connection​_date`, "%Y %m"), u.user_group ASC

这项工作由:

• 计算每个月连接的 DISTINCT users.user_ id，从而防止在一个月内重新计算多个连接的用户 COUNT(DISTINCT u.user_​id)
• 使用subselect 计算用户组到该月的注册用户 (SELECT COUNT(用户 ID ) FROM users WHERE users.creation_date <= adddate(last_day( c .连接 _日期 ), 1) AND users.user_group = u.user_group)

引用:

adddate(last_day(`c`.`connection​_date`), 1)

返回1st day of the next month日期