JPA按日期聚合数据异常：GROUP BY日期后仍出现重复日期记录

我明白你现在的困惑——明明写了按日期GROUP BY，结果却还是每个日期拆成了多条独立记录，这确实让人摸不着头脑。咱们一步步来排查和解决问题：

1. 先补全实体类的遗漏字段 #

你提供的PageView实体里没有site属性，但Repository的查询语句里用到了pv.site = :site，这会导致JPA解析查询时出错。请确保实体类中存在正确的关联字段，比如：

data class PageView(
    @Id @GeneratedValue(strategy = GenerationType.UUID)
    val id: String? = null,

    val sessionId: String,
    val url: String,
    val viewedAt: LocalDateTime,
    @ManyToOne // 补充关联注解
    val site: Site // 补充site字段
)

2. 统一日期转换逻辑，避免JPA解析差异 #

不同数据库对CAST(timestamp as date)的语法支持可能有细微差别，建议改用JPA标准的FUNCTION函数调用数据库原生的日期转换方法，确保SELECT和GROUP BY的字段完全一致：

修改后的Repository查询：

@Query(
    """
    SELECT new com.eventuallycoding.blogiq.repository.DailyViewStats(
        FUNCTION('DATE', pv.viewedAt),
        COUNT(pv.id),
        COUNT(DISTINCT pv.sessionId) 
    )
    FROM PageView pv
    WHERE pv.site = :site
    AND (:url IS NULL OR pv.url = :url)
    AND pv.viewedAt BETWEEN :startDate AND :endDate
    GROUP BY FUNCTION('DATE', pv.viewedAt)
    ORDER BY FUNCTION('DATE', pv.viewedAt) ASC
    """
)
fun findDailyViewStatsBySiteAndInterval(
    @Param("site") site: Site,
    @Param("startDate") startDate: LocalDateTime,
    @Param("endDate") endDate: LocalDateTime,
    @Param("url") url: String? = null
): List<DailyViewStats>

FUNCTION('DATE', pv.viewedAt)会调用数据库原生的日期提取函数（比如PostgreSQL的DATE()、MySQL的DATE()），比CAST更跨数据库，也能确保分组字段和查询字段的逻辑完全一致。

3. 验证数据库层面的分组逻辑 #

为了排除JPA的干扰，直接在数据库中执行原生SQL查询，看分组结果是否正常：

-- 以PostgreSQL为例，其他数据库调整DATE函数写法
SELECT DATE(viewed_at) AS view_date, 
       COUNT(id) AS total_views, 
       COUNT(DISTINCT session_id) AS unique_visitors
FROM page_view
WHERE site_id = ? -- 替换为你的Site实体的主键值
AND viewed_at BETWEEN ? AND ? -- 替换为startDate和endDate的数据库兼容格式
GROUP BY view_date
ORDER BY view_date ASC;

• 如果原生SQL返回的结果是正常的（每个日期一条记录，统计值正确）：说明问题出在JPA的查询解析或映射上，可以尝试调整DailyViewStats的构造函数，直接接收LocalDate而不需要通过java.sql.Date中转；
• 如果原生SQL也返回重复日期：说明你的数据本身或查询参数有问题，比如startDate/endDate的范围设置错误，或者site参数匹配了多个不相关的记录。

4. 检查参数传递的时间范围 #

确保startDate是查询起始日的00:00:00，endDate是结束日的23:59:59.999，避免因时间范围设置不当导致记录被错误拆分。比如：

val startDate = LocalDate.of(2024, 10, 31).atStartOfDay()
val endDate = LocalDate.of(2024, 11, 1).atTime(23, 59, 59, 999_999_999)

备注：内容来源于stack exchange，提问作者Hugo Lassiège