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