Python大数据SQL面试宝典2022年_第1页
Python大数据SQL面试宝典2022年_第2页
Python大数据SQL面试宝典2022年_第3页
Python大数据SQL面试宝典2022年_第4页
Python大数据SQL面试宝典2022年_第5页
已阅读5页,还剩162页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

Python大数据V2.0高频核心面试题

版本增加模块日志修改时间

V2.0增加腾讯、华泰证券面试题20220101

V2.1增加Hive部分SQL经典10题20220201

适用范围:大数据各个阶段学习完之后提前准备阶段重点面试题

注意:如何讲解项目中的技术点?

(1)讲清楚知识点是什么

(2)讲清楚知识点原理是什么

(3)讲清楚知识点如何使用

讲清楚有没有优化及应用场景使用技巧:以计算组件为核心复习,主要复习Hive,Spark及Flink计算组件。

—1—

目录

Python大数据V2.0高频核心面试题........................................................1

SQL提高秘籍............................................................................6

初级SQL・Hive10题......................................................................7

1.需求列表.......................................................................7

2.第1题:访问量统计.............................................................7

3.第2题:电商场景TopK统计......................................................9

4.第3题:订单量统计............................................................12

5.第4题:大斓排序统计........................................................13

6.第5题:活跃用户统计..........................................................15

7.第6题:电商购买金额统计实战.................................................17

8.第7题:教育领域SQL实战......................................................18

9.第8题:服务日志SQL统计......................................................23

10.第9题:充值日志SQL实战.....................................................24

11.第10题:电商分组TopK实战..................................................26

微众银行..............................................................................28

1.需求列表......................................................................28

2.源数簸:...................................................................28

3.创建库和源数据表,并生成数据。..............................................29

4.第1题:授信金额统计..........................................................35

5.第2题:贷款产品不良统计......................................................41

6.第3题:逾期率统计............................................................46

7.第4题:用户画像统计..........................................................49

8.第5题:Vintage账龄分析.....................................................51

华泰证券..............................................................................61

1.需求列表......................................................................61

2.第1题查询每门课都大于80分的学生............................................61

3.第2题表转置.................................................................62

—2—

4.第3题.........................................................................65

5.第4题查询每日买入金额前十和卖出金额前十的基金.............................67

6.第5题求每日每个客户每只股票的市值增长率....................................68

7.第6题计算基金净值回撤.......................................................69

招商信诺人寿...........................................................................71

1.需求列表......................................................................71

2.第1题排序取第8-16条记录.....................................................71

3.第2题SQL执行顺序...........................................................72

4.第3题班级分组内按成绩排名...................................................72

5.第4题聚合窗口函数...........................................................74

6.第5题时间序列犍...........................................................75

交通银行..............................................................................79

1.需求列表......................................................................79

2.第1题金额环比增长率.........................................................79

3.第2题金额排名占比...........................................................80

4.第3题数据发散问题...........................................................82

5.第4题left/semi/anti/join区分.................................................84

百丽..................................................................................86

1.需求列表......................................................................86

2.第1题Hive雌导入...........................................................86

3.第2题Groupingsets18s分析................................................86

4.第3题百丽业务统计分析.......................................................87

跨越物流..............................................................................90

1.需求列表......................................................................90

2.第1题求出每个部门工资最高的前三名员工并计算这些员工的工资占所属部门总工资的

百分比。........................................................................90

3.第2题统计每年入职总数以及截至本年累计入职总人数。.........................92

4.第3题每个品牌的总营销天数(时间序列题型).....................................93

腾讯外包(微信相关).....................................................................95

1.需求列表......................................................................95

2.第1题Python数据清洗转换...................................................95

—3—

3.第2题查询当天阅读最多的100篇文章........................................97

4.第3题查询当天阅读最多的10个公众号.......................................97

小米电商..............................................................................99

1.需求列表......................................................................99

2.第1题用户订单面计.........................................................99

3.第2题利用增量表得到全量表.................................................101

4.第3题订单量中位数..........................................................101

5.第3题订单top10的城市......................................................102

鄂州做.............................................................................103

