PostgreSQL Generate_Series按月生成日期带条件查询失效问题

我在Spring Boot应用中使用PostgreSQL的GENERATE_SERIES生成数据库中无记录的月份数据，原始SQL如下：

SELECT
    asl.id, asl.outstanding_principal as outstandingPrincipal, the_date as theDate, asl.interest_rate as interestRate, asl.interest_payment as interestPayment, asl.principal_payment as principalPayment,
    asl.total_payment as totalPayment, asl.actual_delta as actualDelta, asl.outstanding_usd as outstandingUsd, asl.disbursement, asl.floating_index_rate as floatingIndexRate,
    asl.upfront_fee as upfrontFee, asl.commitment_fee as commitmentFee, asl.other_fee as otherFee, asl.withholding_tax as withholdingTax, asl.default_fee as defaultFee,
    asl.prepayment_fee as prepaymentFee, asl.total_out_flows as totalOutFlows, asl.net_flows as netFlows, asl.modified, asl.new_row as newRow, asl.interest_payment_modified as
    interestPaymentModified, asl.date, asl.amortization_schedule_initial_id as amortizationScheduleInitialId, asl.tranche_id as trancheId, asl.user_id as userId, tr.local_currency_id as localCurrencyId,
    f.facility_id
FROM
    GENERATE_SERIES
    (
        (SELECT MIN(ams.date) FROM amortization_schedules ams),
        (SELECT MAX(ams.date) + INTERVAL '1' MONTH FROM amortization_schedules ams),
        '1 MONTH'
    ) AS tab (the_date)
FULL JOIN amortization_schedules asl on to_char(the_date, 'yyyy-mm') = to_char(asl.date, 'yyyy-mm')
LEFT JOIN tranches tr ON asl.tranche_id = tr.id
LEFT JOIN facilities f on tr.facility_id = f.id

添加筛选条件WHERE f.id = :id and tr.tranche_number_id = :trancheNumberId后，GENERATE_SERIES失效，原本应返回30条结果，现在仅返回3条。

尝试过两种JOIN条件：

• 使用FULL JOIN amortization_schedules asl on to_char(the_date, 'yyyy-mm') = to_char(asl.date, 'yyyy-mm')：无法生成完整月份，仅返回3条匹配数据
• 改为to_char(the_date, 'yyyy') = to_char(asl.date, 'yyyy')：虽能生成数据，但按年关联不符合需求，会出现同一年所有月份关联同一条记录的情况

期望结果是：按月生成完整的theDate序列，同时每个月份关联对应时间段的amortization_schedules记录，缺失记录的字段用对应时间段的有效值填充（比如上一期的outstandingPrincipal）。

解决方案 #

核心思路

问题根源在于WHERE子句的筛选会直接过滤掉GENERATE_SERIES生成的无匹配记录的行，且原始JOIN逻辑未基于目标筛选数据集生成时间序列。正确步骤：

1. 先筛选出符合条件的目标数据集，限定时间范围的来源
2. 基于目标数据集的时间范围生成完整月份序列
3. 用LEFT JOIN保留所有生成的月份行
4. 用窗口函数填充缺失字段的延续值

最终SQL

