PostgreSQL教程

空值处理函数

COALESCE函数

返回参数列表中第一个非空值：

SELECT 
    COALESCE(NULL, 'Default Value') AS coalesce_result1,  -- 返回 'Default Value'
    COALESCE(NULL, NULL, 'Third Value') AS coalesce_result2, -- 返回 'Third Value'
    COALESCE(username, email, 'Unknown User') AS user_display_name -- 用户显示名
FROM users;

NULLIF函数

如果两个参数相等则返回NULL，否则返回第一个参数：

SELECT 
    NULLIF(10, 10) AS nullif_result1,                     -- 返回 NULL
    NULLIF(10, 5) AS nullif_result2,                      -- 返回 10
    NULLIF('', '') AS nullif_result3;                     -- 返回 NULL

ISNULL和IS NOT NULL

检查值是否为NULL：

SELECT 
    username,
    CASE WHEN username IS NULL THEN 'No Username' ELSE username END AS safe_username,
    COALESCE(phone, 'No Phone') AS contact_phone
FROM users;

条件选择函数

CASE表达式

SQL标准的条件表达式：

SELECT 
    username,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group,
    CASE status
        WHEN 'active' THEN 'Active User'
        WHEN 'inactive' THEN 'Inactive User'
        ELSE 'Unknown Status'
    END AS status_description
FROM users;

简单CASE表达式

基于单个表达式的条件判断：

SELECT 
    product_name,
    CASE category_id
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Books'
        WHEN 3 THEN 'Clothing'
        ELSE 'Other'
    END AS category_name
FROM products;

值比较函数

GREATEST函数

返回参数列表中的最大值：

SELECT 
    GREATEST(10, 20, 30) AS greatest_result,              -- 返回 30
    GREATEST('apple', 'banana', 'cherry') AS greatest_string, -- 返回 'cherry'
    GREATEST(score1, score2, score3) AS best_score        -- 返回最高分
FROM student_scores;

LEAST函数

返回参数列表中的最小值：

SELECT 
    LEAST(10, 20, 30) AS least_result,                    -- 返回 10
    LEAST('apple', 'banana', 'cherry') AS least_string,   -- 返回 'apple'
    LEAST(price1, price2, price3) AS lowest_price         -- 返回最低价
FROM product_prices;

布尔函数

TRUE和FALSE函数

处理布尔值：

SELECT 
    username,
    is_active,
    CASE 
        WHEN is_active IS TRUE THEN 'Active'
        WHEN is_active IS FALSE THEN 'Inactive'
        WHEN is_active IS NULL THEN 'Unknown'
    END AS status_text
FROM users;

布尔逻辑函数

组合布尔条件：

SELECT 
    username,
    COALESCE(is_premium, FALSE) AS is_premium_safe,
    COALESCE(is_verified, FALSE) AS is_verified_safe,
    COALESCE(is_premium, FALSE) AND COALESCE(is_verified, FALSE) AS is_eligible
FROM users;

实际应用场景

数据清洗和验证

-- 清洗用户数据，处理空值
UPDATE users 
SET 
    display_name = COALESCE(display_name, username, email, 'Anonymous'),
    phone = NULLIF(TRIM(phone), ''),
    age = GREATEST(LEAST(age, 150), 0) -- 限制年龄在0-150之间
WHERE id IN (SELECT id FROM users WHERE display_name IS NULL OR phone = '' OR age < 0 OR age > 150);

报告和统计

-- 生成用户状态报告
SELECT 
    CASE 
        WHEN last_login_date >= CURRENT_DATE - INTERVAL '7 days' THEN 'Active'
        WHEN last_login_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Recent'
        WHEN last_login_date IS NOT NULL THEN 'Inactive'
        ELSE 'Never Logged In'
    END AS user_activity_level,
    COUNT(*) AS user_count
FROM users
GROUP BY 
    CASE 
        WHEN last_login_date >= CURRENT_DATE - INTERVAL '7 days' THEN 'Active'
        WHEN last_login_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Recent'
        WHEN last_login_date IS NOT NULL THEN 'Inactive'
        ELSE 'Never Logged In'
    END
ORDER BY user_count DESC;

价格和折扣计算

-- 计算最终价格，考虑多种折扣
SELECT 
    product_name,
    price AS original_price,
    COALESCE(member_discount, 0) AS member_discount,
    COALESCE(seasonal_discount, 0) AS seasonal_discount,
    COALESCE(coupon_discount, 0) AS coupon_discount,
    price * (1 - GREATEST(
        COALESCE(member_discount, 0),
        COALESCE(seasonal_discount, 0),
        COALESCE(coupon_discount, 0)
    )) AS final_price
