时间戳

首页 > 解决方案 > 注册日期后 1 个月和 2 个月内从 1 月开始每位会员的平均收入

mysql - 注册日期后 1 个月和 2 个月内从 1 月开始每位会员的平均收入

问题描述

我需要计算注册日期后 1 个月和 2 个月内 1 月份注册的每位会员的平均收入。

仅使用 SELECT 语句提供输出（无临时表或函数）。也在寻找MYSQL语法

我有 2 张桌子。

Table: memberships, Columns:

member_id int PK
signup_date datetime
join_country varchar(2)
email varchar(45)
cancellation_date date
expiry_date date

Table: transactions, Columns:

transaction_id int PK
member_id int
transaction_date datetime
transaction_type_ID int
product_id int
transaction_amount float


SELECT m.member_id, m.signup_date, AVG(t.transaction_amount)
FROM memberships m
INNER JOIN transactions t
ON m.member_id = t.member_id
WHERE m.signup_date = '2018-01-01'
AND t.transaction_date >= '2018-01-01' AND t.transaction_date < '2018-03-01';

标签： mysqlaverage

解决方案

您只针对在 1 月 1 日注册的会员，而不是在整个 1 月份。同样，您从 1 月 1 日算起 2 个月，而不是他们的实际注册日期。

您不是在计算每个成员的平均值，而是在计算每笔交易的平均值。您需要一个子查询来获取每个成员的总收入，然后主查询可以获得这个的平均值。

您没有按月对收入进行分组，因此在注册后的 1 个月和 2 个月内不会产生单独的结果。

SELECT month, AVG(total) AS avg_revenue
FROM (
    SELECT TIMESTAMPDIFF(month, m.signup_date, t.transaction_date)+1 AS month,
            SUM(t.transaction_amount) AS total
    FROM mindgeek.memberships m
    INNER JOIN mindgeek.transactions t
    ON m.member_id = t.member_id AND t.transaction_date < DATE_ADD(m.signup_date, INTERVAL 2 MONTH)
    WHERE m.signup_date BETWEEN '2018-01-01' AND '2018-01-31'
    GROUP BY m.member_id, month
) AS per_member
GROUP BY month

我添加1到结果中，TIMESTAMPDIFF()因为它返回整月数，四舍五入。所以从注册到1个月后的时间返回0。

推荐阅读