WITH filtered_data AS (
    SELECT
        asl.id,
        asl.outstanding_principal,
        asl.interest_rate,
        asl.interest_payment,
        asl.principal_payment,
        asl.total_payment,
        asl.actual_delta,
        asl.outstanding_usd,
        asl.disbursement,
        asl.floating_index_rate,
        asl.upfront_fee,
        asl.commitment_fee,
        asl.other_fee,
        asl.withholding_tax,
        asl.default_fee,
        asl.prepayment_fee,
        asl.total_out_flows,
        asl.net_flows,
        asl.modified,
        asl.new_row,
        asl.interest_payment_modified,
        asl.date,
        asl.amortization_schedule_initial_id,
        asl.tranche_id,
        asl.user_id,
        tr.local_currency_id,
        f.facility_id
    FROM amortization_schedules asl
    JOIN tranches tr ON asl.tranche_id = tr.id
    JOIN facilities f ON tr.facility_id = f.id
    WHERE f.id = :id AND tr.tranche_number_id = :trancheNumberId
),
date_series AS (
    SELECT generate_series(
        (SELECT MIN(date) FROM filtered_data),
        (SELECT MAX(date) + INTERVAL '1' MONTH FROM filtered_data),
        '1 MONTH'
    ) AS the_date
)
SELECT
    COALESCE(fd.id, LAG(fd.id) OVER (ORDER BY ds.the_date)) AS id,
    COALESCE(fd.outstanding_principal, LAG(fd.outstanding_principal) OVER (ORDER BY ds.the_date)) AS outstandingPrincipal,
    ds.the_date AS theDate,
    COALESCE(fd.interest_rate, LAG(fd.interest_rate) OVER (ORDER BY ds.the_date)) AS interestRate,
    COALESCE(fd.interest_payment, LAG(fd.interest_payment) OVER (ORDER BY ds.the_date)) AS interestPayment,
    COALESCE(fd.principal_payment, LAG(fd.principal_payment) OVER (ORDER BY ds.the_date)) AS principalPayment,
    COALESCE(fd.total_payment, LAG(fd.total_payment) OVER (ORDER BY ds.the_date)) AS totalPayment,
    COALESCE(fd.actual_delta, LAG(fd.actual_delta) OVER (ORDER BY ds.the_date)) AS actualDelta,
    COALESCE(fd.outstanding_usd, LAG(fd.outstanding_usd) OVER (ORDER BY ds.the_date)) AS outstandingUsd,
    COALESCE(fd.disbursement, LAG(fd.disbursement) OVER (ORDER BY ds.the_date)) AS disbursement,
    COALESCE(fd.floating_index_rate, LAG(fd.floating_index_rate) OVER (ORDER BY ds.the_date)) AS floatingIndexRate,
    COALESCE(fd.upfront_fee, LAG(fd.upfront_fee) OVER (ORDER BY ds.the_date)) AS upfrontFee,
    COALESCE(fd.commitment_fee, LAG(fd.commitment_fee) OVER (ORDER BY ds.the_date)) AS commitmentFee,
    COALESCE(fd.other_fee, LAG(fd.other_fee) OVER (ORDER BY ds.the_date)) AS otherFee,
    COALESCE(fd.withholding_tax, LAG(fd.withholding_tax) OVER (ORDER BY ds.the_date)) AS withholdingTax,
    COALESCE(fd.default_fee, LAG(fd.default_fee) OVER (ORDER BY ds.the_date)) AS defaultFee,
    COALESCE(fd.prepayment_fee, LAG(fd.prepayment_fee) OVER (ORDER BY ds.the_date)) AS prepaymentFee,
    COALESCE(fd.total_out_flows, LAG(fd.total_out_flows) OVER (ORDER BY ds.the_date)) AS totalOutFlows,
    COALESCE(fd.net_flows, LAG(fd.net_flows) OVER (ORDER BY ds.the_date)) AS netFlows,
    COALESCE(fd.modified, LAG(fd.modified) OVER (ORDER BY ds.the_date)) AS modified,
    COALESCE(fd.new_row, LAG(fd.new_row) OVER (ORDER BY ds.the_date)) AS newRow,
    COALESCE(fd.interest_payment_modified, LAG(fd.interest_payment_modified) OVER (ORDER BY ds.the_date)) AS interestPaymentModified,
    COALESCE(fd.date, LAG(fd.date) OVER (ORDER BY ds.the_date)) AS date,
    COALESCE(fd.amortization_schedule_initial_id, LAG(fd.amortization_schedule_initial_id) OVER (ORDER BY ds.the_date)) AS amortizationScheduleInitialId,
    COALESCE(fd.tranche_id, LAG(fd.tranche_id) OVER (ORDER BY ds.the_date)) AS trancheId,
    COALESCE(fd.user_id, LAG(fd.user_id) OVER (ORDER BY ds.the_date)) AS userId,
    COALESCE(fd.local_currency_id, LAG(fd.local_currency_id) OVER (ORDER BY ds.the_date)) AS localCurrencyId,
    COALESCE(fd.facility_id, LAG(fd.facility_id) OVER (ORDER BY ds.the_date)) AS facility_id
FROM date_series ds
LEFT JOIN filtered_data fd ON to_char(ds.the_date, 'yyyy-mm') = to_char(fd.date, 'yyyy-mm')
ORDER BY ds.the_date;

关键说明

1. CTE筛选目标数据：filtered_data先获取符合条件的所有记录，确保后续生成的时间序列仅覆盖目标数据的时间范围，避免冗余
2. 生成精准时间序列：date_series基于筛选后数据的最小/最大日期生成月份序列，保证序列的完整性和相关性
3. LEFT JOIN保留所有月份：用LEFT JOIN关联日期序列和筛选数据，即使月份无匹配记录，生成的日期行也不会被过滤
4. 窗口函数填充缺失值：LAG()窗口函数获取上一条非空记录的值，填充当前月份的缺失字段，实现“延续上一期数据”的效果，完全匹配你期望的结果格式

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