时间戳

首页 > 解决方案 > MYSQL查询每周获取新客户

mysql - MYSQL查询每周获取新客户

问题描述

我正在努力解决如何每周获得新客户的报告（那些以前从未订购过的客户）。结果应如下所示：

WEEK_ENDING_DATE   NEW_CUSTOMERS
----------------   -------------
2019-02-03         50
2019-02-10         60

我的查询执行 old_orders 和 new_orders 的右外连接以查找新客户（请参阅下面的查询）。

我还有一个名为 my_calendars 的辅助表，它可以帮助我按 week_end_date 进行分组。my_calendars 表的行包含一年中的每个日期、该日期对应的 week_begin_date 和 week_end_date。例如，对于像 2019-02-15 这样的日期，week_begin_date 是 2019-02-11，week_end_date 是 2019-02-17（周是周一 - 周日，格式 = YYYY-MM-DD）。帮助表如下所示：

DATE           WEEK_BEGIN_DATE      WEEK_END_DATE
----------     ----------------     -------------
2019-02-15     2019-02-11           2019-02-17
2019-01-08     2019-01-07           2019-01-13

现在，回到我的查询。我希望每周都能找到新客户。我遇到的问题是我无法弄清楚如何将一年中的每一周放在查询中以便比较订单日期。old_orders 是“本周”之前发生的订单，new_orders 是“本周”发生的订单。当我使用静态日期时，查询工作正常，但我正在努力使日期变量，即一年中的每周。在我遇到挑战的查询中查看我的问题。

SELECT 
    new_orders.week_end_date
    ,COUNT(DISTINCT new_orders.customer) AS new_customers       
FROM
      (SELECT * 
        FROM orders old
        INNER JOIN my_calendar cal ON cal.date = old.order_date
        #The line below works, but it's a static date of Feb 4. How do I replace it with each week in the calendar
        WHERE cal.week_end_date < '2019-02-04'
        #The commented line below does not work
        #WHERE cal.date < cal.week_end_date
      ) AS old_orders
RIGHT OUTER JOIN (SELECT * 
                  FROM order_items_view new
                  INNER JOIN my_calendar cal ON cal.date = new.order_date
                  #How do I replace the static dates below and compare with each week in the calendar  
                  WHERE cal.date BETWEEN '2019-02-04' and '2019-02-10'
                  #The commented line below didn't work
                  #WHERE cal.week_end_date = cal.week_end_date
                 ) AS new_orders
ON new_orders.customer = old_orders.customer
WHERE old_orders.customer IS NULL
GROUP BY new_orders.week_end_date

标签： mysqlsql

解决方案

我将首先使用聚合子查询来计算每个客户的第一个订单日期，然后将结果与日历表连接起来：

SELECT
    c.week_end_date,
    COUNT(o.customer) AS new_customers
FROM
    my_calendar AS c
    LEFT JOIN (
        SELECT customer, MIN(order_date) first_order_date
        FROM orders
        GROUP BY customer
    ) AS o ON c.date = o.first_order_date
GROUP BY c.week_end_date

推荐阅读