php - 获取推荐用户的支付金额总和

Question

我正在开发一个推荐系统，作为该仪表板的一部分，我需要输出用户收到的推荐数量、他们因这些推荐购买商品而获得的金币总额，以及最后有多少推荐人购买了商品以及总金额。

例如，显示 User_ID 1 的信息:

Referrals: 3
Total no. of purchases from all referrals: 2
Sum of purchase amount: $14.00
Coins earned as a result of those purchases: 140 (14 * 10)

我的表结构如下:

用户

|-------------------------------------------------|
|  id  | ... some other columns ... | referred_by |
|-------------------------------------------------|
|   1  |      ..............        |      0      |
|   2  |      ..............        |      1      |
|   3  |      ..............        |      1      |
|   4  |      ..............        |      1      |
---------------------------------------------------

购买

|---------------------------------------------------------|
|  purchase_id  |  user_id  |  payment_status  |  amount  |
|---------------------------------------------------------|
|       1       |     2     |     Completed    |  10.00   |
|       2       |     3     |     Completed    |   4.00   |
|       3       |     1     |     Completed    |   9.00   |
-----------------------------------------------------------

目前我正在运行这个查询。出于某种原因，我无法解决这个问题，它只获取它匹配的第一个用户的总和(在本例中为 id = 2)。 count(id) = 3 但 sum(amount) = 10.00 而不是预期的 14.00。有什么想法吗？

SELECT count(id) AS count, 
(SELECT COALESCE(sum(amount), 0.00) 
    FROM purchases
    WHERE purchases.user_id = users.id 
    AND payment_status = "Completed" 
) AS purchases_amount, 
(SELECT COALESCE(sum(round(amount) * 10), 0) 
    FROM purchases
    WHERE users.id = purchases.user_id 
    AND payment_status = "Completed"
) AS coins_earned,    
(SELECT count(purchase_id) 
    FROM purchases
    WHERE users.id = purchases.user_id
    AND payment_status = "Completed"
) AS purchases_count
FROM (`users`) 
WHERE `referred_by` = '1' 
AND `created` >= '2015-01-01 00:00:00' // created = user's registration date
AND `created` <= '2015-12-31 23:59:59'

返回的数据集:

count: 3 (expected: 3)
purchases_amount: 10.00 (expected: 14.00)
coins_earned: 100 (expected: 140)
purchases_count: 1 (expected: 2)

Answer 1

您遇到了 MySQL 问题，它默默地允许聚合与非聚合组合，而无需适当的分组依据。其他 RDBMS 会提示您的查询，因为第一列中的 COUNT() 聚合与查询的其余部分返回的多行冲突。

尝试删除选择中的第一列 (count(id) AS count)，您将看到查询为每个用户 ID(2、3 和 4)返回一行。

要解决此问题，请对每个子查询的所有结果求和:

SELECT count(id) AS count, 
sum((SELECT COALESCE(sum(amount), 0.00) 
    FROM purchases
    WHERE purchases.user_id = users.id 
    AND payment_status = "Completed" 
)) AS purchases_amount, 
sum((SELECT COALESCE(sum(round(amount) * 10), 0) 
    FROM purchases
    WHERE users.id = purchases.user_id 
    AND payment_status = "Completed"
)) AS coins_earned,    
sum((SELECT count(purchase_id) 
    FROM purchases
    WHERE users.id = purchases.user_id
    AND payment_status = "Completed"
)) AS purchases_count
FROM (`users`) 
WHERE `referred_by` = '1' 
AND `created` >= '2015-01-01 00:00:00' // created = user's registration date
AND `created` <= '2015-12-31 23:59:59'