本文属于SQL系列之一,本系列主要介绍在mysql等数据库中一些比较有用的关键词或者小技巧。
本文简单介绍下SQL中case-when-end关键词的小技巧,熟练运用这个关键词可以做很多工作哦~
效率 up up up!!
1)case表达式
这玩意儿跟if-else的逻辑是一样的,case表达式会逐个检查条件,并返回第一个满足的条件对应的值。即:一旦某个条件为真,它就会停止并返回结果;否则就会返回else对应的值。
2)场景
2.1 分组统计
目前已有各个省市区的人口数据, 现在需要按照六大地理分区的方式统计各个区域的人口数据
- 华北地区(北京市、天津市、河北省、山西省)
- 东北地区(内蒙古自治区、辽宁省、吉林省、黑龙江省)
- 华东地区(上海市、江苏省、浙江省、安徽省、福建省、江西省、山东省、台湾省)
- 中南地区(河南省、湖北省、湖南省、广东省、广西壮族自治区、海南省)
- 西南地区(重庆市、四川省、贵州省、云南省、西藏自治区)
- 西北地区(陕西省、甘肃省、青海省、宁夏回族自治区、新疆维吾尔自治区)
数据示例(数据随机生成的)
- 创建表格
CREATE TABLE privince_data (
id INT PRIMARY KEY AUTO_INCREMENT,
privince_name VARCHAR(50),
population INT
);
- 导入数据
INSERT INTO privince_data (id, privince_name, population) VALUES
(1, '北京市', 2150),
(2, '天津市', 1800),
(3, '河北省', 7550),
(4, '山西省', 3600),
(5, '内蒙古自治区', 2560),
(6, '辽宁省', 4360),
(7, '吉林省', 2500),
(8, '黑龙江省', 3780),
(9, '上海市', 2300),
(10, '江苏省', 8080),
(11, '浙江省', 5900),
(12, '安徽省', 6550),
(13, '福建省', 4900),
(14, '江西省', 4580),
(15, '山东省', 10050),
(16, '河南省', 9500),
(17, '湖北省', 5900),
(18, '湖南省', 6700),
(19, '广东省', 11500),
(20, '广西壮族自治区', 5100),
(21, '海南省', 1400),
(22, '重庆市', 3300),
(23, '四川省', 8000),
(24, '贵州省', 3600),
(25, '云南省', 4800),
(26, '西藏自治区', 290),
(27, '陕西省', 4400),
(28, '甘肃省', 2600),
(29, '青海省', 620),
(30, '宁夏回族自治区', 850),
(31, '新疆维吾尔自治区', 3800);
- 分组统计
接下来我们将使用case-when的命令来对数据进行分组统计, case的使用格式如下所示,将对应的字段写在case后,当该字段的值与when中的字段对应时,返回then中的值.
SELECT
CASE privince_name
WHEN '北京市' THEN '华北地区'
WHEN '天津市' THEN '华北地区'
WHEN '河北省' THEN '华北地区'
WHEN '山西省' THEN '华北地区'
WHEN '内蒙古自治区' THEN '东北地区'
WHEN '吉林省' THEN '东北地区'
WHEN '辽宁省' THEN '东北地区'
WHEN '黑龙江省' THEN '东北地区'
WHEN '上海市' THEN '华东地区'
WHEN '浙江省' THEN '华东地区'
WHEN '安徽省' THEN '华东地区'
WHEN '福建省' THEN '华东地区'
WHEN '江西省' THEN '华东地区'
WHEN '山东省' THEN '华东地区'
WHEN '台湾省' THEN '华东地区'
......
else '其他地区'
END,
SUM(population)
FROM privince_data
GROUP BY CASE privince_name
WHEN '北京市' THEN '华北地区'
WHEN '天津市' THEN '华北地区'
WHEN '河北省' THEN '华北地区'
WHEN '山西省' THEN '华北地区'
WHEN '内蒙古自治区' THEN '东北地区'
WHEN '吉林省' THEN '东北地区'
WHEN '辽宁省' THEN '东北地区'
WHEN '黑龙江省' THEN '东北地区'
WHEN '上海市' THEN '华东地区'
WHEN '浙江省' THEN '华东地区'
WHEN '安徽省' THEN '华东地区'
WHEN '福建省' THEN '华东地区'
WHEN '江西省' THEN '华东地区'
WHEN '山东省' THEN '华东地区'
WHEN '台湾省' THEN '华东地区'
......
else '其他地区'
END;
结果:
+---------+--------+
| 华北地区 | 15100 |
| 东北地区 | 13200 |
| 华东地区 | 34280 |
| 其他地区 | 80440 |
+--------+--------+
但是我们可以明显感觉到这种写法很傻叉, 所以我们可以将case后的字段省略, 使用如下的写法,可以明显感觉到sql语句简洁了很多。下方所示的sql不仅是case后的写法有所改变,同时在case-when-end后多写了一个 as distinct_name语句, 后边在group by的时候就不需要写两次了。这个技巧很简单,但是可能有的时候想不到。
SELECT
CASE
WHEN privince_name IN ('北京市', '天津市', '河北省', '山西省') THEN '华北地区'
WHEN privince_name IN ('内蒙古自治区', '热河省', '辽西省', '辽东省', '吉林省', '松江省', '黑龙江省') THEN '东北地区'
WHEN privince_name IN ('上海市', '江苏省', '浙江省', '安徽省', '福建省', '江西省', '山东省', '台湾省') THEN '华东地区'
WHEN privince_name IN ('河南省', '湖北省', '湖南省', '广东省', '广西壮族自治区', '海南省') THEN '中南地区'
WHEN privince_name IN ('重庆市', '四川省', '贵州省', '云南省', '西藏自治区') THEN '西南地区'
WHEN privince_name IN ('陕西省', '甘肃省', '青海省', '宁夏回族自治区', '新疆维吾尔自治区') THEN '西北地区'
else '其他地区'
END AS distinct_name,
SUM(population) AS population_all
FROM privince_data
GROUP BY distinct_name;
2.2 统计成功率(占比)
有了2.1的示例,2.2的写起来就会简单很多了。
- 建表sql
-- 创建交易流水表格
CREATE TABLE trans_flow (
flow_no VARCHAR(20),
trans_code VARCHAR(20),
trans_date DATE,
trans_time DATETIME,
trans_status VARCHAR(8),
PRIMARY KEY (flow_no)
);
- 导入数据
-- 导入1000条随机数据
INSERT INTO trans_flow (flow_no, trans_code, trans_date, trans_time, trans_status)
SELECT
LEFT(MD5(RAND()), 16),
CASE FLOOR(RAND() * 4)
WHEN 0 THEN 'TRAN0001'
WHEN 1 THEN 'TRAN0002'
WHEN 2 THEN 'TRAN0003'
WHEN 3 THEN 'TRAN0004'
END AS trans_code,
DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND() * 365) DAY) AS trans_date,
TIMESTAMPADD(SECOND, FLOOR(RAND() * 86400), '2024-01-01') AS trans_time,
CASE WHEN RAND() < 0.6 THEN '00' ELSE -FLOOR(RAND() * 999999) END AS trans_status
FROM
INFORMATION_SCHEMA.TABLES AS t1,
INFORMATION_SCHEMA.TABLES AS t2
LIMIT 1000;
- 统计成功率首先检查一下,目前数据库所有失败[trans_status不为00]的交易, 并按组统计:
select trans_code, count(*) from trans_flow where trans_status!='00' group by trans_code;
+------------+----------+
| trans_code | count(*) |
+------------+----------+
| TRAN0001 | 111 |
| TRAN0004 | 80 |
| TRAN0003 | 100 |
| TRAN0002 | 90 |
+------------+----------+
4 rows in set (0.00 sec)
但是我们需要的是统计每个交易的成功律, 所以我们可以这样:
select trans_code, sum(case when trans_status='00' then 1 else 0 end)/count(*) as '成功率'
from trans_flow group by trans_code order by trans_code;
select trans_code, sum(case when trans_status='00' then 1 else 0 end)/ sum(1) as '成功率'
from trans_flow group by trans_code order by trans_code;
+------------+--------+
| trans_code | 成功率 |
+------------+--------+
| TRAN0001 | 0.6119 |
| TRAN0002 | 0.6234 |
| TRAN0003 | 0.5833 |
| TRAN0004 | 0.6596 |
+------------+--------+
4 rows in set (0.00 sec)
这两种写法都一样, 也就是说count(*)和sum(1)的效果是一样的。需要注意的是case-when-end与sum结合的使用技巧。首先因为要统计成功律,所以分母就是交易总数[可以使用count()函数或者sum(1)], 分子就是交易状态为成功的那些交易的数量。
小拓展: 其实也很容易能看出来这个技巧不止在统计成功率的时候生效,在分组计数时也可以生效,具体方式就是多写几个sum(case-when-end)语句即可。
3)注意事项
- case-when各个条件返回的数据类型是一致的,不能第一个返回int,第二个返回varchar。
- 使用case-when时,需要用end来结尾
- 养成写else的习惯,如果不写else的话,默认会返回null,在某些场景可能会出现问题(可能)。
- 在写when后判断语句时,不能直接用null来判断
case xxx
when null then 'xx'
else 'y'
end
在这个语句中, when null这一行总是返回unKnown[因为这个等同于 when xxx = null], 所以永远不会出现false的情况, 需要改写为:
case
when xxx is null then 'xx'
else 'y'
end
这个会不会在后面版本的mysql中优化也不一定.
Comments NOTHING