Sql必会的常用函数

1、条件函数

计算25岁以上和以下的用户数量

现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量。
本题注意：age为null 也记为 25岁以下。

//用if函数的写法。
select
    if(age >= 25, "25岁及以上", "25岁以下") as age_cut,
    count(*) as number
from
    user_profile
group by
    age_cut;

//case的写法。
select
    (
        case
            when age >= 25 then '25岁及以上'
            else '25岁以下'
        end
    ) as age_cut,
    count(*) as number
from
    user_profile
group by
    age_cut

查看不同年龄段的用户明细

现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

//用if函数的写法。
select
    device_id,
    gender,
    if (
        age >= 25,
        '25岁及以上',
        if (age >= 20, '20-24岁', if (age >= 0, '20岁以下', '其他'))
    ) as age_cut
from
    user_profile

//case的写法。
select
    device_id,
    gender,
    case
        when age >= 25 then '25岁及以上'
        when age >= 20 then '20-24岁'
        when age < 20 then '20岁以下'
        else '其他'
    end as age_cut
from
    user_profile

2、日期函数

计算用户8月每天的练题数量

现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

select
    day (date) as day,
    count(question_id) as question_cnt
from
    question_practice_detail
where
    month (date) = 8
    and year (date) = 2021
group by
    date

计算用户的平均次日留存率

现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

select
    avg(if (datediff (date2, date1) = 1, 1, 0)) as avg_ret
from
    (
        select distinct
            device_id,
            date as date1,
            lead (date) over (
                partition by
                    device_id
                order by
                    date
            ) as date2
        from
            (
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            ) as uniq_id_date
    ) as id_last_next_date

3、文本函数

统计每种性别的人数

现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果。

//SUBSTRING_INDEX的写法
SELECT
    SUBSTRING_INDEX (profile, ",", -1) gender,
    COUNT(*) number
FROM
    user_submit
GROUP BY
    gender;

//LIKE的写法
SELECT
    IF (profile LIKE '%female', 'female', 'male') gender,
    COUNT(*) number
FROM
    user_submit
GROUP BY
    gender;

截取出年龄

现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果。

select
    substring_index (substring_index (profile, ',', 3), ',', -1) as age,
    count(device_id) as number
from
    user_submit
group by
    age

提取博客URL中的用户名

对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

select
    device_id,
    substring_index (blog_url, '/', -1) as user_name
from
    user_submit

4、窗口函数

找出每个学校GPA最低的同学

现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

select
    device_id,
    university,
    gpa
from
    user_profile
where
    (university, gpa) in (
        select
            university,
            min(gpa)
        from
            user_profile
        group by
            university
    )
order by
    university