使用JpaRepository/PSQL统计符合条件的去重影片租赁数量

需求说明 #

需要统计满足以下两个条件的影片数量：

• 曾被租赁：该影片至少有一条rented=true的租赁记录
• 当前未在租：该影片所有rented=true的记录中，return_time均早于指定日期（示例中为2024-05-07）

数据表movie_rental_deals字段说明：

• dvd_id/bluray_id：二者始终一填一空，代表影片的两种介质ID
• rented：布尔类型，租赁后不会改为false，新租赁生成新行
• return_time：日期类型，归还时间

测试数据（当前日期：2024-05-07） #

根据上述数据，应统计出2条结果（蓝光碟1和DVD 2）。

现有尝试 #

初始查询尝试 #

尝试通过DISTINCT ON按介质ID去重并获取最新租赁记录，但未加入条件筛选：

@Query("SELECT DISTINCT ON (mrd.dvd_id, mrd.bluray_id) mrd FROM MovieRentalDeal mrd " +
  "WHERE mrd.rented = true " +
  "ORDER BY mrd.dvd_id, mrd.bluray_id, mrd.return_time DESC")
List<MovieRentalDeal> findReturned();

5.15更新后的查询 #

已能筛选出符合条件的租赁记录，但未实现去重和统计，需后续通过Java流处理：

@Query("SELECT mrd FROM MovieRentalDeal mrd " +
    "LEFT JOIN FETCH mrd.dvd " +
    "LEFT JOIN FETCH mrd.bluray " +
    "WHERE mrd.rented = true " +
    "AND mrd.return_time < :date " +
    "AND (" +
      "(mrd.dvd IS NOT NULL AND mrd.dvd NOT IN (" +
        "SELECT mrd2.dvd FROM MovieRentalDeal mrd2 " +
        "WHERE mrd2.rented = true " +
        "AND mrd2.return_time > :date " +
        "AND mrd2.dvd IS NOT NULL" +
      ")) OR " +
      "(mrd.bluray IS NOT NULL AND mrd.bluray NOT IN (" +
        "SELECT mrd2.bluray FROM MovieRentalDeal mrd2 " +
        "WHERE mrd2.rented = true " +
        "AND mrd2.return_time > :date " +
        "AND mrd2.bluray IS NOT NULL" +
    ")))")
List<MovieRentalDeal> findReturned(Date date);

当前通过Java流统计的方式 #

可行但希望通过JPQL直接优化：

int rentedCurrentlyReturnedMovies = dealRepository.findReturned(new Date())
    .stream().map(mrd -> 
        mrd.getDvd() != null 
            ? mrd.getDvd().getId() 
            : mrd.getBluray().getId())
    .collect(Collectors.toSet()).size();

解决方案 #

方案1：直接统计数量的JPQL查询 #

通过子查询排除当前在租的影片，再按介质ID去重统计：

@Query("SELECT COUNT(DISTINCT COALESCE(mrd.dvd.id, mrd.bluray.id)) " +
       "FROM MovieRentalDeal mrd " +
       "WHERE mrd.rented = true " +
       "AND COALESCE(mrd.dvd.id, mrd.bluray.id) NOT IN (" +
           "SELECT COALESCE(mrd2.dvd.id, mrd2.bluray.id) " +
           "FROM MovieRentalDeal mrd2 " +
           "WHERE mrd2.rented = true " +
           "AND mrd2.return_time > :date" +
       ")")
long countReturnedMovies(@Param("date") Date date);

• 用COALESCE统一获取影片ID（优先取dvd.id，否则取bluray.id）
• 子查询筛选出当前仍在租的影片ID，主查询排除这些ID后统计去重后的数量

方案2：优化现有查询实现去重 #

如果需要获取影片实体而非仅数量，可以修改查询直接返回去重后的介质实体：

@Query("SELECT DISTINCT COALESCE(mrd.dvd, mrd.bluray) " +
       "FROM MovieRentalDeal mrd " +
       "WHERE mrd.rented = true " +
       "AND mrd.return_time < :date " +
       "AND COALESCE(mrd.dvd.id, mrd.bluray.id) NOT IN (" +
           "SELECT COALESCE(mrd2.dvd.id, mrd2.bluray.id) " +
           "FROM MovieRentalDeal mrd2 " +
           "WHERE mrd2.rented = true " +
           "AND mrd2.return_time > :date" +
       ")")
List<Object> findReturnedMovies(@Param("date") Date date);

之后直接通过findReturnedMovies(date).size()即可得到数量，无需额外流处理。

补充说明 #

• COALESCE函数用于处理dvd_id和bluray_id二选一的场景，统一逻辑
• DISTINCT确保同一影片的多条租赁记录只被统计一次
• 子查询精准排除当前仍在租的影片，避免误统计

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