[Querydsl] 집합 함수, GroupBy

# 집합 함수

/**
 * JPQL
 * select
 *  COUNT(m),       // 회원 수
 *  SUM(m.age),     // 나이 합
 *  AVG(m.age),     // 평균 나이
 *  MAX(m.age),     // 최대 나이
 *  MIN(m.age),     // 최소 나이
 * from Member m
 */
@Test
public void aggregation(){
    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);
}

 

 

 

# Group By

/**
 * 팀의 이름과 각 팀의 평균 연령을 구하라
 */
@Test
public void group() {
    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);
}

 

 

• groupBy, 그룹화된 결과를 제한하려면 having() 을 사용한다.

.groupBy(item.price)
.having(item.price.gt(1000))