FROM products
WHERE is_active = TRUE;

评分和排名

-- 计算综合评分
SELECT 
    student_name,
    test1_score,
    test2_score,
    test3_score,
    GREATEST(test1_score, test2_score, test3_score) AS best_score,
    LEAST(test1_score, test2_score, test3_score) AS worst_score,
    ROUND((test1_score + test2_score + test3_score) / 3.0, 2) AS average_score,
    CASE 
        WHEN (test1_score + test2_score + test3_score) / 3.0 >= 90 THEN 'A'
        WHEN (test1_score + test2_score + test3_score) / 3.0 >= 80 THEN 'B'
        WHEN (test1_score + test2_score + test3_score) / 3.0 >= 70 THEN 'C'
        WHEN (test1_score + test2_score + test3_score) / 3.0 >= 60 THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM student_scores;

性能优化

索引考虑

在使用条件函数时考虑索引影响：

-- 不好的查询（可能无法使用索引）
SELECT * FROM users 
WHERE COALESCE(phone, email) = 'john@example.com';

-- 优化后的查询（可以使用索引）
SELECT * FROM users 
WHERE phone = 'john@example.com' OR email = 'john@example.com';

-- 为条件查询创建合适的索引
CREATE INDEX idx_users_contact ON users (phone, email);

函数索引

为常用条件函数创建索引：

-- 为显示名创建函数索引
CREATE INDEX idx_users_display_name ON users (COALESCE(display_name, username));

-- 为年龄范围创建部分索引
CREATE INDEX idx_users_adults ON users (age) WHERE age BETWEEN 18 AND 65;

高级应用

嵌套条件函数

SELECT 
    username,
    COALESCE(
        CASE 
            WHEN is_premium IS TRUE THEN 'Premium User'
            WHEN is_verified IS TRUE THEN 'Verified User'
            ELSE NULL
        END,
        'Regular User'
    ) AS user_type
FROM users;

与聚合函数结合

SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    GREATEST(AVG(salary) * 1.1, MAX(salary) * 0.9) AS target_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

-- 创建用户分析函数
CREATE OR REPLACE FUNCTION analyze_user_engagement()
RETURNS TABLE(
    engagement_level TEXT,
    user_count BIGINT,
    avg_session_duration NUMERIC,
    most_common_action TEXT
) AS $$
BEGIN
    RETURN QUERY
    WITH user_stats AS (
        SELECT 
            u.id,
            u.username,
            COUNT(s.id) AS session_count,
            COALESCE(AVG(EXTRACT(EPOCH FROM (s.end_time - s.start_time))), 0) AS avg_duration_seconds,
            CASE 
                WHEN COUNT(s.id) >= 10 THEN 'Highly Engaged'
                WHEN COUNT(s.id) >= 5 THEN 'Moderately Engaged'
                WHEN COUNT(s.id) > 0 THEN 'Low Engaged'
                ELSE 'Inactive'
            END AS engagement_category
        FROM users u
        LEFT JOIN user_sessions s ON u.id = s.user_id
        GROUP BY u.id, u.username
    ),
    action_stats AS (
        SELECT 
            us.engagement_category,
            a.action_type,
            COUNT(*) AS action_count
        FROM user_stats us
        JOIN user_sessions s ON us.id = s.user_id
        JOIN user_actions a ON s.id = a.session_id
        GROUP BY us.engagement_category, a.action_type
    ),
    top_actions AS (
        SELECT 
            engagement_category,
            action_type,
            ROW_NUMBER() OVER (PARTITION BY engagement_category ORDER BY action_count DESC) as rn
        FROM action_stats
    )
    SELECT 
        us.engagement_category AS engagement_level,
        COUNT(*) AS user_count,
        ROUND(AVG(us.avg_duration_seconds) / 60, 2) AS avg_session_duration,
        COALESCE(ta.action_type, 'No Actions') AS most_common_action
    FROM user_stats us
    LEFT JOIN top_actions ta ON us.engagement_category = ta.engagement_category AND ta.rn = 1
    GROUP BY us.engagement_category, ta.action_type
    ORDER BY user_count DESC;
END;
$$ LANGUAGE plpgsql;

-- 使用函数分析用户参与度
SELECT * FROM analyze_user_engagement();