PostgreSQL 表达式

概述

表达式是由值、运算符和函数组成的组合，PostgreSQL 对其求值后返回计算结果。表达式可以在 SELECT、WHERE、HAVING 等子句中使用。

表达式类型

1. 常量表达式

-- 数值常量
SELECT 42;
SELECT 3.14159;
SELECT 1.5e10;

-- 字符串常量
SELECT 'Hello, World!';
SELECT 'It''s a nice day';  -- 用两个单引号转义

-- 布尔常量
SELECT TRUE;
SELECT FALSE;
SELECT NULL;

-- 日期/时间常量
SELECT DATE '2024-01-15';
SELECT TIME '14:30:00';
SELECT TIMESTAMP '2024-01-15 14:30:00';
SELECT INTERVAL '1 day';

2. 列引用表达式

-- 简单列引用
SELECT name FROM users;
SELECT price FROM products;

-- 带表名的列引用
SELECT users.name FROM users;
SELECT products.price FROM products;

-- 带表别名的列引用
SELECT u.name, o.order_number
FROM users u
JOIN orders o ON u.id = o.user_id;

3. 算术表达式

-- 基本运算
SELECT 10 + 5 AS addition;        -- 加法
SELECT 10 - 5 AS subtraction;     -- 减法
SELECT 10 * 5 AS multiplication;  -- 乘法
SELECT 10 / 5 AS division;        -- 除法
SELECT 10 % 3 AS modulo;          -- 取模

-- 复杂计算
SELECT price * quantity AS subtotal FROM order_items;
SELECT (price * quantity) * (1 + tax_rate) AS total FROM orders;
SELECT ROUND(price * 0.9, 2) AS discounted_price FROM products;

-- 使用括号控制优先级
SELECT (10 + 5) * 2 AS result;  -- 结果为 30
SELECT 10 + (5 * 2) AS result;  -- 结果为 20

4. 字符串表达式

-- 使用 || 连接字符串
SELECT 'Hello' || ' ' || 'World' AS greeting;
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- 使用 CONCAT 函数连接字符串
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT CONCAT('订单 #', order_id, '：', status) AS order_info FROM orders;

-- 常用字符串函数
SELECT UPPER(name) AS uppercase_name FROM users;          -- 转大写
SELECT LOWER(email) AS lowercase_email FROM users;         -- 转小写
SELECT LENGTH(name) AS name_length FROM users;             -- 获取长度
SELECT SUBSTRING(name, 1, 5) AS short_name FROM users;    -- 截取子串
SELECT TRIM('  Hello  ') AS trimmed;                       -- 去除首尾空格
SELECT REPLACE(description, 'old', 'new') AS replaced FROM products;  -- 替换

5. 比较表达式

-- 简单比较
SELECT * FROM products WHERE price > 100;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM orders WHERE status = 'completed';

-- 将比较结果作为列输出
SELECT
    name,
    price,
    price > 100 AS is_expensive,
    stock = 0 AS out_of_stock
FROM products;

-- 复合比较
SELECT * FROM products
WHERE price BETWEEN 50 AND 200
    AND category IN ('Electronics', 'Computers');

6. 逻辑表达式

-- AND 表达式
SELECT * FROM users
WHERE status = 'active' AND country = 'USA';

-- OR 表达式
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Books';

-- NOT 表达式
SELECT * FROM users WHERE NOT status = 'banned';

-- 组合逻辑表达式
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
    AND price < 500
    AND NOT discontinued = true;

条件表达式

CASE 表达式

简单 CASE 表达式

将一个表达式与一组固定值逐一比较：

-- 基本语法
SELECT
    name,
    status,
    CASE status
        WHEN 'active' THEN '活跃用户'
        WHEN 'pending' THEN '待审核'
        WHEN 'suspended' THEN '已停用'
        ELSE '未知状态'
    END AS status_description
FROM users;

-- 实际应用：分类映射
SELECT
    product_name,
    category,
    CASE category
        WHEN 'Electronics' THEN '科技数码'
        WHEN 'Books' THEN '图书文学'
        WHEN 'Clothing' THEN '服装时尚'
        ELSE '其他'
    END AS category_group
FROM products;

搜索 CASE 表达式

对多个独立的布尔条件分别判断：

-- 按年龄分组
SELECT
    name,
    age,
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age >= 18 AND age < 65 THEN '成年人'
        ELSE '老年人'
    END AS age_group
FROM users;

-- 价格区间分层
SELECT
    product_name,
    price,
    CASE
        WHEN price < 50 THEN '经济型'
        WHEN price >= 50 AND price < 200 THEN '中档'
        WHEN price >= 200 AND price < 1000 THEN '高端'
        ELSE '奢侈品'
    END AS price_tier
FROM products;

