学习MySQL也有一阵子,从秦路老师的七周成为数据分析,到《对比EXCEL学习,轻松学习SQL》,再到刷牛客网的SQL题目。总结过SQL要点,也对比EXCLE总结过SQL,但还是觉得不成一体。
所以我想借此文,将自己现在掌握的MySQL一一呈现,也算给自己个交代。
至今学过的知识点:
- 表相关(创建,更新,删除表,插入值)
- 简单查询(select)
- 复杂查询:子查询,多表查询,聚合函数(group by)的使用
- 表连接(left/right/inner/outer/cross)
- 行转列问题(归根结底case when end的用法)列转行(union)
- SQL中常用高级函数相关的题目【case when end】【字符串相关:substr/concat/split】
- 基础进阶【日期函数】【组内排序:row_number()/rank()/dense_rank()】【取百分比】【窗口函数】 Reference: 无眠:数据分析面试必备——SQL你准备好了吗?
- 运营数据分析:复购率、回购率
- DAU的分析,次日留存、三日留存、七日留存,及留存率
不熟悉的SQL函数
1)取百分比可以用【percentile】
但不知为何我的电脑里不存在percentile,用法记录在此
#想要获取top10%的值?—— percentile 百分位函数
-- 获取income字段的top10%的阈值
select percentile(CAST (salary AS int),0.9)) as income_top10p_threshold from table_1;
-- 获取income字段的10个百分位点
select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentiles
from table_1;
#CAST它常用于string/int/double型的转换。
查找前20%的数据,我用以下方法
【解题思路】①用row_rumber()对数据进行降序排名,得到ranking ②ranking<=(select max(ranking) from 表)
#查找前20%数据(拼多多面试题)
用户访问次数表,列名包括用户编号、用户类型、访问量。要求在剔除访问次数前20%的用户后,每类用户的平均访问次数。
SELECT t.用户类型,avg(t.访问量) from
(SELECT *,row_number() over (ORDER BY 访问量 desc) as ranking from 拼多多访问) t
where t.ranking>(SELECT count(用户编号) from 拼多多访问)*0.2
GROUP BY t.用户类型;
2)字符串相关
- substr/concat/split
- concat( A, B...)返回将A和B按顺序连接在一起的字符串,如:concat('foo', 'bar') 返回'foobar'
select concat('www','.iteblog','.com') from
iteblog;
--得到 www.iteblog.com
#变形①--以分隔符连接
SELECT CONCAT_WS(',','GOOD','JOB')
---得到GOOD,JOB
#变形②--同组连接
select dept_no,group_concat(emp_no) as employees from dept_emp
group by dept_no
;
--将表dept_emp中相同dept_no的emp_no连接(from牛客网53题)
2. split(str, regex)用于将string类型数据按regex提取,分隔后转换为array。???
-- 以","为分隔符分割字符串,并转化为array
Select split("1,2,3",",")as value_array from table_1;
-- 结合array index,将原始字符串分割为3列
select value_array[0],value_array[1],value_array[2] from
(select split("1,2,3",",")as value_array from table_1 )t
3. substr(str,0,len) 截取字符串从0位开始的长度为len个字符。
select substr('abcde',3,2) from
iteblog;
-- 得到cd
- 字符串长度/替换
查找字符串'10,好,B' 中逗号','出现的次数cnt(第51题)
#总长度-剔除(,)长度
#剔除(,)用replace
SELECT CHAR_LENGTH('10,好,B')-CHARACTER_LENGTH(replace('10,好,B',',',''))
注意:char_length()基于字符计数,length()基于字节计数
SELE