IT面试真题详解SQL（领取SQL详解一份）

刺骨的言语ヽ痛彻心扉 2024-04-08 09:46 29阅读 0赞

【课程简介】

SQL不仅在工作中使用频繁，在IT面试中SQL题出现频率也很高，而且题目占比越来越大，经常有同学面试挂在SQL题不会做痛失offer。本次课程将从技术使用跟面试真题两方面展开讲解，分享高效的SQL代码写法，告别拐弯抹角长篇大论轻松满足工作需求；并且将40多家大厂面试真题整理出SQL题集，把常见专题总结出简易解法，套上公式就会做。助力大家轻松掌握SQL！

【教程推荐】[IT面试真题详解SQL（一）][IT_SQL]

【主讲内容】

1.怎样套上公式就会做

2.40+真实大厂原题解法

3.快如闪电高效SQL写法

【主讲教师】

金牌讲师：陈老师

前中国平安高级技术专家，有9年大数据研发经验，精通SQL授课通俗易懂。

![5f256e4b7eef4138bcf3f6c314475272.png][]

### **1. SQL执行顺序** ###

上面的SQL语句的执行顺序是: from (去加载table1 和 table2这2个表 ) -> join -> on -> where -> group by->select 后 面的普通字段，聚合函数count,sum -> having -> distinct -> order by -> limit

\--举例：

select a.sex, b.city, count(1) as cnt, sum(salary) as sum1

from table1 a

join table2 b on a.id=b.id

where a.name=b.name

group by a.sex,b.city

having cnt>=2

order by a.sex,b.city

limit 10

\--或者是

select distinct

a.sex, b.city, a.age

from table1 a

join table2 b on a.id=b.id

where a.name=b.name

order by a.sex,b.city

limit 10

\--on 和where的先后顺序讨论

\--下面用left join 各得到结果，结果不一样。

\--下面可知，先执行on，再执行where

select \*

from table1 a left join table2 b

on a.id=b.id

where a.name=b.name;

\--下面的条数可能会比上面多。

select \*

from table1 a left join table2 b

### 2. hive10题 ###

先配置环境 在pycharm或datagrip或idea中配置hive数据源。也可以配置一个sparkSQL数据源，来加快速度。 如果配置hive数据源： 需要提前启动hdfs和yarn，hive的metastore，hive的hiveserver2

on a.id=b.id

and a.name=b.name;

\--下面用inner join 各得到结果，结果是一样的

select \*

from table1 a

join table2 b

on a.id=b.id

where a.name=b.name;

select \*

from table1 a

join table2 b

on a.id=b.id

and a.name=b.name;

\#启动hdfs和yarn

start-all.sh

\# hive的metastore

nohup /export/server/hive/bin/hive --service metastore 2>&1 > /tmp/hivemetastore.log &

\#hive的hiveserver2 \#hiveserver2开启后，等过2分钟后才能生效。

nohup /export/server/hive/bin/hive --service hiveserver2 2>&1 > /tmp/hivehiveserver2.log &

如果遇到下面的问题 解决办法 如果配置SparkSQL数据源 需要提前启动hdfs，hive的metastore，Spark的Thriftserver服务。 下面是spark3集成hive3需要的jar包，如果是spark2集成hive2，则jar包不一样。

hive/conf/hive-env.sh中加入

export HADOOP\_CLIENT\_OPTS=" -Xmx512m" export HADOOP\_HEAPSIZE=1024

改完重启hiveserver2

\#启动hdfs和yarn

start-all.sh

\# hive的metastore

nohup /export/server/hive/bin/hive --service metastore 2>&1 > /tmp/hivemetastore.log &

\#Spark的Thriftserver服务

/export/server/spark/sbin/start-thriftserver.sh \\ --hiveconf hive.server2.thrift.port=10001 \\ --hiveconf hive.server2.thrift.bind.host=node1 \\ --master local\[\*\]

show databases ;

create database if not exists test\_sql; use test\_sql;

\-- 一些语句会走 MapReduce，所以慢。 可以开启本地化执行的优化。

set hive.exec.mode.local.auto=true;-- (默认为false) --第1题：访问量统计

CREATE TABLE test\_sql.test1 ( userId string, visitDate string, visitCount INT ) ROW format delimited FIELDS TERMINATED BY "\\t";

