例子：最近3个月订单的平均消费额计算

这节课我们来看一个挺实用的例子。

平均消费额——就是一个指标，能看出客户每次买东西大概花多少钱。这是很重要的业务指标，可以用来：

• 分析购买力的变化，
• 发现销售趋势，
• 评估营销活动的效果。

任务描述

假设我们有个数据库，里面有个orders表，存着订单。我们的目标：

1. 算出最近三个月下单的平均消费额。
2. 用procedure自动化这个计算。
3. 把结果存到单独的表里，方便后续分析。

扩展我们的数据库：orders表结构

首先要确保我们有个包含必要数据的表。orders表结构大概长这样：

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

• order_id — 订单唯一ID。
• customer_id — 下单的客户。
• order_date — 下单日期。
• total_amount — 订单总金额。

举个例子，往表里插几条数据，方便后面操作：

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

手动计算平均消费额

在自动化之前，先写个基础查询，算最近3个月的平均消费额。我们会用当前日期（CURRENT_DATE）和AVG()函数来算平均值。

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

这里发生了什么：

• AVG(total_amount) — 聚合函数，算total_amount的平均值。
• CURRENT_DATE - INTERVAL '3 months' — 选出最近三个月的订单。
• ROUND(..., 2) — 结果保留两位小数。

查询结果大概是这样：

用procedure自动化

现在我们的目标——写个procedure自动算平均消费额，并把结果写到单独的日志表里。先建个表存分析日志。

创建log_analytics表

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — 记录的日期和时间。
• metric_name — 指标名（这里用"averagecheck_3_months"）。
• metric_value — 算出来的指标值。

写procedure

现在写个procedure，它会：

1. 算最近三个月的平均消费额。
2. 把结果存到log_analytics表。

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- 第一步：算平均消费额
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- 第二步：写日志
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- 可选：调试信息
    RAISE NOTICE '平均消费额: %', avg_check;
END;
$$ LANGUAGE plpgsql;

现在你可以直接调用这个function，它会自动把结果写到log_analytics表：

SELECT calculate_average_check();

用任务调度器自动化

上一节我们已经装好任务调度器了。如果你用Linux——那就是pg_cron扩展；如果是Windows或macOS——大概率是用系统自带的调度器（cron或Task Scheduler）。现在一切就绪，把procedure加到定时任务里。

如果你在Linux上用pg_cron，先确保扩展在目标数据库里启用了：

CREATE EXTENSION IF NOT EXISTS pg_cron;

（提醒一下：pg_cron的安装和shared_preload_libraries参数的配置，上节课已经讲过了。）

现在可以安排我们的calculate_average_check()函数每天凌晨执行一次：

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

解释一下：

• 'daily_avg_check' — 任务名；
• '0 0 * * *' — cron表达式，每天00:00执行；
• $$里的命令——要执行的SQL。

如果你用Windows或macOS，pg_cron在这些系统上用不了（Windows完全不支持，macOS要自己编译）。不过你已经配好系统调度器了——只要把SQL文件接上就行。

1. 新建一个SQL脚本：

   echo "SELECT calculate_average_check();" > /path/to/script.sql
   

2. 用psql定时执行这个文件：

   • 在Linux/macOS下：

       0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

     （加到crontab -e里）
   • 在Windows Task Scheduler下：
     • 指定psql.exe的路径。
     • 参数写成：
       -U postgres -d your_database -f "C:\path\to\script.sql"

这样，不管你用什么系统，procedure都会自动定时执行，把平均消费额写到log_analytics表里。如果你不确定自己用的是哪种方式，可以回去看看上节课——里面有各种平台的安装和配置方法。

检查和分析结果

来看看效果。查一下log_analytics表的数据：

SELECT * FROM log_analytics ORDER BY log_date DESC;

结果示例：

现在我们有了所有平均消费额的日志！这些数据可以用来做报表，或者分析指标随时间的变化。

常见错误和怎么避免

写这种算平均消费额的分析procedure时，经常会遇到一些典型的坑。

其中一个——忘了处理空结果。如果最近三个月没有订单，AVG()会返回NULL，日志里就可能出问题。为了避免，可以用COALESCE()：

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

还有一个坑——orders表里有脏数据。比如订单金额是负数，或者日期不对。建议定期检查数据，或者在数据库层加约束（比如CHECK (total_amount > 0)）。

恭喜，现在你已经有了一个完整的procedure，能自动算最近三个月的平均消费额，并把结果存起来方便后续分析。这只是PostgreSQL和PL/pgSQL自动化分析任务的一个例子。下节课我们会继续讲更复杂的分析场景。下次见！