1.需求列表....................................................................103

2.第1题SQL郸出考察..........................................................103

腾讯频雌开发A卷.................................................................107

1.需求列表....................................................................107

2.选择题.......................................................................107

3.解答题.......................................................................108

4.问答题.......................................................................115

腾讯酶号游戏直播..................................................................119

1.需求列表....................................................................119

2.第1题:活跃用户数、次日留存率..............................................119

3.第2题:用户留存率、用户活跃度得分排序......................................122

4.第3题考察点:row_number..........................................................................................124

5.第4题考察点:having子句的使用.............................................124

帆软[MySQL方向].....................................................................127

1.需求列表....................................................................127

2.第1题:销售环比增长率.......................................................127

3.第2题分组max-min-avg................................................................................................128

4.第3题销售业绩综合统计.....................................................130

5.第4题LEFTJION.RIGHTJONXINNERJOIN.FULLJOIN的区别...............137

PayPal................................................................................................................................................139

1.需求列表....................................................................139

2.第1题:UsePysparktogenerateplotlinechart...........................................................139

—4—

3.第2题:WriteaSQLquerytogetthesecondhighestsalaryfromtheEmployeetable.139

4.第3题:HiveSQL累积访问次数................................................140

Oppo...................................................................................................................................142

1.需求列表....................................................................142

2.第1题举例一下Hive中的数据类型?...........................................142

3.第2题最高成绩课程&每门课程大于80分的学生................................142

4.第3题连续三天登录人员.....................................................143

附录.................................................................................148

1.微众银行....................................................................148

2.华泰证券....................................................................151

3.招商信诺人寿................................................................154

4.交通银行....................................................................157

5.百丽.........................................................................162

6.跨越物流....................................................................163

7.腾讯外包(微信相关)...........................................................164

8.小米电商....................................................................164

9.鄂州顺....................................................................165

10.腾讯游戏数据开发A卷.......................................................165

11.腾讯视频号游戏直播.........................................................165

12.帆软[MySQL方向]...........................................................165

13.PayPal..................................................................................................................166

14.Oppo....................................................................................................................166

—5—

大数据必备SQL提高秘籍

提高SQL功底的思路。

1、造数据。因为有期g支撑,会方便我们根据数据结果去不断调整SQL的写法。

造数据语法既可以createtable再insertinto,也可以用下面的createtemporaryviewxxas

values语句,更简单。

其中createtemporaryviewxxasvalues语句,SparkSQL语法支持,hive不支持

2、先将结果表画出来,包括结果字段名有哪些,数据量也画几条。这是分析他要什么。

从藤到结果表,一路可能要走多个步骤,其实就是可育牌要多个子直询,过程多就用withas来重

构提高可读性。

3、要由简单过度到复杂,不要一下子就写一个很复杂的。

先写简单的select*fromtabIe..每个中间步骤都执行打印结果,看是否符合预

期,根据中间结果,进一步调整修饰SQL语句,再执行,直到接近结果表。

4、数据量要小,工具要快,如果用hive,就设置set

hive.exec.mode.local.auto=true;如果是SparkSQL,就的shuffle并行度,

setspark.sql.shuffle.partitions=4;

—6—

初级SQL-HivelO题

1.需求列表

2.第1题:访问量统计

2.1需求

我们有如下的用户访问数据

useridvisitDatevisitCoun

t

uO12017/1/215

u022017/1/236

u032017/1/228

u042017/1/203

uO12017/1/236

uO12017/2/218

U022017/1/236

U012017/2/224

要求使用SQLi充计出每个用户的累积访问次数,如下表所示:

用户id月份小计累积

u012017-1111

01

u012017-1223

02

u022017-1212

01

u032017-88

01

—7—

u042017-0133

实现

2.2数据准备

CREATETABLEtest_sql.test1(

useridstring,

visitDate

string,

visitCount

INT)

ROWformatdelimitedFIELDSTERMINATEDBY

V;INSERTINTOTABLEtest_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'f6),

2.3查询SQL

SELECTt2.userid,

t2.visitmonth,

