JOIN 테이블의 평균값 구하고 정렬 해주기

 회사에서 구현 해야 할 문제 메모

• User 는 여러개의 태그를 가지고 있음 (최대 50개)
• Paper 는 여러개의 태그를 가지고 있음 (최대 30개)
• Paper 는 평가 될 수 있음. Paper 에 대해 3 가지 항목을 평가 할 수 있으며 평균값도 컬럼에 저장
• User 가 가지고 있는 태그 중 Paper 도 하나 이상 가지고 있다면 그 Paper 들의 평균점수를 구해야 함
• 평균 점수가 가장 높은 5개의 Paper 순으로 정렬하여 데이터를 넘겨줌 

예를 들어 내가 A, B, C 태그를 가지고 있다. 이 중에서 Paper 가 내가 가지고 있는 태그 중 한 개의 태그라도 가지고 있다면

(어떤 Paper 는 B, F, D, G, H 를 가지고 있을 수 있고 다른 Paper 는 A, B, X, Y 이렇게 가지고 있을 수 있음) 그 Paper 의 평균 점수를 구해야함.

 

• User 와 Tag 의 M:N 관계를 나타낸 UserTag 테이블
• Paper 와 Tag 의 M:N 관계를 나타낸 PaperTag 테이블
• Paper 와 User 의 M:N 관계인 Rates 테이블이 존재 (Paper 를 평가한 데이터들이 존재)

위의 두 테이블을 가지고 내가 가진 태그를 하나라도 포함하고 있는 Paper Id 목록 추출

 

그리고 마지막 아래의 쿼리를 실행. 

SELECT p.id, p.title, p.description, p.publisher, p.uuid, AVG(r.avg) as TotalAvg 
FROM papers p 
RIGHT OUTER JOIN rates r ON r.postId = p.id 
WHERE PaperId IN (:PostIdArr) AND p.ratingCount >= :ratingCount GROUP BY PaperId ORDER BY TotalAvg DESC LIMIT 5`;

 

IN (:PostIdArr) 안에 내가 추출해낸 PaperId 목록을 배열로 넣어주면 됨.