INSERT overwrite TABLE test\_sql.test1

VALUES

( 'u01', '2017/1/21', 5 ), ( 'u02', '2017/1/23', 6 ), ( 'u03', '2017/1/22', 8 ), ( 'u04', '2017/1/20', 3 ), ( 'u01', '2017/1/23', 6 ), ( 'u01', '2017/2/21', 8 ), ( 'u02', '2017/1/23', 6 ), ( 'u01', '2017/2/22', 4 );

select \*, sum(sum1) over(partition by userid order by month1 /\*rows between unbounded preceding and current row\*/ ) as \`累积\` from

(select userid, date\_format(replace(visitdate,'/','-'),'yyyy-MM') as month1, sum(visitcount) sum1

from test\_sql.test1

group by userid, date\_format(replace(visitdate,'/','-'),'yyyy-MM')) as t;

**-- 第2题：电商场景TopK统计**

CREATE TABLE test\_sql.test2 ( user\_id string, shop string ) ROW format delimited FIELDS TERMINATED BY '\\t';

INSERT INTO TABLE test\_sql.test2 VALUES

( 'u1', 'a' ), ( 'u2', 'b' ), ( 'u1', 'b' ), ( 'u1', 'a' ), ( 'u3', 'c' ), ( 'u4', 'b' ), ( 'u1', 'a' ), ( 'u2', 'c' ), ( 'u5', 'b' ), ( 'u4', 'b' ), ( 'u6', 'c' ), ( 'u2', 'c' ), ( 'u1', 'b' ), ( 'u2', 'a' ), ( 'u2', 'a' ), ( 'u3', 'a' ), ( 'u5', 'a' ), ( 'u5', 'a' ), ( 'u5', 'a' );

\--（1）每个店铺的UV（访客数）

\-- UV和PV -- PV是访问当前网站所有的次数

\-- UV是访问当前网站的客户数(需要去重)--(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

select shop, count(distinct user\_id) as uv

from test\_sql.test2 group by shop ;

\--上面的拆解来看，等价于

\--distinct后可以接多个字段，表示联合去重

select shop, count(user\_id) as uv

from

(select distinct shop, user\_id

from test\_sql.test2 ) as t

group by shop ;

\--也等价于

select shop, count(user\_id) as uv

from

(select shop, user\_id

from test\_sql.test2 group by shop, user\_id) as t

group by shop ;

select \* from

(select \*, row\_number() over (partition by shop order by cnt desc) as rn

from

(select shop,user\_id,count(1) as cnt from test\_sql.test2 group by shop,user\_id ) as t) t2

where t2.rn3;

**-- 第3题：订单量统计**

CREATE TABLE test\_sql.test3 ( dt string, order\_id string, user\_id string, amount DECIMAL ( 10, 2 ) ) ROW format delimited FIELDS TERMINATED BY '\\t';

INSERT overwrite TABLE test\_sql.test3 VALUES

('2017-01-01','10029028','1000003251',33.57), ('2017-01-01','10029029','1000003251',33.57), ('2017-01-01','100290288','1000003252',33.57), ('2017-02-02','10029088','1000003251',33.57), ('2017-02-02','100290281','1000003251',33.57), ('2017-02-02','100290282','1000003253',33.57), ('2017-11-02','10290282','100003253',234), ('2018-11-02','10290284','100003243',234);

\-- (1)给出 2017年每个月的订单数、用户数、总成交金额。

\-- (2)给出2017年11月的新客数(指在11月才有第一笔订单)

select date\_format(dt,'yyyy-MM') as month1, count(distinct order\_id) as cnt1, count(distinct user\_id) as cnt2, sum(amount) as amt from test\_sql.test3

where year(dt)=2017

group by date\_format(dt,'yyyy-MM');

select count(user\_id) cnt from

(select user\_id, min(date\_format(dt,'yyyy-MM')) min\_month

from test3 group by user\_id) as t where min\_month='2017-11';

\--统计每个月的新客户数

select min\_month, count(user\_id) cnt

from (select user\_id, min(date\_format(dt, 'yyyy-MM')) min\_month from test3 group by user\_id) as t

group by min\_month;

**-- 第4题：大数据排序统计**

CREATE TABLE test\_sql.test4user (user\_id string,name string,age int);

CREATE TABLE test\_sql.test4log (user\_id string,url string);

INSERT INTO TABLE test\_sql.test4user VALUES('001','u1',10), ('002','u2',15), ('003','u3',15), ('004','u4',20), ('005','u5',25), ('006','u6',35), ('007','u7',40), ('008','u8',45), ('009','u9',50), ('0010','u10',65);

INSERT INTO TABLE test\_sql.test4log VALUES('001','url1'), ('002','url1'), ('003','url2'), ('004','url3'), ('005','url3'), ('006','url1'), ('007','url5'), ('008','url7'), ('009','url5'), ('0010','url1');

select \* from test\_sql.test4user ;

select \* from test\_sql.test4log ;

\--有一个5000万的用户文件(user\_id，name，age)，

\-- 一个2亿记录的用户看电影的记录文件(user\_id，url)，根据年龄段观看电影的次数进行排序？

\--取整函数有 round，floor，ceil

select \*, round(x,0) as r,--四舍五入

floor(x) as f,--向下取整

ceil(x) as c--向上取整

from

(select 15/10 as x union all

select 18/10 as x union all

select 24/10 as x union all

select 27/10 as x ) as t;

select type, sum(cnt) as sum1

from

(select \*, concat(floor(age/10)\*10,'-',floor(age/10)\*10+10) as type

from test\_sql.test4user as a

\-- join前最好提前减小数据量

join (select user\_id,count(url) as cnt from test\_sql.test4log group by user\_id) as b

on a.user\_id=b.user\_id) as t

group by type

order by sum(cnt) desc;

**-- 第5题：活跃用户统计**

CREATE TABLE test5( dt string, user\_id string, age int) ROW format delimited fields terminated BY ',';

INSERT overwrite TABLE test\_sql.test5 VALUES ('2019-02-11','test\_1',23), ('2019-02-11','test\_2',19), ('2019-02-11','test\_3',39), ('2019-02-11','test\_1',23), ('2019-02-11','test\_3',39), ('2019-02-11','test\_1',23), ('2019-02-12','test\_2',19), ('2019-02-13','test\_1',23), ('2019-02-15','test\_2',19), ('2019-02-16','test\_2',19);

select \* from test\_sql.test5 order by dt,user\_id;

\--有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）

\-- type 总数 平均年龄

\-- '所有用户' 3 27 -- '活跃用户' 1 19

with t1 as (select distinct dt, user\_id,age from test\_sql.test5), t2 as (select \*,row\_number() over (partition by user\_id order by dt) as rn from t1 ), t3 as (select \*,date\_sub(dt,rn) as dt2 from t2), t4 as (select dt2,user\_id,age,count(1) cnt from t3 group by dt2,user\_id,age), t5 as (select \* from t4 where cnt>=2), t6 as (select distinct user\_id,age from t5)

select '所有用户' as type, count(user\_id) cnt,avg(age) as avg\_age

from (select distinct user\_id,age from test\_sql.test5) t union all

select '活跃用户' as type, count(user\_id) cnt,avg(age) as avg\_age from t6;

\-- 用思路2来分析连续2天登录

with t1 as (select distinct dt, user\_id from test\_sql.test5), t2 as (select \*, date\_add(dt,1) as dt2, lead(dt,1)over(partition by user\_id order by dt) as dt3 from t1)

select count(distinct user\_id) from t2 where dt2=dt3;

**-- 第6题：电商购买金额统计实战**

CREATE TABLE test\_sql.test6 ( userid string, money decimal(10,2), paymenttime string, orderid string);

INSERT INTO TABLE test\_sql.test6 VALUES('001',100,'2017-10-01','123'), ('001',200,'2017-10-02','124'), ('002',500,'2017-10-01','125'), ('001',100,'2017-11-01','126');

select \* from test\_sql.test6 order by userid,paymenttime;

\--请用sql写出所有用户中在今年10月份第一次购买商品的金额，

select userid,paymenttime,money

from

(select \*, row\_number() over (partition by userid order by paymenttime) as rn from test\_sql.test6 where date\_format(paymenttime,'yyyy-MM')='2017-10' ) as t

where t.rn=1

;

**-- 第7题：教育领域SQL实战**

CREATE TABLE test\_sql.book(book\_id string, \`SORT\` string, book\_name string, writer string, OUTPUT string, price decimal(10,2));

INSERT INTO TABLE test\_sql.book VALUES

('001','TP391','信息处理','author1','机械工业出版社','20'), ('002','TP392','数据库','author12','科学出版社','15'), ('003','TP393','计算机网络','author3','机械工业出版社','29'), ('004','TP399','微机原理','author4','科学出版社','39'), ('005','C931','管理信息系统','author5','机械工业出版社','40'), ('006','C932','运筹学','author6','科学出版社','55');

CREATE TABLE test\_sql.reader (reader\_id string, company string, name string, sex string, grade string, addr string);

INSERT INTO TABLE test\_sql.reader VALUES

('0001','阿里巴巴','jack','男','vp','addr1'), ('0002','百度','robin','男','vp','addr2'), ('0003','腾讯','tony','男','vp','addr3'), ('0004','京东','jasper','男','cfo','addr4'), ('0005','网易','zhangsan','女','ceo','addr5'), ('0006','搜狐','lisi','女','ceo','addr6');

CREATE TABLE test\_sql.borrow\_log(reader\_id string, book\_id string, borrow\_date string);

INSERT INTO TABLE test\_sql.borrow\_log VALUES ('0001','002','2019-10-14'), ('0002','001','2019-10-13'), ('0003','005','2019-09-14'), ('0004','006','2019-08-15'), ('0005','003','2019-10-10'), ('0006','004','2019-17-13');

select \* from test\_sql.book;

select \* from test\_sql.reader;

select \* from test\_sql.borrow\_log;

\--（8）考虑到数据安全的需要，需定时将“借阅记录”中数据进行备份，请使用一条SQL语句，

\-- 在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW\_LOG\_BAK. -- 井且将“借阅记录”中现有数据全部复制到BORROW\_L0G\_ BAK中。

create table test\_sql.BORROW\_LOG\_BAK as select \* from test\_sql.borrow\_log;

select \* from test\_sql.BORROW\_LOG\_BAK;

\--（9）现在需要将原Oracle数据库中数据迁移至Hive仓库，

\-- 请写出“图书”在Hive中的建表语句（Hive实现，提示：列分隔符|；

\-- 数据表数据需要外部导入：分区分别以month＿part、day＿part 命名）

CREATE TABLE test\_sql.book2

( book\_id string, \`SORT\` string, book\_name string, writer string, OUTPUT string, price decimal(10, 2) )partitioned by (month\_part string,day\_part string ) row format delimited fields terminated by '|';

\--（10）Hive中有表A，现在需要将表A的月分区 201505 中 -- user＿id为20000的user＿dinner字段更新为bonc8920，其他用户user＿dinner字段数据不变，

\-- 请列出更新的方法步骤。（Hive实现，提示：Hive中无update语法，请通过其他办法进行数据更新）

\--A -- user\_id user\_dinner part -- 20000 aaaaa 201505 -- 30000 bbbbb 201505

create table A (user\_id int,user\_dinner string) partitioned by (part string);

insert overwrite table A partition (part = '201505')

values (20000, 'aaaaa'), (30000, 'bbbbb'), (40000, 'ccccc');

select \* from A;

\--update A set user\_dinner='bonc8920' where user\_id=20000;

insert overwrite table A partition (part = '201505')

select user\_id, 'bonc8920' as user\_dinner from A where user\_id=20000 and part = '201505'

union all

select user\_id, user\_dinner from A where user\_id!=20000 and part = '201505' ;

**-- 第8题：服务日志SQL统计**

CREATE TABLE test\_sql.test8(\`date\` string, interface string, ip string);

INSERT INTO TABLE test\_sql.test8 VALUES

('2016-11-09 11:22:05','/api/user/login','110.23.5.23'), ('2016-11-09 11:23:10','/api/user/detail','57.3.2.16'), ('2016-11-09 23:59:40','/api/user/login','200.6.5.166'), ('2016-11-09 11:14:23','/api/user/login','136.79.47.70'), ('2016-11-09 11:15:23','/api/user/detail','94.144.143.141'), ('2016-11-09 11:16:23','/api/user/login','197.161.8.206'), ('2016-11-09 12:14:23','/api/user/detail','240.227.107.145'), ('2016-11-09 13:14:23','/api/user/login','79.130.122.205'), ('2016-11-09 14:14:23','/api/user/detail','65.228.251.189'), ('2016-11-09 14:15:23','/api/user/detail','245.23.122.44'), ('2016-11-09 14:17:23','/api/user/detail','22.74.142.137'), ('2016-11-09 14:19:23','/api/user/detail','54.93.212.87'), ('2016-11-09 14:20:23','/api/user/detail','218.15.167.248'), ('2016-11-09 14:24:23','/api/user/detail','20.117.19.75'), ('2016-11-09 15:14:23','/api/user/login','183.162.66.97'), ('2016-11-09 16:14:23','/api/user/login','108.181.245.147'), ('2016-11-09 14:17:23','/api/user/login','22.74.142.137'), ('2016-11-09 14:19:23','/api/user/login','22.74.142.137');

select \* from test\_sql.test8;

\--求11月9号下午14点（14-15点），访问/api/user/login接口的top10的ip地址

select ip, count(1) cnt

from test\_sql.test8

where date\_format(\`date\`, 'yyyy-MM-dd HH') = '2016-11-09 14'

and interface = '/api/user/login'

group by ip

order by cnt desc limit 10

;

**-- 第9题：充值日志SQL实战**

CREATE TABLE test\_sql.test9( dist\_id string COMMENT '区组id', account string COMMENT '账号', \`money\` decimal(10,2) COMMENT '充值金额', create\_time string COMMENT '订单时间');

INSERT INTO TABLE test\_sql.test9 VALUES ('1','11',100006,'2019-01-02 13:00:01'), ('1','22',110000,'2019-01-02 13:00:02'), ('1','33',102000,'2019-01-02 13:00:03'), ('1','44',100300,'2019-01-02 13:00:04'), ('1','55',100040,'2019-01-02 13:00:05'), ('1','66',100005,'2019-01-02 13:00:06'), ('1','77',180000,'2019-01-03 13:00:07'), ('1','88',106000,'2019-01-02 13:00:08'), ('1','99',100400,'2019-01-02 13:00:09'), ('1','12',100030,'2019-01-02 13:00:10'), ('1','13',100003,'2019-01-02 13:00:20'), ('1','14',100020,'2019-01-02 13:00:30'), ('1','15',100500,'2019-01-02 13:00:40'), ('1','16',106000,'2019-01-02 13:00:50'), ('1','17',100800,'2019-01-02 13:00:59'), ('2','18',100800,'2019-01-02 13:00:11'), ('2','19',100030,'2019-01-02 13:00:12'), ('2','10',100000,'2019-01-02 13:00:13'), ('2','45',100010,'2019-01-02 13:00:14'), ('2','78',100070,'2019-01-02 13:00:15');

select \* from test\_sql.test9 order by dist\_id , money desc;

\--请写出SQL语句，查询充值日志表2019年01月02号每个区组下充值额最大的账号，要求结果：

\--区组id，账号，金额，充值时间

select \* from

(select \*, row\_number() over (partition by dist\_id order by money desc) rn

from test\_sql.test9 where to\_date(create\_time)='2019-01-02') t

where t.rn=1;

**-- 第10题：电商分组TopK实战**

CREATE TABLE test\_sql.test10( \`dist\_id\` string COMMENT '区组id', \`account\` string COMMENT '账号', \`gold\` int COMMENT '金币');

INSERT INTO TABLE test\_sql.test10 VALUES ('1','77',18), ('1','88',106), ('1','99',10), ('1','12',13), ('1','13',14), ('1','14',25), ('1','15',36), ('1','16',12), ('1','17',158), ('2','18',12), ('2','19',44), ('2','10',66), ('2','45',80), ('2','78',98);

year month amount 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 year m1 m2 m3 m4 1991 1.1 1.2 1.3 1.4 1992 2.1 2.2 2.3 2.4

**SQL强化面试题（pdf版）**

![a9046d3fcf224f878208771eeb0eed6f.jpeg][]

[IT_SQL]: http://yun.itheima.com/open/790.html?22sjn
[5f256e4b7eef4138bcf3f6c314475272.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/cb3531ae2129453882f7398869fb305e.png
[a9046d3fcf224f878208771eeb0eed6f.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/566e2e87bbd844baa616f63e56ace06e.jpeg