【Kay】MySQL必会常用函数

1.条件函数 CASE WHEN

运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

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.日期函数

1. 运营想要计算出2021年8月每天用户练习题目的数量

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.文本函数

1. 运营想要统计每个性别的用户分别有多少参赛者

 我的做法：

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.运营想要统计每个年龄的用户分别有多少参赛者(截取年龄）

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

 4.窗口函数

运营想要找到每个学校gpa最低的同学

 方法一：括号()不要落下

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

方法二（开窗函数）：

先执行WHERE，再执行开窗函数，故要套一层

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