MySQL 仅对新行求和

Question

我有一个采用这种格式的 MySQL 表

transaction |  user   | amount | week
------------|---------|--------|------
     1      | user_1  |  100   |  1
     2      | user_2  |   50   |  1
     3      | user_1  |   50   |  2
     4      | user_3  |  200   |  2

我知道如何在 MySQL 中计算每周的总金额，但是 MySQL 中有没有办法计算每周的新用户总金额？

因此对于这张表来说，它是:

week 1 = 150
week 2 = 200

Answer 1

是的，有。您可以使用MIN(week)的值来指示特定用户的第一笔交易。

SELECT w, SUM(amount)
FROM(
    SELECT user, amount, MIN(week) AS w
    FROM `trans`
    GROUP BY user) newUserTrans
GROUP BY w

如果您仍想显示没有新用户的一周，那么您可以使用以下命令:

SELECT week, IFNULL(SUM(amount),0) AS total
FROM(
    SELECT user, amount, MIN(week) AS w
    FROM `trans`
    GROUP BY user) newUserTrans RIGHT JOIN (SELECT DISTINCT week FROM trans) weeks ON newUserTrans.w = weeks.week
GROUP BY w
ORDER BY week

更新:

根据@skobaljic的意见，我还为用户在同一周内可能有多个记录的情况提供了另一种替代方案。

SELECT weeks.week AS Week, IFNULL(SUM(amount),0) AS Total
FROM(
    SELECT trans.user, trans.amount, trans.week
    FROM trans
      JOIN (SELECT user, MIN(week) AS w
              FROM trans
              GROUP BY user) newWeek ON trans.user = newWeek.user
           AND trans.week = newWeek.w) newUserTrans
    RIGHT JOIN (SELECT DISTINCT week FROM trans) weeks ON newUserTrans.week = weeks.week
GROUP BY newUserTrans.week
ORDER BY weeks.week

希望有帮助。