Querydsl 통계 작업(미완료)

 

요구사항 

 

1. 연령별 통계 

2. 최초 진입 (날짜 검색 없을시) 30일전 ~ 현재 날짜 통계

 

날짜 테이블을 100년치 생성 후 member 테이블에 있는 가입날짜와  조인 하여 통계가 0인 날짜도 대응하였다.

---

1. 최초 진입 30일전 ~ 현재 날짜

 

검색조건 class

 

 

currentDate : 현재 날짜

oneMonthAgoDate : 30일 전 날짜 

sdate :  30일 전 날짜 

edate :   현재 날짜 

 

 

이렇게 사용하니 코드가 훨씬 간결해졌다

여기서 만약 요구사항이 바뀌면 변수 만 수정하면 된다

 

---

 

쿼리

# 봉사자 연령별 통계
SELECT calender_tb.calendar_date AS '날짜',
       SUM(CASE
               WHEN YEAR(NOW()) - YEAR(STR_TO_DATE(IFNULL(t2.birth, '000000'), '%y%m%d')) <= 49 THEN 1
               ELSE 0
           END)                  AS '49세 이하',
       SUM(CASE
               WHEN YEAR(NOW()) - YEAR(STR_TO_DATE(IFNULL(t2.birth, '000000'), '%y%m%d')) BETWEEN 50 AND 54 THEN 1
               ELSE 0
           END)                  AS '50~54세',
       SUM(CASE
               WHEN YEAR(NOW()) - YEAR(STR_TO_DATE(IFNULL(t2.birth, '000000'), '%y%m%d')) BETWEEN 55 AND 59 THEN 1
               ELSE 0
           END)                  AS '55~59세',
       SUM(CASE
               WHEN YEAR(NOW()) - YEAR(STR_TO_DATE(IFNULL(t2.birth, '000000'), '%y%m%d')) BETWEEN 60 AND 64 THEN 1
               ELSE 0
           END)                  AS '60~64세',
       SUM(CASE
               WHEN YEAR(NOW()) - YEAR(STR_TO_DATE(IFNULL(t2.birth, '000000'), '%y%m%d')) BETWEEN 65 AND 69 THEN 1
               ELSE 0
           END)                  AS '65~69세',
       SUM(CASE
               WHEN YEAR(NOW()) - YEAR(STR_TO_DATE(IFNULL(t2.birth, '000000'), '%y%m%d')) BETWEEN 70 AND 74 THEN 1
               ELSE 0
           END)                  AS '70~74세',
       SUM(CASE
               WHEN YEAR(NOW()) - YEAR(STR_TO_DATE(IFNULL(t2.birth, '000000'), '%y%m%d')) BETWEEN 75 AND 79 THEN 1
               ELSE 0
           END)                  AS '75~79세',
       SUM(CASE
               WHEN YEAR(NOW()) - YEAR(STR_TO_DATE(IFNULL(t2.birth, '000000'), '%y%m%d')) >= 80 THEN 1
               ELSE 0
           END)                  AS '80세 이상'
FROM calender_tb
         LEFT JOIN member_tb t2
                   ON DATE_FORMAT(t2.reg_dt, '%Y-%m-%d') = calender_tb.calendar_date
WHERE calender_tb.calendar_date BETWEEN '2024-02-01' AND '2024-03-28'
GROUP BY 날짜
ORDER BY 날짜;

---

impl

 

참고 https://kdhyo98.tistory.com/20#google_vignette 

 

https://jjunn93.com/entry/QueryDSL-DATEFORMAT-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0

@Override
public List<StatisticsMemberAgeDto> statisticsMemberAge(StatisticsCondition condition) {

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

    StringTemplate birthYear = Expressions.stringTemplate("YEAR(STR_TO_DATE(IFNULL({0}, '000000'), '%y%m%d'))", member.birth);
    System.out.println("birthYear = " + birthYear);
    
    NumberExpression<Integer> age = Expressions.numberTemplate(Integer.class,
            "YEAR(CURRENT_DATE()) - {0}", birthYear);

    System.out.println("age = " + age);

    return queryFactory.select(Projections.fields(StatisticsMemberAgeDto.class,
                    calendar.calendarDate,
                    Expressions.numberTemplate(Integer.class,
                    "CASE WHEN {0} <= 49 THEN 1 ELSE 0 END", age).sum().as("ageUnder49Count"),
            Expressions.numberTemplate(Integer.class,
                    "CASE WHEN {0} BETWEEN 50 AND 54 THEN 1 ELSE 0 END" , age).sum().as("ageUnder54Count"),
                    Expressions.numberTemplate(Integer.class,
                            "CASE WHEN {0} BETWEEN 55 AND 59 THEN 1 ELSE 0 END" , age).sum().as("ageUnder59Count"),
                    Expressions.numberTemplate(Integer.class,
                            "CASE WHEN {0} BETWEEN 60 AND 64 THEN 1 ELSE 0 END" , age).sum().as("ageUnder64Count"),
                    Expressions.numberTemplate(Integer.class,
                            "CASE WHEN {0} BETWEEN 65 AND 69 THEN 1 ELSE 0 END" , age).sum().as("ageUnder69Count"),
                    Expressions.numberTemplate(Integer.class,
                            "CASE WHEN {0} BETWEEN 70 AND 74 THEN 1 ELSE 0 END" , age).sum().as("ageUnder74Count"),
                    Expressions.numberTemplate(Integer.class,
                            "CASE WHEN {0} BETWEEN 75 AND 79 THEN 1 ELSE 0 END" , age).sum().as("ageUnder79Count"),
                    Expressions.numberTemplate(Integer.class,
                            "CASE WHEN {0} >= 80  THEN 1 ELSE 0 END" , age).sum().as("ageOver80Count"),
                    Expressions.numberTemplate(Integer.class,
                            "CASE WHEN {0} >= 0  THEN 1 ELSE 0 END" , age).sum().as("totalCount")

            ))
            .from(calendar)
            .leftJoin(member).on(formattedDate.eq(calendar.calendarDate.stringValue()))
            .where(
                    condStatisticsDateBetween(condition)
            )
            .groupBy(calendar.calendarDate)
            .orderBy(calendar.calendarDate.asc())
            .fetch();

}