表达式技巧

在 PostgreSQL 中，表达式是由常量、运算符、函数、列名等组成的。熟练掌握表达式技巧，能显著提高查询的效率和灵活性。

条件表达式

CASE 表达式

CASE 表达式允许在 SQL 查询中执行条件逻辑。

简单 CASE 表达式

SELECT name, 
       CASE department_id
         WHEN 1 THEN 'HR'
         WHEN 2 THEN 'Engineering'
         ELSE 'Other'
       END AS department_name
FROM employees;

搜索 CASE 表达式

SELECT name, 
       CASE
         WHEN salary > 100000 THEN 'High Salary'
         WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium Salary'
         ELSE 'Low Salary'
       END AS salary_level
FROM employees;

COALESCE 表达式

COALESCE 函数返回第一个非 NULL 的值。

SELECT name, COALESCE(phone_number, 'N/A') AS contact FROM employees;

NULLIF 表达式

NULLIF 函数在两个值相等时返回 NULL，否则返回第一个值。

SELECT name, NULLIF(salary, 0) AS adjusted_salary FROM employees;

数学表达式

PostgreSQL 提供了丰富的数学运算符和函数，用于处理数值类型的运算。

数学运算符

• +：加法
• -：减法
• *：乘法
• /：除法
• %：取余

SELECT salary * 1.1 AS increased_salary FROM employees;
SELECT salary % 1000 AS salary_remainder FROM employees;

使用常量

PostgreSQL 允许在查询中使用常量，例如：

SELECT salary + 5000 AS salary_with_bonus FROM employees;

字符串表达式

字符串连接

使用 || 运算符进行字符串连接。

SELECT first_name || ' ' || last_name AS full_name FROM employees;

字符串的模式匹配

• LIKE：模糊匹配
• ILIKE：不区分大小写的模糊匹配

SELECT name FROM employees WHERE name LIKE 'A%';
SELECT name FROM employees WHERE name ILIKE 'a%';

正则表达式匹配

PostgreSQL 支持通过正则表达式进行复杂的模式匹配。

• ~：匹配正则表达式
• ~*：不区分大小写的正则匹配

SELECT name FROM employees WHERE name ~ '^A.*';
SELECT name FROM employees WHERE email ~* 'example.com';

日期与时间表达式

日期运算

PostgreSQL 允许对日期进行加减运算，支持使用 INTERVAL 类型。

SELECT CURRENT_DATE + INTERVAL '1 month' AS next_month;
SELECT CURRENT_DATE - INTERVAL '1 day' AS yesterday;

日期格式化

使用 TO_CHAR 函数格式化日期或时间值。

SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS formatted_date;
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'DD-Mon-YYYY HH24:MI:SS') AS formatted_time;

提取日期部分

使用 EXTRACT 函数提取日期的特定部分，如年、月、日等。

SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS current_year;
SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS current_month;

集合与数组表达式

PostgreSQL 支持数组和集合操作，可以方便地处理多个值。

数组构造

使用 {} 来构造数组。

SELECT ARRAY[1, 2, 3] AS numbers;
SELECT ARRAY['a', 'b', 'c'] AS letters;

数组操作

PostgreSQL 提供了多种操作符和函数用于数组操作。

• array_length()：返回数组的长度
• array_append()：向数组中添加元素

SELECT array_length(ARRAY[1, 2, 3], 1) AS array_size;
SELECT array_append(ARRAY[1, 2, 3], 4) AS updated_array;

ANY 和 ALL 操作符

ANY 和 ALL 用于与集合中的元素进行比较。

SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 1);
SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 1);

聚合与窗口函数

PostgreSQL 提供了强大的聚合和窗口函数，用于在查询中进行数据聚合和排序计算。

聚合函数

如 SUM、AVG、COUNT、MAX、MIN 等可以在查询中进行统计计算。

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

窗口函数

窗口函数允许在查询中对分组后的数据进行进一步操作，如排名、累计等。

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

小结

表达式技巧是 PostgreSQL 查询中不可或缺的部分，通过合理使用各种运算符、函数和表达式，可以提升查询效率和复杂度，满足多种复杂的数据处理需求。掌握这些技巧有助于优化数据查询和操作，提高数据库的使用效能。