[querydsl] group by 쿼리만들기

 

데이터를 집계해서 뽑아야 할때가 있는데 예를들면 날짜별로 있는 데이터를 월별로 뽑아야 할때 등이다.

 

예를들어 다음과 같은 테이블과 데이터가 있다고 해보자. 해당 테이블을 월별로 뽑을때 말이다.

 

쿼리를 만든다면 MYSQL의 경우 DATE_FORMAT을 이용해 만들 수 있는데, 해당 기능은 Mysql에 의존하기 때문에 일반적으로는 불가하다. 그럼에도 호출하려면 Expressions를 이용해 수행할 수 있다.

StringTemplate formattedDate = Expressions.stringTemplate(
                "DATE_FORMAT({0}, {1})"
                , salesDaily.yyyymmdd
                , ConstantImpl.create("%Y-%m"));

 

Expressions.stringTemplate 기능을 사용해 실행할 수 있는데, 첫번째 인자는 함수명을, 두번째는 컬럼명({0} 에 들어갈 값), 그리고 세번째는 포멧할 유형을 입력했다.

 

저것을 쿼리 그대로 한다면 다음과 같다.

SELECT
	DATE_FORMAT(yyyymmdd, '%Y-%m') 
from sales_daily

 

 

그래서 위의 format을 SELECT 와 GROUP 안에 넣으면 된다.

 

데이터를 조회하고 난 뒤 데이터를 저장할 객체를 만든다.

내 경우 QueryProjection을 이용하는데, 의존성이 있긴 하지만 이쪽을 선호하는 편이다.

 

@Getter
@ToString
public class SalesMonthlyTestDto {
    private String yyyymmdd;

    private Long cntSum;

    private Long salesSum;

    @QueryProjection
    public SalesMonthlyTestDto(String yyyymmdd, Long cntSum, Long salesSum) {
        this.yyyymmdd = yyyymmdd;
        this.cntSum = cntSum;
        this.salesSum = salesSum;
    }
}

 

이렇게 생성하고 gradle의 compileQuerydsl을 수행하면 Q로 시작하는 객체가 생성된다. 해당 객체를 queryFactory 에 적용해서 다음과 같이 작성했다.

...
    public List<SalesMonthlyTestDto> testGroupbyQuery() {
        LocalDate startDate = LocalDate.of(2018, 1, 1);
        LocalDate endDate = LocalDate.of(2018, 5, 31);

        StringTemplate formattedDate = Expressions.stringTemplate(
                "DATE_FORMAT({0}, {1})"
                , salesDaily.yyyymmdd
                , ConstantImpl.create("%Y-%m"));

        List<SalesMonthlyTestDto> fetch = queryFactory
                .select(
                        new QSalesMonthlyTestDto(
                                formattedDate.as("yyyymm")
                                , salesDaily.cnt.sum()
                                , salesDaily.sales.sum()
                        )
                )
                .from(salesDaily)
                .where(salesDaily.yyyymmdd.goe(startDate)
                        , salesDaily.yyyymmdd.loe(endDate)
                )
                .groupBy(formattedDate)
                .orderBy(formattedDate.asc())
                .fetch();

        return fetch;
    }
...

 

formattedDate 를 select의 한 부분과 groupBy, orderBy 부분에 적용하여 조회를 한다.

 

그럼 이제 호출해보는 테스트를 작성한다

@SpringBootTest
class GroupRepositoryTest {
    @Autowired
    private TestRepository testRepository;

    @Test
    @DisplayName("그룹 쿼리 실행(querydsl)")
    void querydslGroupbyTest() {
        List<SalesMonthlyTestDto> salesMonthlyTestDtos = testRepository.testGroupbyQuery();

        for (SalesMonthlyTestDto salesMonthlyTestDto : salesMonthlyTestDtos) {
            System.out.println("salesMonthlyTestDto = " + salesMonthlyTestDto);
        }
    }
}

 

시스템 로그를 보면 다음의 쿼리가 수행된다

SELECT 
    DATE_FORMAT(salesdaily0_.yyyymmdd, '%Y-%m') AS col_0_0_,
    SUM(salesdaily0_.cnt_sum) AS col_1_0_,
    SUM(salesdaily0_.sales_sum) AS col_2_0_
FROM
    sales_daily_temp salesdaily0_
WHERE
    salesdaily0_.yyyymmdd >= '2018-01-01T00:00:00.000+0900'
        AND salesdaily0_.yyyymmdd <= '2018-05-31T00:00:00.000+0900'
GROUP BY DATE_FORMAT(salesdaily0_.yyyymmdd, '%Y-%m')
ORDER BY DATE_FORMAT(salesdaily0_.yyyymmdd, '%Y-%m') ASC;

 

조회 결과 객체에 잘 담겨있는지 확인.

salesMonthlyTestDto = SalesMonthlyTestDto(yyyymmdd=2018-01, cntSum=97061, salesSum=2462960812) salesMonthlyTestDto = SalesMonthlyTestDto(yyyymmdd=2018-02, cntSum=90798, salesSum=2388801767) salesMonthlyTestDto = SalesMonthlyTestDto(yyyymmdd=2018-03, cntSum=111121, salesSum=2804843966) salesMonthlyTestDto = SalesMonthlyTestDto(yyyymmdd=2018-04, cntSum=114052, salesSum=2831963106) salesMonthlyTestDto = SalesMonthlyTestDto(yyyymmdd=2018-05, cntSum=121383, salesSum=3034483299)

 

 

끝.