用PL/pgSQL做分析报表

分析报表就是把数据系统化地展示出来，帮你做决策。比如：

• 经理想看上个月的收入是多少。
• 分析师在找市场趋势。
• 开发者在监控应用性能。

想象一下，你是个大餐厅的主厨。你想知道哪些菜最受欢迎，就得有个报表。PostgreSQL就是你的菜谱和订单数据库，PL/pgSQL（存储过程）就是你厨房里的小助手，帮你自动分析订单。

分析报表的基本套路

分析报表其实就是聚合、过滤、排序和整理数据，让你能看出有用的信息。一般来说，报表结构分这几步：

1. 数据准备：从表里查数据，过滤和预处理。
2. 数据聚合：算各种指标（比如平均订单金额、总销售额啥的）。
3. 格式化：把数据整理成好看的样子。
4. 输出结果：把报表展示给用户，或者存到表里。

这些步骤都可以用PL/pgSQL的存储过程搞定。

写个分析报表的存储过程

来，咱们看个基础例子。假设有个orders表，里面存着订单数据：

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount NUMERIC(10, 2)
);

我们的目标：做一个指定月份的销售总额报表。也就是说，我们想看到：

• 月份。
• 这个月的总销售额。

存储过程结构

咱们的存储过程大致这样（别怕，PL/pgSQL编程其实很友好）：

1. 接收一个输入参数——月份。
2. 从orders表里查这个月的数据。
3. 算出总销售额。
4. 返回结果。

存储过程实现

代码示例：

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- 查指定月份的数据并聚合
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY 1;
END;
$$ LANGUAGE plpgsql;

1. 输入参数：p_month——日期。用来按月份过滤数据。
2. RETURN QUERY：这个很神奇，可以直接从存储过程返回数据。
3. DATE_TRUNC：把order_date取到月初。
4. SUM：聚合函数，算所有订单的总额。
5. GROUP BY：按月份分组，因为报表是按月的。

现在可以这样调用我们的函数：

SELECT * FROM monthly_sales_report('2023-08-01');

你会得到类似这样的结果：

这个函数就是个基础版。来点进阶的！

写个更复杂的报表

现在假设我们想按客户拆分销售额。也就是说，报表要显示：

• 客户
• 月份
• 这个客户当月的订单总额

改造一下存储过程

CREATE OR REPLACE FUNCTION customer_monthly_report(p_month DATE)
RETURNS TABLE (
    customer_id INT,
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.customer_id,
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY o.customer_id, DATE_TRUNC('month', o.order_date);
END;
$$ LANGUAGE plpgsql;

现在调用存储过程：

SELECT * FROM customer_monthly_report('2023-08-01');

结果可能是这样：

用临时表

有时候做复杂报表时，用临时表会很方便。比如你要处理中间结果。

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- 创建临时表
    CREATE TEMP TABLE temp_sales AS
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', p_month)
    GROUP BY customer_id, DATE_TRUNC('month', order_date);

    -- 可以对这个表做进一步处理
    -- 比如输出本月销售额前3的客户
    RAISE NOTICE '本月前3客户 %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE '客户: %, 金额: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

这里临时表temp_sales就是用来存中间结果的。

小贴士

1. 优化：用索引让查数据更快。
2. 除零报错：算东西时别忘了检查除数，别让报表崩了。
3. 日期格式化：用TO_CHAR这种函数让输出更友好。

希望你没看困！后面还有更难更有趣的内容，别松懈哈！