数据分析中常见的SQL语句

1.表信息

假设有订单表orderinfo（orderId，userId，isPaid， price， paidTime）

用户表userinfo（userId， sex，birth）

2. 业务需求

统计不同月份的下单人数

统计用户三月份的回购率和复购率

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

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

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

统计消费的二八法则，消费的top20%用户，贡献了多少额度

 

3.SQL

统计不同月份的下单人数

select month(paidTime), count(distinct userId) 

from orderinfo

where isPaid = '已支付'

group by month(paidTime)

统计用户三月份的回购率和复购率

三月份的回购率 = 三月份购买四月份也购买的人数 / 三月份购买人数

每个月份的回购率

select month1, count(t2.month2) / count(*)

from

(select userId, date_format(paidTime, "%Y-%m-01") as month1 from orderinfo where isPaid = 'yizhifu'  group by userId, month1) t1

left join

(select userId, date_format(paidTime, "%Y-%m-01") as month2 from orderinfo where isPaid = 'yizhifu' group by userId, month2) t2

on t1.userId = t2.userId

where date_sub(month2, interval 1 month) = month1

group by month1

 

三月份的复购率 = 三月份购买多次的人数 / 三月份购买的人数

select count(if(freq>1, 1, null)) / count(*) from 

( select userId, count(*) as freq from orderinfo where isPaid = '已支付' and month(paidTime) = 3 groupby userId) t

 

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

select sex, avg(freq)

from

(select userId, count(*) as freq from orderinfo  where isPaid = '已支付' group by userId) t1

left join userinfo t2 on t1.userId = t2.userinfo

group by sex

 

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

select userId, date_diff(max(paidTime)-min(paidTime))

from orderinfo 

where isPaid = '已支付'

group by userId 

having count(*) > 1

 

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

select age, avg(total) 

from

(select userId, sum(price) as total from orderinfo where isPaid = '已支付' group by userId) t1

join

(select userId, ceil(year(now()) - year(birth)) as age from userinfo) t2

on t1.userId = t2.userId

group by age

 

统计消费的二八法则，消费的top20%用户，贡献了多少额度

select count(*)  as user_total, sum(price_per) as total_price

from

(select userId, sum(price) price_per from orderinfo where isPaid = '已支付'  group by userId) t

select sum(price_per)

from 

(select userId, sum(price) price_per from orderinfo where isPaid = '已支付'  group by userId order by price_per desc limit 0.2 * 总用户数) t

 

采用ROW_NUMBER() 

select sum(price_total) as all_price, sum(if(index_row>count(*) * 0.2, price_total, 0)) as top_price

from

(select ROW_NUMBER() over (order by price_total desc) as index_row, * 

from

（select userId, sum(price) price_total from orderinfo where isPaid = 'yizhifu' group by userId）t1

) t2