1. PostgreSQL函数入门：从零开始掌握基础操作

第一次接触PostgreSQL函数时，我完全被它强大的功能震撼到了。记得当时我需要处理一个简单的数据转换任务，同事告诉我可以用::操作符快速完成，从此打开了新世界的大门。PostgreSQL函数就像数据库中的瑞士军刀，能帮你解决各种数据处理难题。

最基础的格式转换函数就有三种写法，我最常用的是双冒号语法，因为它简洁明了。比如要把字符串'123'转成整数，只需要：

SELECT '123'::int;

这种写法在日常开发中特别实用，尤其是处理用户输入或外部数据导入时。但要注意，如果字符串包含非数字字符，转换会失败。我曾在项目中遇到过这种问题，后来学会了先用正则表达式验证数据格式。

数据类型转换函数CAST是更标准的SQL写法，适合需要跨数据库兼容的场景：

SELECT CAST('2023-01-01' AS date);

对于日期处理，to_char函数是我的得力助手。它支持各种格式化选项，比如：

SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS');

这个函数在生成报表时特别有用，可以按照业务需求灵活调整日期显示格式。记得有一次客户要求日期显示为"January 01, 2023"格式，用to_char轻松实现了：

SELECT to_char(current_date, 'Month DD, YYYY');

2. 数学与逻辑运算：数据分析的基石

数学函数是数据分析的基础工具。PostgreSQL提供了丰富的数学运算功能，从简单的加减乘除到复杂的统计计算应有尽有。在实际项目中，我经常用这些函数处理业务指标计算。

基础的算术运算符大家都熟悉，但有几个实用技巧值得分享。比如计算百分比时，整数除法会截断小数部分：

SELECT 5/2;  -- 结果是2，不是2.5

这时可以强制转换类型来保留小数：

SELECT 5::numeric/2;  -- 结果是2.5

数学函数中，round和trunc经常被混淆。round是四舍五入，而trunc是直接截断：

SELECT round(3.14159, 2), trunc(3.14159, 2);  -- 结果分别是3.14和3.14

逻辑运算在业务规则处理中特别重要。PostgreSQL的标准逻辑运算符AND、OR、NOT用法直观，但要注意NULL值的处理。比如：

SELECT NULL AND true;  -- 结果是NULL，不是false

比较谓词中的BETWEEN语法很实用，但要注意它是包含边界值的：

SELECT 10 BETWEEN 5 AND 10;  -- 结果是true

3. 字符串处理：文本操作的利器

字符串处理是数据库开发中最常见的任务之一。PostgreSQL的字符串函数非常强大，能处理各种复杂的文本操作需求。

基础的字符串连接有三种方式，我最推荐||运算符，因为它简洁高效：

SELECT 'Hello' || ' ' || 'World';

对于包含NULL值的连接，concat函数更安全，它会自动忽略NULL值：

SELECT concat('Hello', NULL, 'World');  -- 结果是HelloWorld

字符串分割和提取是常见需求。split_part函数可以按分隔符拆分字符串：

SELECT split_part('a,b,c,d', ',', 3);  -- 结果是c

正则表达式函数功能强大但学习曲线较陡。regexp_replace可以完成复杂的文本替换：

SELECT regexp_replace('foo123bar456', '\d+', 'X', 'g');  -- 结果是fooXbarX

在实际项目中，我常用这些函数清洗用户输入的脏数据，比如去除多余空格、统一格式等。

4. 日期时间处理：业务系统的核心需求

日期时间处理是业务系统的核心需求，PostgreSQL提供了全面的日期时间函数集。掌握这些函数能大幅提高开发效率。

获取当前时间有多个函数，区别很关键：

• now()返回事务开始时间
• clock_timestamp()返回实际调用时间
• current_timestamp是now()的别名

日期加减运算很常用，比如计算3天后的日期：

SELECT current_date + interval '3 days';

提取日期部分用date_part或extract：

SELECT extract(year from current_date);

日期格式化我习惯用to_char，它支持丰富的格式选项：

SELECT to_char(current_timestamp, 'Day, DD Month YYYY');

处理时区时要特别注意。我建议在数据库中统一存储UTC时间，只在显示时转换：

SELECT now() AT TIME ZONE 'Asia/Shanghai';

5. 高级函数技巧：窗口函数与自定义函数

窗口函数是PostgreSQL的高级特性，能实现复杂的分析需求而不需要自连接。我第一次使用窗口函数就爱上了它的强大功能。

row_number()是最基础的窗口函数，为结果集中的行分配序号：

SELECT name, salary, row_number() OVER (ORDER BY salary DESC) 
FROM employees;

rank()和dense_rank()处理并列排名，区别在于如何处理序号：

SELECT name, score, 
       rank() OVER (ORDER BY score DESC),
       dense_rank() OVER (ORDER BY score DESC)
FROM exam_results;

lag和lead函数可以访问前后行的数据，非常适合计算环比：

SELECT month, revenue,
       revenue - lag(revenue) OVER (ORDER BY month) AS growth
FROM monthly_sales;

自定义函数能封装复杂逻辑。创建一个简单的格式化函数：

CREATE FUNCTION format_phone(text) RETURNS text AS $$
BEGIN
    RETURN regexp_replace($1, '(\d{3})(\d{4})(\d{4})', '\1-\2-\3');
END;
$$ LANGUAGE plpgsql;

6. 性能优化：让函数飞起来

函数使用不当会导致性能问题。经过多次优化实践，我总结出几个关键点。

避免在WHERE子句中使用函数转换列值，这会阻止索引使用：

-- 不好的写法
SELECT * FROM users WHERE lower(name) = 'alice';

-- 好的写法
SELECT * FROM users WHERE name = 'Alice';

对于频繁使用的复杂计算，可以考虑物化视图或函数索引：

CREATE INDEX idx_lower_name ON users (lower(name));

集合函数如array_agg处理大数据集时可能消耗大量内存，考虑使用游标或分页：

SELECT department, array_agg(name) 
FROM employees 
GROUP BY department
LIMIT 100;

对于复杂的分析查询，窗口函数的性能通常优于自连接，但要注意分区大小。

7. 实战案例：电商数据分析

结合电商场景，演示PostgreSQL函数的实际应用。假设我们需要分析用户购买行为。

计算用户首次购买和最近购买时间：

SELECT user_id,
       min(purchase_date) AS first_purchase,
       max(purchase_date) AS last_purchase,
       count(*) AS purchase_count
FROM orders
GROUP BY user_id;

使用窗口函数计算用户消费排名：

SELECT user_id, total_spend,
       dense_rank() OVER (ORDER BY total_spend DESC) AS rank
FROM (
    SELECT user_id, sum(amount) AS total_spend
    FROM orders
    GROUP BY user_id
) t;

分析复购周期：

WITH user_purchases AS (
    SELECT user_id, purchase_date,
           lead(purchase_date) OVER (PARTITION BY user_id ORDER BY purchase_date) AS next_purchase
    FROM orders
)
SELECT user_id, avg(next_purchase - purchase_date) AS avg_repurchase_days
FROM user_purchases
WHERE next_purchase IS NOT NULL
GROUP BY user_id;

这些例子展示了如何组合使用各种PostgreSQL函数解决实际的业务分析需求。