使用GROUP BY與聚合(Aggregation)函式

本文為【Spring Boot情境式網站開發指南：使用Spring Data JPA、Spring Security、Spring Web Flow】一書的【 第4章 Criteria API入門】延續，完整範例程式碼可至出版社下載

聚合函式不一定套用在全部資料，也可以使用CriteriaBuilder的groupBy()方法予以分組再執行聚合函式，並可搭配CriteriaBuilder的having()方法過濾分組條件。

groupBy() & count()

當JPQL使用以下查詢時：

SELECT e.dept, COUNT(e) FROM Employee e GROUP BY e.dept

對應的Criteria API用法如下：

@Test 
public void Criteria_GroupBy4Count() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery

【說明】
行8-9：

1. 使用CriteriaBuilder的groupBy()指定分組的依據欄位；該欄位也要出現在multiselect()方法中。
2. 分組的目的在使用count()方法計算各組數量。

行5：

1. 查詢分組欄位與各分組數量共2個欄位，使用Object[]型態輸出。

groupBy() & avg()

當JPQL使用以下查詢時：

SELECT e.dept, AVG(e.salary) FROM Employee e GROUP BY e.dept                                                            

對應的Criteria API用法如下範例程式碼行8-10。同前例，只是將count()改為avg()，且avg()只接受數字欄位：

@Test
public void Criteria_GroupBy4Average() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery

MAX()、GROUP BY、HAVING

當JPQL使用以下查詢時：

SELECT e.dept, MAX(e.salary) FROM Employee e 
GROUP BY e.dept 
HAVING e.dept IN ('IT', 'Admin')                                                                                       

對應的Criteria API用法如下：

@Test
public void Criteria_GroupByAndHaving() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery

【說明】
行10：

使用CriteriaBuilder的having()方法限定分組欄位dept只能是IT與Admin兩種值：emp.get(Employee_.dept)in("IT", "Admin"));

相似的情況，也可以再加上orderBy()方法排序輸出結果。當JPQL使用以下查詢時：

SELECT NEW entity.DeptGroup(e.dept, COUNT(e.dept)) 
  FROM Employee e 
 GROUP BY e.dept 
 HAVING COUNT(e.dept) > 1 
 ORDER BY COUNT(e.dept) DESC, e.dept ASC                                                                                                                            

對應的Criteria API用法如下，可以由JPQL_GroupByWithConstructor()驗證：

@Test
public void Criteria_GroupByWithConstructor() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(DeptGroup.class);
    Root emp = sql.from(Employee.class);
    // reusable column expressions
    Expression deptExp = emp.get(Employee_.dept);
    Expression countExp = cb.count(deptExp);
    // make sql
    sql.multiselect(deptExp, countExp);
    sql.groupBy(deptExp);
    sql.having(cb.gt(countExp, 1));
    sql.orderBy(cb.desc(countExp), cb.asc(deptExp));
    // run sql
    TypedQuery query = em.createQuery(sql);
    List resultList = query.getResultList();
    resultList.forEach(System.out::println);
    assertThat(resultList, 
            contains(new DeptGroup("Admin", 2), new DeptGroup("IT", 2)));
}                                                                                                                       

【說明】
行8-9： 

群組欄位emp.get(Employee_.dept)與套用聚合函式的欄位在建構SQL時會反覆使用，因此宣告變數以利重複使用：
Expression<String> deptExp = emp.get(Employee_.dept);
Expression<Long> countExp = cb.count(deptExp);

行10-14： 

建構SQL： sql.having(cb.gt(countExp, 1))：要求分組後的數量統計必須great than(gt) 1。
sql.orderBy(cb.desc(countExp), cb.asc(deptExp))：輸出時排序。