SQL基础学习笔记＜第三天＞

3.1 SQL时间

SELECT
	paidTime,
	date( paidTime ),
	date_format( paidtime, '%Y-%m-%d' ),
	DATE_ADD( paidtime, INTERVAL 1 DAY ) 
FROM
	test2.orderinfo

3.2 练习

1)统计不同月份的下单人数

SELECT MONTH
	( paidTime ) AS '月',
	count( DISTINCT userId ) AS '用户数' 
FROM
	orderinfo 
WHERE
	ispaid = '已支付' 
GROUP BY
	MONTH (
	paidTime)

结果如下:

2)三月份复购率与回购率

复购率

SELECT
	count( ct ),
	count(
	IF
	( ct > 1, 1, NULL )) 
FROM
	( SELECT userid, count( userid ) AS ct FROM orderinfo WHERE isPaid = '已支付' AND MONTH ( paidTime ) = 3 GROUP BY userid ) d

回购率

SELECT
	t1.m,
	count( t1.m ),
	count( t2.m ) 
FROM
	(
	SELECT
		userid,
		DATE_FORMAT( paidTime, "%Y-%m-01" ) m 
	FROM
		orderinfo 
	WHERE
		ispaid = '已支付' 
	GROUP BY
		userId,
	DATE_FORMAT( paidTime, "%Y-%m-01" )) t1
	LEFT JOIN (
	SELECT
		userid,
		DATE_FORMAT( paidTime, "%Y-%m-01" ) m 
	FROM
		orderinfo 
	WHERE
		ispaid = '已支付' 
	GROUP BY
		userId,
	DATE_FORMAT( paidTime, "%Y-%m-01" )) t2 ON t1.userid = t2.userid 
	AND t1.m = DATE_SUB( t2.m, INTERVAL 1 MONTH ) 
GROUP BY
	t1.m

结果如下:

3)统计男女用户的消费频次是否有差异

SELECT
	sex,
	avg( ct ) 
FROM
	(
	SELECT
		o.userid,
		sex,
		count( 1 ) AS ct 
	FROM
		orderinfo o
		JOIN ( SELECT * FROM `user.info` WHERE sex <> '' ) a ON o.userid = a.userid 
	WHERE
		isPaid = '已支付' 
	GROUP BY
		a.userid,
		sex 
	) t2 
GROUP BY
	sex

结果如下:

4)统计多次消费的用户 ，第一次消费和最后一次消费间隔是多少？

SELECT
	userid,
	max( paidTime ),
	min( paidTime ),
	datediff(
		max( paidTime ),
	min( paidTime )) 
FROM
	orderinfo 
WHERE
	isPaid = '已支付' 
GROUP BY
	userid 
HAVING
	count( 1 ) > 1

结果如下:

5)统计不同年龄段，用户的消费金额是否有差异

SELECT
	age,
	avg( ct ) 
FROM
	(
	SELECT
		o.userid,
		age,
		count( o.userId ) AS ct 
	FROM
		orderinfo o
		LEFT JOIN ( SELECT userid, CEIL(( YEAR ( now()) - YEAR ( birth ))/ 10 ) age FROM `user.info` WHERE birth > '1901-00-00' ) t ON o.userId = t.userid 
	WHERE
		ispaid = '已支付' 
	GROUP BY
		o.userid,
		age 
	) t2 
GROUP BY
	age

结果如下: