条件表达式和运算符

本章将讨论 PostgreSQL 条件表达式和运算符，包括：使用 CASE 构造条件查询、使用 COALESCE 过滤非空参数、使用 NULLIF 处理 NULL 值以及使用 CAST 进行数据类型转换。

CASE

CASE和其它编程语言中的 IF/ELSE 相当：用于构造条件查询。它是一个表达式，因此可以在任何使用表达式的地方使用它，比如：SELECT、WHERE、GROUP BY 和 HAVING。

它有两种形式：通用形式和简单形式。

通用形式

CASE 的通用形式的语法如下 ：

CASE 
      WHEN 条件1  THEN 结果1
      WHEN 条件2  THEN 结果2
      [WHEN ...]
      [ELSE 其它结果]
END

• 如果没有提供 ELSE，则返回 NULL

示例：根据时长返回不同的时长说明

SELECT title,
       length,
       CASE
           WHEN length> 0
                AND length <= 50 THEN '短视频'
           WHEN length > 50
                AND length <= 120 THEN '中等长度'
           WHEN length> 120 THEN '长视频'
       END AS duration
FROM film
ORDER BY title;

示例二：结合聚合函数返回影片租金的说明

SELECT
	SUM (CASE
               WHEN rental_rate = 0.99 THEN 1
	       ELSE 0
	      END
	) AS "经济型",
	SUM (
		CASE
		WHEN rental_rate = 2.99 THEN 1
		ELSE 0
		END
	) AS "大众型",
	SUM (
		CASE
		WHEN rental_rate = 4.99 THEN 1
		ELSE 0
		END
	) AS "精英型"
FROM
	film;

简单形式

PostgreSQL 提供了另一种称为”简单形式“的 CASE：

CASE 表达式
   WHEN 值1 THEN 结果1
   WHEN 值2 THEN 结果2 
   [WHEN ...]
ELSE
   其它结果
END

• 首先计算 表达式 的值
• 然后将这个值分别与每个 WHEN 进行对比，如果等于某个 WHEN 值，就返回对应的结果。比如，如果等于 值1 就返回 结果1
• 如果所有的 WHEN 都不满足 表达式 的值，则返回 ELSE 后面的 其它结果。如果没有提供 ELSE，则返回 NULL

SELECT title,
       rating,
       CASE rating
           WHEN 'G' THEN '一般观众'
           WHEN 'PG' THEN '建议家长指导'
           WHEN 'PG-13' THEN '家长强烈警告'
           WHEN 'R' THEN '受限制的'
           WHEN 'NC-17' THEN '仅限成年人'
       END rating_description
FROM film
ORDER BY title;

示例二：结合聚合函数返回评级说明：

SELECT
       SUM(CASE rating
             WHEN 'G' THEN 1 
		     ELSE 0 
		   END) "一般观众",
       SUM(CASE rating
             WHEN 'PG' THEN 1 
		     ELSE 0 
		   END) "建议家长指导",
       SUM(CASE rating
             WHEN 'PG-13' THEN 1 
		     ELSE 0 
		   END) "家长强烈警告",
       SUM(CASE rating
             WHEN 'R' THEN 1 
		     ELSE 0 
		   END) "受限制的",
       SUM(CASE rating
             WHEN 'NC-17' THEN 1 
		     ELSE 0 
		   END) "仅限成年人"
FROM film;

COALESCE

该函数接收不限个数的参数，返回第一个不为空（NULL）的参数的值。如果所有参数均为 NULL，则函数也返回 NULL。一旦发现非空参数，直接返回该参数的值，而不再处理后面的参数。

它和 MySQL 的 IFNULL 函数的功能相同。

SELECT
	COALESCE (1, 2);
-- 结果为：1

SELECT
	COALESCE (NULL, 2 , 1);
-- 结果为：2

CREATE TABLE items (
	ID serial PRIMARY KEY,
	product VARCHAR (100) NOT NULL,
	price NUMERIC NOT NULL,
	discount NUMERIC
);

INSERT INTO items (product, price, discount)
VALUES
	('A', 1000 ,10),
	('B', 1500 ,20),
	('C', 800 ,5),
	('D', 500, NULL);

SELECT
	product,
	(price - discount) AS net_price
FROM
	items;

结果并不正确，因为产品D的折扣是 NULL，而【万恶】的 NULL 无论参与何种运算，结果都是 NULL。

我们进行改良，如果折扣为 NULL，那就设置为 0：

SELECT
	product,
	(price - COALESCE(discount,0)) AS net_price
FROM
	items;

结果终于正确了。

我们可以使用 CASE 来模拟上面的 COALESCE：

SELECT
	product,
	(
		price - CASE
		WHEN discount IS NULL THEN
			0
		ELSE
			discount
		END
	) AS net_price
FROM
	items;

NULLIF

语法：

NULLIF(参数1,参数2);

如果两个参数的值相等，返回 NULL；否则返回 参数1

比如：

SELECT
	NULLIF (1, 1); -- 返回 NULL

SELECT
	NULLIF (1, 0); -- 返回 1

SELECT
	NULLIF ('A', 'B'); -- 返回 A

示例数据：

CREATE TABLE posts (
  id serial primary key,
	title VARCHAR (255) NOT NULL,
	excerpt VARCHAR (150),
	body TEXT,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP
);

INSERT INTO posts (title, excerpt, body)
VALUES
      ('test post 1','test post excerpt 1','test post body 1'),
      ('test post 2','','test post body 2'),
      ('test post 3', null ,'test post body 3');
      

SELECT
	id,
	title,
	COALESCE (
		NULLIF (excerpt, ''),
		LEFT (body, 40)
	)
FROM
	posts;

示例2：防止被零除

CREATE TABLE members (
	ID serial PRIMARY KEY,
	first_name VARCHAR (50) NOT NULL,
	last_name VARCHAR (50) NOT NULL,
	gender SMALLINT NOT NULL -- 1: 男, 2 女
);

INSERT INTO members (
	first_name,
	last_name,
	gender
)
VALUES
	('John', 'Doe', 1),
	('David', 'Dave', 1),
	('Bush', 'Lily', 2);
	
SELECT
	(
		SUM (
			CASE
			WHEN gender = 1 THEN
				1
			ELSE
				0
			END
		) / NULLIF (
			SUM (
				CASE
				WHEN gender = 2 THEN
					1
				ELSE
					0
				END
			),
			0
		)
	) * 100 AS "Male/Female ratio"
FROM
	members;

CAST

CAST 用于将表达式的值转换为目标数据类型：

CAST(表达式 AS 目标数据类型);

PostgreSQL 提供了等价的 :: 运算符用于简化操作：

表达式::目标数据类型;

比如：

示例：

-- 将字符串转换成整数
SELECT '100'::INTEGER;

-- 将字符串转换成日期
SELECT '2023-07-31'::DATE;

-- 将字符串转换成浮点
SELECT '10.2'::FLOAT;

-- 将字符串转换成布尔
SELECT 'true'::BOOLEAN, 'false'::BOOLEAN, 'T'::BOOLEAN, 'NO'::BOOLEAN;

-- 将字符串转换成时间戳
SELECT '2019-06-15 12:32:43'::TIMESTAMP;

-- 将字符串转换成时间间隔
SELECT '15 minute'::interval,
 '2 hour'::interval,
 '1 day'::interval,
 '2 week'::interval,
 '3 month'::interval;