친구 수 집계하기
WITH tmp AS (
SELECT user_a_id AS user_id
, user_b_id AS friend_id
FROM edges
UNION ALL
SELECT user_b_id AS user_id
, user_a_id AS friend_id
FROM edges
)
SELECT u.user_id
, COUNT(DISTINCT t.friend_id) AS num_friends
FROM users AS u
LEFT JOIN tmp AS t ON u.user_id = t.user_id
GROUP BY u.user_id
ORDER BY num_friends DESC , user_id
작성 순서
1. edges 테이블에서 1행의 데이터는 하나의 관계를 나타내고 있다. 양방향의 상호관계를 하나의 행으로 나타낸 것.
한 행의 데이터는 하나의 관계 정보를 나타내지만, 동시에 2개의 정보를 갖고 있다고 볼 수도 있다. 예를 들어 edges 테이블이 아래와 같다고 해보자.
1번 행 (jenny - ben)을 통해 우리가 알 수 있는 정보는 (1), (2)와 같다.
(1) 양방향 관계를 나타낸 형태
- jenny와 ben은 서로 친구 관계다.
(2) 관계를 명시적으로 나열하여 한 방향에서 정리한 형태
- jenny는 ben이라는 친구가 1명 있다.
- ben은 jenny라는 친구가 1명 있다.
즉 각 유저가 각각 1명의 친구를 갖고 있다고 해석할 수도 있다. 따라서 각 유저 별로 친구 수를 구하려면? 양방향 친구 관계를 user_id 기준으로 한방향 리스트로 정리해야 한다.
한방향 리스트로 정리하는 방법은? edges 테이블의 두 컬럼 위치를 바꾼 테이블을 만들어 기존 edges 테이블과 합치면 -> 하나의 컬럼을 기준으로 친구 수를 카운트할 수 있다.
2. 모든 사용자의 친구 수를 나타내는 쿼리를 작성해야 하므로, users 테이블과 1번에서 UNION한 테이블(tmp)을 LEFT 조인으로 연결한다.
3. user_id별 친구 수를 카운트 집계한다.
세 명이 서로 친구인 관계 찾기
WITH friends AS (
SELECT user_a_id AS user_id
, user_b_id AS friend_id
FROM edges
UNION
SELECT user_b_id AS user_id
, user_a_id AS friend_id
FROM edges
)
SELECT *
FROM (
SELECT f1.user_id AS user_a_id
, f2.user_id AS user_b_id
, f3.user_id AS user_c_id
FROM friends AS f1
INNER JOIN friends AS f2 ON f1.friend_id = f2.user_id
INNER JOIN friends AS f3 ON f2.friend_id = f3.user_id AND f1.user_id = f3.friend_id
WHERE f1.user_id < f2.user_id
AND f2.user_id < f3.user_id
) AS all_tab
WHERE 3820 IN (user_a_id, user_b_id, user_c_id);
여러 번의 시도 끝에 성공..
느낀점
- 여러 번 JOIN하여 푸는 문제였는데, 생각보다 어렵다.
새로 알게된 것
- WHERE 매칭할 조건 IN (컬럼1, 컬럼2, 컬럼3) => 이렇게도 쓸 수 있구나.
작성 순서
1. edges 테이블은 양방향 친구 관계를 한 행으로 나타내고 있기 때문에, 이를 user_id 기준으로 하여 한방향 리스트로 정리한다. 한방향 리스트로 정리하는 방법은? 위와 동일. edges 테이블의 두 컬럼 위치를 바꾼 테이블을 만들어 기존 edges 테이블과 합치면 -> 하나의 컬럼을 기준으로 각 유저가 보유하고 있는 친구가 정리된다. (user_id - friend_id)
2. 조인을 이용하여 세 명이 서로 친구인 경우만 뽑는다.
(a-b 가 친구이고, b-c가 친구일 때, c-a도 친구라면 세명은 서로 친구)