MySQL查找第一次没有购买的客户的交易(付款)

Question

我有一个包含交易的简单表格，我想了解每个月有多少消费者进行的交易总额大于 0，并且他们的第一笔交易不在该月。首次交易是指客户在当月首次购买。

我试图得到的结果是以下形式:

+--------+---------+-----------------------------------+
|  Year  |  Month  |  NumOfCustomersWithPositiveTotals |
+--------+----------------------------+----------------+
|  2014  |    1    |                 22                |
+--------+----------------------------+----------------+
|  2014  |    2    |                 10                |
+--------+----------------------------+----------------+

我有一个 SQL fiddle ，我在其中找到了相同的东西，但对于那些在该月内进行第一笔交易的消费者。实际上，我正在寻找的查询是相同的，但对于其他消费者而言。

这是 fiddle :http://sqlfiddle.com/#!9/31538/24

Answer 1

我想这就是你想要的:

SELECT count(consumerId) as NumOfCust, mm, yy FROM
(
    SELECT consumerId, month(date) as mm, year(date) as yy, sum(amount) as total, mdate FROM beta.transaction as t
    LEFT JOIN (
            SELECT min(month(date)) as mdate, consumerId as con FROM beta.transaction
            GROUP BY consumerId
            ) as MinDate ON con = t.consumerId
    GROUP BY month(date), consumerId
    HAVING mdate < mm AND total > 0
) as res
GROUP BY res.mm;

我会尝试从内到外解释它

让我们看看名为 minDate 的 JOIN 表有什么:

SELECT min(month(date)) as mdate, consumerId as con FROM beta.transaction
GROUP BY consumerId

-- Here we find the first date of transaction per consumerId

下一步

SELECT consumerId, month(date) as mm, year(date) as yy, sum(amount), mdate FROM beta.transaction as t
    LEFT JOIN (
            SELECT min(month(date)) as mdate, consumerId as con FROM beta.transaction
            GROUP BY consumerId
            ) as MinDate ON con = t.consumerId
    GROUP BY month(date), consumerId
    HAVING mdate < mm AND total > 0

 -- Here we find total amount per consumerId per month and count only the consumers whose first transact (aka minDate) is lower than current month AND total is greater than 0

最后，我们使用外部 SELECT 计算上述按月分组的结果。我希望它是你想要的。