아무리 사소하더라도 배움이 없는 날은 없다.
문제 링크
case when과 concat, group_concat을 섞어 쓰는 문제.
난이도는 hard라고 되어있지만 차분히 따라가다 보면 크게 어렵진 않음.
우선 cte를 하나 만들어서
본 쿼리에서 연산할 수 있게 power와 factor를 조합해 준다.
이 때 power를 위에서부터 구분하기는 어려우니 밑에서부터
- power가 0이고 factor가 양수일 때
- power가 0이고 factor가 음수일 때
- power가 1이고 factor가 양수일 때
- power가 1이고 factor가 음수일 때
- power가 2 이상이고 factor가 양수일 때
- power가 2 이상이고 factor가 음수일 때
를 case when으로 구분해 주고,
factor가 양수일 경우는 concat의 인자로 '+' 기호를 넣어준다.
그리고 power도 cte에 함께 넣어주는데,
이건 본 쿼리에서 group_concat을 쓸 때 order by의 기준이 필요하기 때문이다.
cte 단계까지 만들고 실행시킨 결과는 아래와 같다.
SELECT
CASE
WHEN power = 0
AND factor > 0 THEN concat('+',factor)
WHEN power = 0
AND factor < 0 THEN factor
WHEN power = 1
AND factor > 0 THEN concat('+',factor,'X')
WHEN power = 1
AND factor < 0 THEN concat(factor,'X')
WHEN power >=2
AND factor > 0 THEN concat('+',factor,'X^',power)
WHEN power >=2
AND factor < 0 THEN concat(factor,'X^',power)
end AS 'term',
power
FROM terms
이제 cte의 결과로부터 본 쿼리를 작성한다.
먼저 group_concat을 써서 여러 행에 흩어져 있는 값들을 하나로 합친다.
이 때, power가 높은 순에서 낮은 순으로 합쳐져야 하므로
power를 기준으로 내림차순한다는 조건을 추가해야 한다.
이 때문에 위의 cte를 만드는 단계에서 power를 추가해 준 것.
SELECT Group_concat(term ORDER BY power DESC SEPARATOR '')
FROM cte; group_concat으로 값들을 합칠 때
다른 문자가 들어갈 필요 없이 그냥 연결만 되면 되므로,
separator 인자에는 공백''으로 처리하면 된다.
아래는 출력 결과.
이제 저 group_concat의 결과와
방정식의 우변을 concat으로 연결하고
알맞은 컬럼명을 지정해 주면 완성.
WITH cte
AS
(
SELECT
CASE
WHEN power = 0
AND factor > 0 THEN concat('+',factor)
WHEN power = 0
AND factor < 0 THEN factor
WHEN power = 1
AND factor > 0 THEN concat('+',factor,'X')
WHEN power = 1
AND factor < 0 THEN concat(factor,'X')
WHEN power >=2
AND factor > 0 THEN concat('+',factor,'X^',power)
WHEN power >=2
AND factor < 0 THEN concat(factor,'X^',power)
end AS 'term',
power
FROM terms)
SELECT concat(group_concat(term ORDER BY power DESC SEPARATOR ''),'=0') AS "equation"
FROM cte;
문제 링크
왜 이렇게 헷갈렸던 거지 이 문제는;;
공통된 친구의 숫자를 찾아야 하는데,
먼저 CTE를 만들어서 user1_id 기준으로 모든 친구의 조합을 구한다.
아래는 CTE와 실행결과.
WITH all_friendship
AS (SELECT user1_id,
user2_id
FROM friendship
UNION
SELECT user2_id,
user1_id
FROM friendship
ORDER BY 1,
2)
이제 모든 친구 조합이 다 나왔으니
all_friendship CTE를 셀프 조인해서
공통 친구의 조합으로 간주되는 것들을 구한다.
문제에서 주어진 대로 common_friend가 3 이상이라는 조건도 추가.
WITH all_friendship
AS (SELECT user1_id,
user2_id
FROM friendship
UNION
SELECT user2_id,
user1_id
FROM friendship
ORDER BY 1,
2)
SELECT a1.user1_id AS "user1_id",
a2.user1_id AS "user2_id",
Count(DISTINCT a1.user2_id) AS "common_friend"
FROM all_friendship a1
JOIN all_friendship a2
ON a1.user2_id = a2.user2_id
WHERE a1.user1_id < a2.user1_id
GROUP BY 1,
2
HAVING common_friend >= 3 이 때 join의 조건으로
- 공통된 친구가 있을 것 (a1.user2_id = a2.user2_id)
- a2.user_id가 a1.user_id보다 클 것
이렇게 두 가지를 주어야 한다.
특히 2번 조건의 경우 단순히 a1.user1_id <> a2.user1_id 이렇게만 하면
1,3 조합 뿐 아니라 3,1 조합까지 모두 뽑히게 되는데,
어차피 같은 조합인데 계산만 헷갈릴 수 있기 때문.
(아니면 least, greatest 함수를 쓰는 등 뒷처리를 또 한번 해줘야 한다)
이 쿼리까지 실행하고 나면 결과가 이렇게 나온다.
나는 여기까지만 구해서 답을 못 냈었는데,
이 뒤에 과정이 하나 더 추가되어야 한다.
위의 이미지 사례에서 1과 12의 공통 친구가 3명이라는 결과가 나왔는데,
실제로도 1과 12가 친구인지 확인이 필요한 것.
그도 그럴 것이 위에서 CTE를 구하면서 내려올 때는
실제로 친구인지 여부까진 확인하지 않았었기 때문이다.
따라서, 위의 쿼리까지도 CTE로 한 번 더 묶어준 뒤
본 쿼리를 아래와 같이 작성해 주면 완성이다.
WITH all_friendship
AS (SELECT user1_id,
user2_id
FROM friendship
UNION
SELECT user2_id,
user1_id
FROM friendship
ORDER BY 1,
2),
result
AS (SELECT a1.user1_id AS "user1_id",
a2.user1_id AS "user2_id",
Count(DISTINCT a1.user2_id) AS "common_friend"
FROM all_friendship a1
JOIN all_friendship a2
ON a1.user2_id = a2.user2_id
WHERE a1.user1_id < a2.user1_id
GROUP BY 1,
2
HAVING common_friend >= 3)
SELECT r.user1_id,
r.user2_id,
r.common_friend
FROM result r
INNER JOIN friendship f
ON r.user1_id = f.user1_id
AND r.user2_id = f.user2_id;CTE를 두 개 만들었으면 간단하게 해결됐을 텐데
하나만 만들어서 해결하려다 보니 더 답이 안 나왔던 것 같다.
