TIL 2024-09-13 ( SQL_걷기반 6~7 )

6) 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!

lol_users table

• lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요. 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요 (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)

SELECT *, RANK() OVER(PARTITION BY region ORDER BY rating DESC) ranking
FROM lol_users

각 유저의 레이팅을 순위로 계산하는 쿼리.

순위면 RANK() OVER() 함수를 써야하는데 기본꼴은 SQL 5주차 수업에 자세히 적혀있다.

문제에서 순위를 매길 컬럼과 순위 기준 컬럼을 모두 알려주어 문법에 맞게 작성하면 된다.

 

order by는 항상 오름차순(낮은 수에서 높은 수)이 기본이기에 DESC를 붙여 내림차순으로 만들어주고 함수 컬럼명을 따로 지정해주지 않으면 너무 길기 때문에 'ranking' 이라는 별명을 새로 지어주었다.

 

 

 

 

 

 

• lol_users 테이블에서 가장 늦게 게임을 시작한(join_data) 유저의 이름(name)을 선택하는 쿼리를 작성해주세요.

SELECT name 
FROM lol_users 
ORDER BY join_date DESC 
limit 1

게임을 가장 늦게 시작한 유저의 이름을 선택하는 쿼리이기에

join_date가 가장 높은 날짜의 유저 이름을 가져오면 된다.

order by로 date를 내림차순 정렬하고 limit를 사용한다.

• limit : 불러올 데이터 수를 정해준다.

limit 1을 하면 맨 위에 데이터를 가져와준다.

 

 

 

 

 

 

• lol_users 테이블에서 지역별로 레이팅(rating)이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요.

SELECT * 
FROM lol_users
ORDER BY region ,rating DESC

지역별로 나눠야 하기 때문에 order by에 region을 넣어준다.

• why? order by는 정렬을 할 때 종복되는 데이터 값이 있으면 붙여서 정렬해준다.
• 즉. 1, 2, 4, 1, 3, 2, 3 ,5 데이터를 1, 1, 2, 2, 3, 3, 4, 5 이런 식으로 정렬한다.

여기서 rating이 내림차순으로 정렬만 되면 두 조건이 모두 맞춰진다.

 

 

 

 

 

 

 

• lol_users 테이블에서 지역별로 평균 레이팅(rating)을 계산하는 쿼리를 작성해주세요.

SELECT region , AVG(rating) avg_rating
FROM lol_users
GROUP BY region

이번엔 지역별로 평균 레이팅 이기에 조회했을 때 보기 편하게 지역과 그 지역의 평균값만 나오게 컬럼을 적어준다.

지역별 평균이기에 GROUP BY로 지역을 묶어주기만 하면 된다. 

 

 

 

 

---

 

 

 

 

7) 랭크게임 하다가 싸워서 피드백 남겼어요… 

lol_feedbacks table

 

• lol_feedbacks 테이블에서 만족도 점수에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요.

SELECT *
FROM lol_feedbacks
ORDER BY satisfaction_score DESC

만족도 점수도 컬럼에 이미 존재하며 내림차순이기에 그저 order by ... DESC 를 사용해주었다.

 

 

 

 

• lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요.

SELECT user_name, MAX(feedback_date)
FROM lol_feedbacks
GROUP BY user_name

각 유저별 이기에 group by로 각 user_name으로 나눠주고 각 user_name의 최신 피드백.

즉 가장 높은 날짜를 출력해주면 된다.

 

 

 

 

 

• lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요.

SELECT satisfaction_score, COUNT(1) cnt 
FROM  lol_feedbacks
WHERE satisfaction_score = 5

만족도 점수가 5점인 피드백의 수만 계산하기위해 조건문(where)을 사용해주고 COUNT() 를 이용해서 데이터의 갯수를 카운트만 해주면 된다.

 

 

 

 

 

• lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요.

SELECT user_name, COUNT(1) cnt_name 
FROM  lol_feedbacks
GROUP BY user_name
ORDER BY cnt_name DESC
LIMIT 3

이 문제에서 많은 시간을 사용했는데 이유는 조금 뒤에 말하겠다.

나는 결과창에 유저 이름과 피드백 갯수를 보고 싶기에 user_name 과 cnt_name(유저 이름의 갯수를 세준다)

 

중복되는 유저 이름을 찾고 싶기에 group by를 사용해주고 cnt_name을 기준으로 내림차순 정렬을 해준다.

 

상위 3명이기에 LIMIT 3을 이용해서 위에 데이터 3개만 뽑아온다.

 

하지만 나는 여기서 만약 상위 3명이 아니라 3위이면 어떻게 될까?

이름 갯수가 3, 2, 1, 1 라면 둘 다 표시되야 하는 게 맞지 않을까 하는 생각으로

상위 3명이 아닌 3등까지 표시되도록 쿼리를 작성해보았다.

 

SELECT user_name, ranking, cnt_name
FROM
(
SELECT user_name, cnt_name ,RANK() OVER(order by cnt_name DESC) ranking
FROM
(
SELECT user_name , COUNT(1) cnt_name 
FROM food_orders 
GROUP BY user_name
) a
) b
WHERE ranking <= 3
order by 2

이러면 공동 순위가 있어도 순위가 3등 안에 드는 사람을 모두 나타낸다.

 

 

• lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요.

SELECT feedback_date, avg(satisfaction_score) avg
FROM lol_feedbacks 
GROUP BY feedback_date
ORDER BY avg DESC
LIMIT 1

평균 만족도 점수가 가장 높은 날짜 이기에 

날짜별로 평균 만족도 점수를 구하고 평균점수로 내림차순 만든 다음 맨 위에 데이터만 보이도록 하면 평균 만족도 점수가 제일 높은 날짜만 나타낼 수 있다.

 

당연히 만족도가 높은 날짜가 보여야 하므로 feedback_date 컬럼을 넣어주고 중복되는 날이 점수를 평균 내야하기에 group by로 묶어주었다.

 

마지막으로 order by ... desc로 내림차순으로 만든다음 LIMIT 1을 하면 평균 점수가 제일 높은 날짜만 나타난다.