친구 수 집계하기 : solvesql

edge가 친구의 관계를 나타낼 때, node의 위치가 다른 관계에서 값이 유일할 수도 중복될 수도 있다는 점을 고려해야 할 것 같다.

SELECT  *
FROM    edges e1 INNER JOIN edges e2
        ON e1.user_b_id = e2.user_a_id AND e1.user_a_id = e2.user_b_id
LIMIT   10

쿼리 결과 = 0, 이를 통해서 a,b의 관계가 바뀌어도 중복되는 행은 없음을 알 수 있다.
a에만 있는 행이 있을수도 있고, b에만 있는 행이 있을 수도 있다.

UNION ALL을 한 테이블에는 GROUP BY를 바로 활용할 수 없다. GROUP BY는 개별 SELECT 내부에서 작동하는 절이다.

WITH CTE AS (
  SELECT  user_a_id as id1, user_b_id as id2
  FROM    edges

  UNION ALL

  SELECT  user_b_id as id1, user_a_id as id1
  FROM    edges)

SELECT  id1 as user_id, COUNT(*) as num_friends
FROM    CTE
GROUP BY  
        id1
ORDER BY
        num_friends DESC, id1 ASC

레코드가 부족하다. 왜 그럴까 생각해보니까, 친구가 없는 행들이 있을 것 같다.

SELECT COUNT(user_id) FROM users

쿼리를 작성해보니 사용자 수는 총 4039명인데 반해, 내가 작성한 쿼리의 총 수는 3959명이다. 여기에서 문제가 생겼다. UNION ALL을 통해 컬럼의 순서를 다 바꾼 후 결합을 시키면 친구가 없는 사용자도 결국에는 다 출력이 될 줄 알았는데, 여기가 잘못됐다.

answer

WITH CTE AS (
  SELECT  user_a_id as id1, user_b_id as id2
  FROM    edges

  UNION ALL

  SELECT  user_b_id as id1, user_a_id as id1
  FROM    edges)

SELECT  u.user_id as user_id, COUNT(c.id1) as num_friends
FROM    users u LEFT JOIN CTE c
        ON  u.user_id = c.id1
GROUP BY  
        user_id
ORDER BY
        num_friends DESC, user_id ASC

여기에서 COUNT(*)를 하지 않고 COUNT(c.id1)을 한 이유는, count(*)로 친구 수를 세게 되면 친구 수가 NULL 값인 행도 행이 존재한다는 이유로 친구 수를 1로 세게 된다. 따라서, 컬럼에 대하여 count를 해야 NULL 값인 행은 세지 않고 친구 수가 0이라고 나타나게 된다.