SQLite | Case 子句

相关文章：

SQL | 目录

SQLite | Select

SQLite | Where

SQLite | Group by and Order by

1. The CASE Statement

我们在上一篇中介绍了 Group by 和 Order by，
接下来我们将使用 CASE 语句为符合不同条件的情况赋值。

• 使用Jupyter Notebook 运行 SQL 语句需安装 ipython-sql

• %sql 以及 %%sql 为在 Notebook 中运行 SQL 语句，在 SQLite 命令行或 SQLite Stiduo 中不需要 %sql 或 %%sql

载入 SQL 以及连接 SQLite：

本文将使用 weather_stations.db 数据库，其中包含了 STATION_DATA 表。

%load_ext sql
%sql sqlite:///DataBase/weather_stations.db

'Connected: @DataBase/weather_stations.db'

1.1 The CASE Statement

一个 case 语句可以让我们让我们为每一个不同的条件语句匹配不同的值。
在使用时，你可以以 case 开始，以 end 结束，在这中间，你能用
where [condiction] then [value] 的表达来为不同条件赋值。
在你指定了不同的 condiction-value 组合后，还可以用 else 为没有符合任何条件的
值指定一个默认值。举个例子，我们可以将 wind_speed 归入 wind_severity 类，大于 40
的为 HIGH，30 至 40 为 MODERATE，其他的都为 LOW：

%%sql
select report_code, year, month, day, wind_speed,
case
    when wind_speed >= 40 then 'HIGH'
    when wind_speed >= 30 and wind_speed < 40 then 'MODERATE'
    else 'LOW'
end as wind_severity
from station_data
limit 0,5;

 * sqlite:///DataBase/weather_stations.db
Done.

report_code	year	month	day	wind_speed	wind_severity
34DDA7	2002	12	21	0.2	LOW
39537B	1998	10	1	6.7	LOW
C3C6D5	2001	5	18	4.3	LOW
145150	2007	10	14	2.5	LOW
EF616A	1967	7	29	1.2	LOW

事实上，我们可以忽略 AND wind_speed < 40 这个条件，因为 sql 从上至下读取 case 语句，
那些符合 wind_speed >= 40 的记录已经被赋值为 HIGH 并且不再进行第二个条件的判断了，
所以能够到达第二个条件语句的记录必然是 wind_speed < 40* 的，因此可以简写为：

%%sql
select report_code, year, month, day, wind_speed,
case
    when wind_speed >= 40 then 'HIGH'
    when wind_speed >= 30 then 'MODERATE'
    else 'LOW'
end as wind_severity
from station_data
limit 0,5;

 * sqlite:///DataBase/weather_stations.db
Done.

report_code	year	month	day	wind_speed	wind_severity
34DDA7	2002	12	21	0.2	LOW
39537B	1998	10	1	6.7	LOW
C3C6D5	2001	5	18	4.3	LOW
145150	2007	10	14	2.5	LOW
EF616A	1967	7	29	1.2	LOW

当你创建 case 语句时，还可以同时做一些强大的转换。如使用 group by 语句对数据进行分组：

%%sql
select year,
case
    when wind_speed >= 40 then 'HIGH'
    when wind_speed >= 30 then 'MODERATE'
    else 'LOW'
end as wind_severity,
count(*) as record_count
from station_data
group by 1,2
limit 0,5;

 * sqlite:///DataBase/weather_stations.db
Done.

year	wind_severity	record_count
1930	LOW	5
1932	LOW	3
1933	LOW	6
1935	LOW	2
1936	LOW	18

1.2 The “Zero/Null” CASE Trick

在使用 case 语句时，还有一些奇技淫巧，如 “zero/null” CASE 技巧，让你能够
在一个 select 语句内实现在不同的聚合值上进行分组，这是你使用 where 所无法达到的。
因为 where 只能够在所有值上判断同一个条件，而 case 能够每一种聚合值创建不同的条件语句。

