如何通过Criteria API构建SQL Rank窗口函数实现分组取首行？

需要用JPA Criteria API实现以下SQL的逻辑：

select * from (
    select *, rank() over (partition by some_id order by some_date desc) rk from table1
 ) t1
 where t1.rk = 1

现有一个支持多列动态选择和过滤的查询代码基础：

HibernateCriteriaBuilder cb = (HibernateCriteriaBuilder) entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<Table1> table1= query.from(Table1.class);
Join<Table1, Table2> joinTable2= Table1.join(Table1_.some_fK_field);
.........
query.multiselect(createSelectionCollection());
query.where(cb.and(createPredicatesCollection())); 

entityManager
              .createQuery(query)
              .unwrap(Query.class)
              .setResultTransformer(new SomeTransformer())
              .setFirstResult(pageable.offset())
              .setMaxResults(pageable.limit())
              .getResultList()

现在需要调整逻辑，先对Table1按指定字段分组做Rank并取每组首行，再执行关联和过滤，最终生成的SQL如下：

select * from (
    select *, rank() over (partition by some_field order by some_date desc) rk from table1
 ) t1
 join table2 t2 on t2.id = t1.some_fK_field
.......
where t1.rk = 1
and t1.some_field in (....)
and t2.some_field in (....)

...etc

不清楚该在何处设置criteriaBuilder.rank()来实现这个逻辑。

要实现该逻辑，需要先构建包含Rank窗口函数的子查询，再将子查询结果作为主查询的数据源，后续完成关联和过滤操作，具体步骤如下：

1. 构建包含Rank窗口函数的子查询 #

子查询需要查询Table1的所有字段，同时计算Rank值，用Subquery实现：

// 创建子查询，返回包含Table1数据和rank的Tuple结果
Subquery<Tuple> subquery = query.subquery(Tuple.class);
Root<Table1> subRoot = subquery.from(Table1.class);

// 定义Rank窗口函数：按some_field分区，some_date降序排序
Expression<Integer> rankExpr = cb.rank()
    .over()
    .partitionBy(subRoot.get(Table1_.some_field))
    .orderBy(cb.desc(subRoot.get(Table1_.some_date)));

// 子查询选择Table1实体（或具体字段）+ rank值，并给rank别名
List<Selection<?>> subSelections = new ArrayList<>();
subSelections.add(subRoot);
subSelections.add(rankExpr.alias("rk"));
subquery.select(cb.tuple(subSelections.toArray(new Selection[0])));

2. 主查询关联子查询结果 #

主查询不再直接从Table1查询，而是以子查询结果作为数据源，再关联Table2：

// 主查询使用子查询结果作为Root
Root<Tuple> t1 = query.from(subquery);
// 从子查询的Tuple中提取Table1实体
Expression<Table1> table1FromSub = t1.get(0, Table1.class);
// 基于子查询中的Table1实体关联Table2
Join<Table1, Table2> joinTable2 = table1FromSub.join(Table1_.some_fK_field);

3. 调整动态选择与过滤条件 #

• 动态选择字段时，从table1FromSub和joinTable2中选取目标字段，替换原有的直接从Root<Table1>选择的逻辑
• 过滤条件中添加rk = 1的规则，合并原有动态过滤条件：

// 添加rk=1的过滤条件
Predicate rankPredicate = cb.equal(t1.get("rk"), 1);
// 合并rank条件与原有动态条件
Predicate finalPredicate = cb.and(rankPredicate, cb.and(createPredicatesCollection()));
query.where(finalPredicate);

// 调整multiselect，使用子查询关联后的数据源字段
query.multiselect(createAdjustedSelectionCollection(table1FromSub, joinTable2));

4. 执行查询（保留原有分页与转换逻辑） #

这部分可沿用原有代码，若结果转换逻辑依赖数据源结构，需对应调整SomeTransformer：

entityManager
        .createQuery(query)
        .unwrap(Query.class)
        .setResultTransformer(new SomeTransformer())
        .setFirstResult(pageable.offset())
        .setMaxResults(pageable.limit())
        .getResultList();

关键说明 #

• 子查询用Tuple承载多列结果（Table1实体 + rank值），方便主查询提取对应数据；若不需要整实体，也可在子查询中明确选择Table1的具体字段
• 必须使用HibernateCriteriaBuilder，因为JPA标准CriteriaBuilder对窗口函数的支持有限，Hibernate扩展提供了完整的窗口函数API

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