如何每天按唯一身份和重复客户在特定日期获得客户数?

问题描述

我正在尝试从订单表中获取结果，以获取第一次订购和重复订购的客户计数列表.像下面这样.

I am trying to get a result from my order table to get list of counts of customers who 1st time ordered and repeat orders. Something like below.

Date         1st time time   repeat order
2014-09-01      43               90
2014-09-02       3               45
2014-09-03      12               30
2014-09-04      32                0
2014-09-05       1               98

我是sql的初学者，我使用mysql.

I am beginner in sql and i ma using mysql.

我的表结构就像.

My table structure is like.

OrderNumber int
OrderDate datetime
CustomerID int

---

我已经在mysql中尝试过此查询，但它只给我第一次定时排序的计数.

---

I have tried this query in mysql but it only gives me first timed ordered count.

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

推荐答案

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

You can get the total orders per day by grouping on OrderDate:

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

您可以得到编号.以下查询每天的第一笔订单数:

And you can get the no. of first orders per day from the following query :

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上将这两个都加入，以获取第一笔订单和重复订单的分布:

Now join these two on OrderDate to get the distribution of first and repeated orders :

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)

这篇关于如何每天按唯一身份和重复客户在特定日期获得客户数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！