The Ultimate MySQL Bootcamp 15강을 공부하며 정리한 내용입니다.
15강에서는 방대한 양의 데이터를 가지고 회사에서 실제로 동료로부터 받을 수 있는 요청을 sql문으로 작성해 데이터를 조회하는 연습을 해보도록 한다.
🙋💬
"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;🙋💬
"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 dayname,
COUNT(*) AS total
FROM users
GROUP BY dayname
ORDER BY total DESC
LIMIT 1;🙋💬
"We want to target our inactive users with an email campaign. Find the users who have never posted a photo"
SELECT
username
FROM users
LEFT JOIN photos
ON users.id = photos.user_id
WHERE photos.id IS NULL;🙋💬
"We're running a new contest to see who can get the most likes on a single photo. Who won?"
SELECT
username,
photos.id,
count(*) AS total
FROM photos
INNER JOIN likes
ON photos.id = likes.photo_id
INNER JOIN users
ON users.id = photos.user_id
GROUP BY photos.id
ORDER BY total DESC
LIMIT 1; 🙋💬
"Our investors want to know... How many times does the average user post?"
SELECT
(SELECT COUNT(*) FROM photos) / (SELECT COUNT(*) FROM users) AS avg; 🙋💬
"A brand wants to know which hashtags to use in a post. What are the top 5 most commonly used hashtags?"
SELECT
tags.tag_name,
COUNT(*) AS total
FROM tags
INNER JOIN photo_tags
ON tags.id = photo_tags.tag_id
GROUP BY tags.tag_name
ORDER BY total DESC
LIMIT 5;🙋💬
"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 num_likes
FROM users
INNER JOIN likes
ON users.id = likes.user_id
GROUP BY likes.user_id
HAVING num_likes = (SELECT COUNT(*) FROM photos);새로 배운 내용
group by와 having
- having 절은 where 절과 비슷하지만, 그룹을 나타내는 결과 집합의 행에만 적용된다는 점에서 차이가 있다.
- having 절은 group by 뒤에 작성해주어야한다.
- 반면, where 절은 개별 행에 적용된다.
- where 절은 group by 앞에 사용해야하며, 집계함수와는 함께 사용할 수 없다.
- 쿼리에는 where 절과 having 절이 모두 포함될 수 있다.
예를 들어, 다음 문장을 아래와 같이 쿼리문으로 작성할 수 있다.
"titles와 publishers 테이블을 조인하여 캘리포니아 주에 있는 출판사들 중 출판 도서의 평균가격을 구하고, 이때 평균가격이 10달러 이상인 경우로 제한한다."
SELECT
titles.pub_id,
AVG(titles.price)
FROM titles
INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(price) > 10;reference
