条件分析

AI 助力的 SQL 查询进阶

Jasmin Ludolf

Senior Data Science Content Developer

条件聚合

提示： 按年份显示有预算与无预算的电影数量

SELECT 
    release_year,
    COUNT(CASE WHEN budget IS NOT NULL THEN 1 END) AS films_with_budget,
    COUNT(CASE WHEN budget IS NULL THEN 1 END) AS films_without_budget
FROM films
GROUP BY release_year;

|release_year|films_with_budget|films_without_budget|
|------------|-----------------|--------------------|
|1964        |10               |0                   |
|1969        |9                |1                   |
...

条件聚合

条件示意图

条件聚合

提示： 按年份显示有预算与无预算的电影数量

SELECT 
    release_year,
    COUNT(CASE WHEN budget IS NOT NULL THEN 1 END) AS films_with_budget,
    COUNT(CASE WHEN budget IS NULL THEN 1 END) AS films_without_budget
FROM films
GROUP BY release_year;

|release_year|films_with_budget|films_without_budget|
|------------|-----------------|--------------------|
|1964        |10               |0                   |
|1969        |9                |1                   |
...

符合条件的电影计为 1

条件聚合

提示： 按年份显示有预算与无预算的电影数量

SELECT 
    release_year,
    SUM(CASE WHEN budget IS NOT NULL THEN 1 ELSE 0 END) AS films_with_budget,
    SUM(CASE WHEN budget IS NULL THEN 1 ELSE 0 END) AS films_without_budget
FROM  films
GROUP BY release_year;

|release_year|films_with_budget|films_without_budget|
|------------|-----------------|--------------------|
|1964        |10               |0                   |
|1969        |9                |1                   |
...

含 NULL 的条件聚合

提示： 按年份显示有预算与无预算的电影数量

SELECT 
    release_year,
    COUNT(CASE WHEN budget IS NOT NULL THEN 1 END) AS films_with_budget,
    COUNT(CASE WHEN budget IS NULL THEN 1 END) AS films_without_budget
FROM films
GROUP BY release_year;

NULL 计入 films_without_budget
可能需要用 WHERE 先过滤

组合技巧

提示： 对于 2000 年之后、且至少有 5 部包含预算数据的年份，将电影按预算>100 万为高预算、≤100 万为低预算分类，并按年份显示每类数量，取前 10 条

SELECT 
    release_year,

    COUNT(CASE WHEN budget > 1000000 THEN 1 END) AS high_budget_count,
    COUNT(CASE WHEN budget <= 1000000 THEN 1 END) AS low_budget_count

FROM films

WHERE release_year > 2000 AND budget IS NOT NULL

GROUP BY release_year

HAVING COUNT(*) >= 5

ORDER BY release_year
LIMIT 10;

超越计数

条件模式也适用于其他聚合

试验不同条件与阈值

挖掘隐藏洞见

发现洞见

开始练习！

AI 助力的 SQL 查询进阶