mysql刷題筆記

近期，為提升自己的工程能力，在休息時常通過刷題來回顧一下基礎性知識。

於是選擇了牛客網上的mysql知識題庫練手，過程中，主要遇到了幾個比較有意思的題，記錄下來，方便回顧。

題1：SQL29 計算用戶的平均次日留存率

描述

題目：現在運營想要查看用戶在某天刷題後第二天還會再來刷題的平均概率。請你取出相應數據。
示例：question_practice_detail
id device_id quest_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15
……        
根據示例，你的查詢應返回以下結果：
avg_ret
0.3000

題目分析

所謂次日留存，指的是同一用戶（在本題中則為同一設備，即device_id）在當天和第二天都進行刷題。注意，在這題我們不關心同一用戶（設備）在這天答了什麼題、答題結果如何，只關心他是否答題，因此對於這題來說存在重複的數據（如下圖紅框所示），需要使用 DISTINCT 去重。

而次日留存率可以這樣表示：次日留存率=去重的數據表中符合次日留存的條目數目/去重的數據表中所有條目數。
具體而言，使用兩個子查詢，查詢出兩個去重的數據表，並使用條件（q2.date應該是q1.date的後一天）進行篩選，如下所示（數據未顯示完全，從左至右順序，列表名為 q1.device_id, q1.date, q2.device_id, q2.date)。

因為使用的是q1左級聯q2，所以q1的所有信息是顯示的；而q2中只顯示留存的信息，否則為null。

最後，分別統計q1.device_id 和 q2.device_id 作去重後的所有條目數和去重後的次日留存條目數，即可算出次日留存率。

具體實現

SELECT
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
    (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

 

題2：SQL34 統計復旦用戶8月練題情況

描述

題目： 現在運營想要了解復旦大學的每個用戶在8月份練習的總題目數和回答正確的題目數情況，請取出相應明細數據，對於在8月份沒有練習過的用戶，答題數結果返回0.
示例：用戶信息表user_profile
id device_id gender age university gpa active_days_within_30
1 2138 male 21 北京大學 3.4 7
2 3214 male   復旦大學 4.0 15
3 6543 female 20 北京大學 3.2 12
4 2315 female 23 浙江大學 3.6 5
5 5432 male 25 山東大學 3.8 20
6 2131 male 28 山東大學 3.3 15
7 4321 female 26 復旦大學 3.6 9

示例：question_practice_detail

id device_id question_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15
……        
 
根據示例，你的查詢應返回以下結果：
device_id university question_cnt right_question_cnt
3214 復旦大學 3 0
4321 復旦大學 0 0

問題分解

  • 限定條件：需要是復旦大學的（來自表user_profile.university），8月份練習情況（來自表question_practice_detail.date）
  • 從date中取month：用month函數即可；
  • 總題目：count(question_id)
  • 正確的題目數：sum(if(qpd.result='right', 1, 0))
  • 按列聚合：需要輸出每個用戶的統計結果，因此加上group by up.device_id

細節問題

  • 8月份沒有答題的用戶輸出形式：題目要求『對於在8月份沒有練習過的用戶，答題數結果返回0』因此明確使用left join即可，即輸出up表中復旦大學的所有用戶，如果8月沒有練習記錄，輸出0就好了
  • 老樣子-表頭：as語法重命名後兩列就好

完整代碼

select up.device_id, '復旦大學' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up
 
left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8
 
where up.university = '復旦大學'
group by up.device_id
 
題3：SQL35 浙大不同難度題目的正確率

描述

題目：現在運營想要了解浙江大學的用戶在不同難度題目下答題的正確率情況，請取出相應數據，並按照準確率升序輸出。
示例： user_profile
id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大學 3.4 7 2 12
2 3214 male   復旦大學 4 15 5 25
3 6543 female 20 北京大學 3.2 12 3 30
4 2315 female 23 浙江大學 3.6 5 1 2
5 5432 male 25 山東大學 3.8 20 15 70
6 2131 male 28 山東大學 3.3 15 7 13
7 4321 female 26 復旦大學 3.6 9 6 52
示例： question_practice_detail
id device_id question_id result
1 2138 111 wrong
2 3214 112 wrong
3 3214 113 wrong
4 6543 111 right
5 2315 115 right
6 2315 116 right
7 2315 117 wrong
 
示例： question_detail
question_id difficult_level
111 hard
112 medium
113 easy
115 easy
116 medium
117 easy
根據示例，你的查詢應返回以下結果：
difficult_level correct_rate
easy 0.5000
medium 1.0000

問題分解

  • 限定條件：浙江大學的用戶；
  • 不同難度：difficult_level（question_detail表中的列），需要分組統計，因此用到group by；
  • 正確率：表面理解就是正確數÷總數，正確的是result=’right’（question_practice_detail表），數目用函數count，總數是count(question_id)；
  • 多張表聯合查詢：需要用到join，join有多種語法，因為條件限定需要是浙江大學的用戶，所以需要是user_profile表的並且能統計出題目難度的記錄，因此用user_profile表inner join另外兩張表。

    圖片說明

細節問題

  • 表頭重命名：根據輸出示例，正確率用as語法重命名
  • 升序輸出：order by xxx asc
  • 正確率的計算方式：判斷result是否為right，是的話賦值為1，對於正確的數目，可以用count，也可以用sum，正確率還可以直接用avg計算。
  • join方式選擇：如果前面inner join改成left join，為了防止結果中有難度為None的結果，需要在order by前加一句 having qd.difficult_level != 'None'

完整代碼

select difficult_level,
    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
#    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up
 
inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id
 
inner join question_detail as qd
    on qd.question_id = qpd.question_id
 
where up.university = '浙江大學'
group by qd.difficult_level
order by correct_rate asc;

 

幾個常用函數的套路：

1.substring_index():分割字符串，連續分割字符串

SELECT SUBSTRING_INDEX('15,151,152,16',',',1); ==>得到結果為： 15
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',2),',',-1);==>得到結果為： 151
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',-2),',',1);==> 得到結果為：152
SELECT SUBSTRING_INDEX('15,151,152,16',',',-1);==>得到結果為： 16

2.case when:分組

格式說明：

簡單用法：

　　　　case 列名

　　　　when   條件值1   then  選項1

　　　　when   條件值2    then  選項2.......

　　　　else     默認值      end

搜索用法：
　　　　case  

　　　　when  列名= 條件值1   then  選項1

　　　　when  列名=條件值2    then  選項2.......

　　　　else    默認值 end

例子：

SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class,
COUNT(*)
FROM    Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;

 

Tags:

VirMach 便宜 VPS

QNews

QNews