아,.ㅋㅋ 오늘 적을 생각없었는데,
생각보다 과제가 나에게.. 배울점을 많이 주네...?
.
.
문제는 5번, 6번이었다.
문제2 - JOIN 응용1
조건1) users 테이블에서 서버번호가 2 이상인 데이터와 payment 테이블에서 결제방식이 CARD 모두를 만족하는 경우를 알맞은 방식으로 join 해 주세요.
.
조건2) 조인한 결과를 바탕으로 users 테이블의 game_account_id 를 기준으로 game_actor_id수를 중복값없이 세고 컬럼 이름을 actor_cnt로 지정해주세요. 또한 pay_amount 값을 더해주시고, 컬럼 이름을 sumamount로 지정해주세요.
.
조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요. 그리고 sumamount를 기준으로 내림차순 정렬해주세요.
select count(distinct(s.game_actor_id))
as actor_cnt,
sum(s.pay_amount) as sumamount
from (select *
from basic.users u
join basic.payment p
on u.game_account_id=p.game_account_id
where u.serverno >=2
and p.pay_type ='card'
) s대충 이렇게 적어나가고 있었다.
근데 오류가 떴다.
SQL Error [1060] [42S21]: Duplicate column name 'game_account_id'
🙄⁉
이는 테이블에 동일한 칼럼이 존재하는 경우를 말한다고 한다.
angkeum.tistory.com
.
동일한 칼럼? 당연히 동일한 칼럼으로 묶어줘야되는거 아니야?
음... 뭔말인지 이해가 가면서도 안가는.. 근데 일단 이렇게 계속 쓰다가는 조건 2,3을 못풀 것 같다는 느낌이 강하게 듦.
💡💡💡
옛날에 라이브세션을 보고 여러 서브쿼리가 사용된 구조를 적어놨던 노트(왼쪽)인데,
배웠던 내용을 다시 훑어보다 이렇게 u랑 p를 분리시켜야겠다는 생각을 함.
🔻
그리고 써봤다.
select count(distinct(game_actor_id)) actor_cnt
, sum(pay_amount) sumamount
from ( select *
from basic.users
where serverno>=2
) u
join
( select*
from basic.payment p
where pay_type='card'
) p
on u.game_account_id=p.game_account_id
group by u.game_account_id여기까지 오류 없는지 확인; ok
select *
from (select u.game_account_id
,count(distinct(game_actor_id)) actor_cnt
, sum(pay_amount) sumamount
from ( select *
from basic.users
where serverno>=2
) u
join
( select*
from basic.payment p
where pay_type='card'
) p
on u.game_account_id=p.game_account_id
group by u.game_account_id
) s
where actor_cnt >=2
order by sumamount desc
이렇게 작성한 답안을 제출했다✅
문제3 - JOIN 응용2
조건1) user 테이블에서 game_account_id, first_login_date, serverno 를 추출한 결과와
.
조건2) payment 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 그 컬럼이름을 date2로 지정해주세요. 그 다음 inner join 을 진행해주세요. 다만, 첫 접속일자보다 마지막 결제일자가 큰 경우만 추출해주세요.
.
조건3) 조인 결과를 바탕으로 마지막 결제일자-첫 접속일자 를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.
.
조건4) 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주시고, 컬럼이름을avgdiffdate로 설정해주세요. 해당컬럼은 정수 형태로 출력되어야 합니다.
.
조건5) 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요.
.
힌트) 소수점을 반올림해주는 round 함수를 활용해주세요!
❌조건 4로 넘어가서 풀고있는데, serverno에 오류가 떴다
보니까 서브쿼리에서 불러오지를 않았었네..?
다시 섭쿼리 안에 써주고
최종적으로 완성✅
select s.serverno, round(avg(diffdate),0) as avgdiffdate
from(select serverno
,datediff(date_format(p.date2,'%Y-%m-%d'), u.first_login_date)
as diffdate
from (select game_account_id
,first_login_date
,serverno
from basic.users
) u
join
( select Max(approved_at) as date2
,game_account_id
from basic.payment p
group by game_account_id
) p
on u.game_account_id=p.game_account_id
where p.date2 > u.first_login_date
having diffdate >= 10
)s
group by 1
order by 1 desc📌질문사항
마지막문제 조건 5번을 풀 때,
having절을 본쿼리에 작성하면
SQL Error [1054] [42S22]: Unknown column 'diffdate' in 'having clause'라는 오류가 뜬다. 작동순서상 having 다음에 select가 작동하는데 , 왜 이러한 오류가 뜬 것인지 궁금해서 튜터님께 질문을 남겼다. 답변을 받으면 이부분에 대한 답변을 추가해야겠다!
⭐⭐⭐
<25.03.05 수정>
:: having은 group by의 결과를 필터링해준다. 따라서 서버별로 묶고 diffdate>=10으로 필터링을 해주는 것이 아닌,
밖 쿼리에서 작성할 것이라면, where문을 통해서where diffdate >= 10 group by 1 order by 1 desc이렇게 작성해서 전체 데이터셋에 대한 필터링 이후, 서버넘버에 맞춰 그룹화를 진행해주어야 한다.
