집합

집합 groupby having에 대해서 알아보자.

 

 

test를 실행해보자. tuple 쓴 이유는 타입이 count age.sum 등등 여러개가 들어오기 때문에 tuple을 썼다.

( querydsl이 제공하는 tuple)

@Test
public void aggregation() {
    // tuple로 조회하게 되는데 (여러개 타입 있을 때 꺼내 올수 있는거)
    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.max())).isEqualTo(25);
    assertThat(tuple.get(member.age.min())).isEqualTo(10);
}

 

아래와 같이 JPQL이 나가게 된다.

/* select
        count(member1),
        sum(member1.age),
        avg(member1.age),
        max(member1.age),
        min(member1.age) 
    from
        Member member1
        
        
        // 실행된 sql도 아래와 같이 나오게 된다.
        select
            count(member0_.id) as col_0_0_,
            sum(member0_.age) as col_1_0_,
            avg(cast(member0_.age as double)) as col_2_0_,
            max(member0_.age) as col_3_0_,
            min(member0_.age) as col_4_0_ 
        from
            member member0_

 

 

group과 관련해서 test를 해보면 아래처럼 쿼리가 나가게 된다.

@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);

    // teamA와 관련된거
    assertThat(teamA.get(team.name)).isEqualTo("teamA");
    assertThat(teamA.get(member.age.avg())).isEqualTo(15); // (10 + 20) // 2

    // teamB와 관련된거
    assertThat(teamA.get(team.name)).isEqualTo("teamB");
    assertThat(teamA.get(member.age.avg())).isEqualTo(35); // (30 + 40) // 2
}

// member랑 team이 groupby를 해서 내부 join을 하게된다.

/* select
        team.name,
        avg(member1.age) 
    from
        Member member1   
    inner join
        member1.team as team 
    group by
        team.name */ select
            team1_.name as col_0_0_,
            avg(cast(member0_.age as double)) as col_1_0_ 
        from
            member member0_ 
        inner join
            team team1_ 
                on member0_.team_id=team1_.id 
        group by
            team1_.name

 

 

아래처럼 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)
                .having()
                .fetch();

 

 

 

 

 

 

 

 

 

<출처 김영한: 실전! Querydsl >

https://www.inflearn.com/course/Querydsl-%EC%8B%A4%EC%A0%84/dashboard

실전! Querydsl - 인프런 | 강의