mysql - jpa criteria group by aggregates 值

Question

我需要一个查询来将时间(毫秒)转换为每小时分布。

我的表有一个名为 timestamp 的列:以毫秒为单位的值。按小时分组我需要做以下事情除以 1000 -> 秒除以 3600 -> 小时 -> mod 24 -> 获取创建记录的时间。

时间戳很长:

    public static volatile SingularAttribute<VersionJpa, Long> timestamp;

然后我可以在天数分布中显示结果。但是，这不起作用，该组不起作用，我得到的完整列表未分组且未计算在内。

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<VersionJpa> root = cq.from(VersionJpa.class);
    cq.where(p);
    Expression<Integer> group = cb.mod(cb.toInteger(cb.quot(root.get(VersionJpa_.timestamp), 3600000)),24);
    cq.multiselect(group, cb.count(root));
    cq.groupBy(group);
    TypedQuery<Tuple> tq = em.createQuery(cq);
    return tq.getResultList();

此 SQL (mysql) 生成正确的结果。 JPA 中有等效的楼层吗？试过。(整数)|| toInteger()

    select mod(floor(stamp/3600000), 24) , count(*) from versions group by mod(floor(stamp/3600000), 24);

我发现在单元测试期间(在 Derby 上)运行良好，但在更多生产环境中，如设置 (MySQL)，它不会将最内层的聚合转换为整数，因此该组是在浮点值上完成的，而不是在24 (0, 1, .. 23) 个潜在值(返回千组)。

    Expression<Integer> hours = cb.mod(cb.quot(root.get(VersionJpa_.timestamp).as(Integer.class), 3600000).as(Integer.class),24).as(Integer.class);
    cq.multiselect(hours, cb.count(root));
    cq.groupBy(hours);

添加 PostgreSQL 支持和测试会导致另一个错误，这让我认为我的查询是错误的:

    RuntimeException Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: column "versions.stamp" must appear in the GROUP BY clause or be used in an aggregate function Position: 13 Error Code: 0 Call: SELECT MOD((STAMP / ?), ?), COUNT(ID) FROM VERSIONS GROUP BY MOD((STAMP / ?), ?) LIMIT ? OFFSET ? bind => [6 parameters bound] Query: TupleQuery(referenceClass=VersionJpa sql="SELECT MOD((STAMP / ?), ?), COUNT(ID) FROM VERSIONS GROUP BY MOD((STAMP / ?), ?) LIMIT ? OFFSET ?")

感谢任何帮助。

Answer 1

您可以使用 function() 方法调用任何数据库函数。

看， http://java-persistence-performance.blogspot.com/2012/05/jpql-vs-sql-have-both-with-eclipselink.html

Postgres 似乎更提示在 group by 中使用函数，在 JPQL 中你可以尝试，

Select mod((v.timestamp / 3600000), 24) h, count(v) from Version v group by h