# 表达式技巧

URL: https://caijiao.org/postgresql/advanced/functions/expressions
Source: docs/postgresql/advanced/functions/expressions.md
Description: 介绍 PostgreSQL 中常用的表达式技巧，帮助开发者在查询中更高效地使用各种运算符、函数和表达式，提升查询灵活性和性能。

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

## 条件表达式

### CASE 表达式

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

#### 简单 CASE 表达式

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

#### 搜索 CASE 表达式

```sql
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 的值。

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

### NULLIF 表达式

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

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

## 数学表达式

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

### 数学运算符

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

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

### 使用常量

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

```sql
SELECT salary + 5000 AS salary_with_bonus FROM employees;
```

## 字符串表达式

### 字符串连接

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

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

### 字符串的模式匹配

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

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

### 正则表达式匹配

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

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

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

## 日期与时间表达式

### 日期运算

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

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

### 日期格式化

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

```sql
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` 函数提取日期的特定部分，如年、月、日等。

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

## 集合与数组表达式

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

### 数组构造

使用 `{}` 来构造数组。

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

### 数组操作

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

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

```sql
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` 用于与集合中的元素进行比较。

```sql
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` 等可以在查询中进行统计计算。

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

### 窗口函数

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

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

## 小结

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