时间戳

首页 > 解决方案 > 提供每年/每月新客户和回头客的数量

mysql - 提供每年/每月新客户和回头客的数量

问题描述

嗨：从名为 的源表中orders，如下所示，我想获取每个月的新客户数量。我的源表如下所示：

  client|order_id|date_order    
    111|50|2019-01-03
    122|51|2019-02-05
    111|52|2019-02-03
    133|53|2019-03-13
    144|54|2019-04-23    
    122|55|2019-05-15
    133|56|2019-05-17
    161|62|2019-06-07
    163|65|2019-07-22
    172|66|2019-08-10
    111|84|2019-08-18     
    ...|...|...

我想每年，每月获得新客户与回头客的数量。这是输出应该如下所示：

    year|month|new|repeat_cust|total
    2019|01|1|0|1
    2019|02|1|1|2
    2019|03|1|0|1
    2019|04|1|0|1
    2019|05|0|2|2
    2019|06|1|0|1
    2019|07|1|0|1
    2019|08|1|1|2

Thank you for your help.

标签： mysqlsqldate

解决方案

您可以使用窗口函数（在 MySQL 8+ 中可用）获取某人第一次订购的时间，然后使用该信息进行聚合：

select year(date), month(date),
       count(distinct case when first_date = date then client end) as new,
       count(distinct case when first_date < date then client end) as repeat,
       count(distinct client) as total 
from (select t.*, min(date) over (partition by client) as first_date
      from t
      group by client
     ) t
group by year(date), month(date);

注意：根据您的定义，如果客户在购买的第一个月有两个订单，这可以计算两次。您可能实际上想要：

select year(date), month(date),
       count(distinct case when first_date = date then client end) as new,
       (count(distinct client) -
        count(distinct case when first_date = date then client end) 
       ) as repeat,
       count(distinct client) as total 
from (select t.*, min(date) over (partition by client) as first_date
      from t
     ) t
group by year(date), month(date);

编辑：

在早期版本中，您可以使用group byandjoin来获得相同的效果：

select year(date), month(date),
       count(distinct case when first_date = date then client end) as new,
       (count(distinct client) -
        count(distinct case when first_date = date then client end) 
       ) as repeat,
       count(distinct client) as total 
from t join
     (select t.client, min(date) as first_date
      from t
     ) c
     using (client)
group by year(date), month(date);

推荐阅读