如何查询各月中≥指定日期且最接近月末的汇率数据？

现有可用查询 #

原生查询可获取≤指定日期的对应货币最近汇率：

@Query(value = "select e.* from exchange_rates e where e.date <=:currentDate and e.local_currency_id = :localCurrencyId order by e.downloaddate desc limit 1", nativeQuery = true)
Optional<ExchangeRate> findAllByDateAndLocalCurrency_Id(Date currentDate, Long localCurrencyId);

待实现需求 #

查询指定货币下，每个月中日期≥指定日期（currentDate）、且date字段最接近月末的汇率记录。

原尝试查询的问题分析 #

提交的查询未生效，核心问题包括：

• JOIN子查询已通过e.date = m_date.max_date关联到每月最大日期记录，但WHERE条件中e.date < m_date.max_date与之矛盾，导致无匹配结果。
• 主查询重复选择e.date字段，属于冗余代码。
• LIMIT 1限制仅返回单条记录，不符合“每个月一条”的需求（若需求为单条需调整逻辑）。

可行查询方案 #

方案1：获取所有符合条件的月度月末汇率（多条记录） #

若需返回指定货币下，所有≥currentDate的月份中，每个月最接近月末的汇率记录，使用以下查询：

@Query(value = """
    SELECT e.*
    FROM exchange_rates e
    JOIN (
        SELECT 
            local_currency_id,
            DATE_TRUNC('month', date) AS month_period,
            MAX(date) AS end_of_month_date
        FROM exchange_rates
        WHERE local_currency_id = :localCurrencyId
            AND date >= :currentDate
        GROUP BY local_currency_id, DATE_TRUNC('month', date)
    ) AS monthly_max 
    ON e.local_currency_id = monthly_max.local_currency_id 
        AND e.date = monthly_max.end_of_month_date
    ORDER BY e.date DESC
""", nativeQuery = true)
List<ExchangeRate> findMonthlyEndOfMonthRates(Date currentDate, Long localCurrencyId);

逻辑说明：

1. 子查询先筛选指定货币且日期≥currentDate的记录，按货币+月份分组，提取每个月的最大日期（即最接近月末的日期）。
2. 主查询关联原表，获取每个月最大日期对应的完整汇率数据，最终按日期倒序排列。

方案2：获取最近的一条月末汇率记录（单条） #

若仅需返回指定货币下，≥currentDate的最近一个月末的汇率记录，使用以下查询：

@Query(value = """
    SELECT e.*
    FROM exchange_rates e
    WHERE e.local_currency_id = :localCurrencyId
        AND e.date >= :currentDate
    ORDER BY DATE_TRUNC('month', e.date) DESC, e.date DESC
    LIMIT 1
""", nativeQuery = true)
Optional<ExchangeRate> findLatestEndOfMonthRate(Date currentDate, Long localCurrencyId);

逻辑说明：

1. 直接筛选指定货币且日期≥currentDate的记录。
2. 排序优先级：先按月份倒序（优先最近的月份），再按日期倒序（取该月最大日期），最后通过LIMIT 1获取最近的那条月末记录。

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