问题描述
我有两个表，customerusermap和users。每当用户注册我们的产品时，他们都会立即被添加到名为用户的表中，但是直到他们开始为用户付费时，他们才被添加到名为 customerusermap的表中。
I have two tables, customerusermap and users. Whenever a user signs up with our product, they immediately get added into a table called users but it isn't until they start paying for a user that they get added to a table called customerusermap.
users表如下：
id | customer_id | firstname | lastname | created_at
-------------------------------------------------------
1725 | cus_3hEmhErE2jbwsO | Abby | Smith | 2015-03-19
1726 | cus_7oNweUrE4jbwr2 | Sam | Peters | 2015-06-20
customerusermap表如下所示：
The customerusermap table looks like this:
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个用户付费。她从2015年7月5日开始为用户2358付费，因此应将她视为付费客户07-2015，而不是03-2015。 Sam正在为2位用户付费，并且他在08-2015年开始为用户8094付费，因此他被视为08-2015年的付费客户，而不是06-2015年的付费客户。我有一个查询，它按每月付费客户的数量进行分组：
In this example, Abby(cus_3hEmhErE2jbwsO) is paying for 6 users. She started paying for user 2358 2015-07-05 so she should be considered a paying customer 07-2015, not 03-2015. Sam is paying for 2 users and he started paying for user 8094 in 08-2015 so he is considered to be a paying customer for 08-2015, not 06-2015. I have a query that grabs and groups by the number of paying customers each month:
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表中的日期？在此示例中，所需的输出应如下所示：
But this grabs and counts by the date the customer was added to the users table, not the date they actually started paying. How would I grab the counts so that it grabs for the earliest date in the customerusermap table? What the needed output should look like in this example is:
Month | Total AB Paying Customers
-------------------------------------
07-2015 | 1
08-2015 | 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 运算符来排除与为自己付费有关的记录客户（如果这确实是您的意图）。
I used a NOT EXISTS operator to exclude records that relate to 'paying for themselves' customers (if that is really your intention).
一旦您获得每个<$的 MIN（created_at）日期c $ c> customer_id ，那么您可以轻松地在外部查询中按日期进行计数。
Once you get the MIN(created_at) date per customer_id, then you can easily count per date in an outer query.
这篇关于用内部联接中发现的最早日期进行计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！