-- 订单优先级
SELECT
    order_id,
    total_amount,
    customer_type,
    CASE
        WHEN customer_type = 'VIP' THEN 1
        WHEN total_amount > 1000 THEN 2
        WHEN total_amount > 500 THEN 3
        ELSE 4
    END AS priority
FROM orders;

嵌套 CASE 表达式

SELECT
    product_name,
    category,
    price,
    stock,
    CASE
        WHEN stock = 0 THEN '缺货'
        WHEN stock < 10 THEN
            CASE
                WHEN price > 500 THEN '低库存 - 高价值'
                ELSE '低库存 - 普通'
            END
        ELSE '有货'
    END AS stock_status
FROM products;

COALESCE 表达式

COALESCE 返回参数列表中第一个非 NULL 的值：

-- 返回第一个非 NULL 值
SELECT COALESCE(phone, '未提供电话') AS contact_phone FROM users;

-- 多个备选值
SELECT COALESCE(mobile, home_phone, work_phone, '无联系方式') AS phone FROM users;

-- 实际应用
SELECT
    name,
    COALESCE(email, 'no-email@example.com') AS email,
    COALESCE(phone, '暂无') AS phone,
    COALESCE(address, '未提供地址') AS address
FROM users;

-- 在计算中使用
SELECT
    product_name,
    price,
    COALESCE(discount_price, price) AS final_price,
    price - COALESCE(discount_price, price) AS savings
FROM products;

NULLIF 表达式

NULLIF(a, b) 在两个值相等时返回 NULL，否则返回第一个值：

-- 值相等时返回 NULL
SELECT NULLIF(price, 0) FROM products;
SELECT NULLIF(email, '') FROM users;

-- 避免除以零的错误
SELECT
    total_sales,
    total_orders,
    total_sales / NULLIF(total_orders, 0) AS average_order_value
FROM sales_summary;

-- 实际应用：检测价格是否变化
SELECT
    product_name,
    old_price,
    new_price,
    NULLIF(old_price, new_price) AS price_changed
FROM products;

GREATEST 和 LEAST 表达式

-- GREATEST 返回最大值
SELECT GREATEST(10, 20, 15) AS max_value;  -- 结果为 20
SELECT GREATEST(price, cost, 0) AS highest FROM products;

-- LEAST 返回最小值
SELECT LEAST(10, 20, 15) AS min_value;  -- 结果为 10
SELECT LEAST(stock, reorder_level) AS min_stock FROM products;

-- 实际应用：比价
SELECT
    product_name,
    price,
    competitor_price,
    LEAST(price, competitor_price) AS best_price,
    GREATEST(price, competitor_price) - LEAST(price, competitor_price) AS price_difference
FROM products;

聚合表达式

-- 基本聚合函数
SELECT COUNT(*) AS total_users FROM users;
SELECT SUM(amount) AS total_sales FROM orders;
SELECT AVG(price) AS average_price FROM products;
SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products;

-- 条件聚合（FILTER 子句）
SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    SUM(total_amount) AS total_revenue,
    SUM(total_amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

-- 分组聚合
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category;

-- 字符串聚合
SELECT
    category,
    STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM products
GROUP BY category;

类型转换表达式

-- 使用 :: 运算符进行类型转换
SELECT '100'::INTEGER AS int_value;
SELECT '2024-01-15'::DATE AS date_value;
SELECT NOW()::DATE AS today;
SELECT 10::NUMERIC(10, 2) AS decimal_value;

-- 使用 CAST 函数进行类型转换
SELECT CAST('100' AS INTEGER) AS int_value;
SELECT CAST('2024-01-15' AS DATE) AS date_value;
SELECT CAST(price AS TEXT) AS price_text FROM products;

-- 实际应用
SELECT
    order_id,
    order_date::DATE AS date_only,
    total_amount::INTEGER AS rounded_amount,
    CAST(customer_id AS TEXT) AS customer_id_text
FROM orders;

数组表达式

-- 构造数组
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;
SELECT ARRAY['apple', 'banana', 'orange'] AS fruits;

-- 数组操作
SELECT ARRAY[1,2,3] || ARRAY[4,5,6] AS combined;  -- 结果为 {1,2,3,4,5,6}
SELECT ARRAY[1,2,3] || 4 AS appended;              -- 结果为 {1,2,3,4}

-- 数组访问（PostgreSQL 数组下标从 1 开始）
SELECT tags[1] AS first_tag FROM products;
SELECT tags[1:3] AS first_three_tags FROM products;

-- 数组函数
SELECT array_length(tags, 1) AS tag_count FROM products;
SELECT unnest(ARRAY[1,2,3]) AS value;  -- 展开数组为多行

子查询表达式

标量子查询

-- 子查询返回单个值
SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,
    price - (SELECT AVG(price) FROM products) AS price_diff
FROM products;

-- 相关子查询
SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

EXISTS 子查询

-- 检查是否存在
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

-- NOT EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.product_id = p.id
);

