1번.
조건1) 알맞은 join 방식을 사용하여 users 테이블을 기준으로, payment 테이블을 조인해주세요.
조건2) case when 구문을 사용하여 결제를 한 유저와 결제를 하지 않은 게임계정을 구분해주시고, 컬럼이름을 gb로 지정해주세요.
조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다.
SELECT
gb
, count(gb) AS 'usercnt'
FROM (SELECT
CASE
WHEN p.game_account_id IS NULL THEN '결제안함'
WHEN p.game_account_id IS NOT NULL THEN '결제함'
END AS 'gb'
FROM
(SELECT
DISTINCT GAME_ACCOUNT_ID
FROM
basic.users
) u
LEFT JOIN
(SELECT
DISTINCT game_account_id
FROM
basic.payment
) p
ON u.GAME_ACCOUNT_ID = p.GAME_ACCOUNT_ID
) aa
GROUP BY
gb
ORDER BY
gb
;데이터를 먼저 확인해라
중복값이 존재하는가
처음에는 FROM에 users와 payment 테이블을 그대로 넣었으나 결과값이 다르게 나왔다
생각해보니 users나 payment나 ID가 unique하다는 설명은 없었다
확인해보니 count가 2 이상인 값들이 다수 존재했다
당장 payment만 생각해봐도, 한 사람이 여러건 결제할 수 있는 거니까
2번.
조건1) users 테이블에서 서버번호가 2 이상인 데이터와 payment 테이블에서 결제방식이 CARD 모두를 만족하는 경우를 알맞은 방식으로 join 해 주세요. payment 테이블의 매출 금액이 중복되는 것을 방지하기 위해 모든 값을 고유하게 추출해야 합니다.
조건2) 조인한 결과를 바탕으로 users 테이블의 game_account_id 를 기준으로 game_actor_id수를 중복값없이 세고 컬럼 이름을 actor_cnt로 지정해주세요. 또한 pay_amount 값을 더해주시고, 컬럼 이름을 sumamount로 지정해주세요.
조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요. 그리고 sumamount를 기준으로 내림차순 정렬해주세요.
결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.
일단 문제를 봐서는 INNER JOIN인 것으로 보인다
조건 1과 2를 고려하면 users에서는 game_account_id, game_actor_id가 필요하고, payment에서는 game_account_id, pay_amount가 필요하다
조건 2까지 작성한 쿼리를 전체 묶어서 인라인뷰로 넣고, where actor_cnt >= 2로 추출하면 끝
SELECT
*
FROM (
SELECT
u.game_account_id
, count(DISTINCT u.game_actor_id) AS 'actor_cnt'
, sum(p.pay_amount) AS 'sumamount'
FROM
(
SELECT
game_account_id
, game_actor_id
FROM basic.users
WHERE serverno >= 2
) u
INNER JOIN
(SELECT
game_account_id
, pay_amount
FROM basic.PAYMENT
WHERE pay_type = 'card'
) p
ON u.game_account_id = p.game_account_id
GROUP BY
u.game_account_id
) aa
WHERE
actor_cnt >= 2
ORDER BY
sumamount desc
;disticnt를 여러 컬럼에 동시에 적용
• 매출 금액이 중복되는 것을 방지하기 위해, 아래와 같은 구문이 사용되었습니다.
distinct game_account_id, pay_amount, approved_at ← 이는 distinct 뒤의 세 컬럼을 하나의 덩어리로 인식하고, 이 하나의 덩어리를 고유하게 추출해줍니다. 실제로 데이터의 중복 적재가 일어나는 경우를 방지하기 위해 많이 사용됩니다. (고객은 1번 결제했는데 똑같은 데이터가 2번 기록되면, 매출이 2배로 잘못 찍힘을 방지)
SELECT
distinct game_account_id
, pay_amount
, approved_at
FROM basic.PAYMENT
WHERE pay_type = 'card'
;
SELECT
distinct game_account_id, pay_amount, approved_at
FROM basic.PAYMENT
WHERE pay_type = 'card'
;- 위 문제의 데이터는 중복값이 없게 나와서 distinct를 안 써도 괜찮았지만, 실제로 현업에서는 데이터 중복이 빈번하게 발생한다 -> 특히 매출 같은 거! 반드시 distinct 사용해서 1개만 보도록 해야 한다!!!
3번.
조건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일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.
조건1은 그냥 추출하면 되고
조건2에서는 max(approved_at) as 'date2'로 추출
INNER JOIN 메인쿼리에서 조건문 WHERE first_login_date < date2까지
SELECT
u.game_account_id
, u.serverno
, (date_format(date2, '%Y-%m-%d') - date_format(u.first_login_date, '%Y-%m-%d')) AS 'diffdate'
FROM
(SELECT
game_account_id
, first_login_date
, serverno
FROM
basic.USERS
) u
INNER JOIN
(SELECT
game_account_id
, max(approved_at) AS 'date2'
FROM
basic.PAYMENT
GROUP BY
GAME_ACCOUNT_ID
) p ON u.game_account_id = p.game_account_id
WHERE
u.first_login_date < p.date2문제가 생겼다
Truncated incorrect DOUBLE value 에러가 발생
검색해보니 데이터 타입이 불일치하는 경우 나타나는 에러라고 한다
max(approved_at)는 에러가 나지 않았고,
date_format(max(approved_at), '%Y-%m-%d')로 해보아도 문제는 없었다
이것저것 확인해보니 문제는 (date_format(date2, '%Y-%m-%d') - date_format(u.first_login_date, '%Y-%m-%d')) AS 'diffdate' 이놈이었다.
일부러 양쪽 다 date_format() 함수를 써서 타입을 맞춰줬다고 생각했는데...
각각을 select할 때는 아무런 문제가 없는데, diffdate 칼럼을 구하려고 할 때마다 문제가 생긴다
string 간의 차를 구해서 그런가?
그래서 그냥 연산자를 사용하지 않고 datediff() 함수를 사용하기로 했다
SELECT
u.game_account_id
, u.serverno
, datediff(date_format(date2, '%Y-%m-%d'), date_format(u.first_login_date, '%Y-%m-%d')) AS 'diffdate'
FROM
(SELECT
game_account_id
, first_login_date
, serverno
FROM
basic.USERS
) u
INNER JOIN
(SELECT
game_account_id
, max(approved_at) AS 'date2'
FROM
basic.PAYMENT
GROUP BY
GAME_ACCOUNT_ID
) p ON u.game_account_id = p.game_account_id
WHERE
u.first_login_date < p.date2조건4에서 avgdiffdate를 구하는 것까진 수월했고, 대신 '정수 형태'라는 게 소수점만 없으면 되는걸까 싶어서 일단 round() 함수를 사용했다
diffdate 10이상, serverno 내림차순까지 진행했고, 최종 결과는 아래와 같다
SELECT
serverno
, round(avg(diffdate),0) AS 'avgdiffdate'
FROM (
SELECT
u.game_account_id
, u.serverno
, datediff(date_format(date2, '%Y-%m-%d'), date_format(u.first_login_date, '%Y-%m-%d')) AS 'diffdate'
FROM
(SELECT
game_account_id
, first_login_date
, serverno
FROM
basic.USERS
) u
INNER JOIN
(SELECT
game_account_id
#계정별로 1개씩만 찍히니까 distinct를 한 것 같은 효과가 있다
, max(approved_at) AS 'date2'
FROM
basic.PAYMENT
GROUP BY
GAME_ACCOUNT_ID
) p ON u.game_account_id = p.game_account_id
WHERE
date_format(u.first_login_date, '%Y-%m-%d') < date_format(date2, '%Y-%m-%d')
) aa
WHERE
diffdate >= 10
GROUP BY
serverno
ORDER BY
serverno desc
;