SQL实现按月统计截至各月末的账户最新金额平均值

现有一段使用窗口函数的SQL逻辑用于全量数据统计：先提取每个账户的最新金额（amount），再计算所有账户该金额的平均值。
需要改造为按月分段统计能力，规则如下：

• 若指定统计月份内某账户无对应数据，取该账户距离统计月最近的历史金额值
• 每个月的统计范围为数据起始时间至统计当月月末
• 最终输出自数据起始以来每个自然月对应的平均金额值，替代原有逻辑返回的单个全局平均结果

原有SQL代码如下：

SELECT AVG(amount) as "average amount" 
FROM (
    SELECT * 
    FROM(
        SELECT account_no,amount,_date,row_number() over(partition by account_no order by _date desc) as rn, source 
        FROM ('another subquery too long to write out fully') k
    ) j
    WHERE j.rn = 1
  ) l

核心思路是先构造全量统计月份序列，再逐月份匹配每个账户的最近历史金额，最后聚合计算平均值，具体步骤：

1. 保留原有全量数据的子查询作为基础数据源
2. 基于数据源的时间范围，生成所有需要统计的自然月维度，包含每个月的月末截止日期
3. 将统计月份和全量账户数据做关联，仅保留账户数据日期早于等于统计月月末的记录
4. 按「统计月份+账户」分组，用窗口函数筛选出每个账户在对应月份的最新有效金额
5. 按统计月份分组，计算当月所有账户有效金额的平均值即可

参考实现SQL（兼容支持标准SQL的数仓/数据库引擎，如BigQuery、PostgreSQL、Spark SQL、MySQL 8.0+等）：

WITH
-- 原有全量数据子查询，直接复用即可
source_data AS (
    SELECT account_no, amount, _date, source
    FROM ('another subquery too long to write out fully') k
),
-- 生成数据覆盖范围内的所有统计自然月
stat_months AS (
    SELECT
        DATE_TRUNC(_date, 'MONTH') AS stat_month,
        LAST_DAY(_date) AS month_end
    FROM source_data
    GROUP BY stat_month, month_end
),
-- 匹配每个月每个账户的最新有效金额
account_month_amount AS (
    SELECT
        m.stat_month,
        d.account_no,
        d.amount,
        ROW_NUMBER() OVER (PARTITION BY m.stat_month, d.account_no ORDER BY d._date DESC) AS rn
    FROM stat_months m
    LEFT JOIN source_data d
        ON d._date <= m.month_end
)
-- 按月聚合计算平均金额
SELECT
    stat_month,
    AVG(amount) AS "average amount"
FROM account_month_amount
WHERE rn = 1
GROUP BY stat_month
ORDER BY stat_month

注意事项 #

• 如果使用的SQL引擎不支持DATE_TRUNC、LAST_DAY函数，替换为对应引擎的日期计算函数即可，核心逻辑不变；MySQL 8.0生成连续月份如果不想用内置函数，也可以通过预建月份维度表、递归CTE的方式实现
• 逻辑默认覆盖所有在统计月之前产生过记录的账户，如果需要过滤已销户/无效账户，在关联数据源时加上对应状态过滤条件即可
• 如果存在某账户在数据起始第一个统计月之前都没有记录的场景，该账户在无记录的月份不会被纳入平均计算，符合常规统计逻辑

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