如何用HQL查询按30天访问量差值排序的顶级雇主

你的问题根源很明确：HQL的子查询别名（groupedEv、groupedEv2）只在定义它们的子查询范围内有效，外层的ORDER BY子句无法直接引用这些别名。我们可以通过将需要的计算字段通过子查询提前查询出来，或者使用JOIN关联两个子查询结果的方式来解决这个问题。

根据你的实体结构和需求，这里提供一个可行的HQL方案：

思路说明 #

我们需要为每个雇主获取两组数据：

• 最新的访问记录（当前日期最近的那条）
• 30天前的最新访问记录（即日期不超过当前日期-30天的最近那条）
  然后计算这两条记录的总访问量（visit_counter + visit_before_date_total_counter）的差值，按差值降序排序取前N个。

完整HQL查询代码 #

// 先计算30天前的日期，复制当前calendar并调整日期
Calendar thirtyDaysAgo = (Calendar) calendar.clone();
thirtyDaysAgo.add(Calendar.DAY_OF_MONTH, -30);

String hql = "SELECT " +
        "    latest.employerVisitId.employerId, " +
        "    (latest.visitCounter + latest.visitBeforeDateTotalCounter) - " +
        "    (COALESCE(thirtyAgo.visitCounter + thirtyAgo.visitBeforeDateTotalCounter, 0)) AS growth " +
        "FROM EmployerVisit latest " +
        // 关联获取每个雇主的最新记录
        "INNER JOIN (" +
        "    SELECT ev.employerVisitId.employerId, MAX(ev.employerVisitId.date) AS maxDate " +
        "    FROM EmployerVisit ev " +
        "    GROUP BY ev.employerVisitId.employerId" +
        ") latestDates ON latest.employerVisitId.employerId = latestDates.employerId " +
        "               AND latest.employerVisitId.date = latestDates.maxDate " +
        // 左关联获取每个雇主30天前的最新记录（兼容无历史记录的情况）
        "LEFT JOIN (" +
        "    SELECT ev.employerVisitId.employerId, MAX(ev.employerVisitId.date) AS maxThirtyDate " +
        "    FROM EmployerVisit ev " +
        "    WHERE ev.employerVisitId.date <= :thirtyDaysAgoDate " +
        "    GROUP BY ev.employerVisitId.employerId" +
        ") thirtyAgoDates ON latest.employerVisitId.employerId = thirtyAgoDates.employerId " +
        "LEFT JOIN EmployerVisit thirtyAgo ON thirtyAgo.employerVisitId.employerId = thirtyAgoDates.employerId " +
        "                                 AND thirtyAgo.employerVisitId.date = thirtyAgoDates.maxThirtyDate " +
        "ORDER BY growth DESC";

Query<Object[]> query = getSession().createQuery(hql)
        .setParameter("thirtyDaysAgoDate", thirtyDaysAgo.getTime())
        .setMaxResults(size);

// 处理结果集，如需返回完整EmployerVisit对象可调整SELECT字段
List<Object[]> results = query.list();

代码解释 #

1. 子查询latestDates：获取每个雇主的最新记录日期，通过INNER JOIN关联主表得到最新的完整访问记录。
2. 子查询thirtyAgoDates：获取每个雇主在30天前及之前的最新记录日期，用LEFT JOIN确保即使某个雇主30天前没有访问记录也能被纳入统计。
3. COALESCE函数：处理30天前无记录的边界情况，此时默认将历史总访问量视为0，差值直接等于最新记录的总访问量。
4. 排序与分页：直接使用计算出的growth字段排序，并用setMaxResults获取指定数量的顶级增长雇主。

另一种更简洁的写法（嵌套子查询） #

如果你只需要雇主ID和增长值，也可以用嵌套子查询直接获取每个雇主的两个总访问量，写法更紧凑：

String hql = "SELECT " +
        "    ev.employerVisitId.employerId, " +
        "    (ev.visitCounter + ev.visitBeforeDateTotalCounter) - " +
        "    (SELECT COALESCE(ev2.visitCounter + ev2.visitBeforeDateTotalCounter, 0) " +
        "     FROM EmployerVisit ev2 " +
        "     WHERE ev2.employerVisitId.employerId = ev.employerVisitId.employerId " +
        "       AND ev2.employerVisitId.date = (" +
        "           SELECT MAX(ev3.employerVisitId.date) " +
        "           FROM EmployerVisit ev3 " +
        "           WHERE ev3.employerVisitId.employerId = ev2.employerVisitId.employerId " +
        "             AND ev3.employerVisitId.date <= :thirtyDaysAgoDate" +
        "       )) AS growth " +
        "FROM EmployerVisit ev " +
        "WHERE ev.employerVisitId.date = (" +
        "    SELECT MAX(ev4.employerVisitId.date) " +
        "    FROM EmployerVisit ev4 " +
        "    WHERE ev4.employerVisitId.employerId = ev.employerVisitId.employerId" +
        ") " +
        "ORDER BY growth DESC";

Query<Object[]> query = getSession().createQuery(hql)
        .setParameter("thirtyDaysAgoDate", thirtyDaysAgo.getTime())
        .setMaxResults(size);

这种写法代码量更少，但数据量大时性能可能不如JOIN方案，你可以根据实际业务场景选择。

内容的提问来源于stack exchange，提问作者Никита Башаров