如何编写SQL查询计算客户全周期及月度平均支付金额

问题背景 #

现有存储1年客户交易数据的表transaction_info，记录了客户不同时段的商品购买数量及对应支付金额，部分示例数据如下：

date_new    id_check    id_client   count_products  sum_payment
2001-03-20  2271145      104027      2               23.31
2001-03-20  2271145      104027      1               31.75
2001-03-20  2271145      104027      1               6.8
2001-07-20  1771932      112005      1               2.15
2001-07-20  1771932      112005      1               8.63
2001-08-20  1795365      112005      1               1.37
2001-05-20  2426443      185106      1               7.97
2001-05-20  2426443      185106      8               57.97

需求是编写SQL查询获取两个指标：

• 每个客户全周期的平均支付金额mean_sum_payment_period
• 每个客户的月度平均支付金额mean_sum_payment_month

原有错误原因 #

你编写的查询执行失败核心问题有两个：

1. 子查询没有和外层查询的id_client做关联，导致子查询返回的是所有客户+所有月份的分组聚合结果，行数远多于外层每个客户一行的结果，无法做列匹配
2. 子查询没有做结果限定，直接返回多行列集，外层无法将多值映射到单条客户记录的单个字段上

修复方案 #

首先明确业务中最常用的指标计算逻辑：

• mean_sum_payment_period：客户所有历史单笔交易的支付金额平均值
• mean_sum_payment_month：先计算客户每个自然月的总支付金额，再对该客户所有有交易的月份的总支付额取平均值

修复后的SQL如下（兼容MySQL、PostgreSQL等主流数据库，跨年度场景也适用）：

SELECT 
    t1.id_client,
    AVG(t1.sum_payment) AS mean_sum_payment_period,
    AVG(t2.month_total_payment) AS mean_sum_payment_month
FROM transaction_info t1
LEFT JOIN (
    -- 先聚合得到每个客户每个月的总支付金额
    SELECT 
        id_client,
        DATE_FORMAT(date_new, '%Y-%m') AS trade_month, -- 若为PostgreSQL可替换为TO_CHAR(date_new, 'yyyy-mm')
        SUM(sum_payment) AS month_total_payment
    FROM transaction_info
    GROUP BY id_client, DATE_FORMAT(date_new, '%Y-%m')
) t2 ON t1.id_client = t2.id_client
GROUP BY t1.id_client;

如果你需要的mean_sum_payment_month是客户所有月度的单笔交易支付额的平均值，只需将子查询中的SUM(sum_payment)替换为AVG(sum_payment)即可。

内容的提问来源于stack exchange，提问作者user16795517