[Querydsl] 집합(groupBy, having)

집합 함수

여러 필드의 집합함수를 이용해 조회할 경우에는 반환타입이 List<Tuple>이 된다

/**
     * JPQL
     * select
* COUNT(m), //회원수
* SUM(m.age), //나이 합
* AVG(m.age), //평균 나이
* MAX(m.age), //최대 나이
* MIN(m.age) //최소 나이 * from Member m
*/
@Test
public void aggregation() throws Exception {
    List<Tuple> result = queryFactory
           .select(member.count(),
                    member.age.sum(),
                    member.age.avg(),
                    member.age.max(),
                    member.age.min())
            .from(member)
            .fetch();
            
    Tuple tuple = result.get(0);
    assertThat(tuple.get(member.count())).isEqualTo(4);
    assertThat(tuple.get(member.age.sum())).isEqualTo(100);
    assertThat(tuple.get(member.age.avg())).isEqualTo(25);
    assertThat(tuple.get(member.age.max())).isEqualTo(40);
    assertThat(tuple.get(member.age.min())).isEqualTo(10);
  }

GroupBy 사용

그룹화된 결과를 제한하려면 having 

**
* 팀의 이름과 각 팀 회원의 평균 연령을 구해라.
*/
@Test
public void group() throws Exception {

    List<Tuple> result = queryFactory
            .select(team.name, member.age.avg())
            .from(member)
            .join(member.team, team)
            .groupBy(team.name)
            .fetch();
            
    Tuple teamA = result.get(0);
    Tuple teamB = result.get(1);
    
    assertThat(teamA.get(team.name)).isEqualTo("teamA");
    assertThat(teamA.get(member.age.avg())).isEqualTo(15);
    assertThat(teamB.get(team.name)).isEqualTo("teamB");
    assertThat(teamB.get(member.age.avg())).isEqualTo(35);

}

//having 사용하면
.groupBy(item.price)
.having(item.price.gt(1000)) //가격 1000원이상인 아이템을 조회
...