如何查询PostgreSQL汇率表中当日的货币记录

针对你提到的每小时插入数据、存在重复记录的PostgreSQL表，以下是几种基于JPA的查询方案，涵盖基础当日数据查询和去重取最新记录的场景：

1. 基础JPQL查询当日所有记录 #

利用PostgreSQL的DATE_TRUNC函数截断时间到"天"维度，和当前日期做匹配：

@Repository
public interface FxRatesRepository extends JpaRepository<FxRates, Long> {

    @Query("SELECT f FROM FxRates f WHERE FUNCTION('DATE_TRUNC', 'day', f.createdAt) = FUNCTION('DATE_TRUNC', 'day', CURRENT_TIMESTAMP)")
    List<FxRates> findTodayRecords();
}

该方法会返回当日所有插入的汇率记录，包含重复数据。

2. 处理重复数据：获取每个货币当日最新的汇率 #

如果需要每个货币仅保留当日最新的一条记录，推荐以下两种方案：

方案一：JPQL子查询实现 #

通过子查询找到每个货币当日的最大时间戳，匹配主表对应记录：

@Repository
public interface FxRatesRepository extends JpaRepository<FxRates, Long> {

    @Query("SELECT f FROM FxRates f WHERE f.createdAt = (" +
           "SELECT MAX(f2.createdAt) FROM FxRates f2 " +
           "WHERE f2.currency = f.currency " +
           "AND FUNCTION('DATE_TRUNC', 'day', f2.createdAt) = FUNCTION('DATE_TRUNC', 'day', CURRENT_TIMESTAMP))")
    List<FxRates> findTodayLatestRatesPerCurrency();
}

方案二：原生SQL结合窗口函数 #

窗口函数ROW_NUMBER()可以更高效地实现分组取最新记录，适合数据量较大的场景：

@Repository
public interface FxRatesRepository extends JpaRepository<FxRates, Long> {

    @Query(value = "SELECT * FROM (" +
                   "SELECT *, ROW_NUMBER() OVER (PARTITION BY currency ORDER BY created_at DESC) rn " +
                   "FROM fxrates " +
                   "WHERE DATE_TRUNC('day', created_at) = DATE_TRUNC('day', CURRENT_TIMESTAMP)" +
                   ") t WHERE rn = 1", nativeQuery = true)
    List<FxRates> findTodayLatestRatesPerCurrencyNative();
}

解释：按currency分组，每组内按created_at降序排序，取排序后第一条（rn=1）即为该货币当日最新汇率。

3. Spring Data JPA方法命名简化查询 #

通过方法命名规则快速实现当日区间查询，需提前计算当日的时间范围：

@Repository
public interface FxRatesRepository extends JpaRepository<FxRates, Long> {

    List<FxRates> findByCreatedAtBetween(Instant startOfDay, Instant endOfDay);
}

调用时计算当日的起始和结束时间（以UTC时区为例）：

// 计算当日0点UTC时间
Instant startOfDay = LocalDate.now().atStartOfDay(ZoneId.of("UTC")).toInstant();
// 计算次日0点UTC时间（作为区间结束）
Instant endOfDay = startOfDay.plus(1, ChronoUnit.DAYS);

List<FxRates> todayRecords = fxRatesRepository.findByCreatedAtBetween(startOfDay, endOfDay);

若需去重，可添加Distinct关键字：

List<FxRates> findDistinctByCurrencyAndCreatedAtBetween(String currency, Instant startOfDay, Instant endOfDay);

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