Spring - MySQL과 Querydsl 통계 쿼리 처리(group by, Expressions)

관련 내용

• Querydsl 이해와 사용법

Querydsl 이해와 사용법

• Querydsl JOIN((INNER, LEFT, RIGHT, THETA, FETCH) 사용법

Querydsl 의 JOIN (INNER, LEFT, RIGHT, THETA, FETCH) 사용법

개요 목적

이번 시간에는 Mysql과 Querydsl을 사용한 통계 쿼리 처리 방법에 대해서 알아본다.

통계 처리할 작업은 두 가지이다.

• 하루 단위 가게 전체 매출 구하기
  • 배달 서비스에 등록된 모든 음식점이 하루 판매 금액을 알 수 있다.
• 하루 단위 가게 별(company_member_id) 매출 구하기
  • 각 음식점이 오늘 판매한 금액을 알 수 있다.

먼저 MySQL 쿼리를 직접 작성하는 방식으로 통계 처리 하는 법을 알아보고,

해당 동작을 Querydsl은 어떻게 구현하는 지 알아보겠다.

테스트에 사용할 DB 테이블, Entity 파악하기

order_detail 테이블은 orders id를 외래키로 가진다.

두 엔티티 연관 관계는 다대일 양방향 매핑이다. 외래키 관리는 다쪽인 orderDetailEntity에서 한다.

하루 단위 전체 가게 매출 통계 내기

MySQL 쿼리 사용하기

select date_format(a.registration_date, '%Y%m%d') as 'date',
       sum(ad.food_price*ad.food_amount) as sum
from orders as a
         join order_detail as ad
              on a.id = ad.order_id
group by  date_format(a.registration_date, '%Y%m%d')
order by a.registration_date desc;

먼저 orders와 order_detail 테이블을 inner join으로 통합된 데이터를 가져온다.

해당 데이터를 어떤 단위로 분할할지 group by 설정을 한다.

group by date_format(a.registration_date, '%Y%m%d')설정으로 일 단위로 데이터를 분할 한다.

월 단위로 그룹화 하고 싶을 때는, group by date_format(a.registration_date, '%Y%m)을 사용하면 된다.

마지막 select 절 안에, 해당 날짜와, 음식 총 가격을 담아 원하는 정보를 가져온다.

<출력 결과>

원하는 date 형식으로 그룹화 출력된 것을 확인할 수 있다.

Querydsl로 변환하기

이번에는 해당 SQL 쿼리를 Querydsl 기술로 표현해보자.

@Repository
@RequiredArgsConstructor
public class OrderStatisticRepository {

    private final JPAQueryFactory queryFactory;

    public List<FoodPriceSumDto> companyAllOfDay() {
        return queryFactory
            .select(new QFoodPriceSumDto(
                Expressions.stringTemplate("DATE_FORMAT({0},{1})", orderEntity.registrationDate,
                    "%Y%m%d"),
                orderDetailEntity.foodPrice.multiply(orderDetailEntity.foodAmount).sum()))
            .from(orderDetailEntity)
            .join(orderDetailEntity.orderEntity, orderEntity)
            .groupBy(Expressions.stringTemplate(
                "DATE_FORMAT({0},{1})", orderEntity.registrationDate, "%Y%m%d"))
            .orderBy(orderEntity.registrationDate.desc())
            .fetch();
    }
}

Querydsl Expressions를 사용하여 SQL에서 지원하는 function을 호출할 수 있다.

하루 단위 가게 별 매출 구하기

MySQL 쿼리 사용하기

이번에는 전체 가게가 아니라, 가게 별 하루 매출 전체를 파악한다.

해당 쿼리를 사용하여 가게 별 매출 차이도 분석할 수 있다.

select ad.company_member_id,
       date_format(a.registration_date, '%Y%m%d') as 'date',
       sum(ad.food_price*ad.food_amount) as sum
from orders as a
    join order_detail as ad
        on a.id = ad.order_id
group by ad.company_member_id, 
         date_format(a.registration_date, '%Y%m%d')
order by ad.company_member_id asc, 
         'date' desc

이번에는 date 형식과 member_id 별로 두 개의 그룹화를 진행했다.

모든 음식점의 하루 단위 매출을 확인할 수 있다.

<출력 결과>

멘토의 조언

→ 데이터를 가져올 땐 DB에 쿼리 보내는 수를 최대한 줄여야 한다.

그래서 memberId를 지정해서 하루 단위 매출을 가져오는 것이 아니라,

모든 memberId를 포함하는 데이터를 가져오는 것이 맞다.

Querydsl로 변환하기

@Repository
@RequiredArgsConstructor
public class OrderStatisticRepository {

    private final JPAQueryFactory queryFactory;

    public List<FoodPriceSumDto> companyMemberOfDay() {
        return  queryFactory
            .select(new QFoodPriceSumDto(
                orderDetailEntity.companyMemberEntity.id,
                Expressions.stringTemplate("DATE_FORMAT({0},{1})", orderEntity.registrationDate,
                    "%Y%m%d"),
                orderDetailEntity.foodPrice.multiply(orderDetailEntity.foodAmount).sum()
            ))
            .from(orderDetailEntity)
            .join(orderDetailEntity.orderEntity, orderEntity)
            .groupBy(
                orderDetailEntity.companyMemberEntity.id,
                Expressions.stringTemplate(
                    "DATE_FORMAT({0},{1})", orderEntity.registrationDate, "%Y%m%d"))
            .orderBy(
                orderDetailEntity.companyMemberEntity.id.asc(),
                orderEntity.registrationDate.desc())
            .fetch();
    }
}