Python大數(shù)據(jù)SQL面試寶典2022年

Python大數(shù)據(jù)V2.0高頻核心面試題

版本增加模塊日志修改時間

V2.0增加騰訊、華泰證券面試題20220101

V2.1增加Hive部分SQL經(jīng)典10題20220201

適用范圍：大數(shù)據(jù)各個階段學習完之后提前準備階段重點面試題

注意：如何講解項目中的技術點？

(1)講清楚知識點是什么

(2)講清楚知識點原理是什么

(3)講清楚知識點如何使用

講清楚有沒有優(yōu)化及應用場景使用技巧：以計算組件為核心復習，主要復習Hive,Spark及Flink計算組件。

—1—

目錄

Python大數(shù)據(jù)V2.0高頻核心面試題........................................................1

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

初級SQL?Hive10題......................................................................7

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

2.第1題：訪問量統(tǒng)計.............................................................7

3.第2題：電商場景TopK統(tǒng)計......................................................9

4.第3題：訂單量統(tǒng)計............................................................12

5.第4題：大斕排序統(tǒng)計........................................................13

6.第5題：活躍用戶統(tǒng)計..........................................................15

7.第6題：電商購買金額統(tǒng)計實戰(zhàn).................................................17

8.第7題：教育領域SQL實戰(zhàn)......................................................18

9.第8題：服務日志SQL統(tǒng)計......................................................23

10.第9題：充值日志SQL實戰(zhàn).....................................................24

11.第10題：電商分組TopK實戰(zhàn)..................................................26

微眾銀行..............................................................................28

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

2.源數(shù)簸：...................................................................28

3.創(chuàng)建庫和源數(shù)據(jù)表，并生成數(shù)據(jù)。..............................................29

4.第1題：授信金額統(tǒng)計..........................................................35

5.第2題：貸款產(chǎn)品不良統(tǒng)計......................................................41

6.第3題：逾期率統(tǒng)計............................................................46

7.第4題：用戶畫像統(tǒng)計..........................................................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執(zhí)行順序...........................................................72

4.第3題班級分組內按成績排名...................................................72

5.第4題聚合窗口函數(shù)...........................................................74

6.第5題時間序列犍...........................................................75

交通銀行..............................................................................79

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

2.第1題金額環(huán)比增長率.........................................................79

3.第2題金額排名占比...........................................................80

4.第3題數(shù)據(jù)發(fā)散問題...........................................................82

5.第4題left/semi/anti/join區(qū)分.................................................84

百麗..................................................................................86

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

2.第1題Hive雌導入...........................................................86

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

4.第3題百麗業(yè)務統(tǒng)計分析.......................................................87

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

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

2.第1題求出每個部門工資最高的前三名員工并計算這些員工的工資占所屬部門總工資的

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

3.第2題統(tǒng)計每年入職總數(shù)以及截至本年累計入職總人數(shù)。.........................92

4.第3題每個品牌的總營銷天數(shù)（時間序列題型）.....................................93

騰訊外包（微信相關）.....................................................................95

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

2.第1題Python數(shù)據(jù)清洗轉換...................................................95

—3—

3.第2題查詢當天閱讀最多的100篇文章........................................97

4.第3題查詢當天閱讀最多的10個公眾號.......................................97

小米電商..............................................................................99

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

2.第1題用戶訂單面計.........................................................99

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

4.第3題訂單量中位數(shù)..........................................................101

5.第3題訂單top10的城市......................................................102

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

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

2.第1題SQL鄲出考察..........................................................103

騰訊頻雌開發(fā)A卷.................................................................107

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

2.選擇題.......................................................................107

3.解答題.......................................................................108

4.問答題.......................................................................115

騰訊酶號游戲直播..................................................................119

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

2.第1題：活躍用戶數(shù)、次日留存率..............................................119

3.第2題：用戶留存率、用戶活躍度得分排序......................................122

4.第3題考察點：row_number..........................................................................................124

5.第4題考察點：having子句的使用.............................................124

帆軟［MySQL方向］.....................................................................127

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

2.第1題：銷售環(huán)比增長率.......................................................127

3.第2題分組max-min-avg................................................................................................128

4.第3題銷售業(yè)績綜合統(tǒng)計.....................................................130

5.第4題LEFTJION.RIGHTJONXINNERJOIN.FULLJOIN的區(qū)別...............137

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

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

2.第1題:UsePysparktogenerateplotlinechart...........................................................139

—4—

3.第2題:WriteaSQLquerytogetthesecondhighestsalaryfromtheEmployeetable.139

4.第3題:HiveSQL累積訪問次數(shù)................................................140

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

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

2.第1題舉例一下Hive中的數(shù)據(jù)類型？...........................................142

3.第2題最高成績課程&每門課程大于80分的學生................................142

4.第3題連續(xù)三天登錄人員.....................................................143

附錄.................................................................................148

1.微眾銀行....................................................................148

2.華泰證券....................................................................151

3.招商信諾人壽................................................................154

4.交通銀行....................................................................157

5.百麗.........................................................................162

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

7.騰訊外包（微信相關）...........................................................164

8.小米電商....................................................................164

9.鄂州順....................................................................165

10.騰訊游戲數(shù)據(jù)開發(fā)A卷.......................................................165

