mysql - 按求和变量分组

Question

SELECT deposit.numberSuccessfulDeposits, count(distinct userid) 
FROM deposit WHERE deposit.asOfDate between '2016-04-01 00:00:00' and '2016-04-03 23:59:59'
AND deposit.licenseeId = 1306
GROUP BY deposit.numberSuccessfulDeposits

示例输出

numberSuccessfulDeposits     count(distinct userid) 
 0   228 
 1   878 
 2   90 
 3   37 
 4   17 

但是，如果鲍勃在周一存款 1 笔，周二存款 3 笔，则成功存款次数将同时计入“1”和“3”。

numberSuccessfulDeposits     count(distinct userid) 
 0   ## 
 1   1 
 2   ## 
 3   1 
 4   ## 

理想情况下，它应该只计入“4”

numberSuccessfulDeposits     count(distinct userid) 
 0   ## 
 1   ## 
 2   ## 
 3   ## 
 4   1

想法？

Answer 1

将分组更改为基于用户并对所有发生的存款进行求和。然后计算每笔存款的用户数:

SELECT
  numberSuccessfulDeposits,
  COUNT(userid) AS users_count
FROM (
  SELECT 
    sum(numberSuccessfulDeposits) AS numberSuccessfulDeposits, 
    userid
  FROM deposit
  WHERE asOfDate between '2016-04-01 00:00:00' and '2016-04-03 23:59:59'
    AND licenseeId = 1306
  GROUP BY userid
  ) t
GROUP BY numberSuccessfulDeposits

编辑:将存款分为 0、1、2、3+ 类别，如下所示:

SELECT
  numberSuccessfulDeposits,
  COUNT(userid) AS user_count
FROM (
  SELECT
    CASE WHEN numberSuccessfulDeposits >= 3 THEN '3+' ELSE numberSuccessfulDeposits::TEXT END AS numberSuccessfulDeposits,
    userid
  FROM (
    SELECT 
      sum(numberSuccessfulDeposits) AS numberSuccessfulDeposits, 
      userid
    FROM deposit
    WHERE asOfDate between '2016-04-01 00:00:00' and '2016-04-03 23:59:59'
      AND licenseeId = 1306
    GROUP BY userid
    ) t
  ) f
GROUP BY numberSuccessfulDeposits