mysql - 如何获取特定日期的唯一客户和重复客户每天的客户数量？

Question

我正在尝试从订单表中获取结果，以获取第一次订购和重复订单的客户计数列表。如下所示。

Date         1st time time   repeat order2014-09-01      43               902014-09-02       3               452014-09-03      12               302014-09-04      32                02014-09-05       1               98

我是 sql 初学者，我正在使用 mysql。

我的表结构是这样的。

OrderNumber intOrderDate datetimeCustomerID int

<小时/>我已经在 mysql 中尝试过这个查询，但它只给我第一次计时的有序计数。

SELECT DATE(OrderDate), COUNT(*) FROM orders T JOIN (SELECT MIN(OrderDate) as minDate, CustomerIDFROM ordersGROUP BY CustomerID) T2 ON T.OrderDate = T2.minDate AnD T.CustomerID = T2.CustomerIDGROUP BY DATE(T.OrderDate)

Answer 1

您可以通过按 OrderDate 分组来获取每天的总订单数:

SELECT OrderDate, COUNT(OrderNumber) AS total FROM orders GROUP BY OrderDate

你可以获得号码。来自以下查询的每天第一笔订单:

SELECT OrderDate, COUNT(q1.CustomerID) AS first FROM (SELECT CustomerID, min(OrderDate) AS OrderDate FROM orders GROUP BY CustomerID)q1 GROUP BY q1.OrderDate

现在将这两个订单在 OrderDate 上连接起来，以获得第一个订单和重复订单的分布:

SELECT a.OrderDate, a.first, (b.total - a.first) AS repeated FROM
(SELECT OrderDate, COUNT(q1.CustomerID) AS first FROM (SELECT CustomerID, min(OrderDate) AS OrderDate FROM orders GROUP BY CustomerID)q1 GROUP BY q1.OrderDate)a
JOIN
(SELECT OrderDate, COUNT(OrderNumber) AS total FROM orders GROUP BY OrderDate)b
on(a.OrderDate = b.OrderDate)