앱을 개발하면서 이런 기능이 필요했다.

일정 시간마다 평점을 취합하여 평균을 계산하고 업데이트한다.

이 기능을 구현하기 위해서 총 2가지의 시도를 했다.

1. 모든 평점을 조회하는 SELECT 문 작성 후 쿼리의 결과 값을 Javascript로 계산 후 다시 UPDATE 쿼리 요청 - DB의 역할은 거의 없고 Javascript로 알고리즘을 작성해야함
2. MariaDB에서 모든 연산과 함께 SELECT 요청 후 Javascript로 결과 값을 SQL로 만들어서 UPDATE 쿼리 요청 - DB에서 모든 역할을 담당하고 Javascript는 역할이 거의 없다

처음에는 1번으로 시도 했으나 너무 어려워서 MariaDB의 기능들을 찾아봤다. 그리고 GROUP BY 를 알았고 적극 활용하여 비교적 쉽게 기능을 구현할 수 있었다.

로직

일단 테이블을 살펴보자

내가 처음 설계한 로직은 이렇다.

1. 각 store에 등록된 평점을 모두 조회한다.
   1-1. (각 Row는 store_vote_grade 의 question1, question2, question3 을 포함한다.)
   1-2. (결과로 리턴받는 Row는 store_id 와 member_id 가 함께 중복 될 수 없다. 각각 하나씩은 중복가능)
2. 쿼리의 결과를 각 store_id 별로 계산하여 JSON 형태로 가공한다. (여기서 실패)
   2-1. 각 Row들을 순회하며 store_id를 기준으로 question1, question2, question3 을 모두 더한다.
   2-2. 각 question들의 평균을 구해서 question1_average, question2_average, question3_average 에 업데이트한다.
   2-3. 각 question들의 average들의 평균을 구하여 전체의 평균인 question_average 에 업데이트한다.
3. 가공한 JSON을 SQL과 매핑하여 업데이트한다.

여기서 2번이 너무 어려웠다. 그래서 알아본 Group By를 활용하여 설계한 로직은 이렇다.

1. 각 store에 등록된 평점을 모두 조회한다. 단, 이미 평균이 모두 구해진 채로
2. 쿼리의 결과를 SQL과 매핑하여 업데이트한다.

이게 끝이다. 젠장.. 아는 것이 힘이다.. 이제 새로 설계한 로직을 어떻게 구현 했는지 살펴보자

평균이 구해진 채로 평점 조회하기

다짜고짜 쿼리를 살펴보자

SELECT 
  si.store_id, 
  FORMAT(avg(so.question1), 1) AS question1,
  FORMAT(avg(so.question2), 1) AS question2, 
  FORMAT(avg(so.question3), 1) AS question3, 
  FORMAT((so.question1 + so.question2 + so.question3)/3, 1) AS average
FROM store_information si  
LEFT JOIN store_vote_grade so ON si.store_id = so.store_id  AND is_available = 1 
WHERE so.question1 IS NOT NULL 
GROUP BY si.store_id

맨 아래 GROUP BY를 살펴보면 기준이 될 컬럼을 지정한다. 나는 store_id 를 기준으로 question 들의 점수를 취합하고 싶었으니 당연히 GROUP BY si.store_id 로 작성했다. 그리고 avg(soquestion1) 함수를 사용해서 평균을 구했다. 이외에도 sum(), count() 등 내장 함수가 굉장히 많다.

이후 해당 결과값들을 매핑하여 업데이트 쿼리만 날려주면 끝이다. 여러 행을 업데이트 하는 것은 생각보다는 쉬운 일이 아니였다. INSERT INTO 처럼 , 를 기준으로 쭉 나열하면 되는 줄 알았는데 그게 아니였다.

여러 행을 한번에 처리하는 UPDATE문

검색을 해보니 방법은 여러가지가 있었다. 언제나 그렇듯이

1. 반복문 돌리기 - 탈락! 왜냐면 쿼리를 여러번 날려야하기 때문이다.
   이런 기능을 구현하는 이유는 서버의 부하를 줄이기 위함인데 기능을 구현하는 목적과 맞지 않았다.
2. UPSERT 문 작성하기 - 탈락!
   MariaDB의 UPSERT는 먼저 INSERT를 시도하고 UPDATE를 하는 방식이다. INSERT를 시도할 때 NOT NULL 로 설정되어 있는 Column들의 값을 추가로 매핑해줘야만 했다.
3. CASE 문 작성하기 - 탈락!
   너무 어려워서 탈락;;; 나중에 공부해보겠슴둥;;;
4. JOIN과 UNION ALL 활용하기 - 합격!
   적당히 어렵고 적당히 Javascript로 SQL을 만들기 쉬워보였다.

또 다짜고짜 코드를 보자

let array = []
let i = 0;
let sql = "UPDATE store_information si JOIN ("

for (let row in score) {
  let id = score[row]["store_id"]
  let question1 = score[row]["question1"]
  let question2 = score[row]["question2"]
  let question3 = score[row]["question3"]
  let average = score[row]["average"]
  
  if (i === 0) {
    array.push("SELECT " + id + " as store_id, " 
               + question1 + " as question1_average, " 
               + question2 + " as question2_average, " 
               + question3 + " as question3_average, " 
               + average + " as average")
  } else {
    array.push("SELECT " + id + ", " 
               + question1 + ", " 
               + question2 + ", " 
               + question3 + ", " 
               + average)
  }
  
  ++i;
}

sql += array.join(" UNION ALL ")
sql += ") vals ON si.store_id = vals.store_id \
SET si.question1_average = vals.question1_average, \
si.question2_average = vals.question2_average, \
si.question3_average = vals.question3_average, \
si.vote_grade_average = vals.average"
let [result] = await pool.execute(sql)

위에서 살펴본 GROUP BY를 활용한 SELECT문의 결과값이 score 에 담겨있다. 미천한 코딩 실력으로 결과값을 토대로 쿼리를 생성하는 코드이다. 생성되는 쿼리는 아래와 같다.

UPDATE store_information si 
JOIN (
  SELECT 
    1 as store_id, 
    1.3 as question1_average, 
    2.3 as question2_average, 
    3.3 as question3_average, 
    2.2 as average 
      UNION ALL 
    SELECT 2, 3.6, 2.8, 2.5, 3.5 
      UNION ALL 
    SELECT 3, 2.5, 1.0, 4.5, 2.7 
      UNION ALL 
    SELECT 4, 2.8, 2.2, 4.0, 3.3 
      UNION ALL 
    ...
) vals ON si.store_id = vals.store_id 
  SET 
    si.question1_average = vals.question1_average, 
    si.question2_average = vals.question2_average, 
    si.question3_average = vals.question3_average, 
    si.vote_grade_average = vals.average

현기증이 나지만 순서대로 살펴보자

1. 계산을 마친 결과값을 SELECT문에 매핑하여 ROW를 생성한다.
2. 생성된 ROW들을 UNION 연산자를 활용해 하나의 결과로 합친다
3. store_information 쿼리와 JOIN을 하여 원하는 store_id 를 일치시킨다.
   (vals ON si.store_id = vals.store_id 부분)
4. SET 부분과 같이 UPDATE할 컬럼을 매핑한다.

아래 링크의 방법을 그대로 배꼈다. 그래도 경험치가 늘었으니 만족한다.

참고문서

여러가지 방법 참고 문서
여러가지 방법 참고 문서2