表信息
user1
1.统计不同月份的下单人数
SELECT MONTH(TIME),COUNT(DISTINCT customer) FROM user1
WHERE STATUS = '已支付'
GROUP BY MONTH(TIME)
2.统计用户三月份的复购率和回购率
回购率：本月买，下月继续购买
复购率：一个月份中买卖次数 > 2
计算复购率
逻辑：先利用t表统计3月份的顾客及每人的购买次数，最后从购买次数中筛选出大于1次的并计算复购率。
SELECT COUNT(ct),COUNT(IF(ct>1,1,NULL)),
COUNT(IF(ct>1,1,NULL))/COUNT(ct) AS rate FROM
(SELECT customer,COUNT(customer) AS ct FROM user1
WHERE STATUS = '已支付'
AND MONTH(TIME) = 3
GROUP BY customer) AS t
计算回购率
逻辑：一、先求出每个用户当月是否消费过SELECT customer,DATE_FORMAT(TIME,'%Y-%m') AS m >FROM user1 WHERE STATUS = '已支付'
通过group by去重筛选出每个月购买的人数 SELECT customer,DATE_FORMAT(TIME,'%Y-%m') AS m FROM user1 WHERE STATUS = '已支付' GROUP BY customer,DATE_FORMAT(TIME,'%Y-%m')
二、通过双表连接分别统计出两个月连续购买的人数
①第一个on条件：通过customer将两表相连
②第二个on条件：通过date_sub函数筛选出三四月仍有购买的客户
③最后用group by分组，目的是以t1..m作为主要列，进行筛选
④外部select条件的选择：
count(t1.m) -- 统计出三月购买的人数
count(t2.m) -- 统计出四月购买的人数
date_sub函数：从日期减去指定的时间间隔
使用date_sub函数，日期格式需要写成 y-m-d 的形式
SELECT t1.m,COUNT(t1.m),COUNT(t2.m) FROM
(SELECT customer,DATE_FORMAT(TIME,'%Y-%m-01') AS m FROM user1
WHERE STATUS = '已支付'
GROUP BY customer,DATE_FORMAT(TIME,'%Y-%m-01')) t1
LEFT JOIN
(SELECT customer,DATE_FORMAT(TIME,'%Y-%m-01') AS m FROM user1
WHERE STATUS = '已支付'
GROUP BY customer,DATE_FORMAT(TIME,'%Y-%m-01')) t2
ON t1.customer = t2.customer AND t1.m = DATE_SUB(t2.m,INTERVAL 1 MONTH)
GROUP BY t1.m
只使用一个on条件的结果
使用两个on条件的结果，筛选出比两月均有购买的数据
最后的结果，两者相除即回购率
统计男女消费的差异
①先过滤掉性别为空的行
SELECT * FROM user2 WHERE sex <> ' '②将两张表联系起来
SELECT customer,sex,COUNT(1) AS ct FROM user1 t1 INNER JOIN( SELECT * FROM user2 WHERE sex <> ' ') t2 ON t1.id = t2.id GROUP BY customer,sex③以sex分组，求平均值
注意：使用avg(count(1))会报错SELECT sex,AVG(ct) FROM( SELECT customer,sex,COUNT(1) AS ct FROM user1 t1 INNER JOIN( SELECT * FROM user2 WHERE sex <> ' ') t2 ON t1.id = t2.id GROUP BY customer,sex) t3 GROUP BY sex
统计多次消费的用户，第一次和最后一次是否有差异
datediff -- 计算两日期间隔的天数
SELECT customer,MAX(TIME),MIN(TIME), DATEDIFF(MAX(TIME),MIN(TIME)) FROM user1 WHERE STATUS = '已支付' GROUP BY customer
不同年龄段，用户消费的金额是否有差异
①利用ceiling函数（向下取整），将用户的年龄段进行分类，因为birth中存在控制，利用where条件除去
SELECT CEILING((YEAR(NOW()) - YEAR(birth)) / 10) FROM user2
WHERE birth > '1901-00-00'
②将其与user1相连，目的是求出各年龄段消费的平均金额
SELECT age,AVG(money) FROM user1 t2
INNER JOIN(
SELECT id,CEILING((YEAR(NOW()) - YEAR(birth)) / 10) AS age FROM user2
WHERE birth > '1901-00-00' ) t1
ON t1.id = t2.id
WHERE STATUS = '已支付'
GROUP BY age
统计消费的二八法则，消费的top20%用户，贡献了多少额度
①先统计一共有多少个结果，要算top20%，即count(customer)的20%
SELECT COUNT(customer) * 0.2,SUM(total) FROM(
SELECT customer,SUM(money) AS total FROM user1
WHERE STATUS = '已支付'
GROUP BY customer
ORDER BY total DESC
) t1
②top20%的顾客及消费金额
SELECT customer,SUM(money) AS total FROM user1
WHERE STATUS = '已支付'
GROUP BY customer
ORDER BY total DESC
LIMIT 500
③top20消费的总额
SELECT COUNT(customer),SUM(total) FROM(
SELECT customer,SUM(money) AS total FROM user1
WHERE STATUS = '已支付'
GROUP BY customer
ORDER BY total DESC
LIMIT 500) t
网友评论