1. ERD 설계 및 테이블 생성하기
SNS 어플리케이션인 instagram의 정확한 DB 관계도를 표현한 것이 아니다.
하지만, 실제로 DB가 어떤 식으로 이어지는지 그 흐름과 실질적인 데이터 추출을 연습하고자 따라가는 과정이다.
실습에 필요한 데이터는 혹시 모를 누군가를 위해, 구글 드라이브 링크를 걸어둔다.
위 데이터는 Colt Steele이라는 사람의 데이터를 인용한 것이다. 그의 강의나 유튜브를 참고삼아 공부하는 것도 좋다.
역시 저 사람의 문제를 따라가면서 실습을 해보았는데, 의문인 점이 있어서 질문을 직접 보내놨다.
이 부분은 문제를 따라가면서 밑에서 설명한다.
1차적으로 우선 데이터베이스 틀을 작성해야 한다.
ERD를 보고서 직접 작성해보는 것이 제일 바람직하다고 본다.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);유저 정보에 대한 데이터를 저장하는 테이블이다.
핵심 부분만 간단하게 작성하는 테이블이기에 이름과 생성일자만 담아둔다.
유저 이름은 중복을 허용하지 않아 UNIQUE로 선언하였다.
MySQL 기준으로 실제 테이블을 생성하여 서식을 조회해본다면 위와 같다.
CREATE TABLE photos (
id INT AUTO_INCREMENT PRIMARY KEY,
image_url VARCHAR(255) NOT NULL,
user_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id)
);유저가 올리는 사진에 대해서 저장하는 테이블이다.
이미지 정보는 url 문자로 받아들이고, 없어서는 안 되기에 NOT NULL로 선언한다.
이미지만 있고 유저가 없는 경우도 말이 안 된다. 따라서 유저 아이디도 NOT NULL로 선언한다.
또한 유저 아이디는 users 테이블의 외래키로 사용한다.
MySQL 기준으로 실제 테이블을 생성하여 서식을 조회해본다면 위와 같다.
CREATE TABLE comments(
id INT AUTO_INCREMENT PRIMARY KEY,
comment_text VARCHAR(255) NOT NULL,
user_id INT NOT NULL,
photo_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(photo_id) REFERENCES photos(id)
);계정이 올린 메시지를 관리하는 테이블이다.
내용과 메시지를 작성한 유저가 반드시 있어야 하기에 NOT NULL로 선언한다.
사진은 선택 여부로 두어도 되지만 여기서는 NOT NULL로 선언했다.
유저 정보와 사진 정보는 외래키로 사용한다.
MySQL 기준으로 실제 테이블을 생성하여 서식을 조회해본다면 위와 같다.
CREATE TABLE likes (
user_id INT NOT NULL,
photo_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(photo_id) REFERENCES photos(id),
PRIMARY KEY(user_id, photo_id)
);좋아요를 누를 수 있는 정보를 담는 테이블이다.
이때 유저가 특정 사진에 좋아요를 한 번만 누를 수 있어야 하기에, 두 column의 조합을 기본키로 둔다.
MySQL 기준으로 실제 테이블을 생성하여 서식을 조회해본다면 위와 같다.
CREATE TABLE follows (
follower_id INT NOT NULL,
followee_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(follower_id) REFERENCES users(id),
FOREIGN KEY(followee_id) REFERENCES users(id),
PRIMARY KEY(follower_id, followee_id)
);팔로워와 팔로잉을 저장하는 테이블이다.
likes와 마찬가지로 한 유저가 다른 유저를 한 번만 팔로우할 수 있다.
두 column의 조합을 기본키로 설정한다.
MySQL 기준으로 실제 테이블을 생성하여 서식을 조회해본다면 위와 같다.
CREATE TABLE tags (
id INT AUTO_INCREMENT PRIMARY KEY,
tag_name VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);해시태그를 저장하는 테이블이다.
태그 이름은 중복으로 생성하면 안 되기 때문에 UNIQUE로 생성한다.
태그를 생성하는 방법은 다양한 방법이 있기에 검색하는 것을 추천한다.
MySQL 기준으로 실제 테이블을 생성하여 서식을 조회해본다면 위와 같다.
CREATE TABLE photo_tags (
photo_id INT NOT NULL,
tag_id INT NOT NULL,
FOREIGN KEY(photo_id) REFERENCES photos(id),
FOREIGN KEY(tag_id) REFERENCES tags(id),
PRIMARY KEY(photo_id, tag_id)
);사진에 작성한 태그를 저장하는 테이블이다.
한 사진에 다양한 태그를 넣을 수 있지만, 한 태그는 한 번밖에 넣지 못한다.
따라서 두 column의 조합을 기본키로 선언한다.
MySQL 기준으로 실제 테이블을 생성하여 서식을 조회해본다면 위와 같다.
2. 간단한 예제 풀어보기
실생활에서 실제로 있을 수 있는 질문 7가지가 준비되어 있다.
다음 질문에 맞는 쿼리를 작성해보자.
[ Question 1 ]
We want to reward our users who have been around the longest.
Find the 5 oldest users.
[ Question 2 ]
What day of the week do most users register on?
We need to figure out when to schedule an ad campaign.
[ Question 3 ]
We want to target our inactive users with an email comapaign.
Find the users who have never posted a photo.
[ Question 4 ]
We're running a new contest to see who can get the most likes on a single photo.
[ Question 5 ]
Our investors want to know that
How many times does the average user post?
[ Question 6 ]
A brand wants to know which hashtags to use in a post
What are the top 5 most commmonly used hashtags?
[ Question 7 ]
We have a small problem with bots on our site.
Find users who have liked every single photo on the site
3. SQL 작성하기
-- Question 1
-- We want to reward our users who have been around the longest.
-- Find the 5 oldest users.
SELECT * FROM users ORDER BY created_at ASC LIMIT 5;SNS를 오래 사용한 유저에게 감사의 의미로 기념품을 제공하려고 한다.
이때 가장 오래 사용한 5명의 유저를 추출하라는 문제였다.
users 테이블에서 created_at을 오름차순으로 정렬한 뒤 LIMIT 5를 하는 간단한 문제다.
-- Question 2
-- What day of the week do most users register on?
-- We need to figure out when to schedule an ad campaign.
SELECT
DAYNAME(created_at) AS register_on,
COUNT(*) AS how_many
FROM users
GROUP BY register_on
ORDER BY how_many DESC;광고를 띄우려고 하는데 어떤 요일에 띄우는 것이 제일 좋은지 알고 싶다.
따라서 사람들이 가장 많이 회원 가입을 한 요일을 찾으라는 문제였다.
register_on으로 GROUP BY 한 다음, DAYNAME으로 요일을 추출한다.
그 다음 COUNT()로 개수를 센 다음 내림차순으로 정렬하면 된다.
-- Question 3
-- We want to target our inactive users with an email comapaign.
-- Find the users who have never posted a photo.
SELECT username
FROM users
LEFT JOIN photos ON users.id = photos.user_id
WHERE photos.image_url IS NULL;비활성 유저들 때문에 골머리를 썩고 있다. 활동 촉진을 하든, 계정 삭제를 하든 해야 한다.
그들에게 메일을 보내야 해서, 포스팅 이력이 없는 유저를 찾는 문제였다.
photos 테이블과 users 테이블을 JOIN하여 포스팅 이력을 확인한다.
이때 유저의 어떤 정보로든 NULL을 확인하면 된다.
-- Question 4
-- We're running a new contest to see who can get the most likes on a single photo.
SELECT
users.username,
photos.id,
photos.image_url,
COUNT(*) AS how_many_likes
FROM photos
JOIN likes ON likes.photo_id = photos.id
JOIN users ON users.id = photos.user_id
GROUP BY photos.id
ORDER BY how_many_likes DESC
LIMIT 1;가장 많은 좋아요를 받은 사진을 포스팅한 유저에게 기념품을 주려고 한다.
해당 유저 정보를 찾는 문제였다.
JOIN을 2번해야 하는 조금 복잡한 query였다.
photos와 likes, users를 id를 기준으로 INNER JOIN한다.
그리고 photo를 기준으로 GROUP BY하여 개수를 COUNT()한다.
COUNT()를 기준으로 내림차순하고 LIMIT 1하면 된다.
-- Question 5
-- Our investors want to know that
-- How many times does the average user post?
SELECT (SELECT COUNT(*) FROM photos) / (SELECT COUNT(*) FROM users) AS avg;투자자가 SNS를 인수하려고 한다. 그래서 상품 가치를 판단하려고 한다.
그래서 유저당 평균 포스팅 개수는 얼마인지 구하는 문제였다.
Colt steele은 위와 같은 형태로 query를 작성했다.
단순하게 photos의 개수를 users의 개수로 나눈 것이다.
SELECT AVG(user_post) AS avg_user_post
FROM (
SELECT COUNT(*) AS user_post
FROM comments
GROUP BY user_id
) AS subquery;하지만 의문이 들었다. 저건 단순히 유저-게시글의 비율이지 않은가?
포스팅을 한 유저에 한해서 평균을 내야 한다고 생각하여 작성한 query다.
subquery를 이용하여 문제를 해결했다.
user_id를 기준으로 comments를 GROUP BY하여 유저별 포스팅 개수를 알아낸다.
그리고 해당 테이블에서 전체 평균을 구한다.
-- Question 6
-- A brand wants to know which hashtags to use in a post
-- What are the top 5 most commmonly used hashtags?
SELECT
tags.tag_name,
COUNT(*) as how_many_use
FROM photo_tags
LEFT JOIN tags ON tags.id = photo_tags.tag_id
GROUP BY photo_tags.tag_id
ORDER BY how_many_use DESC
LIMIT 5;회사에서 SNS 현황을 조사하려고 한다. 가장 많이 사용한 TOP 5 해시태그를 조사하는 문제였다.
photo_tags와 tags를 LEFT JOIN하여, tag별로 GROUP BY하고 개수를 센 뒤, DESC하는 간단한 문제였다.
WITH RankedTags AS (
SELECT
tags.tag_name,
COUNT(*) AS how_many_use,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS ranking
FROM photo_tags
LEFT JOIN tags ON tags.id = photo_tags.tag_id
GROUP BY tags.tag_name
)
SELECT *
FROM RankedTags
WHERE ranking < 6;그런데 LIMIT 5를 하면 문제가 있다고 생각했다.
이전 window function에서 RANK()와 DENSE_RANK()를 배웠다.
TOP 5의 정의를 조금 더 명확하게 해주어야 했다고 생각했다.
여기서는 5순위(동순위)까지 포함하는 태그를 찾도록 query를 작성했다.
마찬가지로 subquery를 이용했다.
Quesiton 5에서 사용한 subquery 형태와 조금 다른데, subquery 연습을 위해서 조금 다르게 구현해보았다.
우선 photo_tags와 tags를 LEFT JOIN하여 GROUP BY 해준다.0
그 다음 DESNE_RANK()를 이용하여 동순위를 포함한 순위를 계산한다.
이때 window function은 GROUP BY와 HAVING, WHERE 조건이 전부 끝난 뒤 마지막에 실행한다.
따라서 해당 query로 나온 결과를 RankedTags라는 테이블로 두고 다시 조회를 해야 한다.
RankedTags에서 ranking이 6보다 작으면 전체 정보를 출력한다.
-- Question 7
-- We have a small problem with bots on our site.
-- Find users who have liked every single photo on the site
SELECT
username,
COUNT(*) AS total_likes
FROM likes
JOIN users ON users.id = likes.user_id
GROUP BY user_id
HAVING total_likes = (SELECT COUNT(*) FROM photos);
SNS에 봇이 너무 많아서 문제가 생기고 있다.
모든 사진에 좋아요를 누른 유저가 몇 명인지 찾는 문제였다.
오히려 5번 6번 문제보다는 쉬운 문제라고 생각한다.
likes와 users 테이블을 LEFT JOIN하고 user_id로 GROUP BY한다.
그리고 subquery를 사용하여 likes 개수가 사진 개수와 같은 유저를 찾아낸다.
'Language > SQL' 카테고리의 다른 글
| SQL Window functions (0) | 2023.12.10 |
|---|---|
| SQL View, Mode (0) | 2023.10.11 |
| SQL Many-to-Many Relationship (0) | 2023.10.08 |
| SQL One-to-Many Relationship (0) | 2023.10.02 |
| SQL CONSTRAINT, ALTER (0) | 2023.09.26 |
