〇、概述
1、内容
选择(双分支、多分支)
一、条件函数
1、计算25岁以上和以下的用户数量
CASE THEN END多分支选择
SELECT
(CASE
WHEN age>=25 THEN '25岁及以上'
ELSE '25岁以下'
END) AS age_cut,
COUNT(*) AS number
FROM user_profile
GROUP BY age_cut;
IF ELSE双分支选择
SELECT
IF(age>=25,'25岁及以上','25岁以下') AS age_cut,
COUNT(*) AS number
FROM user_profile
GROUP BY age_cut;
集合运算联合UNION
SELECT
'25岁以下' AS age_cut,
COUNT(*) AS number
FROM user_profile
WHERE
age<25
or
age IS NULL
UNION
SELECT
'25岁及以上' AS age_cut,
COUNT(*) AS number
FROM user_profile
WHERE age>=25;
2、查看不同年龄段的用户明细
CASE WHEN THEN END,中间不加逗号
SELECT
device_id,
gender,
(CASE
WHEN age IS NULL THEN '其他'
WHEN age<20 THEN '20岁以下'
WHEN age BETWEEN 20 and 24 THEN '20-24岁'
ELSE '25岁及以上'
END) AS age_cut
FROM user_profile;
二、日期函数
1、计算用户8月每天的练题数量【多个字段用逗号分隔】
方法1:日期函数(日、月、天)
SELECT
DAY(date) AS day,
COUNT(*) AS question_cnt
FROM question_practice_detail
WHERE
YEAR(date)=2021
and
MONTH(date)=08
GROUP BY day;
方法2:like通配符
SELECT
DAY(date) AS day,
COUNT(*) AS question_cnt
FROM question_practice_detail
WHERE date like '2021-08%'
GROUP BY day;
方法3:substring
SELECT
DAY(date) AS day,
COUNT(*) AS question_cnt
FROM question_practice_detail
WHERE substring(date,1,7)='2021-08'
GROUP BY day;
2、计算用户的平均次日留存率
SELECT
COUNT(b.device_id)/COUNT(a.device_id) AS avg_ret
FROM
(SELECT
DISTINCT device_id,
date
FROM question_practice_detail) AS a
LEFT JOIN
(SELECT
DISTINCT device_id,
date
FROM question_practice_detail) AS b
ON
a.device_id=b.device_id
AND
DATE_ADD(a.date,interval 1 day)=b.date;
三、文本函数
1、统计每种性别的人数
SELECT
SUBSTRING(profile,15) as gender,
COUNT(*)
FROM user_submit
GROUP BY gender;
2、截取出年龄【SUBSTRING的第三个参数表示往后取几个字符】
SELECT
SUBSTRING(profile,12,2) AS age,
COUNT(*) AS number
FROM user_submit
GROUP BY age;
3、提取博客URL中的用户名
SELECT
device_id,
SUBSTRING(blog_url,11) AS user_name
FROM user_submit;
四、窗口函数
包括聚合函数、排名分析
1、找出每个学校GPA最低的同学
SELECT
a.device_id,
a.university,
a.gpa
FROM user_profile a
JOIN
(SELECT
university,
MIN(gpa) AS gpa
FROM user_profile
GROUP BY university
) b
ON
a.university=b.university
AND
a.gpa=b.gpa
ORDER BY a.university ASC;