1 什么是指标，互联网行业常见的指标有哪些，分别是怎么计算的？
2 常用分析方法有哪些
今日内容
- 复习mysql数据库
- 库表记录的增删改查
- 开窗函数
课前提问
提问：若面试时，面试官问公司最近离职率有所下降，你该如何阐释你的分析思路
1 明确问题
时间：这个数据是哪段时间的离职率低
地点：是哪个部门或者哪个团队汇报
事件：聚焦到哪个部门，或者范围
跟谁比
离职率计算的方式
2 分析思路
多维度拆解：新老员工
老员工：内部：薪酬福利 外部诱惑
新员工：内部：无法融入新环境 福利待遇不够 无法认为公司企业文化
3 获取数据
复习数据库部分
1 什么是数据库？什么是SQL？
科学地组织和存储数据，如何高效获取和维护数据
2 一条SQL语句的执行过程
SQL语句就是一个数据库能够识别的指令语言
3 库的增删改查
# 查看库
show databases;
# 创建某个库
create database db1 charset utf8;
# 查看某个库
show create database db1;
# 修改库的编码
alter database db1 charset
# 删除某个库
drop database db1;
4 表的增删改查
增
create table stu(id int primary key auto_increment,name char(16) not null,age int not null) # 数据类型 字符串 char(6) varchar(6) sql_mode 截断 以空间换时间 整型 int 小数 float(5,2) decimal double 日期 date datetime 枚举：enum set() # 约束条件 not null unique primary key 索引密切相关 查询效率 foreign key
问题：
1 char与varchar的区别
1 定长和变长
2 存储方式
char存取速度快 varchar节省空间
改
# 修改表的名称
alter table stu rename stu_new;
# 修改表的数据类型
alter table stu_new modify name varchar(16);
# 修改表的字段名及数据类型（修改完字段名需要带上数据类型和约束条件）
alter table stu_new change name name_new char(16) not null;
# 新增字段
alter table stu_new add sex enum("男","女");
# 删除字段
alter table stu_new drop name,drop age;
查
# desc 表名
desc stu_new;
# show create table 表名 G;
show create table stu_new G;
删
# drop table 表名
drop table stu_new;
5 记录的增删改查
增
# 语法1：insert into 表名 values(值1,值2,值3....) # 语法2：insert into 表名(字段1，字段2) values (值1，值2)
改
# 语法：update 表名 set 字段 = 新值 where 条件
删
delete from 表名 where 条件；
查（单表查询+多表查询）
单表查询
语法: select distinct 字段1,字段2 [,...] from 表名 where 条件 group by field having 筛选条件 order by filed limit 条数 注: group by field 根据什么进行分组,一般是某个字段或多个字段 order by filed 根据什么进行排序,一般是某个字段或多个字段 having主要配合group by使用,对分组后的数据进行过滤,里面可以使用聚合函数 where是针对select查询的过滤,各有区别和用处 优先级: from where group by select distinct having order by limit 解释说明: 1.先找到表:from 2.拿着where指定的约束条件,去表中取出符合条件的一条条数据 3.将取出的数据进行分组group by,如果没有group by,则每行为一组 4.执行select 查询所指定的字段 5.若有distinct 则去重 6.将结果按照条件排序 order by 7.限制结果的显示条数 limit
问题：为什么having后面可以跟聚合函数，而where却不可以
having主要配合group by使用,对分组后的数据进行过滤,里面可以使用聚合函数,聚合函数只能配合group by使用 where是针对select查询的过滤,各有区别和用处
单表查询所需数据
create table emp( id int primary key auto_increment, emp_name char(20) not null, sex enum("male","female") not null default "male", age int(3) unsigned not null default 28, hire_date date not null, post char(50), post_comment char(100), salary double(15,2), office int, depart_id int); -- 插入数据 -- 以下是教学部 insert into emp(emp_name,sex,age,hire_date,post,salary,office,depart_id) values ('huahua','male',18,'20170301','teacher',7300.33,401,1), ('weiwei','male',78,'20150302','teacher',1000000.31,401,1), ('lala','male',81,'20130305','teacher',8300,401,1), ('zhangsan','male',73,'20140701','teacher',3500,401,1), ('liulaogen','male',28,'20121101','teacher',2100,401,1), ('aal','female',18,'20110211','teacher',9000,401,1), ('zhugelang','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), -- 以下是销售部门 ('歪歪','female',48,'20150311','sale',3000.13,402,2), ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), -- 以下是运营部门 ('张野','male',28,'20160311','operation',10000.13,403,3), ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3)
题目
1 查询姓名末尾的那个字符 数据库：substring(string,position,length)---Excel：mid(文本,截止字符的起点，截多长) SELECT SUBSTRING(emp_name,-1) from emp; SELECT SUBSTRING(reverse(emp_name),1,1) from emp; 2 查询以"张"开头的三个字的员工信息 数据库：(_ %)---------- Excel(？*) SELECT * from emp WHERE emp_name like "程__"; 3 查询以"z"开头的员工信息 SELECT * from emp WHERE emp_name like "z%"; 4 计算每个部门都有多少人？大于6个人的部门有哪些？ SELECT post,count(post) from emp GROUP BY post; SELECT post,count(post) from emp GROUP BY post HAVING count(post)>=6; 5 计算每个部门的平均工资并从高到低排序 SELECT post,avg(salary) from emp GROUP BY post ORDER BY AVG(salary) DESC;
多表联查
多表查询数据
create table dep( id int primary key, name char(20) ); create table emp( id int primary key auto_increment, name char(20), sex enum("male","female") not null default "male", age int, dep_id int ); -- 插入数据 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('ailsa','male',18,200), ('lala','female',48,201), ('huahua','male',38,201), ('zhangsan','female',28,202), ('zhaosi','male',18,200), ('shenteng','female',18,204) ; 题目
题：
1 连表查询 查询每个部门的员工信息？查询每个员工所在部门信息？查询所有员工及所有部门的员工部门信息？ 2 子查询 查询平均年龄在25岁以上的部门名 3 查询大于所有人平均年龄的员工与年龄
复杂的多表查询
准备数据
CREATE TABLE class ( cid int(11) NOT NULL AUTO_INCREMENT, caption varchar(32) NOT NULL, PRIMARY KEY (cid) ) ENGINE=InnoDB CHARSET=utf8; INSERT INTO class VALUES (1, '三年二班'), (2, '三年三班'), (3, '一年二班'), (4, '二年九班'); CREATE TABLE teacher( tid int(11) NOT NULL AUTO_INCREMENT, tname varchar(32) NOT NULL, PRIMARY KEY (tid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO teacher VALUES (1, '张磊老师'), (2, '李平老师'), (3, '刘海燕老师'), (4, '朱云海老师'), (5, '李杰老师'); CREATE TABLE course( cid int(11) NOT NULL AUTO_INCREMENT, cname varchar(32) NOT NULL, teacher_id int(11) NOT NULL, PRIMARY KEY (cid), KEY fk_course_teacher (teacher_id), CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO course VALUES (1, '生物', 1), (2, '物理', 2), (3, '体育', 3), (4, '美术', 2); CREATE TABLE student( sid int(11) NOT NULL AUTO_INCREMENT, gender char(1) NOT NULL, class_id int(11) NOT NULL, sname varchar(32) NOT NULL, PRIMARY KEY (sid), KEY fk_class (class_id), CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO student VALUES (1, '男', 1, '理解'), (2, '女', 1, '钢蛋'), (3, '男', 1, '张三'), (4, '男', 1, '张一'), (5, '女', 1, '张二'), (6, '男', 1, '张四'), (7, '女', 2, '铁锤'), (8, '男', 2, '李三'), (9, '男', 2, '李一'), (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四'); CREATE TABLE score ( sid int(11) NOT NULL AUTO_INCREMENT, student_id int(11) NOT NULL, course_id int(11) NOT NULL, num int(11) NOT NULL, PRIMARY KEY (sid), KEY fk_score_student (student_id), KEY fk_score_course (course_id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO score VALUES (1, 1, 1, 10), (2, 1, 2, 9), (5, 1, 4, 66), (6, 2, 1, 8), (8, 2, 3, 68), (9, 2, 4, 99), (10, 3, 1, 77), (11, 3, 2, 66), (12, 3, 3, 87), (13, 3, 4, 99), (14, 4, 1, 79), (15, 4, 2, 11), (16, 4, 3, 67), (17, 4, 4, 100), (18, 5, 1, 79), (19, 5, 2, 11), (20, 5, 3, 67), (21, 5, 4, 100), (22, 6, 1, 9), (23, 6, 2, 100), (24, 6, 3, 67), (25, 6, 4, 100), (26, 7, 1, 9), (27, 7, 2, 100), (28, 7, 3, 67), (29, 7, 4, 88), (30, 8, 1, 9), (31, 8, 2, 100), (32, 8, 3, 67), (33, 8, 4, 88), (34, 9, 1, 91), (35, 9, 2, 88), (36, 9, 3, 67), (37, 9, 4, 22), (38, 10, 1, 90), (39, 10, 2, 77), (40, 10, 3, 43), (41, 10, 4, 87), (42, 11, 1, 90), (43, 11, 2, 77), (44, 11, 3, 43), (45, 11, 4, 87), (46, 12, 1, 90), (47, 12, 2, 77), (48, 12, 3, 43), (49, 12, 4, 87), (52, 13, 3, 87);
题目
1 查询成绩最好的前两名学生姓名 select * from stu where num = (select max(num) from stu) union all select * from stu where num = (select max(num) from stu where num<(select max(num) from stu)); 2 查询每门课程成绩最好的前两名学生姓名 select s.* from stu s left join (select max(num) max_num,cname from stu group by cname ) s1 on s.cname = s1.cname where num = max_num union all select s.* from stu s left join (select s.cname,max(num) second_num from stu s left join (select max(num) max_num,cname from stu group by cname ) s1 on s.cname = s1.cname where num < max_num group by s.cname) m on s.cname = m.cname where s.num = second_num order by cname,num desc
创建个视图(虚拟的表)
不建议建视图，不建议修改视图，它本身是一个虚拟得表， 查询效率不高，灵活性也不够强
如果涉及得数据量不大，而且这段数据经常用就可以创建一个视图
create view stu as select s.sid,gender,sname,caption,cname,tname,num from student s left join class c on s.class_id = c.cid left join score s1 on s.sid = s1.student_id left join course c1 on s1.course_id = c1.cid left join teacher t on t.tid = c1.teacher_id
窗口函数
窗口函数实现 查询每门课程成绩最好的前两名学生姓名
select * from (select *,dense_rank() over(order by num desc) as rank_num from stu) as e where rank_num<=2;
窗口函数介绍
语法
完整语法
函数名([字段]) over(partition by 字段名 order by 字段名)
聚合函数：sum count avg max min
排名函数 row_number rank dense_rank ntile
其他：lag lead first_value last_value
聚合开窗的用法
函数名如果是聚合函数，则成为聚合开窗函数
语法：聚合函数(列) over(partition by 列 order by 列)
常见的聚合函数有：sum() count() average() max() min()
-- 题目1 计算每个学生的及格科目数 select sname,count(sname) from stu where num>=60 group by sname; select *,count(sname) over(partition by sname) 及格的个数 from stu where num>=60 order by sname; -- 每个人的成绩与自己总的平均分的差距 select *, avg(num) over(partition by cname order by cname) as avg_score from stu where num>0;
-- 每个人的成绩与科目平均分的差距
select *,AVG(num) over(partition by cname ORDER BY cname) as avg_score from stu where num>0;
总结：
我们会发现开窗函数不会修改源数据表的结构，也是在表的最后一列添加想要的结果，如果分组存在多行数据，则重复显示，因此对于既想要分组结果，又不想改变数据表的结构时，使用开窗函数效果非常好，但是对于聚合开窗函数来说，个人觉得大部分情况下还是采用聚合函数比较多。
排名开窗函数
排名开窗函数中必须有order by
row_number（行号）
rank（排名）
dense_rank（密集排名）
ntile（分组排名）
-- 对每门课程进行分组排序，然后取出前三名即可
select s.sid,s1.sname,s1.gender,c.cname,s.num, row_number() over (partition by c.cname order by num desc) as row_number排名, rank() over (partition by c.cname order by num desc) as rank排名, dense_rank() over (partition by c.cname order by num desc) as dense_rank排名, ntile(6) over (partition by c.cname order by num desc) as ntile排名 from score s join student s1 on s.student_id = s1.sid left join course c on s.course_id = c.cid
我们一个一个来分析
row_number
原理：根据课程进行分组，然后对每组内的成绩进行降序排序
我们可以看出row_number对于同组内的相同成绩并没有做特殊处理，而仅仅是生成连续的序号，因此用row_number 做成绩排序貌似不准备，当然它通常也不用在此处，这里只是为了方便对比，row_number 常用于按照某列生成连续序号，例如web程序的分页等等
rank（重点）跳跃式排名
rank函数用于返回结果集的分区内每行的排名，简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，通过上面的例子我们也可以看出，rank考虑到值相同情况，并且它的排名存在跳跃性。
dense_rank（重点）密集排名
从字面意思理解，密集排名，也是他在考虑了值相同时排名也相同，但是序号不跳跃，紧跟上一个序号，例如题目中体育成绩有2位同学(张三和刘三)并列第一，如果使用rank排名 ，那钢蛋就是第三名，而如果采用dense_rank 那钢蛋就是第二名，这个很容易理解吧。
utile
我们从代码中可以看出，ntile()中有个数字，那其实ntile有一个叫“桶”的概念
原理是这样的
首先，ntile会先根据你的分组依据，本题中是课程名称，然后把每个组的总记录数进行按照你给的ntile()里的数字进行等分，这个数字就是桶数，相当于是把体育课程总共12条记录，尽量等划分成6桶，然后按照num的排序等级划分，每个桶两条记录，也就是112233445566的排序结果了，很显然，这个排序结果的数字大小只能用于桶与桶之间，而桶内部记录虽然序号相同，但是num不一定相同。
回到本题当中
统计各科成绩前三，那很显然，采用dense_rank 更合适
代码如下
select * from (select s.sid,s1.sname,s1.gender,c.cname,s.num,dense_rank() over (partition by c.cname order by num desc) as dense_rank排名 from score s join student s1 on s.student_id = s1.sid left join course c on s.course_id = c.cid) as e where dense_rank排名 <= 3;
效果
作弊次数的案例
lag(哪一列数据 ，n)
用于统计窗口内往上第n行值
lead(c哪一列数据 ，n)
用于统计窗口内往下第n行值
这两个函数可以用于同列中相邻行的数据相减操作
需求：对于下面的数据，对于同一用户（uid）如果在2分钟之内重新登录，则判断为作弊，统计哪些用户有作弊行为，并计算作弊次数
select *,lead(login_time,1) over(partition by uid order by login_time) as new_time, timestampdiff(second, login_time, (lead(login_time, 1) over(partition by uid order by login_time)))/60 from lag_table;
作弊练习用的数据
create table lag_table(id int primary key, uid int not null, login_time datetime not null); insert into lag_table values(1,1,"2020-4-10 12:02:00"), (2,1,"2020-4-10 12:03:23"), (3,1,"2020-4-10 12:03:59"), (4,1,"2020-4-10 12:06:34"), (5,2,"2020-4-10 13:00:00"), (6,2,"2020-4-10 13:02:00"), (7,2,"2020-4-10 13:02:45")
对于下面的数据，对于同一用户（uid）如果在2分钟之内重新登录，则判断为作弊，统计哪些用户有作弊行为，并计算作弊次数
select uid,count(uid) 作弊次数 from
(select *,lead(login_time,1) over(partition by uid order by login_time) as new_time,
timestampdiff(second, login_time, (lead(login_time, 1) over(partition by uid order by login_time)))/60 相差秒数 from lag_table) as e
where 相差秒数 <=2 group by uid;
计算时间差: mysql 用 Excel:datedif(开始，结束，输出格式)
timestampdiff(时间输出格式，开始时间，结束时间）
first_value(column)
取分组内排序后，截止到当前行，第一个值
select s.sid,s1.sname,s1.gender,c.cname,s.num, first_value(num) over(partition by c.cname order by num desc) as first_value用法 from score s join student s1 on s.student_id = s1.sid left join course c on s.course_id = c.cid
根据分组排序后，每组按照排序后第一个值进行显示
last_value(column)
取分组内排序后，截止到当前行，最后一个值
select s.sid,s1.sname,s1.gender,c.cname,s.num, last_value(num) over(partition by c.cname order by num desc) as last_value用法 from score s join student s1 on s.student_id = s1.sid left join course c on s.course_id = c.cid
咦，为啥这里的last_value的用法不是按照每个组的最后一个值，也就是所谓的最小值来取值的呢？好像一个组中显示的结果也不一样，看着也没啥规律呀
其实，事实是这样的
last_value()默认统计范围是 rows between unbounded preceding and current row，也就是取当前行数据与当前行之前的数据的比较。
那我得改一下呀，这不是我们想要的效果，怎么改呢？
在order by 条件的后面加上语句：rows between unbounded preceding and unbounded following
可以理解为：当前分组数据中的所有数据进行比较，取最后一条记录
修改SQL
select s.sid,s1.sname,s1.gender,c.cname,s.num, last_value(num) over(partition by c.cname order by num desc rows between unbounded preceding and unbounded following) as last_value用法 from score s join student s1 on s.student_id = s1.sid left join course c on s.course_id = c.cid
达到了我们想要的效果
详细解释：
rows beteween 参数1+ 参数 2 and 参数1+ 参数 2
参数一选择：
unbounded 无限制的
current 当前
参数二选择：
preceding 分区的当前记录的向前偏移量
following 分区的当前记录的向后偏移量
附加思考
面试时有没有被问到过如何累计计算每个月的销售额
数据准备
某公司销售数据表
需求：计算每个月的销售额及累计销售额，结果如下：
select 年份,月份,sum(销售金额) 每月销售额,sum(sum(销售金额)) over(order by 月份 rows between unbounded preceding and current row) as 累计销售额 from sale group by 年份,月份;
作业
作业1
你日常生活中常用的APP有哪些？你觉得这款APP好在哪里？那你觉得这款APP有哪些地方使需要优化？ 第一点：不要说太过于成熟和大众的APP 第二点：读书类 健身类 旅游类 第三点：可以跟面试的岗位类似的APP keep： 1 内容优质，包含了健身 跑步 瑜伽 冥想，涵盖各个水平段的，课程比较合理易学 吸引了大量的用户，所以keep有很大的用户群 2 变现能力 指定健身计划，私人教练 会员制 收费的课程 自己的商城 运动服务器械 3 留住用户的能力：社区 达人分享 keeper的日常运动记录 饮食方面 提供积极正能量的信息，激keeper不断努力，加入运动计划 领徽章 打卡 优地方：变现能力有待提高，商城的产品还是比较单一，跟电商比起来没有太大市场；跑步模块的功能完善 它跟竞争对手去比：小米运动 小米手环
面试题目 这是一个关于淘宝店铺商品的相关信息的数据源 题目要求 1.找出一天中有多次更新的商品ID 2.对于一天内有多次更新的商品，只保留当天最后一条记录，去掉其他记录 3.分析商品的最后更新时间分布，看能否得出有价值的结论 数据源： taobao_data.csv 字段说明： item_id:商品ID shop_id:店铺ID insert_time:更新时间 volume:月销量 cprice:实际价格 一家真实的大型互联网公司的面试题，希望能够认真对待，另外，可以选择使用Excel SQL Python 任何一种方式实现， 由于利用python做数据分析还未讲，可以考虑先使用前两种，等讲完python再回过头来做这道题也是不错的方式。 这道题做完每个人把所做过程，包括自己的思路以Word的形式提交！