1.條件函數(shù) CASE WHEN

運營想要將用戶劃分為25歲以下和25歲及以上兩個年齡段，分別查看這兩個年齡段用戶數(shù)量

SELECT CASE WHEN age<25 OR age IS NULL THEN "25歲以下" WHEN age>=25 THEN "25歲及以上" END AS "age_cut",COUNT(*) AS "number" FROM user_profile GROUP BY age_cut

2.日期函數(shù)

1. 運營想要計算出2021年8月每天用戶練習(xí)題目的數(shù)量

SELECT DAY(date) AS day,count(*) AS question_cnt FROM question_practice_detail WHERE MONTH(date)=8 #SUBSTR(date,1,7)='2021-08' GROUP BY date

2. 運營想要查看用戶在某天刷題后第二天還會再來刷題的平均概率 (次日留存率）

SELECT COUNT(DISTINCT q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) AS avg_ret FROM question_practice_detail q1 LEFT OUTER JOIN question_practice_detail q2 ON q1.device_id=q2.device_id AND DATEDIFF(q2.date,q1.date)=1

?

3.文本函數(shù)

1. 運營想要統(tǒng)計每個性別的用戶分別有多少參賽者

?我的做法：

SELECT CASE WHEN SUBSTRing(profile,15,4) = 'male' THEN 'male' ELSE 'female' END AS gender ,COUNT(*) FROM user_submit GROUP BY gender

答案：

SELECT SUBSTRING_INDEX(profile,',',-1) AS gender, COUNT(*) FROM user_submit GROUP BY gender

SUBSTRING_INDEX ( )按照指定的分隔符劃分字符串，取第幾個由自己決定

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1); # www SELECT SUBSTRING_INDEX('www.mysql.com', '.', -1); # com

2.運營想要把用戶的個人博客用戶字段提取出來

法一：

SELECT device_id,SUBSTRING_INDEX(blog_url,"/",-1) AS user_name FROM user_submit

法二：

SELECT device_id, REPLACE(blog_url,'http:/url/','') AS user_name FROM user_submit

3.運營想要統(tǒng)計每個年齡的用戶分別有多少參賽者(截取年齡）

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(profile,",",-2),",",1) AS age, COUNT(*) FROM user_submit GROUP BY age

?4.窗口函數(shù)

運營想要找到每個學(xué)校gpa最低的同學(xué)

?方法一：括號()不要落下

SELECT device_id,university,gpa FROM user_profile u WHERE (university,gpa) IN (SELECT university,MIN(gpa)FROM user_profileGROUP BY university ) ORDER BY university

方法二（開窗函數(shù)）：

先執(zhí)行WHERE，再執(zhí)行開窗函數(shù)，故要套一層

SELECT device_id,university,gpa FROM ( SELECT device_id,university,gpa, ROW_NUMBER() OVER(PARTITION BY university ORDER BY gpa) AS rk FROM user_profile )temp WHERE temp.rk = 1

總結(jié)

以上是生活随笔為你收集整理的【Kay】MySQL必会常用函数的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。