零基础SQL快速入门_牛客刷题指南:26~29 手把手带刷必会的常用函数,条件函数,日期函数

必会的常用函数

条件函数

1. IF 函数

IF 函数用于根据条件返回两个值中的一个。它类似于编程语言中的 if-else 语句

语法：

IF(condition, true_value, false_value)

• condition：条件
• true_value：如果条件为 TRUE，则返回的值。
• false_value：如果条件为 FALSE，则返回的值。

2. CASE 语句

CASE 语句用于根据多个条件返回不同的值。它类似于编程语言中的 switch-case 语句

语法：

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
ELSE default_result
END

3. COALESCE 函数

COALESCE 函数用于返回参数列表中的第一个非 NULL 值。如果没有非 NULL 值，则返回 NULL

语法：

COALESCE(value1, value2, …)

4. NULLIF 函数

NULLIF 函数用于比较两个值，如果它们相等，则返回 NULL，否则返回第一个值

语法：

NULLIF(value1, value2)

5. IFNULL 函数

IFNULL 用于检查一个值是否为 NULL，如果是 NULL，则返回一个指定的默认值

语法:

IFNULL(expression, default_value)

• expression：要检查的表达式。
• default_value：如果 expression 为 NULL，则返回的默认值。

与其他函数的比较

• COALESCE：

  • COALESCE 函数可以接受多个参数，并返回第一个非 NULL 的值。
  • 如果所有参数都是 NULL，则返回 NULL。
  • 语法：COALESCE(value1, value2, …)。
  • 示例：COALESCE(salary, 0, 'Not Available')。

• IF：

  • IF 函数用于根据条件返回两个值中的一个。
  • 语法：IF(condition, true_value, false_value)。
  • 示例：IF(salary IS NULL, 0, salary)。

示例：

IF 函数

CASE语句

NULLIF函数

COALESCE 函数

IFNULL 函数

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

case

select
case
when age>=25 then '25岁及以上'
else '25岁以下'
end age_cut,
count(*) number
from user_profile
group by age_cut;

— 选择并划分年龄段，统计每个年龄段的用户数量
SELECT
CASE
WHEN age < 25 OR age IS NULL THEN '25岁以下'
ELSE '25岁及以上'
END AS age_cut,
COUNT(*) AS number
FROM
user_profile
GROUP BY
age_cut
ORDER BY
CASE
WHEN age_cut = '25岁以下' THEN 1
ELSE 2
END;

代码解释

• SELECT 子句：

  • 使用 CASE WHEN 语句来判断 age 字段：
    • 如果 age 小于25岁或为 NULL，则将其归类为 '25岁以下'。
    • 否则，归类为 '25岁及以上'。
  • 为划分后的结果命名为 age_cut。
  • 使用 COUNT(*) 统计每个年龄段的用户数量，并命名为 number。

• FROM 子句：

  • 指定数据来源为 user_profile 表。

• GROUP BY 子句：

  • 按照划分后的 age_cut 进行分组，以便统计每个组的用户数量。

• ORDER BY 子句：

  • 根据需求，将 '25岁以下' 的结果排在前面，'25岁及以上' 的结果排在后面。
  • 通过 CASE WHEN 语句实现自定义排序。

if

select
if(age<25 or age is null,'25岁以下','25岁及以上') age_cut,
count(*) number
from user_profile
group by age_cut;

UNION ALL并集显示(不推荐)

select '25岁及以上',count(id) number
from user_profile
where age>=25
union all
select '25岁以下',count(id) number
from user_profile
where age<25 or age is null
order by number desc;

SELECT "25岁以下" as age_cut,count(device_id)
FROM user_profile
WHERE age<25 OR age IS null
UNION ALL
SELECT "25岁及以上" as age_cut,count(device_id)
FROM user_profile
WHERE age>=25

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

select device_id,gender,
case
when age<20 then '20岁以下'
when age>=20 and age<=24 then '20-24岁'
when age>=25 then '25岁及以上'
else '其他'
end age_cut
from user_profile;

日期函数

1. 获取当前日期和时间

• NOW()：返回当前日期和时间。
• CURDATE()：返回当前日期。
• CURTIME()：返回当前时间。

2. 提取日期部分

• YEAR(date)：提取日期的年份部分。
• MONTH(date)：提取日期的月份部分。
• DAY(date)：提取日期的日部分。
• HOUR(time)：提取时间的小时部分。
• MINUTE(time)：提取时间的分钟部分。
• SECOND(time)：提取时间的秒部分。

3. 日期计算

• DATE_ADD(date, INTERVAL expr type)：在日期上加上一个时间间隔。
• DATE_SUB(date, INTERVAL expr type)：从日期中减去一个时间间隔。
• DATEDIFF(date1, date2)：计算两个日期之间的天数差。

4. 格式化日期

• DATE_FORMAT(date, format)：将日期格式化为指定的格式。

5. 其他日期函数

• DAYOFWEEK(date)：返回日期是星期几（1 = 星期天，2 = 星期一，…，7 = 星期六）。
• DAYOFYEAR(date)：返回日期是一年中的第几天。
• WEEK(date)：返回日期是一年中的第几周。
• LAST_DAY(date)：返回指定日期所在月份的最后一天。

示例

提取日期部分

日期计算

格式化日期

其他日期函数

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

日期函数

select day(date) day,
count(question_id) question_cnt
from question_practice_detail
where month(date)='08'
group by day;

注意是'08'

• 确保日期的范围仅限于 2021年8月，避免包含其他月份的数据
• 使用 GROUP BY 进行分组统计时，需要基于提取的 day

like模糊查询

select day(date) day,
count(question_id) question_cnt
from question_practice_detail
where date like '%2021-08%'
group by day;

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

select
round(sum(case
when t2.device_id is not null then 1
else 0
end)/count(*),4) avg_ret
from (
select distinct device_id,date
from question_practice_detail
)t1
left join
(
select distinct device_id,date
from question_practice_detail
)t2
on t1.device_id=t2.device_id
and t2.date=DATE_ADD(t1.date,INTERVAL 1 day);

代码解释

t2.date=DATE_ADD(t1.date,INTERVAL 1 day)相差一天    t2.date=t1.date+1天

• 子查询 p1 和 p2：

  • p1：提取每个用户每天的唯一刷题记录。
  • p2：同样提取每个用户每天的唯一刷题记录，用于与 p1 进行自连接。

• LEFT JOIN 连接条件：

  • 连接条件为同一 device_id 且 p2.date 是 p1.date 的次日（即 p1.date 加一天）。
  • 通过这种方式，可以判断用户在某天刷题后次日是否有刷题记录。

• SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END)：

  • 统计有次日刷题记录的用户数量。如果 p2.device_id 不为空，说明用户在次日有刷题行为，计数加1。

• COUNT(*)：

  • 统计总的刷题记录数，即总的 (device_id, date) 组合数量。

• ROUND(…, 4)：

  • 将留存率结果保留四位小数。

SQL 必备常用函数汇总

函数分类

函数名

功能说明

示例