假如说你想要将总降雨量 precipitation 分为两列：有龙卷风时的总降雨量（tornado_precipitation）
和没有龙卷风时的总降雨量（non_tornado_precipitation），然后按年月分组。这个逻辑需要用到两列数据：
precipitation 和 tornado，但是你会怎么编写代码呢？

如果让你写的话，你就会发现无法在一个 where 语句中完成，除非将它们拆分为两个 where语句，一个是
有龙卷风的，一个是没有龙卷风的：

%%sql
select year, month,
sum(precipitation) as tornado_precipitation
from station_data
where tornado == 1
group by year, month
limit 0, 5;

 * sqlite:///DataBase/weather_stations.db
Done.

year	month	tornado_precipitation
1937	7	None
1941	8	0.30000000000000004
1942	10	0
1943	1	None
1943	4	0.15000000000000002

%%sql
select year, month,
sum(precipitation) as non_tornado_precipitation
from station_data
where tornado == 0
group by year, month
limit 0, 5;

 * sqlite:///DataBase/weather_stations.db
Done.

year	month	non_tornado_precipitation
1930	6	0
1930	10	None
1932	3	0
1933	3	0
1933	7	None

然而我们可以用一个 case 语句将这两个查询合并为一个，你可以将判断是否有龙卷风的语句从
where 移到 case，并为不符合条件的赋值为 0，然后使用 sum 就可以了：

我们在使用 sum 聚合语句时，通过使不符合条件的值为 0 ，从而让它不被算入 sum（因为加一个 0 相当于没加）

%%sql
select year, month, 
sum(case when tornado == 1 then precipitation else 0 end) 
    as tornado_precipitation,
sum(case when tornado == 0 then precipitation else 0 end) 
    as non_tornado_precipitation
from station_data
group by year, month
limit 0,5;

 * sqlite:///DataBase/weather_stations.db
Done.

year	month	tornado_precipitation	non_tornado_precipitation
1930	6	0	0
1930	10	0	None
1932	3	0	0
1933	3	0	0
1933	7	0	None

case 语句还可以做一些更复杂的聚合任务，如在使用 min 或 max 操作时，
可以使用 null 值来代替那些不符合条件的值（而不是 0）。如我们可以找出在有无龙卷风条件下的年最大降雨量：

%%sql
select year,
max(case when tornado==0 then precipitation else null end) 
    as max_non_tornado_precipitation,
max(case when tornado==1 then precipitation else null end)
    as max_tornado_precipitation
from station_data
group by year
limit 5 OFFSET 60;

 * sqlite:///DataBase/weather_stations.db
Done.

year	max_non_tornado_precipitation	max_tornado_precipitation
1992	1.5	1.51
1993	1.18	2.13
1994	1.26	1.16
1995	0.91	0.35
1996	3.31	0.68

就像 where 子句一样，你可以在 case 子句中使用布尔表达式（包含 and, or, not）。

你可以使用以下代码来查询 2000 年后有[下雨或冰雹]时每个月的平均气温：

%%sql
select month,
avg(case when rain or hail then temperature else null end)
    as avg_precipitation_temp,
avg(case when NOT (rain or hail) then temperature else null end)
    as avg_non_precipitation_temp
from station_data
where year > 2000
group by month
limit 0,5;

 * sqlite:///DataBase/weather_stations.db
Done.

month	avg_precipitation_temp	avg_non_precipitation_temp
1	35.624242424242425	41.794149908592324
2	33.802439024390246	38.9016233766234
3	46.61333333333332	49.22558823529411
4	49.02916666666667	52.33038194444437
5	55.89777777777778	58.90691489361702

参考资料

[1] Thomas Nield.Getting Started with SQL[M].US: O’Reilly, 2016: 47-52

相关文章：

SQL | 目录
SQLite | SQLite 与 Pandas 比较篇之一
SQLite | Select 语句
SQLite | Where 子句
SQLite | Group by 与 Order by 子句
SQLite | Join 语句
SQLite | 数据库设计与 Creat Table 语句
SQLite | Insert、Delete、Updata 与 Drop 语句