SQL 라이브 3일차 숙제

20240628 TIL

@SQL 라이브 세션 3일차 숙제

문제1

payment 테이블에서 pay_type이 ‘MONEY’이고 pay_amount가 500,000 이상인 데이터의 개수를 count 해주세요. 결과값은 아래와 같아야합니다.

select count(*)
from payment
where pay_type = 'MONEY' and pay_amount >= 500000

---

문제2

group by 와 having 절을 사용하여 pay_type 별 최소 pay_amount를 구하고, 그 값이 500이상인 경우를 추출해주세요. 결과값은 아래와 같아야합니다. 

select pay_type,
       min(pay_amount) min_pay_amt
from payment
group by pay_type
having min_pay_amt >= 500

1. 조회: pay_type, pay_amount
2. 최소 pay_amount를 구해야 하기 때문에 min 함수 사용
3. pay_type 별 최소 pay_amount이기 때문에 group by 절 사용
4. having절을 사용/최소 pay_amount 값이 500 이상 --> having min_pay_amt >= 500

필터링 구문	필터링 시점
WHERE	GROUP BY 전 데이터 필터링
HAVING	GROUP BY 후 결과값을 가지고 데이터 필터링

 

---

문제3

각 서버(serverno)별로 결제한 사용자의 수를 계산하세요. 서버 번호와 해당 서버에서 결제한 사용자 수를 반환해야 합니다. 사용자 수는 중복 제거한 상태로 계산해주세요. 결과값은 아래와 같아야합니다. (순서 상관X)

select u.serverno,
       count(distinct(u.game_account_id)) users_with_payment
from users u inner join payment p on u.game_account_id=p.game_account_id 
group by 1

 

INNER JOIN을 통한 문제 해결

1. 조회 : u.serverno, u.game_account_id
2. game_account_id로 결합
3. 사용자 수를 구해야 하기 때문에 count 함수 사용
4. 중복 제거를 위한 distinct 함수 사용
5. 서버 별로 결제한 사용자의 수를 계산해야하기 때문에 group by 1 사용

---

문제4

• user 테이블에서 서버번호(serverno)가 2 이상인 데이터와 payment 테이블에서 결제 수단(pay_type)이 CARD 인 경우를 join해 주시고
• 게임계정id(game_account_id)별로 게임캐릭터id(game_actor_id)의 갯수를 중복값없이 세어주시고, actorcnt 으로 컬럼명을 명시해주세요.
• 게임계정id(game_account_id) 별 결제금액(pay_amount) 의 합을 출력해주시고, sumamount 으로 컬럼명을 명시해주세요.
• 최종적으로 actorcnt가 2 이상인 경우만 추출하고, sumamount 의 내림차순으로 정렬해주세요.

<나의 풀이>

select *
from
(
select u.game_account_id,
       count(distinct(u.game_actor_id)) actorcnt,
       sum(p.pay_amount) sumamount
from users u inner join payment p on u.game_account_id=p.game_account_id
where u.serverno >= 2 and p.pay_type = 'CARD'
group by 1
) a
where actorcnt >= 2
order by sumamount desc

 

INNER JOIN, SUBQUERY를 통한 문제 해결

<subquery문>

1. 조회 : u.game_acoount_id, u.game_actor_id, p.pay_amount
2. game_account_id로 inner join 결합
3. 조건 : 서버번호가 2 이상, 페이타입은 카드 -- u.serverno >= 2 and p.pay_type = 'CARD'
4. 게임계정 별로 그룹 지정

1. 조회 : 전체 컬럼
2. 조건 : actorcnt가 2 이상인 경우만 추출 -- where actorcnt >= 2
3. 문제에서 sumamount에 대해 내림차순을 하라고 했기 때문에 --order by sumamount desc

<튜터쌤의 풀이>

SELECT u.game_account_id,
       COUNT(DISTINCT u.game_actor_id) AS actorcnt,
       SUM(p.pay_amount) AS sumamount
FROM users u INNER JOIN payment p ON u.game_account_id = p.game_account_id
WHERE u.serverno >= 2 AND p.pay_type = 'CARD'
GROUP BY u.game_account_id
HAVING actorcnt >= 2
ORDER BY SUMAMOUNT DESC;

 

튜텀쌤의 나처럼 서브쿼리를 사용하지 않고 having 절을 이용하여 풀었다. --> 나머지는 다 똑같은 조건

 

▶ 내 풀이와 튜터쌤의 풀이가 다른 이유는?

• having절을 잘 사용하지 않았다보니 어떻게 할까 고민하다 서브쿼리문 이용

▶이번 문제를 통해 깨닫게 된 점

• 굳이 서브쿼리를 사용하지 않아도 풀린다는 점 -- having절을 잘 알고 있더라면 손쉽게 풀릴 문제
• having 절에 대한 유용성에 대해 확실하게 깨닫게 됨!

---

문제5

