아무리 사소하더라도 배움이 없는 날은 없다.
SQL 코드카타 178번
대칭인 조합을 찾는 문제.
대칭의 조건은
- x1 = y2 & x2 = y1
- 이런 조합이 2개 있을 것
예를 들어 x,y가 (22,23), (23,22) 이렇게 배열된 조합이 있으면 이 둘은 대칭이다. 또, (10,10)과 같은 경우도 대칭이지만 반드시 대칭인 조합이 2개 있어야 하므로 (10,10),(10,10) 이렇게 있어야 대칭의 조건에 맞게 된다.
먼저 (10,10)과 같은 간단한 조합의 경우만 쿼리로 뽑아보면 아래와 같다.
SELECT x,
y
FROM functions
WHERE x = y
GROUP BY x,
y
HAVING Count(*) = 2 그리고 (22,23),(23,22)와 같은 형태의 조합을 뽑는 쿼리는
SELECT f1.x,
f1.y
FROM functions f1
INNER JOIN functions f2
ON f1.x = f2.y
AND f1.y = f2.x 이렇게 된다. 이 둘을 모두 뽑는 것이 문제의 요구사항이었기 때문에 union을 써서 두 쿼리를 결합한다.
SELECT x,
y
FROM functions
WHERE x = y
GROUP BY x,
y
HAVING Count(*) = 2
UNION
SELECT f1.x,
f1.y
FROM functions f1
INNER JOIN functions f2
ON f1.x = f2.y
AND f1.y = f2.x마지막으로 x < y인 경우만 출력해야 하므로 where절에 조건을 넣어준 후 order by로 정렬 조건만 맞춰주면 끝.
SELECT x,
y
FROM functions
WHERE x = y
GROUP BY x,
y
HAVING Count(*) = 2
UNION
SELECT f1.x,
f1.y
FROM functions f1
INNER JOIN functions f2
ON f1.x = f2.y
AND f1.y = f2.x
WHERE f1.x < f1.y
ORDER BY 1 SQL 코드카타 179번
CTE를 만들 수 있었으면 좀 더 깔끔하게 풀 수 있을 문제였는데, 해커랭크에서는 MySQL 8.0이상의 버전을 지원하지 않는 건지 with를 쓸 때마다 오류가 계속 났다.
결국 미친듯이 서브서브서브서브쿼리를 써서 해결.
SELECT ct.contest_id,
ct.hacker_id,
ct.name,
Sum(b.total_submissions) total_submissions,
Sum(b.total_accepted_submissions) total_accepted_submissions,
Sum(b.total_views) total_views,
Sum(b.total_unique_views) total_unique_views
FROM contests ct
LEFT JOIN (SELECT cl.contest_id,
Sum(a.total_views) total_views,
Sum(a.total_unique_views) total_unique_views,
Sum(a.total_submissions) total_submissions,
Sum(a.total_accepted_submissions)
total_accepted_submissions
FROM colleges cl
LEFT JOIN (SELECT c.college_id,
Sum(total_views)
total_views,
Sum(total_unique_views)
total_unique_views,
Sum(total_submissions)
total_submissions,
Sum(total_accepted_submissions)
total_accepted_submissions
FROM challenges c
LEFT JOIN (SELECT challenge_id,
Sum(total_views)
AS
total_views,
Sum(
total_unique_views) AS
total_unique_views
FROM view_stats
GROUP BY 1) view_table
ON c.challenge_id =
view_table.challenge_id
LEFT JOIN (SELECT challenge_id,
Sum(
total_submissions)
AS
total_submissions,
Sum(
total_accepted_submissions)
AS
total_accepted_submissions
FROM submission_stats
GROUP BY 1) submissions
ON c.challenge_id =
submissions.challenge_id
GROUP BY 1) a
ON cl.college_id = a.college_id
GROUP BY 1) b
ON ct.contest_id = b.contest_id
GROUP BY 1,
2,
3
HAVING total_submissions
+ total_accepted_submissions + total_views
+ total_unique_views <> 0 이 문제를 풀 때 신경써야 하는 포인트는
- 하나의 college_id가 2개 이상의 challenge_id를 가질 수 있고
- 하나의 challenge_id가 View_Stats 테이블에서 2개 이상의 total_views, total_unique_views를 가질 수 있고
- 하나의 challenge_id가 Submission_Stats 테이블에서 2개 이상의 total_submissions, total_accepted_submissions를 가질 수 있다는 것.
따라서 먼저 View_Stats, Submission_Stats 테이블에서 challenge_id별로 view와 submissions의 합계를 구한 뒤에 계속 인라인뷰 서브쿼리로 처리하면서 Challenge 테이블과 join, Colleges 테이블과 join, Contests 테이블과 join 하면서 올라와야 한다.
문제 해석이나 쿼리의 난이도가 높다기보다는 서브쿼리를 여러 개 중첩해야 하기 때문에 헷갈리거나 빠트리는 게 없는지를 더 신경써야 하는 문제. 쿼리가 무거워서 그런지 답은 맞게 떴는데 제출도 안되더라;;
