SQL:如果以前存在计数>1时，则在给定月份中计数1。

Question

我在一个名为orders的Postgres表中有以下数据

month_year  order_id    customer_id
2016-04     0001        24662
2016-05     0002        24662
2016-05     0002        24662
2016-07     0003        24662
2016-07     0003        24662
2016-07     0004        24662
2016-07     0004        24662
2016-08     0005        24662
2016-08     0006        24662
2016-08     0007        24662
2016-08     0008        24662
2016-08     0009        24662
2016-08     0010        11372
2016-08     0011        11372
2016-09     0012        24662
2016-10     0013        24662
2016-10     0014        11372
2016-11     0015        24662
2016-11     0016        11372
2016-12     0017        11372
2017-01     0018        11372
2017-01     0019        11372  

在http://sqlfiddle.com/#!17/4efe6/1上使用SQL花招。

我希望每个月能统计出“重复顾客”的数量。我们可以将“重复”定义为让下订单(无论是上个月还是几年前)的客户。

例如，客户24662在2016年4月下了第一次订单。因此，在随后的任何一个月中，如果customer 24662下了另一个订单，那么他将在那个月的中得到一个1计数的。

我试过以下几点：

SELECT        
    month_year, 
    COUNT(DISTINCT(customer_id))
FROM 
    orders

GROUP BY 
    month_year

HAVING 
    COUNT(order_id) > 1

这意味着：

month_year   repeat_orders
2016-05      1
2016-07      1
2016-08      2
2016-10      2
2016-11      2
2017-01      1

但是，由于GROUP BY，这仅仅是根据一个客户在一个给定的月中是否有超过一个订单来计算1，而不是他/她是否有过以前的订单。

我正在寻找后者，并期望看到以下内容：

month_year     repeat_orders
2016-05        1
2016-07        1
2016-08        1
2016-09        1
2016-10        2
2016-11        2
2016-12        1
2017-01        1

如能提供任何协助，将不胜感激。谢谢!

Answer 1

自连接通常表现不佳。

当客户出现在数据中时，您可以使用window函数FIRST_VALUE获取第一个日期，并且只扫描一次表。在此之后，只需将当前日期与第一个日期进行比较，以确定这是否是重复的客户。

初步查询：

WITH
CTE
AS
(
  SELECT
    *
    ,FIRST_VALUE(month_year) OVER (PARTITION BY customer_id ORDER BY month_year) AS first_date
  FROM
    orders
)
SELECT
  *
FROM
  CTE
ORDER BY
  customer_id
  ,month_year
  ,order_id
;

这产生了以下结果：

| month_year | order_id | customer_id | first_date |
|------------|----------|-------------|------------|
|    2016-08 |     0010 |       11372 |    2016-08 |
|    2016-08 |     0011 |       11372 |    2016-08 |
|    2016-10 |     0014 |       11372 |    2016-08 |
|    2016-11 |     0016 |       11372 |    2016-08 |
|    2016-12 |     0017 |       11372 |    2016-08 |
|    2017-01 |     0018 |       11372 |    2016-08 |
|    2017-01 |     0019 |       11372 |    2016-08 |
|    2016-04 |     0001 |       24662 |    2016-04 |
|    2016-05 |     0002 |       24662 |    2016-04 |
|    2016-05 |     0002 |       24662 |    2016-04 |
|    2016-07 |     0003 |       24662 |    2016-04 |
|    2016-07 |     0003 |       24662 |    2016-04 |
|    2016-07 |     0004 |       24662 |    2016-04 |
|    2016-07 |     0004 |       24662 |    2016-04 |
|    2016-08 |     0005 |       24662 |    2016-04 |
|    2016-08 |     0006 |       24662 |    2016-04 |
|    2016-08 |     0007 |       24662 |    2016-04 |
|    2016-08 |     0008 |       24662 |    2016-04 |
|    2016-08 |     0009 |       24662 |    2016-04 |
|    2016-09 |     0012 |       24662 |    2016-04 |
|    2016-10 |     0013 |       24662 |    2016-04 |
|    2016-11 |     0015 |       24662 |    2016-04 |

最终查询

WITH
CTE
AS
(
  SELECT
    *
    ,FIRST_VALUE(month_year) OVER (PARTITION BY customer_id ORDER BY month_year) AS first_date
  FROM
    orders
)
SELECT
  month_year
  ,COUNT(DISTINCT CASE WHEN month_year > first_date THEN customer_id END) AS repeat_orders
  ,COUNT(DISTINCT CASE WHEN month_year = first_date THEN customer_id END) AS first_orders
FROM
  CTE
GROUP BY
  month_year
ORDER BY
  month_year
;

此查询产生以下结果：

| month_year | repeat_orders | first_orders |
|------------|---------------|--------------|
|    2016-04 |             0 |            1 |
|    2016-05 |             1 |            0 |
|    2016-07 |             1 |            0 |
|    2016-08 |             1 |            1 |
|    2016-09 |             1 |            0 |
|    2016-10 |             2 |            0 |
|    2016-11 |             2 |            0 |
|    2016-12 |             1 |            0 |
|    2017-01 |             1 |            0 |

性能

如果在(customer_id, month_year)上创建索引，则应该消除查询计划中的排序(即它将运行得更快)。

Answer 2

我认为查询将类似于：

SELECT 
 o1.month_year, 
 count(distinct o1.customer_id) 
from orders o1
left join 
 orders o2 on o1.customer_id = o2.customer_id
  and o1.month_year > o2.month_year
   and o1.order_id != o2.order_id
where o2.order_id is not null
group by month_year

它的开头和你所做的很相似。我们只是在orders表中添加一个自联接，以确定被评估的客户是否在之前的任何一个月中下了订单。我们还确保order_id不匹配，因为我在数据中看到了一些副本。这将产生预期的结果。

希望这有意义。如果你有什么后续行动的话，请告诉我。

编辑

基于“第一次客户”信息也可能需要的评论，我认为我们可以在这里使用CTE。

with first_customers as (
  select 
    customer_id,
    min(month_year) month_year
  from orders
  group by customer_id
), repeat_customers as (
  select distinct on (o.order_id)
    o.customer_id,
    o.month_year
  from orders o
  join first_customers fc on o.customer_id = fc.customer_id and o.month_year > fc.month_year
) select 
    o.month_year, 
    count(distinct fc.customer_id) first_customers, 
    count(distinct rc.customer_id) repeat_customers 
  from orders o
    left join first_customers fc on o.month_year = fc.month_year
    left join repeat_customers rc on o.month_year = rc.month_year
  group by o.month_year
  order by o.month_year

本质上，我们通过将订单数据按customer_id分组并使用最小month_year获取行来查找第一次客户。

寻找repeat_customers的逻辑也在其各自的cte中。它现在有点简化了，因为我们可以使用来自first_customers的信息来查看客户以前是否订购过

在最后的选择中，我们查询orders表并在两个cte上连接，并按月计算它们的不同的customer_ids。