[TIL] SQL 세션 풀이

<문제 1>

- 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 의 내림차순으로 정렬해주세요

 

<문제 분석>

1. user 테이블에서 서버번호(serverno) >= 2          
2. payment 테이블에서 결제 수단(pay_type) =  CARD    
3. user 테이블과 payment 테이블 join        
4. game_account_id별 game_actor_id를 중복값없이 count, column명 actorcnt지정    
5. game_account_id별 pay_amount를 sum, column명 sumamount 지정      
6. actorcnt >= 2 추출               => HAVING
7. sumamount 의 내림차순으로 정렬               =>ORDER BY

<답안>

1. 두 테이블을 join 시켜주고 serverno와 pay_type 조건에 맞게 where에 적어줌.

2. game_account_id별 데이터를 가져오므로 group by 로 묶어줌.

3. 추출할 변수를 모두 select에 적어주며 actorcnt와 sumamount를 지정해줌.

4. actorcnt가 생성된 후 조건을 걸어주므로 having에 작성함.

5. order by에 sumamount 를 내림차순으로 정렬함.

SELECT u.game_account_id, COUNT(DISTINCT u.game_actor_id) AS actorcnt, SUM(p.pay_amount) AS sumamount
FROM basic.users u INNER JOIN basic.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;

문제 1 답안

<오답1>

- SELECT COUNT()에 distinct 넣어주어 중복값을 없애 주어야함.

select u.game_account_id, count(u.game_actor_id) actorcnt, sum(p.pay_amount) sumamount #3. actorcnt와 sumamount 설정 및 group by로 game_account_id 
from basic.users u left join basic.payment p on u.game_account_id = p.game_account_id  #1. 조인
where u.serverno  >= 2 
	  and p.pay_type = 'card' #2. 조인조건
group by u.game_account_id
having sumamount >= 2        #4. actorcnt가 2 이상인 경우만 추출
order by sumamount desc;     #5. sumamount 의 내림차순으로 정렬

문제 1 오답 1 결과

<풀이: 서브쿼리 이용>

 1. user 테이블에서 서버번호(`serverno`)가 2 이상인 데이터와 payment 테이블에서 결제 수단(`pay_type`)이 CARD 인 경우를 join 하여 서브쿼리로 만들어 줌
 2. actorcnt: 게임계정id(`game_account_id`)별로 게임캐릭터id(`game_actor_id`)의 갯수를 중복값없이 새기, sumamount: 게임계정id(`game_account_id`) 별 결제금액(`pay_amount`) 의 합을 출력, group by로 game_account_id 설정
3. actorcnt가 2 이상인 경우만 추출하고, sumamount 의 내림차순으로 정렬

#해당문제에서는 left join과 inner join 모두 사용해도 상관없는 데이터임

select game_account_id, count(distinct game_actor_id) actorcnt, sum(pay_amount) sumamount
from(Select u.game_account_id, u.serverno , u.game_actor_id, p.pay_type, p.pay_amount
	from basic.users u left join basic.payment p on u.game_account_id = p.game_account_id 
	where u.serverno >= 2 and p.pay_type in ('card')
	) a 
group by game_account_id
having actorcnt >=2 
order by sumamount desc;

문제 1 서브쿼리 이용 풀이 결과

---

<문제 2>

- 게임 계정 ID(`game_account_id`)별로 마지막으로 접속한 날짜와 그 날짜의 상세 로그 정보를 조회하세요. ‘마지막 접속한 날짜’를 의미하는 컬럼의 컬럼명은 `*last_login_date`* 로 지정해주세요.

- 여기서 마지막 접속 날짜는 가장 최근의 날짜를 의미하며, 사용자의 상세 로그는 `logid`, `ip_addr`, `date`, `game_actor_id`를 포함해야 합니다.

- 결과는 사용자 구분 없이 최근에 접속한 순서로 정렬되어야 합니다.

