SQL数据分析题

分析题"/>

SQL数据分析题

题目1 找出每个部门工资第二高的员工

现有一张公司员工信息表employee，表中包含如下4个字段。

• employee_id（员工ID）：VARCHAR。

• employee_name（员工姓名）：VARCHAR。

• employee_salary（员工薪资）：INT。

• department（员工所属部门ID）：VARCHAR。

还有一张部门信息表department，表中包含如下两个字段。

• department_id（部门ID）：VARCHAR。

• department_name（部门名称）：VARCHAR。

数据导入的代码如下：

DROP TABLE IF EXISTS employee;
CREATE TABLE employee(
employee_id VARCHAR(8),
employee_name VARCHAR(8),
employee_salary INT(8),
department VARCHAR(8)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
employee (employee_id,employee_name,employee_salary,department)
VALUE ('a001','Bob',7000,'b1'),('a002','Jack',9000,'b1'),('a003','Alice',8000,'b2'),('a004','Ben',5000,'b2'),('a005','Candy',4000,'b2'),('a006','Allen',5000,'b2'),('a007','Linda',10000,'b3');
​
DROP TABLE IF EXISTS department;
CREATE TABLE department(
department_id VARCHAR(8),
department_name VARCHAR(8)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
department (department_id,department_name)
VALUE ('b1','Sales'),('b2','IT'),('b3','Product');

问题：查询每个部门薪资第二高的员工信息。

输出内容包括：

• employee_id（员工ID）

• employee_name（员工姓名）

• employee_salary（员工薪资）

• department_id（员工所属部门名称）

可供参考的解题思路：使用窗口函数根据部门ID分组，在组内按照员工薪资降序排列并记为ranking，然后将该处理后的表和部门信息表进行内连接，从而把部门名称关联进来，最后在连接后的表上使用ranking=2作为薪资第二高的条件进行WHERE筛选，选择需要的列，即可得到结果。

涉及知识点：窗口函数、子查询、多表连接。

SELECT  a.employee_id,a.employee_name,a.employee_salary,b.department_id
FROM 
(SELECT  *,RANK() OVER (PARTITION BY departmentORDER BY employee_salary DESC) AS rankingFROM employee
) AS a
INNER JOIN department AS b
ON a.department = b.department_id
WHERE a.ranking = 2;

题目2：网站登录时间间隔统计

现有一张网站登录情况表login_info，该表记录了所有用户的网站登录信息，包含如下两个字段。

• user_id（用户ID）：VARCHAR。

• login_time（用户登录日期）：DATE。

数据导入的代码如下：

DROP TABLE IF EXISTS login_info;
CREATE TABLE login_info(
user_id VARCHAR(8),
login_time DATE
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
login_info (user_id,login_time)
VALUE ('a001','2021-01-01')
,('b001','2021-01-01')
,('a001','2021-01-03')
,('a001','2021-01-06')
,('a001','2021-01-07')
,('b001','2021-01-07')
,('a001','2021-01-08')
,('a001','2021-01-09')
,('b001','2021-01-09')
,('b001','2021-01-10')
,('b001','2021-01-15')
,('a001','2021-01-16')
,('a001','2021-01-18')
,('a001','2021-01-19')
,('b001','2021-01-20')
,('a001','2021-01-23');

问题：计算每个用户登录日期间隔小于5天的次数。

输出内容包括：user_id num

可供参考的解题思路：本题考查LEAD()函数在处理时间间隔问题上的使用方法，观察内层的查询部分，使用LEAD()函数在原有的login_time字段的基础上创造一列新的时间字段（即该用户下一次登录日期）

经过LEAD()函数处理后，数据会根据user_id字段分组后按照login_time字段排序。经过内层的处理后，只需在外层筛选出next_login_time与login_time字段的日期差小于5天的数据，即最终统计的目标数据，这里使用了TIMESTAMPDIFF(DAY, login_time, next_login_time)计算日期差，最后分组聚合统计不同user_id的记录个数，即每个用户登录日期间隔小于5天的次数。

涉及知识点：窗口函数、子查询、分组聚合、时间函数

SELECT a.user_id,COUNT(*) AS num
FROM 
(SELECT user_id,login_time,LEAD(login_time,1) OVER (PARTITION BY user_idORDER BY login_time) AS next_login_timeFROM login_info
) AS a
WHERE TIMESTAMPDIFF(DAY, login_time, next_login_time) < 5 
GROUP BY user_id;

题目3：用户购买渠道分析

现有一张用户购买信息表purchase_channel，该表记录了用户在某购物平台的购物信息，该购物平台具有网页端（web）和手机端（app）两种访问方式，表中包含如下4个字段。

• user_id（用户ID）：VARCHAR。

• channel（用户购买渠道）：VARCHAR。

• purchase_date（购买日期）：DATE。

• purchase_amount（购买金额）：INT。

数据导入代码如下：

DROP TABLE IF EXISTS purchase_channel;
CREATE TABLE purchase_channel(
user_id VARCHAR(8),
channel VARCHAR(8),
purchase_date DATE,
purchase_amount INT(8)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
purchase_channel (user_id,channel,purchase_date,purchase_amount)
VALUE ('a001','app','2021-03-14',200),('a001','web','2021-03-14',100),('a002','app','2021-03-14',400),('a001','web','2021-03-15',3000),('a002','app','2021-03-15',900),('a003','app','2021-03-15',1000);

问题：查询每天仅使用手机端的用户、仅使用网页端的用户和同时使用网页端和手机端（both）的不同用户人数和总购物金额，并且即使某天某渠道没有用户的购买信息，也需要展示。

输出内容包括：

purchase_date（日期）

channel（购买渠道）

sum_amount（总购买金额）

total_users（不同用户人数）

可供参考的解题思路：根据用户ID和日期进行分组，通过统计用户在各购买渠道购物的记录个数来判断某用户在某日期购物时采用的访问方式（web、app和both）。其中，web和app可以通过一个SELECT语句查询，both则可以通过另一个SELECT语句查询。将两部分使用UNION连接在一起，并将以上部分作为子查询内部，在子查询外部统计不同购买日期、购买渠道的总购买金额和总购买用户。

SELECT  purchase_date,channel,SUM(sum_amount) sum_amount,SUM(total_users) total_users
FROM 
(SELECT  purchase_date,MIN(channel) channel,SUM(purchase_amount) sum_amount,COUNT(DISTINCT user_id) total_usersFROM purchase_channelGROUP BY  purchase_date,user_idHAVING COUNT(DISTINCT channel) = 1 UNIONSELECT  purchase_date,'both' channel,SUM(purchase_amount) sum_amount,COUNT(DISTINCT user_id) total_usersFROM purchase_channelGROUP BY  purchase_date,user_idHAVING COUNT(DISTINCT channel) > 1 
) c
GROUP BY  purchase_date,channel;

题目要求即使某天某渠道没有用户的购买信息，也需要展示。而想要展示更全的信息，则考虑使用最全的信息（所有日期和3个渠道的笛卡尔积）与刚查询出的结果数据表进行LEFT JOIN连接，即可得到两张表根据日期和渠道进行连接的结果。

涉及知识点：UNION、分组聚合、数据去重。

SELECT  t1.purchase_date,t1.channel,t2.sum_amount,t2.total_users
FROM 
(SELECT  DISTINCT a.purchase_date,b.channelFROM purchase_channel a,(SELECT  "app" AS channelUNIONSELECT  "web" AS channelUNIONSELECT  "both" AS channel) b
) t1
LEFT JOIN 
(
SELECT 
purchase_date,
channel,
SUM(sum_amount) sum_amount,
SUM(total_users) total_users
FROM (
SELECT  purchase_date,MIN(channel) channel,SUM(purchase_amount) sum_amount,COUNT(DISTINCT user_id) total_usersFROM purchase_channelGROUP BY  purchase_date,user_idHAVING COUNT(DISTINCT channel) = 1 UNIONSELECT  purchase_date,'both' channel,SUM(purchase_amount) sum_amount,COUNT(DISTINCT user_id) total_usersFROM purchase_channelGROUP BY  purchase_date,user_idHAVING COUNT(DISTINCT channel) > 1
)c GROUP BY purchase_date, channel
) t2
ON t1.purchase_date = t2.purchase_date AND t1.channel = t2.channel;

题目4：hive之行转列

指定数据库

show databases ;
show tables ;
use test_sql;
set hive.exec.mode.local.auto=true;

create table table2(year int,month int ,amount double) ;
insert overwrite table table2 values(1991,1,1.1),(1991,2,1.2),(1991,3,1.3),(1991,4,1.4),(1992,1,2.1),(1992,2,2.2),(1992,3,2.3),(1992,4,2.4);
select * from table2;

-- 扩展，当case when里面的分支只有2个，并且是互斥，则等价于if语句 -- 也就是 【case when month=1 then amount else 0 end】 等价于【if(month=1,amount,0)】 --行转列 --常规做法是，group by+sum(if())

--原始写法

select year,sum(if(month=1,amount,0)) as m1,sum(if(month=2,amount,0)) as m2,sum(if(month=3,amount,0)) as m3,sum(if(month=4,amount,0)) as m4
from table2
group by year;
--或者
select year,sum(case when month=1 then amount else 0 end) as m1,sum(case when month=2 then amount else 0 end) as m2,sum(case when month=3 then amount else 0 end) as m3,sum(case when month=4 then amount else 0 end) as m4
from table2 as t
group by year

-- 腾讯游戏 --建表

create table table1(DDate string, shengfu string) ;
insert overwrite table table1 values ('2015-05-09', "胜"),('2015-05-09', "胜"),('2015-05-09', "负"),('2015-05-09', "负"),('2015-05-10', "胜"),('2015-05-10', "负"),('2015-05-10', "负");
select * from table1;

select DDate,sum(case when shengfu='胜' then 1 else null end) as `胜`,sum(case when shengfu='负' then 1 else null end) as `负`
from  table1 as t
group by DDate;

-- 腾讯QQ

create table tableA(qq string, game string);
insert overwrite table tableA values(10000, 'a'),(10000, 'b'),(10000, 'c'),(20000, 'c'),(20000, 'd');
​
create table tableB(qq string, game string) ;
insert overwrite table tableB values(10000, 'a_b_c'),(20000, 'c_d');
​
select * from tableA;
select * from tableB;

MySQL中，Concat_WS() 函数 用来通过指定符号，将2个或多个字段拼接在一起，返回拼接后的字符串。

Concat_WS语法 Concat_WS( [SEPARATOR 拼接符号] , str1 , str2 , … )

参数说明 SEPARATOR 拼接符号，默认的是 separator ：逗号, str1 , str2 , … 要拼接在一起的字段(字段不存在，MySQL将会报错) 返回值说明 若字段str1、str2中有null，则会被Concat_WS()函数忽略； 若字段只剩下一个字段有值（只有一个字段参与拼接），则直接返回该字段的值；

-- 行转列
select qq,concat_ws( '_', collect_list(game)) arr
from tableA group by qq;
-- 列转行 需要借助【侧视图+explode函数】
select qq,game2
from tableb lateral view explode(split(game,'_')) v1 as game2;

lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。

题目5: hive之连续N天登录

create table game(name string,`date` string);
insert overwrite table game  values('张三','2021-01-01'),('张三','2021-01-02'),('张三','2021-01-03'),('张三','2021-01-02'),
​('张三','2021-01-07'),('张三','2021-01-08'),('张三','2021-01-09'),
​('李四','2021-01-01'),('李四','2021-01-02'),('王五','2021-01-03'),('王五','2021-01-02'),('王五','2021-01-02');

--方案1
with t1 as (select distinct name,`date` from game
),t2 as (select *,row_number() over (partition by name order by `date`) as rnfrom t1),t3 as (select *,date_sub(`date`,rn) as tempfrom t2),t4 as (select name,temp ,count(1) as cntfrom t3group by name,temphaving count(1)>=3)
select distinct name from t4;
​
--方案2
with t1 as (select distinct name,`date` from game
),t2 as (select *,date_add(`date`,2) as date2,lead(`date`,2) over(partition by name order by `date`) as date3from t1)
select distinct name from t2 where date2=date3;

题目6: 窗口函数

create table score(cid int ,sname string,score int);
insert overwrite table score values
(1,'张三',60),
(1,'李四',70),
(1,'王五',80),
(1,'赵六',90),
(2,'安安',80),
(2,'平平',90);

第一类、 聚合类的窗口函数

-- sum() over() ​ -- count/avg/max/min ​ --让每个学生都知道，班级总分是多少 ​ --sum(score) over (partition by cid ) as 班级总分,

--计算同一个班级内，每个同学和比他分数低的总分是多少 --sum(score) over (partition by cid order by score ) as 累加分数1, --上句等价于 --sum(score) over (partition by cid order by score rows between unbounded preceding and current row ) as 累加分数2, --思考：如何计算每个同学，在班级内，包括他自己，和比他低一名的，和比他高1名的，这3个分数求和 --sum(score) over(partition by cid order by score rows between 1 preceding and 1 following) as 累加分数3

第二类、 排序类的窗口函数

--同一个班内，按分数排序打上序号 ​ row_number() over (partition by cid order by score) as 分数序号排名, ​ --考虑并列 ​ rank() over (partition by cid order by score) as 分数序号排名2, ​ dense_rank() over (partition by cid order by score) as 分数序号排名3

第三类、 偏移类的，跨行的

select *,
​-- lag / lead--同一班内，考得比自己低1名的分数是多少lag(score, 1) over (partition by cid order by score)     as `低一名的分数`,--同一班内，考得比自己低1名的分数是多少,如果找不到，则显示为0lag(score, 1, 0) over (partition by cid order by score)  as `低一名的分数2`,--同一班内，考得比自己低2名的分数是多少lag(score, 2) over (partition by cid order by score)     as `低2名的分数`,--同一班内，考得比自己高1名的分数是多少lead(score, 1) over (partition by cid order by score)    as `高一名的分数`
from score;

案例一：求每位学生的总分，排名，占全体总分的比率

create table emp(name string , month string, amt int);
insert overwrite table emp values ('张三', '01', 100),('李四', '02', 120),('王五', '03', 150),('赵六', '04', 500),('张三', '05', 400),('李四', '06', 350),('王五', '07', 180),('赵六', '08', 400);
select * from emp;
with t1 as (select name,sum(amt) as sum_amtfrom empgroup by name
),t2 as (select *,row_number() over (partition by null order by sum_amt desc) as rn, --排名sum(sum_amt) over(partition by null) as sum_all--全体总分from t1)
select *,-- || 两根竖线等价于 concat函数round(sum_amt*100/sum_all,2)||'%' as rate-- 占比
from t2;

案例二：求每年销售员工累计人数

--跨越速运
drop table if exists emp;
create table emp(empno string ,ename string,hiredate string,sal int ,deptno string);
insert overwrite table emp values('7521', 'WARD', '1981-2-22', 1250, 30),('7566', 'JONES', '1981-4-2', 2975, 20),('7876', 'ADAMS', '1987-7-13', 1100, 20),('7369', 'SMITH', '1980-12-17', 800, 20),('7934', 'MILLER', '1982-1-23', 1300, 10),('7844', 'TURNER', '1981-9-8', 1500, 30),('7782', 'CLARK', '1981-6-9', 2450, 10),('7839', 'KING', '1981-11-17', 5000, 10),('7902', 'FORD', '1981-12-3', 3000, 20),('7499', 'ALLEN', '1981-2-20', 1600, 30),('7654', 'MARTIN', '1981-9-28', 1250, 30),('7900', 'JAMES', '1981-12-3', 950, 30),('7788', 'SCOTT', '1987-7-13', 3000, 20),('7698', 'BLAKE', '1981-5-1', 2850, 30);

select *,sum(cnt) over(partition by null order by year1) as `累计人数`
from
(select year(hiredate) as year1,count(empno) as cnt
from emp
group by year(hiredate)) as t;

案例三：求广州银行每种商品中比例

题目7: 存留率

create table if not exists tb_cuid_1d
(cuid         string comment '用户的唯一标识',os           string comment '平台',soft_version string comment '版本',event_day    string comment '日期',visit_time    int comment '用户访问时间戳',duration     decimal comment '用户访问时长',ext          array<string> comment '扩展字段'
);
insert overwrite table tb_cuid_1d values(1,'android',1,'2020-04-01',1234567,100,`array`('')),(1,'android',1,'2020-04-02',1234567,100,`array`('')),(1,'android',1,'2020-04-08',1234567,100,`array`('')),(2,'android',1,'2020-04-01',1234567,100,`array`('')),(3,'android',1,'2020-04-02',1234567,100,`array`(''));

--写出用户表 tb_cuid_1d的 20200401 的次日、次7日留存的具体SQL ： --一条sql统计出以下指标 （4.1号uv，4.1号在4.2号的留存uv，4.1号在4.8号的留存uv）; --uv就是不同的用户数

--方案1 ，性能快，步骤稍微复杂

select '2020-04-01的留存情况' as type,count(cuid) as uv_4_1,count(case when cnt_4_2>0 then 1 else null end) as uv_4_2,count(case when cnt_4_8>0 then 1 else null end) as uv_4_8
from
(select cuid,count(case when event_day='2020-04-01' then 1 else null end) as cnt_4_1,count(case when event_day='2020-04-02' then 1 else null end) as cnt_4_2,count(case when event_day='2020-04-08' then 1 else null end) as cnt_4_8
from tb_cuid_1d
where event_day in ('2020-04-01','2020-04-02','2020-04-08')
group by cuid
having cnt_4_1>0) as t;

--方案2 ，性能慢，步骤比较简单
select count(a.cuid) as uv_4_1,count(b.cuid) as uv_4_2,count(c.cuid) as uv_4_8,count(b.cuid)/count(a.cuid) as rate_4_2,count(c.cuid)/count(a.cuid) as rate_4_8
from (select distinct cuid from tb_cuid_1d where event_day='2020-04-01') a
left join (select distinct cuid from tb_cuid_1d where event_day='2020-04-02') b on a.cuid=b.cuid
left join (select distinct cuid from tb_cuid_1d where event_day='2020-04-08') c on a.cuid=c.cuid

题目8: 分组内TopN

create table emp(empno string ,ename string,hiredate string,sal int ,deptno string);
insert overwrite table emp values('7521', 'WARD', '1981-2-22', 1250, 30),('7566', 'JONES', '1981-4-2', 2975, 20),('7876', 'ADAMS', '1987-7-13', 1100, 20),('7369', 'SMITH', '1980-12-17', 800, 20),('7934', 'MILLER', '1982-1-23', 1300, 10),('7844', 'TURNER', '1981-9-8', 1500, 30),('7782', 'CLARK', '1981-6-9', 2450, 10),('7839', 'KING', '1981-11-17', 5000, 10),('7902', 'FORD', '1981-12-3', 3000, 20),('7499', 'ALLEN', '1981-2-20', 1600, 30),('7654', 'MARTIN', '1981-9-28', 1250, 30),('7900', 'JAMES', '1981-12-3', 950, 30),('7788', 'SCOTT', '1987-7-13', 3000, 20),('7698', 'BLAKE', '1981-5-1', 2850, 30);
​
select * from emp order by deptno,sal desc;

--求出每个部门工资最高的前三名员工，并计算这些员工的工资占所属部门总工资的百分比。

select *,round(sal*100/sum_sal)||'%' as rate
from
(select *,row_number() over (partition by deptno order by sal desc) as rn,--部门内的薪资排名sum(sal)over(partition by deptno) as sum_sal --部门内总薪资
from emp) as t
where rn<=3;

题目9:join系列

create table all_users(id int comment '用户id',name string comment '用户姓名',sex string comment '性别',age int comment '年龄'
) comment '银行用户信息表';
insert overwrite table all_users values(1,'张三','男',20),(2,'李四','男',29),(3,'王五','男',21),(4,'赵六','女',28),(5,'田七','女',22);
select * from all_users;
create table black_list(user_id int comment '用户编号',type string comment '风控类型'
)comment '银行黑名单信息表';
insert overwrite table black_list values(1,'诈骗'),(2,'逾期'),(3,'套现');
select * from black_list;

-- 观察inner join 的效果
select *
from all_users a
inner join black_list b
on a.id=b.user_id;

-- 使用left join对所有用户，如果也在黑名单中，则标记为YES，否则标记为NO。

set spark.sql.shuffle.partitions=4;
select a.*,case  when b.user_id is not null then 'YES'else 'NO'end as flag
from all_users a
left join black_list b
on a.id=b.user_id;

--对所有用户，如果也在黑名单中，则标记为YES，否则标记为NO。 --对上面的问题，使用right join再做一次。 --right join 特点是：

select a.*,if(b.user_id is not null,'YES','NO') as flag
from black_list b
right join all_users a
on b.user_id=a.id;

--使用left semi join对所有用户，如果也在黑名单中，则挑选出来。 -- left semi join结果只显示左表字段，不会显示右表字段

select *
from all_users a
left semi join black_list b on a.id = b.user_id;

--他跟left join的关系是:

select a.*
from all_users a
left join black_list b on a.id = b.user_id
where b.user_id is not null;

--也等价于

select a.*
from all_users a
join black_list b on a.id = b.user_id;

-- 使用left anti join对所有用户，如果不在黑名单中，则挑选出来。

select *
from all_users a
left anti join black_list b on a.id = b.user_id;

--他跟left join的关系是:

select a.*
from all_users a
left join black_list b on a.id = b.user_id
where b.user_id is null;

--用户的存款金额。

drop table if exists deposit;
create table deposit (user_id int comment '用户id',amount int comment '存款金额'
)comment '用户最新银行存款信息表';
insert overwrite table deposit values(1,2000),(2,2900),(3,2100);
select * from deposit;

--用户的负债金额。

drop table if exists debt;
create table debt (user_id int comment '用户id',amount int comment '负债金额'
)comment '用户最新银行负债信息表';
insert overwrite table debt values(3,3400),(4,2800),(5,2200);
select * from debt;

select coalesce(success_cnt,period,1) from tableA 当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），当success_cnt为null，而period不为null的时候，返回period的真实值。只有当success_cnt和period均为null的时候，将返回1。

--使用full join，展示用户的存款金额和负债金额。
select coalesce(a.user_id,b.user_id) as user_id_new,coalesce(a.amount,0) as deposit_amount,coalesce(b.amount,0) as debt_amount
from deposit a
full join debt b on a.user_id=b.user_id;

--coalesce返回第一个不为null的值 select coalesce(123,456) as x; select coalesce(null,456) as x; select nvl(null,0) as x; select coalesce(null,null,456) as x;

NVL2(表达式A，表达式B，表达式C）

如果表达式A为空，则返回表达式C的值；如果表达式A不为空，则返回表达式B的值。

求出：2021-01-01到2021-01-07每个服务器ID，角色ID，角色使用付费和免费购买对应道具消耗的免费元宝数量总和，以及对应比率

create table if not exists dm_paid_buy
(`time`    bigint comment '#购买的时间戳',server_id string comment '#服务器ID',role_id   int comment '#角色ID',cost      int comment '#购买对应道具消耗的付费元宝数量',item_id   int comment '#购买对应道具的id',amount    int comment '#购买对应道具的数量',p_date    string comment '#登录日期, yyyy-MM-dd'
) comment '角色使用付费元宝购买商城道具时候记录一条';
insert overwrite table dm_paid_buy values(1234567,120,10098,2,3,4,'2021-01-01'),(1234567,120,10098,4,3,5,'2021-01-01'),(1234567,123,10098,3,3,2,'2021-01-02'),(1234567,123,10098,2,3,2,'2021-01-02');-- 查看表结构
desc dm_paid_buy;
​
create table if not exists dm_free_buy
(`time`    bigint comment '#购买的时间戳',server_id string comment '#服务器ID',role_id   int comment '#角色ID',cost      int comment '#购买对应道具消耗的免费元宝数量',item_id   int comment '#购买对应道具的id',amount    int comment '#购买对应道具的数量',p_date    string comment '#登录日期, yyyy-MM-dd'
) comment '角色使用免费元宝购买商城道具时候记录一条';
insert overwrite table dm_free_buy values
(1234567,123,10098,8,3,4,'2021-01-01'),
(1234567,123,10098,5,3,5,'2021-01-01'),
(1234567,120,10098,6,3,4,'2021-01-01'),
(1234567,120,10098,9,3,5,'2021-01-01'),
(1234567,123,10098,18,3,2,'2021-01-02'),
(1234567,123,10098,7,3,2,'2021-01-02');

--使用with as 来重构上面的逻辑
with a as (select p_date,server_id,role_id,sum(cost) as costfrom dm_paid_buywhere p_date between '2021-01-01' and '2021-01-07'group by p_date,server_id,role_id
),b as(select p_date,server_id,role_id,sum(cost) as costfrom dm_free_buywhere p_date between '2021-01-01' and '2021-01-07'group by p_date,server_id,role_id)
select coalesce(a.p_date,b.p_date) as p_date,coalesce(a.server_id,b.server_id) as server_id,coalesce(a.role_id,b.role_id) as role_id,coalesce(a.cost,0) as a_cost,coalesce(b.cost,0) as b_cost,coalesce(a.cost,0)/coalesce(b.cost,0) as rate
from  a
full join b
on a.p_date=b.p_date and a.server_id=b.server_id and a.role_id=b.role_id
;

题目10：截取字符串

使用or，因为or自带去重，而union等价于or，但union all 可以不去重

1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；

2、POSITION(substr IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；

3、LEFT(str, length)：从左边开始截取str，length是截取的长度；

4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；

5、SUBSTRING_INDEX(str ,substr ,n)：返回字符substr在str中第n次出现位置之前的字符串;

6、SUBSTRING(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符；

7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；

8、LENGTH(str)：计算字符串str的长度

例子：str=www.wikibt.com

substring_index(str,'.',1)

结果是：www

substring_index(str,'.',2)

结果是：www.wikibt

如果count是正数，那么就是从左往右数，第N个分隔符的左边的所有内容 如果count是负数，那么就是从右往左数，第N个分隔符的右边的所有内容 substring_index(str,'.',-2)

结果为：wikibt.com

有人会问，如果我要中间的的wikibt怎么办？

很简单的，两个方向：

从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：

substring_index(substring_index(str,'.',-2),'.',1);

SQL 之 case 表达式

CASE表达式 是不依赖于具体数据库的技术，所以可以提高 SQL 代码的可移植性。

基本格式如下

-- 简单 CASE表达式
CASE 列(或表达式)WHEN <匹配值1> THEN <表达式>WHEN <匹配值2> THEN <表达式>......ELSE <表达式>
END
​
-- 搜索 CASE表达式
CASE WHEN <判断表达式> THEN <表达式>WHEN <判断表达式> THEN <表达式>WHEN <判断表达式> THEN <表达式>......ELSE <表达式>
END
​
​
-- 简单 CASE表达式 示例
CASE sexWHEN '1' THEN '男'WHEN '2' THEN '女'ELSE '其他' 
END
​
-- 搜索CASE表达式 示例
CASE WHEN sex = '1' THEN '男'WHEN sex = '2' THEN '女'ELSE '其他' 
END

CASE表达式 的 ELSE子句 可以省略，但推荐不要省略

行转列

可能我们用的更多的是 IF（MySQL）或 DECODE（Oracle），但这两者都不是标准的 SQL，更推荐大家用 CASE表达式，移植性更高

有如下表，以及如下数据

CREATE TABLE t_customer_credit (id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',login_name VARCHAR(50) NOT NULL COMMENT '登录名',credit_type TINYINT(1) NOT NULL COMMENT '额度类型，1:自由资金,2:冻结资金,3:优惠',amount DECIMAL(22,6) NOT NULL DEFAULT '0.00000' COMMENT '额度值',create_by VARCHAR(50) NOT NULL COMMENT '创建者',create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',update_by VARCHAR(50) NOT NULL COMMENT '修改者',PRIMARY KEY (id)
);
INSERT INTO `t_customer_credit` VALUES (1, 'zhangsan', 1, 550.000000, 'system', '2019-7-7 11:30:09', '2019-7-8 20:21:05', 'system');
INSERT INTO `t_customer_credit` VALUES (2, 'zhangsan', 2, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (3, 'zhangsan', 3, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (4, 'lisi', 1, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (5, 'lisi', 2, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (6, 'lisi', 3, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');

如果我们要一行显示用户的三个额度，而不是 3 条记录显示 3 个额度，我们应该怎么做，方式有很多种，这里提供如下 3 种

-- 1、最容易想到的IF，不具备移植性，不推荐
SELECT login_name,MAX(IF(credit_type=1, amount, 0)) freeAmount,MAX(IF(credit_type=2, amount, 0)) freezeAmount,MAX(IF(credit_type=3, amount, 0)) promotionAmount
FROM t_customer_credit GROUP BY login_name;
​
-- 2、CASE表达式，标准的 SQL 规范，具备移植性，推荐使用
SELECT login_name,MAX(CASE WHEN credit_type = 1 THEN amount ELSE 0 END) freeAmount,MAX(CASE WHEN credit_type = 2 THEN amount ELSE 0 END) freezeAmount,MAX(CASE WHEN credit_type = 3 THEN amount ELSE 0 END) promotionAmount
FROM t_customer_credit GROUP BY login_name;
​
-- 3、自连接，数据量大的情况下，结合索引，效率不错，具备移植性
SELECTa.login_name,a.amount freeAmount,b.amount freezeAmount,c.amount promotionAmount
FROM (SELECT login_name, amount FROM t_customer_credit WHERE credit_type = 1
)a
LEFT JOIN t_customer_credit b ON a.login_name = b.login_name AND b.credit_type = 2
LEFT JOIN t_customer_credit c ON a.login_name = c.login_name AND c.credit_type = 3;

自连接是效率最高的，不管在不在 login_name 上加索引

转换统计

将已有编号方式转换为新的方式并统计

DROP TABLE t_province_population;
CREATE TABLE t_province_population (id tinyint(2) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',province_name varchar(50) NOT NULL COMMENT '省份名',sex tinyint(1) NOT NULL COMMENT '性别，1:男,2:女',population int(11) NOT NULL COMMENT '人口数',PRIMARY KEY (id)
);
INSERT INTO t_province_population(province_name,sex,population)
VALUES
("黑龙江", 1 ,20),
("黑龙江", 2 ,18),
("内蒙古", 1 ,7),
("内蒙古", 2 ,8),
("海南", 1 ,20),
("海南", 2 ,22),
("西藏", 1 ,8),
("西藏", 2 ,7),
("浙江", 1 ,35),
("浙江", 2 ,35),
("台湾", 1 ,26),
("台湾", 2 ,23),
("河南", 1 ,40),
("河南", 2 ,38),
("湖北", 1 ,27),
("湖北", 2 ,24);

需要按各个省所在的位置，统计出东南西北中，各个区域内的人口数量

东：浙江、台湾，西：西藏，南：海南，北：黑龙江、内蒙古，中：湖北、河南

SELECT CASE province_nameWHEN '浙江' THEN '东'WHEN '台湾' THEN '东'WHEN '海南' THEN '南'WHEN '西藏' THEN '西'WHEN '黑龙江' THEN '北'WHEN '内蒙古' THEN '北'WHEN '河南' THEN '中'WHEN '湖北' THEN '中'ELSE '其他' END district,SUM(population) populations
FROM t_province_population
GROUP BY district;

需要对各个省份做一个人口数级别的统计，统计出各个级别的数量

level_1：population < 20，level_2：20 <= population < 50 ，level_3：50 <= population < 70 ，level_4：>= 70；统计出 level_1 ~ level_4 的数量各有多少

SQL 与执行结果如下

SELECT CASE WHEN population < 20 THEN 'level_1'WHEN population >= 20 AND population < 50 THEN 'level_2'WHEN population >= 50 AND population < 70 THEN 'level_3'WHEN population >= 70 THEN 'level_4'ELSE NULL END pop_level,COUNT(*) cnt
FROM (SELECT province_name,SUM(population) population FROM t_province_population GROUP BY province_name
)a
GROUP BY pop_level

条件分支

SELECT 条件分支

还是以上面的 t_province_population 为例，如果我们想要直观的知道各个省份的男、女数量情况

-- 1、CASE表达式 集合 GROUP BY
SELECT province_name,SUM(CASE WHEN sex = 1 THEN population ELSE 0 END) c,SUM(CASE WHEN sex = 2 THEN population ELSE 0 END) f_pops
FROM t_province_population
GROUP BY province_name;
​
-- 2、自关联
SELECT t.province_name, SUM(t.population) m_pops, SUM(a.population) f_pops
FROM t_province_population t
LEFT JOIN t_province_population a
ON t.province_name = a.province_name
WHERE t.sex = 1 AND a.sex = 2;
GROUP BY province_name;

UPDATE 条件分支

我们有一张薪资表，如下

CREATE TABLE t_user_salaries(id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',name varchar(50) NOT NULL COMMENT '姓名',sex tinyint(1) NOT NULL COMMENT '性别，1:男,2:女',salary int(11) NOT NULL COMMENT '薪资',PRIMARY KEY (id)
);
INSERT INTO t_user_salaries(name, sex,salary) VALUES
("张三", 1, 30000),
("李四", 1, 27000),
("王五", 1, 22000),
("菲菲", 2, 24000),
("赵六", 1, 29000);
SELECT * FROM t_user_salaries;

假设现在需要根据以下条件对该表的数据进行更新：1、对当前工资为 30000 元以上的员工，降薪 10%，2、对当前工资为 25000 元以上且不满 28000 元的员工，加薪 20%。

UPDATE t_user_salaries SET salary = CASE WHEN salary >= 30000 THEN salary * 0.9WHEN salary >= 25000 AND salary < 28000 THEN salary * 1.2ELSE salaryEND;
​
SELECT * FROM t_user_salaries;

CHECK 约束

注意：CHECK 是标准的 SQL，但是 MySQL 却没有实现它，所以 CHECK 在 MySQL 中是不起作用的！

薪资表，假设某个公司有这样一个无理的规定：女性员工的工资不得高于50000

-- 创建表的时候增加约束
CREATE TABLE t_user_salaries_check(name varchar(50) NOT NULL COMMENT '姓名',sex tinyint(1) NOT NULL COMMENT '性别，1:男,2:女',salary int(11) NOT NULL COMMENT '薪资',PRIMARY KEY (id),CONSTRAINT chk_sex_salary CHECK(CASE WHEN sex = 2 THEN CASE WHEN salary <= 50000 THEN 1 ELSE 0 ENDELSE 1 END = 1 )
);
​
-- 若t_user_salaries_check已创建，则补充上约束
ALTER TABLE t_user_salaries_check
ADD CONSTRAINT chk_sex_salary CHECK(CASE WHEN sex = 2 THEN CASE WHEN salary <= 50000 THEN 1 ELSE 0 ENDELSE 1 END = 1 
);

order by 的高级用法

ORDER BY返回的是游标而不是集合

对于带有排序作用的ORDER BY子句的查询，它返回的是一个对象，其中的行按特定的顺序组织在一起，我们把这种对象称为**游标。**

ORDER BY子句是唯一能重用列别名的一步

这里涉及SQL语句的语法顺序和执行顺序了，我们常见的SQL语法顺序如下：

SELECT DISTINCT <Top Num> <select list> FROM [left_table] <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> WITH <CUBE | RollUP> HAVING <having_condition> ORDER BY <order_by_list>

而数据库引擎在执行SQL语句并不是从SELECT开始执行，而是从FROM开始，具体执行顺序如下(关键字前面的数字代表SQL执行的顺序步骤)：

(8)SELECT (9)DISTINCT (11)<Top Num> <select list> (1)FROM [left_table] (3)<join_type> JOIN <right_table> (2) ON <join_condition> (4)WHERE <where_condition> (5)GROUP BY <group_by_list> (6)WITH <CUBE | RollUP> (7)HAVING <having_condition> (10)ORDER BY <order_by_list>

从上面可以看到SELECT在HAVING后才开始执行，这个时候SELECT后面列的别名只对后续的步骤生效，而对SELECT前面的步骤是无效的。所以如果你在WHERE，GROUP BY，或HAVING后面使用列的别名均会报错。

ORDER BY子句是唯一能重用列别名的一步。

谨慎使用ORDER BY 后面接数字的方式来进行排序

SELECT 
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM Customers
ORDER BY 1,2,3

ORDER BY后面的数字1,2,3分别代表SELECT后面的第1，第2，第3个字段(也就是Name，Address，City)

表表达式不能使用ORDER BY排序

表表达式包括视图，内联表值函数，派生表(子查询)和公用表表达式(CTE)。

下面的视图是无效的

CREATE VIEW V_Customers AS
SELECT 
客户ID AS ID,
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM Customers
ORDER BY ID,Name,Address

T-SQL中表表达式加了TOP可以使用ORDER BY

SELECT 
客户ID AS ID,
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM
(SELECT TOP 3 *
FROM Customers
ORDER BY 城市) Customers
ORDER BY ID,Name,Address

因为T-SQL中带有ORDER BY的表表达式加了TOP后返回的是一个没有固定顺序的表。因此，在这种情况下，ORDER BY子句只是为TOP选项定义逻辑顺序，就是下面这个逻辑子句

SELECT TOP 3 *
FROM Customers
ORDER BY 城市

而不保证结果集的排列顺序，因为表表达式外面至少还有一层才是我们最终需要的结果集。

这里的ORDER BY只对当前的子查询生效，到了主查询是不起作用的。必须在主查询末尾继续添加一个ORDER BY子句才能对结果集生效，就像我们例子中写的那样。

除非逻辑要求，一般情况下并不推荐大家这样巧妙的避开子查询中不能使用ORDER BY的限制。

WITH(NOLOCK)用法

SQL在每次新建一个查询，就相当于创建了一个会话。在不同的查询窗口操作，会影响到其他会话的查询。当某张表正在写数据时，这时候去查询很可能就会一直处于阻塞状态

我们这里使用事务来往某张表里写数据，我们知道事务在写完表必须提交（COMMIT）或回滚（ROLLBACK）才能释放表，否则会一直处于阻塞状态。

使用BEGIN TRAN就可以开始一个事务

事务里的数据虽然还没有提交，但是它实际上已经存在内存里面了，这个时候我们使用NOLOCK查询到的结果，实际上还没存储到硬盘。

从上面的两个测试可以看出，NOLOCK的作用其实就是为了防止查询时被阻塞，只是这样会产生脏读（未提交的数据）。

那么一般什么情况下使用NOLOCK呢？

通常是一些被频繁写的表，不管是插入，更新还是删除。这样的表在查询时，使用NOLOCK是非常有效的。

WITH(NOLOCK)和NOLOCK的区别

SELECT * FROM A NOLOCK
SELECT * FROM A (NOLOCK);
SELECT * FROM A WITH(NOLOCK);

• (NOLOCK)这样的写法，NOLOCK其实只是别名的作用，而没有任何实质作用。所以不要粗心将(NOLOCK)写成NOLOCK

• (NOLOCK)与WITH(NOLOCK)其实功能上是一样的。(NOLOCK)只是WITH(NOLOCK)的别名,但是在SQL Server 2008及以后版本中，(NOLOCK)不推荐使用了，"不借助 WITH 关键字指定表提示”的写法已经过时了。

• 在使用链接服务器的SQL当中，(NOLOCK)不会生效，WITH(NOLOCK)才会生效。

--这样会提示用错误
select * from [IP].[dbname].dbo.tableName with (nolock)
--这样就可以
select * from [dbname].dbo.tableName with(nolock)