11.騰訊視頻號游戲直播.........................................................165

12.帆軟［MySQL方向］...........................................................165

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

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

—5—

大數(shù)據(jù)必備SQL提高秘籍

提高SQL功底的思路。

1、造數(shù)據(jù)。因為有期g支撐，會方便我們根據(jù)數(shù)據(jù)結果去不斷調整SQL的寫法。

造數(shù)據(jù)語法既可以createtable再insertinto,也可以用下面的createtemporaryviewxxas

values語句，更簡單。

其中createtemporaryviewxxasvalues語句，SparkSQL語法支持，hive不支持

2、先將結果表畫出來,包括結果字段名有哪些，數(shù)據(jù)量也畫幾條。這是分析他要什么。

從藤到結果表，一路可能要走多個步驟，其實就是可育牌要多個子直詢，過程多就用withas來重

構提高可讀性。

3、要由簡單過度到復雜,不要一下子就寫一個很復雜的。

先寫簡單的select*fromtabIe..每個中間步驟都執(zhí)行打印結果，看是否符合預

期，根據(jù)中間結果，進一步調整修飾SQL語句，再執(zhí)行，直到接近結果表。

4、數(shù)據(jù)量要小，工具要快，如果用hive,就設置set

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

setspark.sql.shuffle.partitions=4;

—6—

初級SQL-HivelO題

1.需求列表

2.第1題：訪問量統(tǒng)計

2.1需求

我們有如下的用戶訪問數(shù)據(jù)

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充計出每個用戶的累積訪問次數(shù)，如下表所示：

用戶id月份小計累積

u012017-1111

01

u012017-1223

02

u022017-1212

01

u032017-88

01

—7—

u042017-0133

實現(xiàn)

2.2數(shù)據(jù)準備

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統(tǒng)計

3.1需求

有50W個京東店鋪，每個顧客訪客訪問田可一個店鋪的任何一個商品時都會產(chǎn)生一條訪問日志，

訪問日志存儲的表名為Visit,訪客的用戶id為usejid,被訪問的店鋪名稱為shop,數(shù)據(jù)如下：

userjdshop

u1a

u2b

u1b

u1a

u3c

u4b

u1a

u2c

u5b

u4b

u6c

u2c

u1b

—9—

黑馬程序員傳智播客旗下

?高端IT教育品牌力41學孑改生命運曲諦堞.學夕夕走2怒曲石杉

u2a

u2a

u3a

u5a

u5a

u5a

請統(tǒng)計：

(1)每個店鋪的UV(訪客數(shù))

(2)每個店鋪訪問次數(shù)top3的訪客信息。輸出店鋪名稱、訪客id、訪問次數(shù)

實現(xiàn)

3.2數(shù)據(jù)準備

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實現(xiàn)

⑴方式1：

〃每個店鋪的UV(訪客數(shù))

SELECTshop,count(DISHNCTuserjd)

FROMtest_sql.test2

GROUPBYshop

方式2：

〃每個店鋪的UV(訪客數(shù))

SELECTt.shop,count(*)

FROM

(SELECT

user_id,shopFROM

test_sql.test2

GROUPBYuser_id,shop)

tGROUPBYtshop

〃每個店鋪訪問次數(shù)top3的訪客信息,輸出店鋪名稱、訪客id、訪問次數(shù)

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題：訂單量統(tǒng)計

4.1需求

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

數(shù)據(jù)樣例：2017-01-01,10029028,1000003251,33.57.

請給出sql進行統(tǒng)計：

（1）給出2017年每個月的訂單數(shù)、用戶數(shù)、總成交

金額。（2）給出2017年11月的新客數(shù)（指在11月才有第一

筆訂單）

實現(xiàn)

4.2數(shù)據(jù)準備

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年每個月的訂單數(shù)、用戶數(shù)、總成交金額。

—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月的新客數(shù)(指在11月才有第一筆訂單)

SELECTcount(user_id)

FROMtest_sql.test3

GROUPBYuser.id

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

5.第4題：大數(shù)據(jù)排序統(tǒng)計

5.1需求

有一個5000萬的用戶文件(usejid,name,age),一個2億記錄的用戶看電影的記錄文件(usejid,url),根據(jù)年

齡段觀看電影的次數(shù)進行排序？

實現(xiàn)

5.2數(shù)據(jù)準備

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題：活躍用戶統(tǒng)計

6.1需求

有日志如下，請寫出代碼求得所有用戶和活躍用戶的總數(shù)及平均年齡.(活躍用戶指連續(xù)兩天都有訪問記錄的用戶)

日期用戶年齡

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

實現(xiàn)

6.2數(shù)據(jù)準備

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題：電商購買金額統(tǒng)計實戰(zhàn)

7.1需求

請用sql寫出所有用戶中在今年10月份第一次購買商品的金額，

表ordertable字段（購買用戶：userid,金額:money,購買時間：paymenttime（格式：2017-10-01）,訂單id:

orderid實現(xiàn)

7.2數(shù)據(jù)準備

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實戰(zhàn)

8.1需求

現(xiàn)有圖書管理數(shù)據(jù)庫的三個數(shù)據(jù)模型如下：

圖書(數(shù)據(jù)表名：BOOK)

序號字段名稱字段描述字段類型

1BOOKJD總編號文本

2SORT分類號