<문제 분석>

• 게임 계정 ID(`game_account_id`)별로 마지막으로 접속한 날짜
• ‘마지막 접속한 날짜’ 컬럼명은 `*last_login_date`*
•  `logid`, `ip_addr`, `date`, `game_actor_id`를 가져오기
• 사용자 구분 없이 최근에 접속한 순서로 정렬

<답안>

1. 게임계정별 날짜가 최대인 값을 먼저 서브쿼리로 만들어줌 이때 game_account_id와 date변수만 사용됨

2. game_account_id와 date를 모두 같은 값을 찾아서 join 시켜줌

3. 추출 조건에 따라 가져와야하는 변수들과, 순서를 정렬함

SELECT u.game_account_id, u.last_login_date, l.logid, l.ip_addr, l.date, l.game_actor_id
FROM (SELECT game_account_id,
	  MAX(date) AS last_login_date
      FROM USERS
      GROUP BY game_account_id
      ) u
      JOIN USERS l ON u.game_account_id = l.game_account_id AND u.last_login_date = l.date
ORDER BY u.last_login_date DESC;

문제 2 답안

 

<오답1>

WHERE 조건때문에 전체 날짜 중 날짜가 최대인 값을 가진 레코드만 불러와서 그에 해당하는 레코드만 추출됨 

select game_account_id, `date` last_login_date, `logid`, `ip_addr`, game_actor_id
from basic.users u
where `date` = (select max(`date`) from basic.users u);

문제 2 오답 1 결과

<오답2>

GROUP BY를 사용하여 game_account_id GROUP 별 MAX('DATE')까지만 조회되고, 사용자의 상세 로그(`logid`, `ip_addr`, `date`, `game_actor_id`)와 함께 조회하면 오류가 뜸. 

select game_account_id, MAX(`date`) last_login_date, `logid`, `ip_addr`, game_actor_id
from basic.users u 
group by game_account_id;

 

따라서 game_account_id 와 logid, ip_addr, date, game_actor_id를 모두 그룹으로 묶어서 조회하었다. 각 레코드가 개별 개체로 지정되어 game_account_id별 값이 아닌 game_account_id, logid, ip_addr, date, game_actor_id 중 하나라도 다른 모든 값이 불러와짐. 

select distinct game_account_id, MAX(`date`) last_login_date, `logid`, `ip_addr`,`date`, game_actor_id
from basic.users u 
group by 1,3,4,5,6
order by last_login_date desc;

문제 2 오답 2 결과

 

<오답3>

1. 필요한 데이터를 가져와서 game_account_id별 날짜순으로 순위를 매김. 날짜는 오름차순일 경우 과거데이터가 ranking의 1위 이므로 desc붙여준다. 

2. 서브쿼리로 작성 후, 필요한 데이터와 각 game_account_id 별 최근 날짜의 값을 추출한다(ranking =1)

3. 정렬한다. 

date를 last_login_date로 사용하고 또 date를 참조하기 때문에 데이터베이스에서 혼돈할 수 있음. 그래서 date(date)로 묶어주면 찾고자하는 값을 찾을 수 있음

select game_account_id, `date` last_login_date, logid, `ip_addr`, `date`, game_actor_id
from (select game_account_id, `date`, logid, `ip_addr`, game_actor_id, 
	  rank() over(partition by game_account_id order by `date` desc) ranking
	  from basic.users u
	  ) a 
where ranking = 1
order by last_login_date desc;

문제2 오답3 결과

<풀이: rank함수 이용>

select game_account_id, `date` last_login_date, logid, `ip_addr`, date(`date`), game_actor_id
from (select game_account_id, `date`, logid, `ip_addr`, game_actor_id, 
	  rank() over(partition by game_account_id order by `date` desc) ranking
	  from basic.users u
	  ) a 
where ranking = 1
order by last_login_date desc;

문제 2 rank함수 이용 결과