CASE WHEN

CASE 
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ELSE default_value
END AS column_name  -- 就像给返回值起个名字

END：结束CASE语句

AS：给结果起别名

CASE WHEN就像编程中的if-else语句：

CASE 
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    ...
    ELSE 默认结果
END

SELECT 
    CASE 
        WHEN age >= 25 THEN '25岁及以上'
        ELSE '25岁以下'  -- 这里自动包含了age<25和age IS NULL
    END AS age_cut,
    COUNT(*) AS number
FROM user_profile
GROUP BY age_cut;

简化写法：

SELECT 
    IF(age < 25 OR age IS NULL, '25岁以下', '25岁及以上') AS age_cut,
    COUNT(*) AS number
FROM user_profile
GROUP BY age_cut;

例2

SELECT 
    device_id,
    gender,
    CASE 
        WHEN age IS NULL THEN '其他'            -- 先处理NULL
        WHEN age < 20 THEN '20岁以下'
        WHEN age <= 24 THEN '20-24岁'          -- 20 ≤ age ≤ 24
        ELSE '25岁及以上'                       -- age ≥ 25
    END AS age_cut
FROM user_profile;

日期函数

从日期中提取各部分

SELECT 
    date,                        -- 原始日期：'2021-08-13 14:30:00'
    YEAR(date) AS year,          -- 2021
    MONTH(date) AS month,        -- 8
    DAY(date) AS day,            -- 13
    HOUR(date) AS hour,          -- 14（如果有时间）
    MINUTE(date) AS minute,      -- 30（如果有时间）
    SECOND(date) AS second       -- 0（如果有时间）
FROM your_table;

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 
    COUNT(DISTINCT b.device_id, DATE(b.date)) / COUNT(DISTINCT a.device_id, DATE(a.date)) AS avg_ret
FROM 
    question_practice_detail a
LEFT JOIN 
    question_practice_detail b 
ON 
    a.device_id = b.device_id 
    AND DATE_ADD(a.date, INTERVAL 1 DAY) = b.date;

解析：

1. FROM question_practice_detail a
• 这是主表，我们把它看作“今天刷题的用户记录”
• 给它起个别名 a（方便后面引用）
2. LEFT JOIN question_practice_detail b
• 这是连接表，我们用它来查找“明天刷题的用户记录”
• 同样一个表，我们用了两次，但目的不同
• 给它起个别名 b
3. ON a.device_id = b.device_id AND DATE_ADD(a.date, INTERVAL 1 DAY) = b.date
这是连接条件，意思是：
• 条件1：a.device_id = b.device_id → a表和b表必须是同一个用户
• 条件2：DATE_ADD(a.date, INTERVAL 1 DAY) = b.date → b表的日期必须是a表日期的第二天
重要：这里用 LEFT JOIN（左连接）而不是 INNER JOIN（内连接）！
• LEFT JOIN：即使b表没有匹配的记录（用户明天没来），a表的记录也会保留
• INNER JOIN：只有b表有匹配的记录才会保留
4. COUNT(DISTINCT a.device_id, DATE(a.date))
统计总共有多少“用户-日期”组合
• DISTINCT：去重，因为同一个用户同一天可能做多道题
• 例如：用户2315在2021-08-13做了2题，但只算1次
这个数字就是分母：今天刷题的总人数（按天去重）
5. COUNT(DISTINCT b.device_id, DATE(b.date))
统计明天也来刷题的“用户-日期”组合数
• 同样需要去重，防止重复计算
• 注意：对于明天没来的用户，b表的device_id和date会是NULL
这个数字就是分子：明天继续刷题的人数
6. / 除法计算
用分子 ÷ 分母，得到留存率
7. AS avg_ret
给计算出的结果列起个别名叫 avg_ret