MySQL 查询统计订单

昨天运营的同事突然让帮忙查询统计数据，有几个数据之前没有处理过，遂记录下来。

1. 每日付费的总人数。

一开始看到这个的时候觉得很简单，只要按照购买日期分组就可以了，SQL如下

SELECT COUNT(1),
		DATE(create_time) AS buy_date
FROM stock_app.t_order
WHERE DATE(create_time) 
BETWEEN '2019-02-14' 
AND '2019-04-23'
GROUP BY buy_date
ORDER BY buy_date

但是查完才发现数据对不上，因为同一个用户可能一天会购买不止一单，但是只能算一个，所以还需要做去重处理，SQL如下

SELECT COUNT(DISTINCT userid),
		DATE(create_time) AS buy_date
FROM stock_app.t_order
WHERE DATE(create_time) 
BETWEEN '2019-02-14' 
AND '2019-04-23'
GROUP BY buy_date
ORDER BY buy_date

COUNT(DISTINCT userid) 可以对相同的userid进行去重处理。

2. 首付费人数

顾名思义就是第一次购买的用户，我们可以先查询出每个用户的首单的订单号，SQL如下

SELECT order_id
FROM  stock_app.t_order a
RIGHT JOIN (
	SELECT MIN(create_time) AS first_order_time,userid
	FROM stock_app.t_order
	GROUP BY userid
) b ON a.userid = b.userid AND a.create_time = b.first_order_time

然后将查询结果作为子查询的条件的做分组查询，SQL如下

SELECT COUNT(1),DATE(create_time) AS buy_date
FROM stock_app.t_order
WHERE DATE(create_time) BETWEEN '2019-02-14' AND '2019-04-23'
AND order_id IN (
	SELECT order_id
	FROM  stock_app.t_order a
	RIGHT JOIN (
		SELECT MIN(create_time) AS first_order_time,userid
		FROM stock_app.t_order
		GROUP BY userid
	) b ON a.userid = b.userid AND a.create_time = b.first_order_time
)
GROUP BY buy_date
ORDER BY buy_date

3. 重复购买用户数

需要先查出一天购买多次的用户和订单时间，SQL如下

SELECT  userid,
	DATE(create_time) AS buy_date
FROM stock_app.t_order 
WHERE DATE(create_time) 
BETWEEN '2019-02-14' 
AND '2019-04-23'
GROUP BY userid,buy_date 
HAVING COUNT(userid) > 1
ORDER BY buy_date

再分组查询每天的重复用户数，如下

SELECT COUNT(1),buy_date
FROM
(
	SELECT  userid,
		DATE(create_time) AS buy_date
	FROM stock_app.t_order 
	WHERE DATE(create_time) 
	BETWEEN '2019-02-14' 
	AND '2019-04-23'
	GROUP BY userid,buy_date 
	HAVING COUNT(userid) > 1
	ORDER BY buy_date
) t1 
GROUP BY buy_date
ORDER BY buy_date

4. 首付费用户购买金额最多的产品

先分组查询出每天各个产品的销售金额，并按金额从大到小排序，再对查询结果按交易日期分组，如下

SELECT  product,buy_date
FROM 
(
	SELECT product,
			SUM(total_fee) AS sum_fee,
			DATE(create_time) AS buy_date
	FROM stock_app.t_order  a
	WHERE DATE(create_time) 
	BETWEEN '2019-02-14' 
	AND '2019-04-23'
	AND userid  NOT IN (
		SELECT userid 
		FROM stock_app.t_order 
		WHERE DATE(create_time) < DATE(a.create_time)
		GROUP BY userid
	)
	GROUP BY product,buy_date 
	ORDER BY buy_date,sum_fee DESC
) t1 
GROUP BY buy_date 

5. 重复购买用户购买金额最多的产品

这个第4个查询差不多，但是需要联表查询重复购买的用户，如下

SELECT  product,sum_fee,buy_date
FROM 
(
SELECT product,
		SUM(total_fee) AS sum_fee,
		DATE(create_time) AS buy_date
FROM stock_app.t_order  a
RIGHT JOIN(
	SELECT  userid,
		DATE(create_time) AS buy_date
	FROM stock_app.t_order 
	WHERE DATE(create_time) 
	BETWEEN '2019-02-14' 
	AND '2019-04-23'
	GROUP BY userid,buy_date 
	HAVING COUNT(userid) > 1
	ORDER BY buy_date 
) b ON a.userid = b.userid AND DATE(a.create_time) = b.buy_date
WHERE DATE(create_time) 
BETWEEN '2019-02-14' 
AND '2019-04-23'
GROUP BY product,buy_date 
ORDER BY buy_date,sum_fee DESC
) t1 
GROUP BY buy_date

仅供参考