5.2Simple Queries With Views

View의 필요성

가장 많이 태그된 유저를 찾는 쿼리를 만들어보자.

테이블은 users(id, username), caption_tags(id, user_id), photo_tags(id, user_id)가 있다.

이 경우, 캡션 태그 1개와 사진 태그 1개가 동일한 태그로 집계해야 하기 때문에, caption_tags와 photo_tags를 새로 tags라는 테이블로 합친 다음에 쿼리를 작성하면 된다.

sql
SELECT username, COUNT(*)
FROM users
JOIN (
  SELECT user_id FROM photo_tags
  UNION ALL
  SELECT user_id FROM caption_tags
) AS tags ON tags.user_id = users.id
GROUP BY users.username
ORDER BY COUNT(*) DESC;

결과는 잘 나오는 쿼리지만, 이와 비슷한 기능을 만들다보면 의문이 생긴다.

caption_tags와 photo_tags는 어차피 같은 태그인데, 애초에 하나의 tags 테이블로 디자인했어야 했나?

즉, 비슷한 기능을 만들때면 매번 두 테이블을 UNION하는 절차를 거쳐야 한다.

해결책 1: 두 테이블을 합치고, 기존 테이블들을 없앤다.

새로 tags 테이블을 만들고 기존 데이터들을 모두 복사한 후, 기존 테이블들을 없앤다.

하지만 이 방식은 문제가 있다.

• photo_tags의 id와 caption_tags의 id가 겹칠 수 있으므로(특히 SERIAL로 만든 경우) id를 새로 만들어야 한다.
• 이 테이블들과 관련된 다른 코드들이나 쿼리들을 모두 수정해야 한다.

해결책 2: View를 만든다.

View는 다른 테이블들을 합친 일종의 가짜 테이블이다. 지난 포스팅의 기본 CTE와 비슷하다고 할 수 있지만, 기본 CTE가 반드시 실행하고자 하는 쿼리 위에 있어야하는 것과 달리 View는 만들어두면 언제든 쓸 수 있다.

sql
CREATE VIEW tags AS (
  SELECT id, created_at, user_id, post_id, 'photo_tag' AS type FROM photo_tags
  UNION ALL
  SELECT id, created_at, user_id, post_id, 'caption_tag' AS type FROM caption_tags
);

이렇게 VIEW를 만들고 나면, 별개 테이블을 쓰는 것처럼 쿼리를 작성할 수 있다.

sql
SELECT username, COUNT(*)
FROM tags
GROUP BY users.username
ORDER BY COUNT(*) DESC;

View를 써야할 때

가장 최신 포스팅 10개를 보여줘야하는 쿼리를 작성한다고 생각해보자.

팔로우 하는 유저들 중 가장 최근 10개 포스팅을 만든 사람들을 뽑거나, 가장 최근 10개 포스팅에서 쓰인 해시태그들만 뽑는다거나 하는 등 쓰일 경우가 많다. 이럴 때, 매번 posts 테이블에서 ORDER BY created_at DESC LIMIT 10을 쓰기보단, 아예 VIEW를 만들고 거기서 데이터를 조회하면 된다.

sql
CREATE VIEW recent_posts AS (
  SELECT *
  FROM posts
  ORDER BY created_at DESC
  LIMIT 10
);

이렇게 VIEW를 만들면, 쿼리를 아래와 같은 방식으로 작성할 수 있다.

sql
SELECT username
FROM recent_posts
JOIN users ON users.id = recent_posts.user_id;

View를 변경하거나 지울 때

CREATE OR REPLACE VIEW 뷰명 AS로 변경 가능하다.

sql
CREATE OR REPLACE VIEW recent_posts AS (
  SELECT *
  FROM posts
  ORDER BY created_at DESC
  LIMIT 15
);

DROP VIEW 뷰명로 삭제 가능하다.

sql
DROP VIEW recent_posts;

PGAdmin에서도 해당 데이터베이스의 Schemas안에 Views 항목에서도 확인 가능하다.