2025年大數(shù)據(jù)分析師專業(yè)能力測(cè)評(píng)試卷及答案一、單項(xiàng)選擇題（每題2分，共20分。每題只有一個(gè)正確答案，錯(cuò)選、多選、不選均不得分）1.某電商公司2024年“618”大促期間，訂單表order_info包含字段order_id、user_id、sku_id、order_amt、pay_time?，F(xiàn)需統(tǒng)計(jì)“大促首日（2024061800:00:00—23:59:59）”每小時(shí)GMV（單位：萬(wàn)元）并保留兩位小數(shù)，以下PrestoSQL寫法正確的是A.selectdate_trunc('hour',pay_time)ashr,round(sum(order_amt)/1e4,2)asgmvfromorder_infowherepay_timebetween'20240618'and'20240619'groupby1orderby1;B.selecthour(pay_time)ashr,round(sum(order_amt)/10000,2)asgmvfromorder_infowherepay_time>='20240618'andpay_time<'20240619'groupby1orderby1;C.selectdate_format(pay_time,'%H')ashr,sum(order_amt)/1e4asgmvfromorder_infowherepay_timelike'20240618%'groupby1;D.selectdate_trunc('hour',pay_time)ashr,round(sum(order_amt)/10000,2)asgmvfromorder_infowherepay_time>='20240618'andpay_time<'20240619'groupby1orderby1;答案：D解析：A把1e4寫成1e4但單位是萬(wàn)元，應(yīng)除以10000；B用hour()函數(shù)會(huì)丟失日期信息，導(dǎo)致跨天混疊；C用like導(dǎo)致索引失效且date_format返回字符串；D用date_trunc精確到小時(shí)，區(qū)間左閉右開(kāi)，符合標(biāo)準(zhǔn)。2.使用Spark3.4讀取Hive分區(qū)表user_active(dt='20240618',hour='14')，表存儲(chǔ)格式為ORC且開(kāi)啟布隆過(guò)濾器，以下最能減少IO的寫法是A.spark.read.table("user_active").filter("dt='20240618'andhour='14'")B.spark.read.option("basePath","/warehouse/user_active").format("orc").load("dt=20240618/hour=14")C.spark.sql("selectfromuser_activewheredt='20240618'andhour='14'")D.spark.read.orc("/warehouse/user_active/dt=20240618/hour=14")答案：B解析：A、C走HiveMetastore但無(wú)法下推分區(qū)過(guò)濾器到ORC的布隆過(guò)濾器；D未啟用向量化讀；B直接指定分區(qū)路徑，Spark可跳過(guò)無(wú)關(guān)文件，且ORC的布隆過(guò)濾器對(duì)點(diǎn)查有效。3.某集群YARN總vcore=800，總mem=2400GB，隊(duì)列queueA配置capacity=50%，maxCapacity=80，userlimitfactor=2。若當(dāng)前queueA已用vcore=300，mem=900GB，用戶u1已占vcore=200，mem=600GB，u1繼續(xù)提交一個(gè)任務(wù)需vcore=150，mem=450GB，則A.因userlimitfactor=2，u1可占用最多400vcore，可立即提交B.因隊(duì)列maxCapacity=80%，隊(duì)列已用資源未超，可立即提交C.因用戶已用資源超過(guò)userlimit，需等待D.因集群總資源不足，需等待答案：C解析：userlimitfactor=2表示單用戶最多占用隊(duì)列容量2=50%2=100%即800vcore、2400GB，但“已用”與“本次申請(qǐng)”累加后vcore=350>4000.5=200，觸發(fā)userlimit限制，需等待。4.在Flink1.17中，使用事件時(shí)間處理，設(shè)置watermark策略為BoundedOutOfOrderness(5s)，某并行源接收到事件e1(2024061814:00:03)、e2(14:00:07)、e3(14:00:02)，當(dāng)前系統(tǒng)時(shí)間為14:00:10，則第三個(gè)watermark值為A.1970010100:00:00B.2024061814:00:02C.2024061814:00:00D.2024061814:00:02.999答案：C解析：watermark=maxTimestamp5s，全局取最??；e1、e2、e3最大事件時(shí)間為14:00:07，減5s得14:00:02，但watermark對(duì)齊到秒級(jí)，故為14:00:00。5.使用Pythonstatsmodels.tsa.statespace.SARIMAX建立(p,d,q)(P,D,Q,s)模型，對(duì)日銷量做7天季節(jié)性預(yù)測(cè)，經(jīng)ADF檢驗(yàn)d=1，經(jīng)OCSB檢驗(yàn)D=1，s=7，若AIC最小組合為p=2,q=3,P=1,Q=2，則模型階數(shù)寫作A.SARIMAX(2,1,3)(1,1,2,7)B.SARIMAX(2,1,3)(1,7,2)C.SARIMAX(2,1,3)(1,1,2)7D.SARIMAX(2,1,3)×(1,1,2)7答案：A解析：statsmodels順序?yàn)?p,d,q)(P,D,Q,s)，故A正確。6.ClickHouseMergeTree引擎表dtDate,sku_idUInt64,priceFloat64,create_timeDateTime)partitionbytoYYYYMM(dt)orderby(sku_id,dt)，以下SQL可正確實(shí)現(xiàn)“20240618當(dāng)天price上漲超過(guò)10%的sku”的是A.selectsku_idfromtablewheredt='20240618'andprice>1.1any(price)B.selectsku_idfromtablewheredt='20240618'andprice>1.1(selectpricefromtableprewheredt='20240617'andsku_id=table.sku_id)C.selecta.sku_idfromtablealeftjointablebona.sku_id=b.sku_idwherea.dt='20240618'andb.dt='20240617'anda.price>b.price1.1D.selectsku_idfromtablearrayjoinpriceaspwheredt='20240618'andp>1.1price[dt='20240617']答案：C解析：ClickHouse不支持correlatedsubquery返回標(biāo)量，B報(bào)錯(cuò)；A無(wú)昨日價(jià)格；D語(yǔ)法錯(cuò)誤；C自連接清晰可行。7.使用Hadoop3.3集群，副本數(shù)=3，塊大小=256MB，現(xiàn)上傳1.8GB文件，NameNode內(nèi)存中每個(gè)塊元數(shù)據(jù)約150字節(jié)，則該文件占用NameNode堆內(nèi)存約為A.3456字節(jié)B.3456KBC.3456MBD.10.8KB答案：B解析：1.8GB/256MB=7.2向上取整8塊，每塊150字節(jié)，83150=3600字節(jié)≈3.5KB，最接近3456KB為B，但精確值3600字節(jié)，選項(xiàng)無(wú)精確，B為最接近數(shù)量級(jí)。8.某GBDT模型使用LightGBM，參數(shù)num_leaves=31,learning_rate=0.1,n_estimators=200,early_stopping_rounds=20，驗(yàn)證集AUC連續(xù)20輪未提升，訓(xùn)練停止于第180輪，則最終模型樹(shù)棵數(shù)為A.180B.200C.160D.181答案：A解析：earlystopping觸發(fā)時(shí)保留最佳迭代，最佳為180輪，故180棵。9.在PostgreSQL15中，表sales(sale_id,sku_id,sale_dt,amt)建立btree索引(sale_dt,sku_id)，以下查詢可走indexonlyscan的是A.selectsku_id,sum(amt)fromsaleswheresale_dtbetween'20240601'and'20240630'groupbysku_id;B.selectcount()fromsaleswheresale_dt='20240618';C.selectsale_dt,sku_idfromsaleswheresku_id=10086;D.selectmax(amt)fromsaleswheresale_dt='20240618';答案：B解析：indexonlyscan需索引覆蓋查詢列，B只查count()，索引包含sale_dt，且visibilitymap全可見(jiàn)，可indexonly；A需amt不在索引；C首列不是sku_id，需回表；D需amt。10.使用Airflow2.7，某DAG每日02:00調(diào)度，start_date=20240610，catchup=True，今日為2024061816:00，則首次觸發(fā)后將回填的run日期為A.20240610B.20240611C.20240617D.20240618答案：A解析：catchup=True會(huì)自start_date起依次回填，故最早run為20240610。二、多項(xiàng)選擇題（每題3分，共15分。每題有兩個(gè)或兩個(gè)以上正確答案，多選、少選、錯(cuò)選均不得分）11.關(guān)于HiveonTez與HiveonSpark對(duì)比，以下說(shuō)法正確的有A.Tez任務(wù)DAG在客戶端構(gòu)建，SparkDAG在Driver構(gòu)建B.Tez支持容器復(fù)用，SparkonYARN也支持executor復(fù)用C.Tez無(wú)Driver常駐，SparkDriver常駐D.Tez任務(wù)失敗重試由AM負(fù)責(zé)，Spark由Driver負(fù)責(zé)答案：ACD解析：B錯(cuò)誤，Sparkexecutor生命周期與Application相同，不存在跨Application復(fù)用。12.在Kafka3.5中，topicfoo，分區(qū)=6，副本=3，min.insync.replicas=2，produceracks=all，以下場(chǎng)景下消息可被commit的有A.兩個(gè)follower副本所在broker同時(shí)宕機(jī)，leader正常B.leader副本宕機(jī)，新leader尚未選出C.leader與一follower正常，另一follower落后但存活D.所有broker正常，但網(wǎng)絡(luò)抖動(dòng)導(dǎo)致leader與一follower暫時(shí)失聯(lián)答案：AC解析：B無(wú)leader；D失聯(lián)后ISR收縮，若leader仍在ISR且insync>=2可commit。13.使用Pythonpandas2.0，df為DataFrame，列sku,sale_dt,price，以下可正確計(jì)算“每個(gè)sku近3日均價(jià)”的代碼有A.df.groupby('sku').apply(lambdax:x.set_index('sale_dt').price.rolling('3D').mean())B.df.set_index('sale_dt').groupby('sku').price.rolling('3D').mean()C.df.sort_values(['sku','sale_dt']).groupby('sku').price.rolling(3,min_periods=1).mean()D.df.assign(avg3=df.sort_values('sale_dt').groupby('sku').price.transform(lambdax:x.rolling(3).mean()))答案：AB解析：C、D為固定窗口3行而非3日；A、B時(shí)間窗口正確。14.關(guān)于ClickHouse物化視圖，以下說(shuō)法正確的有A.物化視圖實(shí)質(zhì)是觸發(fā)器+目標(biāo)表B.源表insert會(huì)同步寫入物化視圖目標(biāo)表C.物化視圖可定義POPULATE關(guān)鍵字回填歷史數(shù)據(jù)D.物化視圖目標(biāo)表可再建物化視圖形成級(jí)聯(lián)答案：ABCD解析：均正確，ClickHouse物化視圖支持級(jí)聯(lián)與POPULATE。15.在TensorFlow2.12中，使用tf.data.Dataset.from_generator讀取大型文本，以下可提升性能的有A.設(shè)置num_parallel_calls=tf.data.AUTOTUNEB.使用.prefetch(tf.data.AUTOTUNE)C.在generator內(nèi)部加time.sleep(0.1)防止CPU占用過(guò)高D.使用.cache()將數(shù)據(jù)緩存在內(nèi)存答案：ABD解析：C人為降速，反而降低性能。三、判斷題（每題1分，共10分。正確打“√”，錯(cuò)誤打“×”）16.HDFS聯(lián)邦機(jī)制中，每個(gè)NameNode管理獨(dú)立的命名空間，但共享DataNode存儲(chǔ)。答案：√17.SparkSQL的catalyst優(yōu)化器在邏輯優(yōu)化階段會(huì)消除笛卡爾積。答案：×18.Flink的checkpoint屏障是ChandyLamport算法的實(shí)現(xiàn)。答案：√19.在Pythonscikitlearn中，RandomForestClassifier的n_jobs=1表示使用所有核心，包括超線程虛擬核心。答案：√20.ORC文件格式支持lightweightupdate，即原地修改單條記錄。答案：×21.Kafka的consumergrouprebalance觸發(fā)條件包括分區(qū)數(shù)變化、consumer成員變化。答案：√22.ClickHouse的ALTERTABLEUPDATE是原子操作，不會(huì)阻塞讀。答案：×23.使用Airflow的Sensor時(shí)，設(shè)置mode='reschedule'可避免占用worker槽位。答案：√24.XGBoost的直方圖算法與LightGBM的GOSS算法均屬于行采樣優(yōu)化。答案：×25.PostgreSQL的MVCC機(jī)制中，更新操作實(shí)際插入新元組，舊元組標(biāo)記為dead。答案：√四、填空題（每空2分，共20分）26.在Spark3.4中，通過(guò)______參數(shù)可開(kāi)啟自適應(yīng)查詢執(zhí)行，默認(rèn)值為_(kāi)_____。答案：spark.sql.adaptive.enabled；true27.Flink的statebackend為RocksDBStateBackend時(shí)，checkpoint默認(rèn)存儲(chǔ)到______，需配置______目錄。答案：JobManager的內(nèi)存；state.checkpoints.dir28.Hive中，將ORC表壓縮算法設(shè)為ZSTD，需在建表語(yǔ)句設(shè)置______='ZSTD'。答案：press29.ClickHouse中，查詢selecttoStartOfInterval(toDateTime('2024061814:35:28'),INTERVAL10minute)返回結(jié)果為_(kāi)_____。答案：2024061814:30:0030.Python中，使用______函數(shù)可將pandasDataFrame直接寫入Kafka，需指定______序列化函數(shù)。答案：df.to_kafka；key.serializer與value.serializer31.在HadoopYARN中，查看正在運(yùn)行的application列表的命令為yarn______。答案：applicationlist32.PostgreSQL中，查看當(dāng)前會(huì)話正在執(zhí)行SQL的pid，可查詢系統(tǒng)視圖______。答案：pg_stat_activity33.使用Linux______命令可查看磁盤塊大小，常見(jiàn)大小為_(kāi)_____KB。答案：stat/boot；434.在TensorFlow中，設(shè)置隨機(jī)種子保證可重復(fù)，需同時(shí)設(shè)置tf.random.set_seed與______。答案：np.random.seed35.Airflow的DAG文件默認(rèn)掃描目錄為_(kāi)_____。答案：$AIRFLOW_HOME/dags五、簡(jiǎn)答題（每題10分，共30分）36.描述一次Kafka集群因brokerfullGC導(dǎo)致頻繁rebalance的排查與解決過(guò)程。答案：1)現(xiàn)象：消費(fèi)組lag突增，rebalance耗時(shí)30s，循環(huán)觸發(fā)。2)監(jiān)控：查看brokergclog，發(fā)現(xiàn)老年代堆占用90%，F(xiàn)ullGC單次2s，間隔10s。3)定位：使用jmapdump堆，MAT分析發(fā)現(xiàn)ReplicaFetcherThread積壓1.2GBByteBuffer。4)根因：副本拉取線程因磁盤IO打滿，fetch響應(yīng)阻塞，導(dǎo)致網(wǎng)絡(luò)層緩存無(wú)法釋放。5)解決：a)升級(jí)brokerJVM至G1GC，設(shè)置XX:MaxGCPauseMillis=100；b)增加磁盤帶寬，SSD緩存熱分區(qū)；c)調(diào)大replica.socket.receive.buffer.bytes=1MB；d)臨時(shí)調(diào)大rebalance.timeout.ms=60s，減少頻繁重試。6)驗(yàn)證：FullGC降至0.2s，rebalance間隔>10min，lag恢復(fù)。37.說(shuō)明使用SparkStructuredStreaming進(jìn)行exactlyonce語(yǔ)義的核心機(jī)制，并給出代碼示例。答案：核心機(jī)制：1)可重播source：如Kafka，維護(hù)offset于checkpoint；2)冪等sink：如MySQL使用insertonduplicatekeyupdate；3)原子提交：微批次結(jié)果與offset同一事務(wù)寫入checkpoint+sink。代碼示例：```scalavalspark=SparkSession.builder.config("spark.sql.streaming.checkpointLocation","/chk").getOrCreate()valdf=spark.readStream.format("kafka").option("kafka.bootstrap.servers","kfk:9092").option("subscribe","foo").load()valparsed=df.selectExpr("cast(valueasstring)asjson").select(from_json($"json",schema).as("data")).select("data.")valquery=parsed.writeStream.outputMode("append").foreachBatch{(batch,batchId)=>batch.coalesce(10).write.format("jdbc").option("url","jdbc:mysql://mysql:3306/test").option("dbtable","target").option("user","root").option("password","").mode("append").save()//offset已自動(dòng)提交至/chk}.start()query.awaitTermination()```38.解釋ClickHouse的MergeTree索引粒度(index_granularity)對(duì)查詢性能的影響，并給出調(diào)優(yōu)案例。答案：index_granularity表示每多少個(gè)行組建一條主鍵索引，默認(rèn)8192。影響：1)粒度越小，索引文件越大，內(nèi)存占用高，但mark過(guò)濾更精細(xì)，適合點(diǎn)查；2)粒度越大，壓縮率更高，順序掃描快，但skipindex效果差。案例：某日志表千億行，按(user_id,event_time)排序，原index_granularity=8192，點(diǎn)查user_id需掃描5GB。調(diào)優(yōu)：1)建表設(shè)置index_granularity=1024；2)增加bloom_filter索引(user_id)granularity=1；3)查詢selectfromlogwhereuser_id=123456，掃描降至80MB，QPS由200提升至1k，內(nèi)存增加8%。六、綜合應(yīng)用題（共25分）39.某短視頻公司2024年6月上線“實(shí)時(shí)熱點(diǎn)榜”，需求：1)統(tǒng)計(jì)過(guò)去1小時(shí)每10秒的視頻播放次數(shù)，延遲<5s；2)過(guò)濾播放時(shí)長(zhǎng)<3s的臟數(shù)據(jù)；3)結(jié)果寫入Redis，格式為zset，key=hot:rank:{{timestamp}}，member=video_id，score=播放次數(shù)；4)支持歷史回溯，可重放。請(qǐng)給出完整技術(shù)方案，包括：a)數(shù)據(jù)流架構(gòu)圖（文字描述即可）；b)Flink作業(yè)核心代碼（Java/Scala/Python均可）；c)Redis寫入冪等保證；d)回溯方案。答案：a)架構(gòu)：APP埋點(diǎn)→Nginx→Kafka(topic=play,partition=24,retention=7天)→Flink→Redis(cluster,持久化AOF)→API網(wǎng)關(guān)→前端。側(cè)路：Kafka→Flume→HDFS（離線備份）。b)Flink代碼（Scala）：```scalavalenv=StreamExecutionEnvironment.getExecutionEnvironmentenv.enableCheckpointing(5000)env.setStreamTimeCharacteristic(TimeCharacteristic.EventTime)valkafkaSrc=newFlinkKafkaConsumer[String]("play",newSimpleStringSchema(),kafkaProps).assignTimestampsAndWatermarks(WatermarkStrategy.forBoundedOutOfOrderness(Duration.ofSeconds(3)))valplay=env.addSource(kafkaSrc).map(JSON.parseObject).filter(_.getLong("play_duration")>=3000).map(x=>(x.getString("video_id"),1L)).keyBy(_._1).window(SlidingEventTimeWindows.of(Time.minutes(60),Time.seconds(10))).aggregate(newCountAgg(),newWindowResult()).addSink(newRedisSink[HotItem](jedisPoolConfig,newRedisMapper[HotItem]{overridedefgetCommandDescription=newRedisCommandDescription(RedisCommand.ZADD,"hot:rank:%s")overridedefgetKeyFromData(t:HotItem)=t.windowEnd+""overridedefgetValueFromData(t:HotItem)=t.videoIdoverridedefgetScoreFromData(t:HotItem)=t.count.toDouble}))env.execute("hotrank")```c)冪等：RedisZADD本身冪等；Flinkcheckpoint與Redis事務(wù)保證exactlyonce；設(shè)置keyTTL=2h，防止堆積。d)回溯：Kafkatopic保留7天，通過(guò)Flinksavepoint指定offset重放；離線部分通過(guò)HDFS按小時(shí)分區(qū)，Spark批處理補(bǔ)錄缺失排名。七、計(jì)算與建模題（共20分）40.某電商提供用戶行為數(shù)據(jù)表：user_log(user_id,sku_id,action_type,log_time)，action_type∈{'view','cart','buy'}，數(shù)據(jù)量50億行，存儲(chǔ)于Hive分區(qū)表，分區(qū)字段dt?，F(xiàn)需構(gòu)建轉(zhuǎn)化率模型，預(yù)測(cè)“加購(gòu)且24小時(shí)內(nèi)購(gòu)買”概率，要求：1)特征工程：提取用戶近7天view→cart→buy序列轉(zhuǎn)化率、sku近30天銷量、用戶近30天客單價(jià)；2)模型：使用XGBoost，調(diào)參目標(biāo)AUC>0.85；3)評(píng)估：按user_id分層采樣，訓(xùn)練/驗(yàn)證/測(cè)試=6/2/2，給出AUC、召回@top10%。請(qǐng)給出：a)特征SQL（可分段）；b)Python建模代碼（含調(diào)參）；c)評(píng)估結(jié)果與解釋。答案：a)特征SQL：```sqlwithbaseas(selectuser_id,sku_id,action_type,log_time,row_number()over(partitionbyuser_id,sku_id,action_typeorderbylog_time)asrnfromuser_logwheredt>=date_sub(current_date,30)),seqas(selectuser_id,sku_id,max(casewhenaction_type='view'thenlog_timeend)aslast_view,max(casewhenaction_type='cart'thenlog_timeend)aslast_cart,max(casewhenaction_type='buy'thenlog_timeend)aslast_buyfrombasegroupbyuser_id,sku_id),labelas(selectuser_id,sku_id,casewhenlast_cartisnotnullandlast_buy>last_cartandunix_timestamp(last_buy)unix_timestamp(last_cart)<=86400then1else0endaslabelfromseq),user_featas(selectuser_id,sum(casewhenaction_type='view'then1else0end)asview_7d,sum(casewhenaction_type='cart'then1else0end)ascart_7d,sum(casewhenaction_type='buy'then1else0end)asbuy_7dfromuser_logwheredt>=date_sub(current_date,7)groupbyuser_id),sku_featas(selectsku_id,count()assale_30dfromuser_logwhereaction_type='buy'anddt>=date_sub(current_date,30)groupbysku_id),user_amtas(selectuser_id,avg(order_amt)asavg_amt_30dfromorderswheredt>=date_sub(current_date,30)groupbyuser_id)selectl.user_id,l.sku_id,l.label,