IN 子查询

-- 检查值是否在子查询结果中
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE active = true
);

-- NOT IN
SELECT * FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

ANY / ALL 子查询

-- ANY：满足任意一个条件即可
SELECT * FROM products
WHERE price > ANY (
    SELECT price FROM products WHERE category = 'Books'
);

-- ALL：必须满足所有条件
SELECT * FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE category = 'Books'
);

窗口表达式

-- ROW_NUMBER：按价格降序编号
SELECT
    name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- RANK 和 DENSE_RANK：分类内排名
SELECT
    name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

-- 聚合窗口函数：累计和移动平均
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total,
    AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

实用示例

示例 1：电商商品展示

SELECT
    product_name,
    price,
    CASE
        WHEN price < 50 THEN '超值推荐'
        WHEN price < 200 THEN '性价比之选'
        ELSE '精品优选'
    END AS price_badge,
    COALESCE(discount_price, price) AS final_price,
    ROUND(
        (price - COALESCE(discount_price, price)) / NULLIF(price, 0) * 100, 0
    ) AS discount_percent,
    stock > 0 AS in_stock,
    CASE
        WHEN stock = 0 THEN '缺货'
        WHEN stock < 10 THEN '仅剩 ' || stock || ' 件'
        ELSE '有货'
    END AS stock_message
FROM products
WHERE NOT discontinued = true;

示例 2：用户统计报表

SELECT
    first_name || ' ' || last_name AS full_name,
    email,
    EXTRACT(YEAR FROM AGE(birth_date)) AS age,
    CASE
        WHEN EXTRACT(YEAR FROM AGE(birth_date)) < 18 THEN '未成年'
        WHEN EXTRACT(YEAR FROM AGE(birth_date)) BETWEEN 18 AND 35 THEN '青年'
        WHEN EXTRACT(YEAR FROM AGE(birth_date)) BETWEEN 36 AND 60 THEN '中年'
        ELSE '老年'
    END AS age_group,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS total_orders,
    COALESCE(
        (SELECT SUM(total_amount) FROM orders WHERE user_id = users.id), 0
    ) AS lifetime_value,
    CASE
        WHEN (SELECT SUM(total_amount) FROM orders WHERE user_id = users.id) > 10000 THEN 'VIP'
        WHEN (SELECT SUM(total_amount) FROM orders WHERE user_id = users.id) > 5000 THEN '黄金会员'
        WHEN (SELECT SUM(total_amount) FROM orders WHERE user_id = users.id) > 1000 THEN '白银会员'
        ELSE '普通会员'
    END AS customer_tier
FROM users
WHERE status = 'active';

示例 3：订单分析

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount,
    total_amount * 0.08 AS tax,
    total_amount * 1.08 AS total_with_tax,
    CASE
        WHEN total_amount > 1000 THEN total_amount * 0.1
        WHEN total_amount > 500 THEN total_amount * 0.05
        ELSE 0
    END AS discount,
    total_amount * 1.08 - CASE
        WHEN total_amount > 1000 THEN total_amount * 0.1
        WHEN total_amount > 500 THEN total_amount * 0.05
        ELSE 0
    END AS final_total,
    EXTRACT(DAY FROM CURRENT_DATE - order_date) AS days_since_order,
    CASE
        WHEN EXTRACT(DAY FROM CURRENT_DATE - order_date) > 30 THEN '超过30天'
        WHEN EXTRACT(DAY FROM CURRENT_DATE - order_date) > 7 THEN '7-30天'
        ELSE '最近7天'
    END AS order_age
FROM orders
WHERE status IN ('completed', 'shipped');

最佳实践

1. 使用括号明确优先级：在复杂表达式中使用括号，避免依赖默认运算优先级
2. 避免过度嵌套：深层嵌套的表达式难以阅读和维护，可拆分为子查询或 CTE
3. 使用有意义的别名：为复杂表达式提供清晰的列别名
4. 正确处理 NULL：使用 COALESCE 或 NULLIF 妥善处理 NULL 值
5. 关注性能：复杂表达式可能影响查询性能，必要时考虑索引优化
6. 类型匹配：确保表达式中的数据类型兼容，避免隐式转换导致意外结果

小结

PostgreSQL 表达式是构建强大查询的基础：

• 常量和列引用：表达式的基本构建块
• 算术和字符串表达式：用于数据计算和转换
• 条件表达式：CASE、COALESCE、NULLIF 实现条件逻辑
• 聚合表达式：对数据进行汇总统计
• 子查询表达式：实现复杂的数据检索
• 窗口表达式：用于高级分析计算