subtotal_visit_cnt,

sum(subtotal_visit_cnt)over(partitionBYuserid

ORDERBYvisitmonth)AStotal_visit_cnt

FROM

(SELECTuserid,

visitmonth,

sum(vi$itcount)ASsubtotal_visit_cnt

FROM

(SELECTuserid,

date_foimat(regexp_replace(visitdate,"A'-'),'yyyy-MM')ASvisitmonth,

—8—

visitcount

FROMtest_sql.test1)

t1GROUPBYuserid,

visitmonth)t2

ORDERBYt2.userid,

t2.visitmonth

3.第2题:电商场景TopK统计

3.1需求

有50W个京东店铺,每个顾客访客访问田可一个店铺的任何一个商品时都会产生一条访问日志,

访问日志存储的表名为Visit,访客的用户id为usejid,被访问的店铺名称为shop,数据如下:

userjdshop

u1a

u2b

u1b

u1a

u3c

u4b

u1a

u2c

u5b

u4b

u6c

u2c

u1b

—9—

黑马程序员传智播客旗下

©高端IT教育品牌力41学孑改生命运曲谛堞.学夕夕走2怒曲石杉

u2a

u2a

u3a

u5a

u5a

u5a

请统计:

(1)每个店铺的UV(访客数)

(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

实现

3.2数据准备

CREATETABLEtest_sql.test2(

userjd

string,shop

string)

ROWformatdelimitedFIELDSTERMINATEDBY'\t';

INSERTINTOTABLEtest_sql.test2

VALUES('u1",'a'),

('02','b'),

('u1',•b'),

('u1','a'),

('u3\'c').

('u4','b'),

('u1','a'),

('u2','c'),

('05','b'),

('u4','b'),

('u6\'c'),

('u2\'c'),

('u1',,b'),

('u2丁a)

(12丁a)

10

黑马程序员传智播客旗下

©高端IT教育品牌力41学孑改生命运曲谛堞.学夕夕走2怒曲石杉

('u3','a'),

(157a)

('u5','a'),

('uS;'a');

3.3查询SQL实现

⑴方式1:

〃每个店铺的UV(访客数)

SELECTshop,count(DISHNCTuserjd)

FROMtest_sql.test2

GROUPBYshop

方式2:

〃每个店铺的UV(访客数)

SELECTt.shop,count(*)

FROM

(SELECT

user_id,shopFROM

test_sql.test2

GROUPBYuser_id,shop)

tGROUPBYtshop

〃每个店铺访问次数top3的访客信息,输出店铺名称、访客id、访问次数

SELECT

t2.shop,t2.user_id,t

FROM

(SELECTt1.*,

row_number()over(partitionBYtl.shopORDERBYtDESQ

rankFROM

(SELECTuser_id,shop,count(*)AS

entFROMtest_sql.test2

GROUPBYuser.id,shop)

t1)t2WHERErank<=3

—11—

黑马程序员传智播客旗下

©高端IT教育品牌力41学孑改生命运曲谛堞.学夕夕走2怒曲石杉

4.第3题:订单量统计

4.1需求

已知一个表STG.0RDER,有如下字段:Date,Order_id,Userjd,amount.

数据样例:2017-01-01,10029028,1000003251,33.57.

请给出sql进行统计:

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

金额。(2)给出2017年11月的新客数(指在11月才有第一

笔订单)

实现

4.2数据准备

CREATETABLEtest_sql.test3(

dtstring,

orderjd

string,userjd

string,

amountDECIMAL(10,2))

ROWformatdelimitedHELDSTERMINATEDBY

,\f;

INSERTINTOTABLEtest_sql.test3VALUES(,2017-01-

01',"10029028V1000003251,,33.57);INSERTINTOTABLEtest_sql.test3VALUES

(,2017-01-0171002902971000003251,,33.57);INSERTINTOTABLEtest_sql.test3

VALUES(,2017-01-01',,100290288',"1000003252;33.57);INSERTINTOTABLE

test_sql.test3VALUES('2017-02-02';10029088',,10000032511,33.57);INSERTINTO

TABLEtest_sql.test3VALUES('2017-02-027100290281',,1000003251;33.57);INSERT

INTOTABLEtest_sql.test3VALUES(,2017-02-02';10029028271000003253,,33.57);

INSERTINTOTABLEtest_sql.test3VALUES(,2017-11-

‘1fpqrpa,'’1nnnneqR’2?4卜INSFRTINTCTARIF3门VAIIIF。A-

4.3查询SQL

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

—12—

黑马程序员I传智播客旗下

www.itheima.comI高端IT教育品牌力41学孑改生命运曲谛堞.X1Z学夕夕走2怒曲石杉

SELECTttmon,

count(t1.orderjd)ASorder_cnt,

count(DISTINCTtl.userjd)ASt,

sum(amount)AStotal_amount

FROM

(SELECTorderjd,

userjd,

amount

date_format(dt,'yyyy-MM')

monFROMtest_sql.test3

WHEREdate_format(dt,'yyyy')

'2017')t1GROUPBYtl.mon

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

SELECTcount(user_id)

FROMtest_sql.test3

GROUPBYuser.id

HAVINGdate_format(min(dt);yyyy-MM')='2017-11

5.第4题:大数据排序统计

5.1需求

有一个5000万的用户文件(usejid,name,age),一个2亿记录的用户看电影的记录文件(usejid,url),根据年

龄段观看电影的次数进行排序?

实现

5.2数据准备

CREATETABLEtest_sql.test4user

(userjdstring,namestring.ageint);

CREATETABLEtest_sql.test4log

黑马程序员传智播客旗下

©高端IT教育品牌力41学孑改生命运曲谛堞.学夕夕走2怒曲石杉

(userjdstring,urlstring);

INSERTINTOTABLEtest_sql.test4user

VALUESCOOV/uTJO);INSERTINTOTABLE

test_sql.test4userVALUES('002','u2/5);INSERTINTO

TABLEtest_sql.test4userVALUES('003','u3',15);INSERT

INTOTABLEtest_sql.test4userVALUES(,004';u4',20);

INSERTINTOTABLEtest_sql.test4user

VALUES('005','u5',25);INSERTINTOTABLE

test_sql.test4userVALUES('006','u6',35);INSERTINTO

TABLEtest_sql.test4userVALUES('007','u7',40);INSERT

INTOTABLEtest_sql.test4userVALUES('008','u8',45);

INSERTINTOTABLEtest_sql.test4user

VALUES('009','u9',50);INSERTINTOTABLE

test_sql.test4userVALUES('OO10';u1O',65);

INSERTINTOTABLEtest_sql.test4log

VALUESCOOI'/urlV);INSERTINTOTABLE

test_sql.test4logVALUES('002','url1');INSERTINTO

TABLEtest_sql.test4logVALUES('003','ur12');INSERT

INTOTABLEtest_sql.test4logVALUES('004','url3');

INSERTINTOTABLEtest_sql.test4log

VALUES('005';url3');INSERTINTOTABLE

test_sql.test4logVALUESCOOe'."urH');INSERTINTO

TABLEtest_sql.test4logVALUESCOOT'/urlS');INSERT

5.3查询SQL

SELECT

t2.age_phase,

sum(t1.ent)as

view_cntFROM

(SELECT

userjd,

count(*)ent

FROMtest_sql.test4log

GROUPBYuserjd)t1

JOIN(SELECTuserjd,

CASEWHENage<=10ANDage>0THEN'0-

10'WHENage<=20ANDage>10THEN'10-

—14—

黑马程序员传智播客旗下

©高端IT教育品牌力41学孑改生命运曲谛堞.学夕夕走2怒曲石杉

WHENage>20ANDage<=30THEN'20-30,

WHENage>30ANDage<=40THEN'30-40,

WHENage>40ANDage<=50THEN'40-50'

WHENage>50ANDage<=60THEN'50-60,

WHENage>60ANDage<=70THEN'60-70,

ELSE'70以上'ENDasage.phase

FROMtest_sql.test4user)t2ONttuserjd=

t2.user_idGROUPBYt2.age_phase

6.第5题:活跃用户统计

6.1需求

有日志如下,请写出代码求得所有用户和活跃用户的总数及平均年龄.(活跃用户指连续两天都有访问记录的用户)

日期用户年龄

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」,23

2019-02-15,test_2,19

2019-02-16,test_2,19

实现

6.2数据准备

CREATETABLEtest5(

dtstring,

userjd

string,age

int)

ROWformatdelimitedfieldsterminatedBY

INSERTINTOTABLEtestsal.test5VALUES「2019-02-11'「test1'.23):

—15—

黑马程序员传智播客旗下

©高端IT教育品牌力41学孑改生命运曲谛堞.学夕夕走2怒曲石杉

INSERTINTOTABLEtest_sql.test5VALUES(,2019-02-

11,;test_2'J9);

INSERTINTOTABLEte$t_sql.test5VALUES(,2019-02-

11','test_3',39);INSERTINTOTABLEtest_sql.test5VALUES

('2019-02-1V,'test」',23);INSERTINTOTABLEtest_sql.test5

VALUES(,2019-02-11';test_3'39);INSERTINTOTABLE

test_sql.test5VALUES('2019-02-11VtestJ',23);INSERTINTO

TABLEtest_sql.test5VALUES('2019-02-12',les^',19);INSERT

INTOTABLEtest.sql.testSVALUES('2019-02-13';test_1',23);

6.3查询SQL

SELECTsum(total_user_cnt)total_user_cnt,

$um(total_user_avg_age)

total_user_avg_age,sum(two_days_cnt)

two_days_cntsum(avg_age)avg_age

FROM

(SELECT0total_user_cnt,

0total_user_avg_age,

count(*)AS

two_days_cnt

cast(sum(age)/count(*)ASdecimal(5,2))ASavg.age

FROM

(SELECTuser_id,max(age)

ageFROM

(SELECTuser_id,max(age)

ageFROM

(SELECTuser_id,age,date_sub(dt,rank)

flagFROM

(SELECTdt,user_id,max(age)

age,row_numberQover(PARTITIONBY

userjd

ORDERBYdt)rank

FROMtest_sql.test5

GROUPBYdt,user_id)t1)

t2GROUPBYuserjd,flag

HAVINGcount(*)>=2}

t3GROUPBYuserjd)t4

UNIONALLSELECTcount(*)total_user_cnt.

16

黑马程序员传智播客旗下

0www.itheima.com高端IT教育品牌力41学孑改生命运曲谛堞.学夕夕走2怒曲石杉

cast(sum(age)/count(*)ASdecimal(5,2))total_user_avg_age,

0two_days_cnt,

0avg_age

FROM

(SELECTuserjd,

max(age)

ageFROM

test_sql.test5GROUP

BYuserid)t5)t6

7.第6题:电商购买金额统计实战

7.1需求

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

表ordertable字段(购买用户:userid,金额:money,购买时间:paymenttime(格式:2017-10-01),订单id:

orderid实现

7.2数据准备

CREATETABLEtest_sql.test6(

useridstring,

money

decimal。0,2),

paymenttime

string,orderid

string);

INSERTINTOTABLEtest_sql.test6VALUES('001'J00,,2017-10-

017123');INSERTINTOTABLEtest_sql.test6

VALUESfOOT,200,,2017-10-02','124');INSERTINTOTABLE

73查询SQL

SELECT

userid.

—17—

黑马程序员传智播客旗下

©高端IT教育品牌力41学孑改生命运曲谛堞.学夕夕走2怒曲石杉

paymenttime,

money

orderi

d

from(SELECTuserid,

money,

paymenttime,

orderid,

row_number()over(PARTITIONBYuserid

ORDERBYpaymenttime)rank

FROMtest_sql.test6

WHEREdate_format(paymenttime,'yyyy-MM')='2017-

8.第7题:教育领域SQL实战

8.1需求

现有图书管理数据库的三个数据模型如下:

图书(数据表名:BOOK)

序号字段名称字段描述字段类型

1BOOKJD总编号文本

2SORT分类号

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论