postgresql - 按从内部联接中找到的最早日期计算？

Question

我有两个表，customerusermap 和 users。每当用户注册我们的产品时，他们都会立即被添加到一个名为 users 的表中，但直到他们开始为用户付费时，他们才会被添加到一个名为 customerusermap 的表中。

用户表如下所示:

id    | customer_id        | firstname | lastname | created_at
-------------------------------------------------------
1725  | cus_3hEmhErE2jbwsO | Abby      | Smith    | 2015-03-19
1726  | cus_7oNweUrE4jbwr2 | Sam       | Peters   | 2015-06-20

customerusermap 表如下所示:

customer_id        | user_id | created_at
------------------------------------------
cus_3hEmhErE2jbwsO | 9275    | 2015-09-01
cus_3hEmhErE2jbwsO | 2628    | 2015-09-05
cus_3hEmhErE2jbwsO | 2358    | 2015-07-05
cus_3hEmhErE2jbwsO | 3158    | 2015-08-05
cus_3hEmhErE2jbwsO | 2487    | 2015-08-05
cus_3hEmhErE2jbwsO | 6044    | 2015-08-05
cus_7oNweUrE4jbwr2 | 8094    | 2015-08-25
cus_7oNweUrE4jbwr2 | 2345    | 2015-09-02

在此示例中，Abby(cus_3hEmhErE2jbwsO) 为 6 个用户付费。她开始为用户 2358 2015-07-05 付款，因此她应该被视为 07-2015 的付费客户，而不是 03-2015。 Sam 正在为 2 个用户付费，他从 08-2015 开始为用户 8094 付费，因此他被认为是 08-2015 的付费客户，而不是 06-2015。我有一个查询，它按每月付费客户的数量进行抓取和分组:

SELECT concat(extract(MONTH from u.created_at),'-',extract(year from u.created_at)) as "Month", 
COUNT(distinct u.email) as "Total AB Paying Customers"
FROM customerusermap AS cm, users AS u
WHERE cm.customer_id=u.customer_id AND cm.user_id <> u.id
GROUP BY 1,extract(month from u.created_at),extract(year from u.created_at)
ORDER BY extract(year from u.created_at),extract(month from u.created_at);

但这会按客户添加到用户表的日期进行抓取和计数，而不是他们实际开始付款的日期。我将如何获取计数以便它获取 customerusermap 表中的最早日期？此示例中所需的输出应如下所示:

Month    | Total AB Paying Customers
-------------------------------------
07-2015  | 1
08-2015  | 1

Answer 1

您可以使用以下查询:

SELECT CONCAT(EXTRACT(MONTH FROM startedPayingDate), '-', 
              EXTRACT(YEAR FROM startedPayingDate)) AS "Month", 
       COUNT(*) AS "Total AB Paying Customers"
FROM (       
   SELECT customer_id, MIN(created_at) AS startedPayingDate 
   FROM customerusermap AS cm
   WHERE NOT EXISTS (SELECT 1
                     FROM users AS u
                     WHERE cm.user_id = u.id)
   GROUP BY customer_id ) AS t
GROUP BY 1   

我使用了 NOT EXISTS 运算符来排除与“为自己付费”的客户相关的记录(如果这确实是您的意图)。

一旦您获得每个 customer_id 的 MIN(created_at) 日期，您就可以在外部查询中轻松计算每个日期。

Demo here