當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

3个常考的SQL数据分析题（含数据和代码）

發布時間：2023/12/19 数据库 35 豆豆

生活随笔收集整理的這篇文章主要介紹了 3个常考的SQL数据分析题（含数据和代码）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

在數據類崗位招聘過程中，經常會考察求職者的SQL能力，這里整理了3個常考的SQL數據分析題，按照由簡單到復雜排序，一起來測試一下你掌握了么？

PS：以下SQL代碼在MySQL8.0及其以上版本運行。

題目1：找出每個部門工資第二高的員工

現有一張公司員工信息表employee，表中包含如下4個字段。

employee_id（員工ID）：VARCHAR。
employee_name（員工姓名）：VARCHAR。
employee_salary（員工薪資）：INT。
department（員工所屬部門ID）：VARCHAR。

employee表的數據如下表所示。

還有一張部門信息表department，表中包含如下兩個字段。

department_id（部門ID）：VARCHAR。
department_name（部門名稱）：VARCHAR。

department表的數據如下表所示。

數據導入的代碼如下：

DROP TABLE IF EXISTS employee; CREATE TABLE employee( employee_id VARCHAR(8), employee_name VARCHAR(8), employee_salary INT(8), department VARCHAR(8) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO employee (employee_id,employee_name,employee_salary,department) VALUE ('a001','Bob',7000,'b1'),('a002','Jack',9000,'b1'),('a003','Alice',8000,'b2'),('a004','Ben',5000,'b2'),('a005','Candy',4000,'b2'),('a006','Allen',5000,'b2'),('a007','Linda',10000,'b3');DROP TABLE IF EXISTS department; CREATE TABLE department( department_id VARCHAR(8), department_name VARCHAR(8) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO department (department_id,department_name) VALUE ('b1','Sales'),('b2','IT'),('b3','Product');

問題：查詢每個部門薪資第二高的員工信息。

輸出內容包括：

employee_id（員工ID）
employee_name（員工姓名）
employee_salary（員工薪資）
department_id（員工所屬部門名稱）

結果樣例如下圖所示。

可供參考的解題思路：使用窗口函數根據部門ID分組，在組內按照員工薪資降序排列并記為ranking，然后將該處理后的表和部門信息表進行內連接，從而把部門名稱關聯進來，最后在連接后的表上使用ranking=2作為薪資第二高的條件進行WHERE篩選，選擇需要的列，即可得到結果。

涉及知識點：窗口函數、子查詢、多表連接。

本題的SQL代碼如下，供讀者參考：

SELECT a.employee_id,a.employee_name,a.employee_salary,b.department_id FROM (SELECT *,RANK() OVER (PARTITION BY department ORDER BY employee_salary DESC) AS rankingFROM employee ) AS a INNER JOIN department AS b ON a.department = b.department_id WHERE a.ranking = 2;

題目2：網站登錄時間間隔統計

現有一張網站登錄情況表login_info，該表記錄了所有用戶的網站登錄信息，包含如下兩個字段。

user_id（用戶ID）：VARCHAR。
login_time（用戶登錄日期）：DATE。

login_info表的數據如下表所示。

數據導入的代碼如下：

DROP TABLE IF EXISTS login_info; CREATE TABLE login_info( user_id VARCHAR(8), login_time DATE ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO login_info (user_id,login_time) VALUE ('a001','2021-01-01') ,('b001','2021-01-01') ,('a001','2021-01-03') ,('a001','2021-01-06') ,('a001','2021-01-07') ,('b001','2021-01-07') ,('a001','2021-01-08') ,('a001','2021-01-09') ,('b001','2021-01-09') ,('b001','2021-01-10') ,('b001','2021-01-15') ,('a001','2021-01-16') ,('a001','2021-01-18') ,('a001','2021-01-19') ,('b001','2021-01-20') ,('a001','2021-01-23');

問題：計算每個用戶登錄日期間隔小于5天的次數。

輸出內容包括：

user_id（用戶ID）
num（用戶登錄日期間隔小于5天的次數）

結果樣例如下圖所示。

可供參考的解題思路：本題考查LEAD()函數在處理時間間隔問題上的使用方法，觀察內層的查詢部分，使用LEAD()函數在原有的login_time字段的基礎上創造一列新的時間字段（即該用戶下一次登錄日期），內層查詢代碼如下：

SELECT??user_id?
???????,login_time?
???????,LEAD(login_time,1)?OVER?(PARTITION?BY?user_id?ORDER?BY?login_time)?AS?next_login_time
FROM?login_info;

查詢結果如下圖所示。

在上圖中可以發現，經過LEAD()函數處理后，數據會根據user_id字段分組后按照login_time字段排序。經過內層的處理后，只需在外層篩選出next_login_time與login_time字段的日期差小于5天的數據，即最終統計的目標數據，這里使用了TIMESTAMPDIFF(DAY, login_time, next_login_time)計算日期差，最后分組聚合統計不同user_id的記錄個數，即每個用戶登錄日期間隔小于5天的次數。

涉及知識點：窗口函數、子查詢、分組聚合、時間函數。

本題的SQL代碼如下，供讀者參考：

SELECT a.user_id,COUNT(*) AS num FROM (SELECT user_id,login_time,LEAD(login_time,1) OVER (PARTITION BY user_id ORDER BY login_time) AS next_login_timeFROM login_info ) AS a WHERE TIMESTAMPDIFF(DAY, login_time, next_login_time) < 5 GROUP BY user_id;

題目3：用戶購買渠道分析

現有一張用戶購買信息表purchase_channel，該表記錄了用戶在某購物平臺的購物信息，該購物平臺具有網頁端（web）和手機端（app）兩種訪問方式，表中包含如下4個字段。

user_id（用戶ID）：VARCHAR。
channel（用戶購買渠道）：VARCHAR。
purchase_date（購買日期）：DATE。
purchase_amount（購買金額）：INT。

purchase_channel表的數據如下表所示。

數據導入代碼如下：

DROP TABLE IF EXISTS purchase_channel; CREATE TABLE purchase_channel( user_id VARCHAR(8), channel VARCHAR(8), purchase_date DATE, purchase_amount INT(8) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO purchase_channel (user_id,channel,purchase_date,purchase_amount) VALUE ('a001','app','2021-03-14',200),('a001','web','2021-03-14',100),('a002','app','2021-03-14',400),('a001','web','2021-03-15',3000),('a002','app','2021-03-15',900),('a003','app','2021-03-15',1000);

問題：查詢每天僅使用手機端的用戶、僅使用網頁端的用戶和同時使用網頁端和手機端（both）的不同用戶人數和總購物金額，并且即使某天某渠道沒有用戶的購買信息，也需要展示。

輸出內容包括：

purchase_date（日期）

channel（購買渠道）

sum_amount（總購買金額）

total_users（不同用戶人數）

結果樣例如下圖所示。

可供參考的解題思路：根據用戶ID和日期進行分組，通過統計用戶在各購買渠道購物的記錄個數來判斷某用戶在某日期購物時采用的訪問方式（web、app和both）。其中，web和app可以通過一個SELECT語句查詢，both則可以通過另一個SELECT語句查詢。將兩部分使用UNION連接在一起，并將以上部分作為子查詢內部，在子查詢外部統計不同購買日期、購買渠道的總購買金額和總購買用戶。

本部分SQL代碼如下：

SELECT purchase_date ,channel ,SUM(sum_amount) sum_amount ,SUM(total_users) total_users FROM (SELECT purchase_date ,MIN(channel) channel ,SUM(purchase_amount) sum_amount ,COUNT(DISTINCT user_id) total_usersFROM purchase_channelGROUP BY purchase_date,user_idHAVING COUNT(DISTINCT channel) = 1 UNIONSELECT purchase_date ,'both' channel ,SUM(purchase_amount) sum_amount ,COUNT(DISTINCT user_id) total_usersFROM purchase_channelGROUP BY purchase_date,user_idHAVING COUNT(DISTINCT channel) > 1 ) c GROUP BY purchase_date,channel;

本部分輸出結果如下圖所示。

上述部分似乎已經完成了本題要求，但仔細觀察就會發現，題目要求即使某天某渠道沒有用戶的購買信息，也需要展示。而想要展示更全的信息，則考慮使用最全的信息（所有日期和3個渠道的笛卡爾積）與剛查詢出的結果數據表進行LEFT JOIN連接，即可得到兩張表根據日期和渠道進行連接的結果。

涉及知識點：UNION、分組聚合、數據去重。

本題的SQL代碼如下，供讀者參考：

SELECT t1.purchase_date,t1.channel,t2.sum_amount,t2.total_users FROM (SELECT DISTINCT a.purchase_date ,b.channelFROM purchase_channel a, (SELECT "app" AS channel UNIONSELECT "web" AS channel UNIONSELECT "both" AS channel ) b ) t1 LEFT JOIN ( SELECT purchase_date, channel, SUM(sum_amount) sum_amount, SUM(total_users) total_users FROM ( SELECT purchase_date ,MIN(channel) channel ,SUM(purchase_amount) sum_amount ,COUNT(DISTINCT user_id) total_usersFROM purchase_channelGROUP BY purchase_date,user_idHAVING COUNT(DISTINCT channel) = 1 UNIONSELECT purchase_date ,'both' channel ,SUM(purchase_amount) sum_amount ,COUNT(DISTINCT user_id) total_usersFROM purchase_channelGROUP BY purchase_date,user_idHAVING COUNT(DISTINCT channel) > 1 )c GROUP BY purchase_date, channel ) t2 ON t1.purchase_date = t2.purchase_date AND t1.channel = t2.channel;

這些題目你做出了么？

本文題目內容摘自最新出版的《SQL數據分析：從基礎破冰到面試題解》，該書包含大量練習題（共48個），并附帶數據導入和參考解析代碼，更多題目見下圖：

常見疑問和解答

1.?沒有基礎可以學嗎？

可以，本書從環境搭建開始，從基礎入門到進階，然后通過題目實戰提升SQL能力，是一本關于SQL 數據分析的實戰手冊。

2.?本書對比市面上大部分SQL書籍的特色是什么？

將數據分析挖掘中所需的SQL能力抽出來專門寫，不會涉及很多不常用的功能語法，由淺入深，并配套大量練習題（可作為求職筆試面試的練習題），每個練習題都配有數據導入、解題思路和參考答案。練習題會結合當前數據分析很多場景需求來編制，例如“活躍用戶分析”、“連續登錄用戶分析”、“社區團購行為分析”、“商品銷量同環比”。

3.?本書適合數據分析相關崗位求職備考準備么？

很適合，本書展現了數據分析工作的日常內容，給出了數據分析崗位的工作技能要求，然后講述了數據分析筆試與面試中對SQL 的考查知識點。通過3種難度的題目練習，能提升求職能力并達到初級數據分析挖掘崗位對SQL的能力要求。

4.?本書的適合什么樣的讀者？

數據分析與數據開發求職者和從業者
計算機科學與技術、統計學、數學、大數據、人工智能、數據科學相關專業的師生
對數據分析和SQL感興趣人群
轉行做數據分析與數據開發的人員

福利

大家如果經常買書都會知道，新書剛出版上市是沒有什么折扣的。

本次給大家申請到了全網最低價福利，原價89元，現在只要49元包郵。

大家可以掃描下方二維碼或者點擊閱讀原文直接購買。

老規矩，還是免費送大家幾本，給本篇文章同時點贊和點在看，然后給我微信發送“已點在看”，并附上截圖，我會隨機抽取5名，各送《SQL數據分析》新書一本。周二(1.4)晚9:00開獎。

我的個人微信(沒加的加一下)

點分享

點收藏

點點贊

點在看

總結

以上是生活随笔為你收集整理的3个常考的SQL数据分析题（含数据和代码）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：抖音怎么切换另一个账号
下一篇：网络用语单推dd是什么意思有什么含义