Travel Tips
Lorem ipsum dolor sit amet, consectetur adipiscing elit.
MYSQl实践
MYSQl实践
根据user_info.csv和order_info.csv在data数据库中创建两个表
user_ifo.csv内容如下图
在数据库中创建一个userInfo数据表，里面字段和csv中字段相对应
右击userInfo数据表->导入向导->csv->选择csv文件->之后一直默认，把csv文件导入到数据库中。
相同道理把另一个csv也导入到数据库中。
新建查询
# 不同月份的人数
SELECT MONTH(paidTime),count(distinct userId) from `data`.orderinfo
where isPaid = "已支付"
GROUP BY month(paidTime)
# 统计用户三月份的回购率和复购率
select count(ct),count(if(ct>1,1,null)) from(
SELECT userId,count(userId) as ct
from `data`.orderinfo
where isPaid = "已支付"
and MONTH(paidTime) = '3'
GROUP BY userId) t
相处就是回购率
# 相除就是回购率
select count() FROM
where usreId in (子查询，算出三月份的userID） and month() = 4
select t1.m, COUNT(t1.m), COUNT(t2.m) from(
select userId,DATE_FORMAT(paidTime, '%Y-%m-%-01') as m from `data`.orderinfo
where isPaid = '已支付'
GROUP BY userId,DATE_FORMAT(paidTime,'%Y-%m-%-01')) t1
LEFT JOIN(
select userId,DATE_FORMAT(paidTime, '%Y-%m-%-01') as m from `data`.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
# 统计男女消费频次的差距
select sex, avg(ct) from(
SELECT o.userId, sex, count(1) as ct from `data`.orderinfo o
INNER JOIN(
SELECT * FROM `data`.userInfo
WHERE sex <> '') t
on o.userId = t.userId
GROUP BY userId, sex) t2
GROUP BY sex
# 统计多次消费用户第一次和最后一次的
SELECT userId, MAX(paidTime), min(paidTime), DATEDIFF(MAX(paidTime),min(paidTime))
FROM `data`.orderinfo
where isPaid = '已支付'
GROUP BY userId HAVING count(1) > 1
# 不同年龄段，消费之间是否有差异
select age,avg(ct） from(
select o.userId,age,count(o.userId) as ct from `data`.orderinfo o
inner join(
select userId,ceil((year(NOW()) - YEAR(userTime))/10) as age from `data`.user_info_utf
where userTime > '1901-00-00') t
on o.userId = t.userId
GROUP BY o.userId, age)t2
GROUP BY age
# top20%贡献多少额度
select userId,SUM(price) as total from `data`.orderinfo o
where isPaid = '已支付'
GROUP BY userId
ORDER BY total desc
降序排序了消费额度
select count(userId),SUM(total)
from(
select userId,SUM(price) as total from `data`.orderinfo o
where isPaid = '已支付'
GROUP BY userId
ORDER BY total desc
LIMIT 17000) t
前20%的用户占比了75%左右的额度
Sed ac lorem felis. Ut in odio lorem. Quisque magna dui, maximus ut commodo sed, vestibulum ac nibh. Aenean a tortor in sem tempus auctor
Agatha Christie
December 4, 2020 at 3:12 pm
Donec in ullamcorper quam. Aenean vel nibh eu magna gravida fermentum. Praesent eget nisi pulvinar, sollicitudin eros vitae, tristique odio.
Danielle Steel
December 4, 2020 at 3:12 pm
我是 s enim interduante quis metus. Duis porta ornare nulla ut bibendum
Rosie
6 minutes ago