表信息

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