SQL :: 점수 합계, 등급 산정하기, PERCENTRANK, SUM

1. 배경

각 유저마다의 체력 등급을 평가하기 위해 체력요인점수를 합하여 높은 순부터 1등급 ~ 9등급을 부여해야 하는 기능을 구현해야했다.

2. 요구사항

2.1. 각 체력요인 점수는 종목 점수 중 제일 높은 것을 선택
2.2. 1 ~ 9등급 비율은 아래의 표를 참고하여 설정

2.3. 각 유저의 5가지 체력요인 점수를 합하여 비교
2.4. prisma 기본 쿼리로는 구현이 복잡해서 Raw Query를 사용해서 구현
2.5. 연도별 조회 기능도 필요하기에 year에 따라 연도별로 계산

3. 해결 & 코드

      select
        t3.*,
      -- 비율마다 1 ~ 9등급 부여
      CASE WHEN t3."totalPercentile" >= 0.96 THEN '1'
          WHEN t3."totalPercentile" >= 0.89 THEN '2'
          WHEN t3."totalPercentile" >= 0.77 THEN '3'
          WHEN t3."totalPercentile" >= 0.60 THEN '4'
          WHEN t3."totalPercentile" >= 0.40 THEN '5'
          WHEN t3."totalPercentile" >= 0.23 THEN '6'
          WHEN t3."totalPercentile" >= 0.11 THEN '7'
          WHEN t3."totalPercentile" >= 0.04 THEN '8'
          ELSE '9'
      END AS "totalGrade"
      from
        (select
          t2.*,
          -- PERCENT_RANK()를 사용하여 비율 구하기
          PERCENT_RANK() OVER (ORDER BY t2."totalScore") as "totalPercentile"
        from
          (select
            t1."userId",
            -- 비율 계산에 필요한 각 유저의 5가지 체력 요인 점수 합계
            cast (sum(t1."max_score") as integer) "totalScore"
          from
            (select
              u."userId" "userId",
              u."factor" "factor",
              max(u.score) "max_score"
            from
              "UserEventScore" u
            where
              -- year에 따라 연도별 조회
              extract(year from u."updateDate") = ${year}
            group by
              u."userId",
              u."factor") t1
        group by
          t1."userId") t2
        ) t3
      where
        t3."userId" = ${userId}

4. Next To Do

4.1. 파일 다운로드, 업로드 기능
4.2. 마이페이지 변경 기능들
4.3. 종목 평균 점수 계산 API 개발
4.4. 체력 진단 기능 API 개발