㈠ 如何用sql分析电商用户行为数据（案例）
本文以“淘宝用户行为数据集”的分析全过程为例，展示数据分析的全过程
——使用工具：MySQL，Excel，Navicat，PowerBI
——分析类型：描述分析，诊断分析
——分析方法：漏斗分析，用户路径分析，RFM用户价值分析，活跃/存留分析，帕累托分析，假设验证分析。
（考虑到阅读体验文章中只放了SQL截图，如需PDF版本，再公众号后台回复“用户行为分析”领取）
（目录如下）
1.分析流程和方法
当没有清晰的数据看板时我们需要先清洗杂乱的数据，基于分析模型做可视化，搭建描述性的数据看板。
然后基于描述性的数据挖掘问题，提出假设做优化，或者基于用户特征数据进行预测分析找规律，基于规律设计策略。简单来说：
——描述性分析就是：“画地图”
——诊断性分析就是：“找问题”
——预测性分析就是 ：“找规律”
在数据分析中有两个典型的场景：
一种是有数据，没有问题：需要先整体分析数据，然后再根据初步的描述分析，挖掘问题做诊断性分析，提出假设，设计策略解决问题。
另一种是已经发现了问题，或者已经有了假设，这种做数据分析更偏向于验证假设。
2.淘宝用户行为分析
本次是对“淘宝用户行为数据集”进行分析，在分析之前我们并不知道有什么问题，所以需要先进行描述性分析，分析数据挖掘问题。
我们首先来看下这个数据集的元数据：
根据以上数据字段我们可以拿用户行为为主轴从纵深方向提出一些问题，然后再从数据中找答案
纵向：
——这个数据集中用户的日活跃和周活跃时间有什么规律吗？
——在当日活跃的用户次日，三日，四日……还有多少活跃？
深向：
——用户从浏览到购买的整体转化率怎么样？
——用户从浏览到购买的路径是怎么样子的？
——平台主要会给用户推送什么商品？
——用户喜欢什么类目？喜欢什么商品？
——怎么判断哪些是高价值用户 ？
下面是叮当整理的常用分析方法：
我们可以给前面的问题匹配一下分析方法，便于后面的分析：
为了便于后面的数据分析，在分析之前我们需要先对做一下清洗
看元数据（字段解释，数据来源，数据类型，数据量……）初步发现问题为之后的处理做准备。
确定缺失值范围，去除不需要字段，填充缺失内容
根据元数据格式和后续分析需要的格式对数据进行处理
去除重复值，异常值
——去除重复值：并把用户ID,商品ID，时间戳设置为主键
——异常值处理：查询并删除2017年11月25日至2017年12月3日之外的数据
查询并删除小于2017-11-25的
——验证数据：
——分析思路：
——SQL提数：
——Excel可视化：
活跃曲线整体为上升状态，同为周六日，12月2号，3号相比11月25日，26日活跃度更高。
用户在周六周日相比其他时间更活跃（周六周日为休息日，用户有更多时间）
一天内用户活跃的最高峰期为21点（用户在这个时间段空闲较多）
——分析思路：
——SQL提数：
列出每用户每天及当天后面又活跃的日期，并创建“活跃时间间隔表”用于后面求次日存留，三日存留……
对“活跃时间间隔表视图”引用进行分组统计，计算每日存留人数并创建视图
对存留人数表进行计算，统计活跃用户留存率
——Excel可视化：
——分析思路：
——SQL提数：
-把各种用户行为分离出来并创建视图方便后续查询用户行为数据
查询整体数据漏斗
——Excel可视化：
用户从浏览到购买整体转化率2.3%，具体主要在哪个环节流失还需要再细分用户路径分析
——分析思路：
——SQL提数：
——PowerBI可视化：
用户从浏览到购买的路径主要有4条，路径越长转化率越底
路径1：浏览→购买：转化率1.45%
路径2：浏览→加购物车→购买：转化率0.33
路径3：浏览→收藏→购买：转化率0.11%
路径4：浏览→收藏→加购物车→购买：转化率0.03%
——分析思路：
——SQL提数：
——Excel可视化：
——描述性分析：
浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越大在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越小，同阶梯内的商品越多。
浏览量TOP100的商品所属类目中，4756105，3607361，4357323三个类目浏览量远超其他类目。
——分析思路：
——SQL提数：
查询计算商品转化率，升序排列，取前100个
——Excel可视化：
——描述性分析：
从商品看：有17款商品转化率超过了1。
从类目看：这些商品所属类目分布均匀，除965809，4801426，2735466，2640118，5063620，4789432，2945933这7个类目之外，其他类目都只有一个商品在转化率TOP100的商品中。
——分析思路：
用户价值分析常用的分析方式是RFM模型
本次分析中的R,F,M具体定义（仅用于演示分析方法，无实际业务参考价值）：
——SQL取数与分析：
1）建立打分标准：先计算R,F的值，并排序，根据R,F值最大值和最小值得区间设计本次得打分标准
-查询并计算R，F值创建视图
-引用RF数值表，分别查询R,F的最大值和最小值
-结合人工浏览的建立打分标准
2）给R，F按价值打分
3）计算价值的平均值
4）用平均值和用户分类规则表比较得出用户分类
——Excel可视化
通过描述性分析得到可视化的数据后我们一般会先看一下是否符合业务常识
如果符合常识接下来我们会通过与行业平均数据和本产品的同比环比对比看是否正常，如果不正常就要找原因，设计解决方案，如果正常那就看是否有可以优化的地方。
我们首先来看一下这些描述性分析是否符合业务常识和指标是否正常：
1.活跃曲线整体为上升状态，同为周六日，12月2号，3号相比11月25日，26日活跃度更高。
2.用户在周六周日相比其他时间更活跃
3.一天内用户活跃的最高峰期为21点
4.从2017年11月15日致2017年12月3日，活跃用户新增38%
5.从2017年11月15日致2017年12月3日，活跃用户次日留存增长18.67%，当日的活跃用户留存也在快速增长，第七日留存比次日留存高18.56%。
6.用户从浏览到购买整体转化率2.3%
7.用户从浏览到购买的路径主要有4条，路径越长转化率越低。
8.浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越大在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越小，同阶梯内的商品越多。
9.浏览量TOP100的商品所属类目中，4756105，3607361，4357323三个类目浏览量远超其他类目。
10.从商品看：有17款商品转化率超过了1。
11.从类目看：这些商品所属类目分布均匀，除965809，4801426，2735466，2640118，5063620，4789432，2945933这7个类目之外，其他类目都只有一个商品在转化率TOP100的商品中。
根据以上诊断分析我们梳理出了以下假设，做假设验证。
假设1：这些商品中有高转化率的爆款商品
对比浏览量TOP5的商品，发现这些商品转化率在同一类目下并不高，假设不成立
假设2：4756105，3607361，4357323三个类目属于高频刚需类目
-创建类目购买频次表
-计算类目购买频次平均值
-查询4756105，3607361，4357323三个类目的购买频次
4756105，3607361，4357323三个类目的用户购买频次明显高于平均值，假设成立
假设3：有部分用户是未点击商详直接从收藏和购物车购买的。
用户不是直接从收藏和购物车购买的，只是后续复购未点击商详，假设不成立
假设4：淘宝推荐的商品主要是“同一类目下的高转化商品”
用Excel对浏览量TOP100的商品ID和转化率TOP100的商品ID进行去重，结果无重复值，假设不成立
3.结论：
1）用户活跃：用户活跃曲线整体呈上升趋势，在一周中周六，周日活跃度比平时更高，在一天中用户活跃曲线从凌晨4点开始往上升，在中午12点和下午5~6点有两个小低谷（吃饭），到晚上9点时活跃度达到顶峰。
2）用户留存：从2017年11月15日致2017年12月3日的用户留存数据来看，淘宝的用户留存数据较好，活跃用户次日留存增长18.67%，当日的活跃用户留存也在快速增长，第七日留存比次日留存高18.56%。
3）用户转化：整体转化2.3%，用户从浏览到购买的路径主要有4条，路径越长转化率越低。
4）平台推荐与用户偏好：从数据集中的数据来看，排除用户兴趣偏好标签，淘宝给用户用户推送的商品主要是高频刚需的类目，促使用户复购，流量回流平台。
以上结论受数据量和数据类型的影响，并不一定准确，仅用来练习数据分析方法。
（考虑到阅读体验文章中只放了SQL截图，如需PDF版本，再公众号后台回复“用户行为分析”领取）
㈡ sql怎麽写，求教
思路：
1、一般统计留存率的时候都会考虑到是某个时间段的数据
2、依据你得表结构是需要用分组(group by)与求和(sum)、去除重复登陆的人数(distinct
)、和内关联(inner join)的方式
实现：
select
(selectsum(distinct)不重复登陆人数from登陆表where登陆时间between-01-01'and-02-08')*1.0
/(selectcount(0)总人数from注册表)
*100
㈢ MySQL新增留存率
没错，留存的问题还没有写完，之前两篇把日、周、月当期活跃用户在后续周期的留存率问题解决了。但是还有个非常重要的指标，当期新增用户的留存率，这个指标也是很有价值的，我们必须要关注不同日期拉新用户的质量如何，看看不同时期新用户的后续留存情况，对后续拉新的时间选择也是有参考价值的。
其实实现也很简单，只需要在之前的基础上，先把当期的首次登陆用户找出来就行了。实现方式是，按照用户聚合，然后取日期最小值就能取出每个用户首次登陆日期了，SQL语句如下↓
然后就以此为基础，通过左连接把用户表格再连接一次，判断与首次登陆的日期相差多少天就行了，就能判断是第N天有活跃，就能计算N日留存和留存率了，SQL语句和结果如下↓
后面就和之前思路一样了，就可以求出日留存率情况了，SQL语句如下，解释可以看前面两篇。
然后按月实现方式和上一篇一样的思路，关联一个辅助表就行了，这里不详细解释了，可以参考上一篇，完整SQL语句和结果如下↓
那么按周的留存率也是一样的，SQL语句和结果如下↓
End
◆ PowerBI开场白
◆ Python高德地图可视化
◆ Python不规则条形图
㈣ 如下问题的sql语句怎么写
由于手上没有sql环境，我这边直接模拟一下,有错误的地方请指出:
1.每天每个地区播放次数最多的三首歌
selecttop3*from
(
selecta.area,b.songid,convert(varchar(30),b.date,111)as[每天],
sum(b.play_cnt)as[播放次数]
fromuseraleftjoinsongbona.userid=b.userid
groupbya.area,b.songid
)torderby[播放次数]desc
2.留存率
select*from(
selectb.*
fromuseraleftjoinsongbona.userid=b.userid
whereb.date>='2019-5-10:00:00'
andb.date<='2019-5-223:59:59'
)t
whereexists(
selectnullfromsongwheresong.songid=t.songid
and
convert(varchar(30),song.date,111)=
convert(varchar(30),dateadd(d,-1,t.date),111)
)
请试一下，如有疑问，及时沟通！
㈤ 求教各位大侠，请问一下SQL如何高效的计算留存率啊
具体的表呢，数据呢，什么都没有怎么计算啊
㈥ sql面试题6 滴滴出行二面笔试题
滴滴出行二面笔试题
开心鸭
一个玩数据分析的鸭鸭
已关注
8 人赞同了该文章
在喝奶茶的时候突然收到一封邮件，猝不及防的开始了滴滴出行国际部二面笔试。感觉题目还是比较基础的，我以为会有什么留存率一些比较困难的业务题，慌的我喝了几杯水，没想到十分钟就做完了，中途还接了个推销电话..
学生表:tb_student(name:学生姓名，id：学号，class：班级，in_time：入学时间，age:年龄，sex：性别，major：专业)
1)学生成绩表：tb_score(id：学号，course：课程，score：分数)
1. 筛选出2017年入学的“计算机”专业年龄最小的10位同学名单（姓名、学号、班级、年龄）
select name,id,class,age
from tb_student
where in_time='2017' and major='计算机'
order by age asc
limit 10
2.统计每个班同学各科成绩平均分大于80分的人数和人数占比
select c.class,
sum (case when c.avg_score >80 then 1 else 0 end) as ' 每个班同学各科成绩平均分大于80分的人数',
sum (case when c.avg_score >80 then 1 else 0 end)/count(c.id) as ' 每个班同学各科成绩平均分大于80分的人数占比',
from
(select a.id,a.class,avg(b.score) as avg_score
from tb_student a inner join tb_score b on a.id=b.id
group by 1)c
group by 1
参考：SELECT a.class,
sum(case when aaa.x > 80 then 1 else 0 end)as num_80 ,
(sum(case when aaa.x > 80 then 1 else 0 end)/count(*))as proportion
from tb_student a
INNER JOIN (SELECT id,avg(score)as x from tb_score GROUP BY id)as aaa
on a.id = aaa.id
GROUP BY class;
解析：写一个子查询，从score表中得到以学号分组的学生各科平均分。命名为aaa，将其与学生表内联结。再以class分组，得到以class分组的各班学生的平均分，最后通过case语句，sum聚合函数得到平均分>80分的计数，和所占各班总人数比例。
2)用户教育经历表：tb_user_e(uid：用户id，star_date：入学时间，end_date：毕业时间，degree：学历，school：学校，major：专业)
3)table1（id：自增id，money：费用）问题：按id顺序累加money，取出累计值与1000相差最小差值的id。
select id
from(
select id,abs(1000-sum(money) over(order by id))as sub
from money
order by sub )a
limit 1
这个重要！
4） Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 DepartmentId。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资第二高的员工。
seelct c.id
(select a.deparmentid,rank( ) over (partition by a.departmentid order by a.salary desc ) as rank_
employee a inner join department b on a.id=b.id)c
where c.rank_=2
写错了，emm 最开始好像并不需要join
㈦ sql计算留存率算法
select ( select count(distinct userid ) from 登录表 where 登录时间 = 今天 ) / ( select count(1) from 注册表 where 注册时间 = 昨天 ) as 户留存率