【SQL查询】必会的常用函数：条件函数、日期函数、文本函数、窗口函数

〇、概述

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;