用条件逻辑创建类别

AI 助力的 SQL 查询进阶

Jasmin Ludolf

Senior Data Science Content Developer

CASE：用于条件逻辑

|duration|
|--------|
|123     |
|110     |
|151     |
...

分类为 "Short"、"Standard"、"Epic"
duration > 90：是或否
使用条件逻辑
更易阅读与沟通

不佳的 CASE 提示

提示： 若时长小于 90 分钟则为 Short，小于 150 为 Standard，否则为 Epic

SELECT 
    id, 
    title, 
    duration,
    CASE 
        WHEN duration < 90 THEN 'Short'
        WHEN duration < 150 THEN 'Standard'
        ELSE 'Epic'
    END AS film_category
FROM films;

不佳的 CASE 提示

提示： 若时长小于 90 分钟为 Short，小于 150 为 Standard，否则为 Epic

SELECT 
    id, 
    title, 
    duration,
    CASE 
        WHEN duration < 150 THEN 'Standard'
        WHEN duration < 90 THEN 'Short'
        ELSE 'Epic'
    END AS film_category
FROM films;

易出错
若同时满足两个条件会分错类
- 如同时小于 90 且小于 150

更好的 CASE 提示

提示： 若小于 90 分钟为 Short，90–149 分钟为 Standard，150 分钟及以上为 Epic，否则为 unknown

SELECT 
    id, 
    title, 
    duration,
    CASE 
        WHEN duration < 90 THEN 'Short'
        WHEN duration BETWEEN 90 AND 149 THEN 'Standard'
        WHEN duration >= 150 THEN 'Epic'
        ELSE 'Unknown'
    END AS category
FROM films;

验证逻辑

|id|title                                           |duration|category|
|--|------------------------------------------------|--------|--------|
|1 |Intolerance: Love's Struggle Throughout the Ages|123     |Standard|
|2 |Over the Hill to the Poorhouse                  |110     |Standard|
|3 |The Big Parade                                  |151     |Epic    |
|4 |Metropolis                                      |145     |Standard|
...

|id  |title  |duration|category|
|----|-------|--------|--------|
|4396|Destiny|        |Unknown |
...

过滤 NULL

提示： 删除所有 NULL 的时长

SELECT 
    id, 
    title, 
    duration,
    CASE 
        WHEN duration < 90 THEN 'Short'
        WHEN duration BETWEEN 90 AND 149 THEN 'Standard'
        WHEN duration >= 150 THEN 'Epic'
        ELSE 'Unknown'
    END AS category
FROM films

WHERE duration IS NOT NULL;

分类的好处

用有意义的标签澄清结果

改善沟通

便于更干净的可视化

¡Vamos a practicar!

AI 助力的 SQL 查询进阶