聚合子查询结果上的 JPA 聚合

Question

我有以下JPA实体(getter、setter和非相关字段省略):

@Entity
@Table(name = "transaction")
public class Transaction {

@Id
@GeneratedValue
@Column(name = "id")
private Long id;

@Column(name = "start_date", nullable = false)
private Date startDate;

}

我的目标是使用JPQL或criteriaAPI实现查询，它将返回每天的平均事务量和最大事务量。

产生预期结果的原生SQL查询(MySQL数据库)如下所示:

select max(cnt) from (
select date(start_date) start_date, count(t.id) cnt 
from transaction t 
group by date(t.start_date)
) t;

select avg(cnt) from (
select date(start_date) start_date, count(t.id) cnt 
from transaction t 
group by date(t.start_date)
) t;

遗憾的是，不鼓励使用本机 SQL 查询，并且 JPQL 不允许在 where 子句中使用子查询。

提前谢谢你。

附加：

我从以下Spring数据查询开始：

@Query("select max(cnt) from ("
+ "select date(t.startDate) startDate, count(t.id) cnt "
+ "from Transaction t "
+ "group by date(t.startDate))")

但显然没有用：

 org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 22 [select max(cnt) from (select date(t.startDate) startDate, count(t.id) cnt from Transaction t group by date(startDate))]

我可以想象，使用排序和限制输出可以管理max的搜索，但这对avg没有帮助。

Answer 1

在JPQL（或Criteria）中无法编写您想要的内容有两个原因：

• From中的子查询，如您所指
• 日期函数-MySQL/Hibernate特定。

对于这个查询，您可能只要求可移植性（尽可能保持原生SQL的标准化）。

一种选择是使用 FluentJPA，它消除了嵌入式字符串，并与 Java 和 JPA 紧密集成。因此，查询将如下所示：

public int getAvgCount() {

    FluentQuery query = FluentJPA.SQL(() -> {

        DailyCount daily = subQuery((Transaction t) -> {

            Date date = alias(DATE(t.getStartDate()), DailyCount::getDate);
            int count = alias(COUNT(t.getId()), DailyCount::getCount);

            SELECT(date, count);
            FROM(t);
            GROUP(BY(date));
        });

        SELECT(AVG(daily.getCount())); // or MAX
        FROM(daily);
    });

    return query.createQuery(em, Integer.class).getSingleResult();
}

类型声明：

@Tuple
@Getter // lombok
public class DailyCount {
    private Integer count;
    private Date date;
}

生成的SQL：

SELECT AVG(q0.count)
FROM (SELECT DATE(t0.start_date) AS date, COUNT(t0.id) AS count
FROM transaction t0
GROUP BY  DATE(t0.start_date)) q0