• 게임 계정 ID(game_account_id)별로 마지막으로 접속한 날짜와 그 날짜의 상세 로그 정보를 조회하세요. ‘마지막 접속한 날짜’를 의미하는 컬럼의 컬럼명은 *last_login_date* 로 지정해주세요.
• 여기서 마지막 접속 날짜는 가장 최근의 날짜를 의미하며, 사용자의 상세 로그는 logid, ip_addr, date, game_actor_id를 포함해야 합니다.
• 결과는 사용자 구분 없이 최근에 접속한 순서로 정렬되어야 합니다.

<before1>

1. 가장 최근 날짜 지정 --- max(date)
2. 그룹 지정 했음 --- 문제에서 게임 계정 별로 최근 날짜를 구하라고 했기 때문에 group by 1
3. 결과는 구분없이 최신 날짜가 나와야 한다길래 order by 2 desc 로 지정

그 외 나머지 필요한 칼럼들을 데리고 왔지만 에러가 떴다

 

이유를 생각해보니 group by 절을 지정한 것때문에 계속해서 에러가 뜨는 것 같았다. 아마 직계함수를 쓴 것과 일반 칼럼들과 부딪힌 듯

그래서 구글링을 하며 찾아본 결과 

 

<before2>

select game_account_id,
       max(date) last_login_date,
       logid,
       ip_addr,
       date,
       game_actor_id
from users
group by 1, users.logid, users.ip_addr, users.date, users.game_actor_id 
order by 2 desc

 

1. 마지막 접속한 날짜=가장 최근의 날짜이기 때문에 max 함수를 이용하여 최근 날짜 구해줌
2. 게임 계정 별로 최근 날짜를 구해야 하기 때문에 group by 절 사용 그 대신 select 에 넣을 칼럼들을 하나씩 group by 절에 다 넣어줘야 함
3. 나머지는 앞에 푼 문제와 동일하게 작성

 

그래도 왜 이렇게 되는지에 대해서 궁금했고 이렇게 내가 구글링해서 푼 방법이 맞는건지도 확인하고 싶어서 튜터님께 여쭤보러 갔다

 

<튜터쌤의 풀이>

SELECT a.*
FROM users AS a
INNER JOIN (
    SELECT
        game_actor_id,
        MAX(`date`) AS last_login_date
    FROM users
    GROUP BY 1
) AS b ON a.game_actor_id = b.game_actor_id AND a.`date` = b.last_login_date
ORDER BY a.game_actor_id, a.date DESC

 

INNER JOIN 과 SUBQUERY를 통한 해결

<subquery문>

1. 조회 : game_actor_id, max(date) last_login_date
2. game_actor_id로 그룹 지어줌

<inner join>

1. 조회 : 전체 컬럼
2. 처음에 적었던 서브 쿼리와 game_actor_id로 결합
3. date=last_login_date로 걸어주면서 date가 두번 나오지 않고 헷갈리지 않도록 함
4. 순서: game_actor_id는 오름차순 date는 내림차순으로 정렬

<최종 나의 풀이>

select a.game_account_id,
       b.last_login_date,
       a.logid,
       a.ip_addr,
       a.`date`, 
       a.game_actor_id
from users a
inner join
(
select game_account_id,
       max(date) last_login_date
from users
group by 1
) b on a.game_account_id=b.game_account_id
order by 2 desc

 

INNER JOIN 과 SUBQUERY를 통한 해결

<subquery>

1. 조회 : game_account_id, max(date)
2. 가장 최근 날짜를 구해야 하기 때문에 max 함수로 구해줌
3. 게임 계정 별 최근 날짜이기 때문에 group by 1로 지정

<inner join>

1. game_account_id로 결합
2. 조회 : game_account_id, last_login_date, logid, ip_addr, date, game_actor_id
3. 순서: 최근 접속한 순서로대로 정렬되어야 한다고 문제에 나와있기 때문에 order by 2(b.last_login_date)

---

▶ 튜터쌤과 내 풀이가 조금 달라진 이유는?

문제에서 답으로 나와야했던 데이터값이 있었고 전체 컬럼이 아닌 지정이 돼있었기 때문에 조금 달라졌다.

아마 나와야하는 데이터 값이 없었더라면 튜터쌤의 코드가 간결했을 것 같다.

 

▶이번 문제를 통해 새롭게 알게 된 점

• join을 사용할때 같은 테이블 결합도 가능하다는 점
• subquery가 더 편하고 코드가 더 간결해지고 깔끔해진다는 점

---

💡4번과 5번이 튜터쌤과의 풀이가 좀 달라서 오히려 이런 다른 풀이도 있다는 거에 대해 깨닫게 됨(풀이가 다 똑같을 순 없다 ..!)
💡이번 숙제들을 풀면서 내가 어디까지 풀 수 있고 아는지에 대해 객관적으로 판단할 수 있는 시간이었음
💡SQL에 대한 흥미가 더 올라가는 중