2026年影視行業(yè)的數(shù)據(jù)分析師之路：SQL技能與行業(yè)洞察練習(xí)題一、SQL基礎(chǔ)操作題（共5題，每題10分，總分50分）1.創(chuàng)建與查詢影視數(shù)據(jù)庫表結(jié)構(gòu)假設(shè)你正在為一個影視公司設(shè)計數(shù)據(jù)庫，需要創(chuàng)建以下兩個表：-`movies`表：包含電影的基本信息，字段包括`movie_id`（主鍵，自增）、`title`（電影名稱）、`release_year`（上映年份）、`genre`（類型）、`budget`（預(yù)算）、`revenue`（票房收入）。-`actors`表：包含演員的基本信息，字段包括`actor_id`（主鍵，自增）、`name`（演員姓名）、`birth_year`（出生年份）、`nationality`（國籍）、`movie_id`（外鍵，關(guān)聯(lián)`movies`表的`movie_id`）。要求：①使用SQL語句創(chuàng)建這兩個表。②編寫SQL查詢語句，列出2020年后上映的所有電影及其類型、預(yù)算和票房收入。2.插入與更新影視數(shù)據(jù)假設(shè)你已創(chuàng)建上述表，請完成以下操作：①插入以下電影數(shù)據(jù)：(title,release_year,genre,budget,revenue)('流浪地球2',2023,'科幻',200000000,450000000),('滿江紅',2023,'懸疑',80000000,300000000),('熊出沒·逆轉(zhuǎn)時空',2023,'動畫',50000000,200000000)②更新電影《滿江紅》的票房收入為350000000。3.連接查詢與統(tǒng)計假設(shè)`actors`表已插入以下演員數(shù)據(jù)：(actor_id,name,birth_year,nationality,movie_id)(1,'張譯',1983,'中國',1),(2,'沈騰',1981,'中國',2),(3,'張子楓',2001,'中國',2),(4,'劉德華',1961,'中國香港',1),(5,'田壯壯',1955,'中國',3)要求：①編寫SQL查詢語句，列出每部電影的導(dǎo)演（假設(shè)導(dǎo)演信息存儲在`directors`表中，字段包括`director_id`、`name`、`movie_id`），并統(tǒng)計每部電影有多少演員。②查詢預(yù)算超過1億且演員數(shù)量超過2的電影名稱和演員數(shù)量。4.子查詢與聚合函數(shù)要求：①查詢2020年后上映的科幻電影中，票房收入最高的電影名稱和票房收入。②查詢演員國籍為“中國”的演員數(shù)量，以及平均出生年份。5.索引與優(yōu)化要求：①為`movies`表的`release_year`字段創(chuàng)建索引，以加速按年份查詢。②編寫SQL語句，解釋創(chuàng)建索引后如何提高查詢效率。二、影視行業(yè)洞察分析題（共5題，每題15分，總分75分）1.中國電影票房地域分布分析假設(shè)你手頭有以下數(shù)據(jù)：-`box_office`表：包含電影票房收入及其地域分布（字段包括`movie_id`、`region`（如“華東”、“華南”、“華北”）、`revenue`）。-`movies`表：包含電影的基本信息（如`movie_id`、`title`）。要求：①分析2023年中國電影的票房收入地域分布，列出各區(qū)域的票房總收入及占比。②找出票房收入最高的區(qū)域，并分析該區(qū)域電影票房占全國總票房的比例。2.影視行業(yè)演員商業(yè)價值評估假設(shè)你手頭有以下數(shù)據(jù)：-`actors`表：包含演員的基本信息（如`actor_id`、`name`、`nationality`）。-`movie_performance`表：包含演員在電影中的表現(xiàn)評分（如`actor_id`、`movie_id`、`score`）。-`movies`表：包含電影的基本信息（如`movie_id`、`release_year`）。要求：①評估2023年后上映的電影中，演員的商業(yè)價值（根據(jù)`movie_performance`表的平均評分和票房收入綜合計算）。②列出商業(yè)價值最高的3位演員及其平均評分和參與電影的票房總收入。3.影視行業(yè)類型趨勢分析假設(shè)你手頭有以下數(shù)據(jù)：-`movies`表：包含電影的基本信息（如`movie_id`、`title`、`genre`、`release_year`）。-`ratings`表：包含觀眾評分（如`movie_id`、`rating`）。要求：①分析2010-2023年間，中國電影市場最熱門的類型（類型票房收入占比最高的3個）。②分析2020年后，哪些類型的電影觀眾評分較高（平均評分最高的3個類型）。4.影視行業(yè)投資回報率分析假設(shè)你手頭有以下數(shù)據(jù)：-`movies`表：包含電影的基本信息（如`movie_id`、`title`、`budget`、`revenue`）。要求：①計算每部電影的ROI（投資回報率=(票房收入-預(yù)算)/預(yù)算），并列出ROI最高的3部電影及其ROI值。②分析2020年后上映的電影中，高ROI（如ROI>50%）的電影類型分布。5.影視行業(yè)演員合作網(wǎng)絡(luò)分析假設(shè)你手頭有以下數(shù)據(jù)：-`actors`表：包含演員的基本信息（如`actor_id`、`name`）。-`collaborations`表：包含演員合作記錄（如`actor_id1`、`actor_id2`、`movie_id`）。要求：①找出合作次數(shù)最多的3對演員（不考慮電影順序），并列出他們合作的電影數(shù)量。②分析合作最多的演員對是否集中在某些類型（如“科幻”、“動作”）的電影中。答案與解析一、SQL基礎(chǔ)操作題答案與解析1.創(chuàng)建與查詢影視數(shù)據(jù)庫表結(jié)構(gòu)SQL語句：sqlCREATETABLEmovies(movie_idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(255)NOTNULL,release_yearINT,genreVARCHAR(50),budgetDECIMAL(15,2),revenueDECIMAL(15,2));CREATETABLEactors(actor_idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(255)NOTNULL,birth_yearINT,nationalityVARCHAR(50),movie_idINT,FOREIGNKEY(movie_id)REFERENCESmovies(movie_id));查詢2020年后上映的電影：sqlSELECTtitle,genre,budget,revenueFROMmoviesWHERErelease_year>2020;解析：-表結(jié)構(gòu)設(shè)計考慮了電影的基本信息，`actors`表通過外鍵關(guān)聯(lián)`movies`表，實現(xiàn)演員與電影的關(guān)聯(lián)。-查詢通過`release_year>2020`篩選條件，確保結(jié)果只包含2020年后上映的電影。2.插入與更新影視數(shù)據(jù)插入數(shù)據(jù)：sqlINSERTINTOmovies(title,release_year,genre,budget,revenue)VALUES('流浪地球2',2023,'科幻',200000000,450000000),('滿江紅',2023,'懸疑',80000000,300000000),('熊出沒·逆轉(zhuǎn)時空',2023,'動畫',50000000,200000000);更新票房收入：sqlUPDATEmoviesSETrevenue=350000000WHEREtitle='滿江紅';解析：-插入數(shù)據(jù)時確保每行符合表結(jié)構(gòu)定義的字段類型。-更新操作通過`title`字段定位電影，避免因`movie_id`變化導(dǎo)致錯誤。3.連接查詢與統(tǒng)計列出每部電影及其演員數(shù)量：sqlSELECTm.title,COUNT(a.actor_id)ASactor_countFROMmoviesmLEFTJOINactorsaONm.movie_id=a.movie_idGROUPBYm.movie_id;查詢預(yù)算超過1億且演員數(shù)量超過2的電影：sqlSELECTm.title,COUNT(a.actor_id)ASactor_countFROMmoviesmJOINactorsaONm.movie_id=a.movie_idGROUPBYm.movie_idHAVINGm.budget>100000000ANDCOUNT(a.actor_id)>2;解析：-第一個查詢通過`LEFTJOIN`確保即使電影沒有演員也能顯示（如導(dǎo)演信息未存儲）。-第二個查詢使用`HAVING`過濾條件，確保結(jié)果滿足預(yù)算和演員數(shù)量要求。4.子查詢與聚合函數(shù)票房最高的科幻電影：sqlSELECTm.title,m.revenueFROMmoviesmWHEREm.genre='科幻'ANDm.release_year>2020ORDERBYm.revenueDESCLIMIT1;演員國籍為“中國”的數(shù)量與平均出生年份：sqlSELECTCOUNT()ASchinese_actor_count,AVG(a.birth_year)ASavg_birth_yearFROMactorsaWHEREa.nationality='中國';解析：-子查詢通過`WHERE`和`ORDERBY`篩選出科幻電影并排序，`LIMIT1`獲取最高票房。-聚合函數(shù)`COUNT`和`AVG`計算演員數(shù)量和平均出生年份。5.索引與優(yōu)化創(chuàng)建索引：sqlCREATEINDEXidx_release_yearONmovies(release_year);優(yōu)化查詢解釋：-索引`idx_release_year`加速按年份查詢，避免全表掃描。-創(chuàng)建索引后，查詢效率提升，尤其是大數(shù)據(jù)量時（如電影表超過百萬條數(shù)據(jù)）。二、影視行業(yè)洞察分析題答案與解析1.中國電影票房地域分布分析票房地域分布：sqlSELECTregion,SUM(revenue)AStotal_revenue,ROUND(SUM(revenue)/(SELECTSUM(revenue)FROMbox_office)100,2)ASpercentageFROMbox_officeGROUPBYregion;票房最高的區(qū)域：sqlSELECTregion,SUM(revenue)AStotal_revenueFROMbox_officeGROUPBYregionORDERBYtotal_revenueDESCLIMIT1;解析：-第一個查詢通過`SUM`和`ROUND`計算各區(qū)域票房占比。-第二個查詢通過`ORDERBYDESC`和`LIMIT1`找出最高票房區(qū)域。2.影視行業(yè)演員商業(yè)價值評估商業(yè)價值評估：sqlSELECT,AVG(p.score)ASavg_score,SUM(m.revenue)AStotal_revenueFROMactorsaJOINmovie_performancepONa.actor_id=p.actor_idJOINmoviesmONp.movie_id=m.movie_idWHEREm.release_year>2023GROUPBYa.actor_idORDERBYtotal_revenueDESC,avg_scoreDESCLIMIT3;解析：-通過多表連接計算演員的平均評分和參與電影的票房總收入。-按票房和評分雙重排序，篩選商業(yè)價值最高的演員。3.影視行業(yè)類型趨勢分析最熱門的類型（票房占比）：sqlSELECTgenre,SUM(revenue)AStotal_revenue,ROUND(SUM(revenue)/(SELECTSUM(revenue)FROMmovies)100,2)ASpercentageFROMmoviesWHERErelease_yearBETWEEN2010AND2023GROUPBYgenreORDERBYtotal_revenueDESCLIMIT3;觀眾評分最高的類型：sqlSELECTm.genre,AVG(r.rating)ASavg_ratingFROMmoviesmJOINratingsrONm.movie_id=r.movie_idWHEREm.release_year>2020GROUPBYm.genreORDERBYavg_ratingDESCLIMIT3;解析：-第一個查詢通過時間范圍和類型分組，計算類型票房占比。-第二個查詢通過評分聚合函數(shù)篩選高評分類型。4.影視行業(yè)投資回報率分析ROI最高的電影：sqlSELECTtitle,(revenue-budget)/budgetASROIFROMmoviesWHERErelease_year>2020ORDERBYROIDESCLIMIT3;高ROI電影的類型分布：sqlSELECTgenre,COUNT()AShigh_ROI_countFROMmoviesmWHERE(m.revenue-m.budget)/m.budget>0.5GROUPBYgenre;解析：-第一個查詢計算ROI并排序，`LIMIT3`獲取最高值。-第二個查詢篩選ROI