3.1 SQL时间
SELECT
paidTime,
date( paidTime ),
date_format( paidtime, '%Y-%m-%d' ),
DATE_ADD( paidtime, INTERVAL 1 DAY )
FROM
test2.orderinfo
3.2 练习
![](https://i-blog.csdnimg.cn/blog_migrate/7f8caad21c12154bc68958604bf88863.png)
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
![](https://i-blog.csdnimg.cn/blog_migrate/c12d882d4a7f4fa1eaf193cb36e363b8.png)
回购率
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
结果如下: