3회차 과제

1. 조건1) 서버별, 월별 게임계정id 수를 중복값 없이 추출해주세요. 월은 첫 접속일자를 기준으로 계산해주세요. 월은 yyyy-mm의 형태로 추출해주세요.

힌트: 월을 추출하는 방법→날짜는 string(문자열) 형식으로 저장되어 있으므로, 문자열을 자르는 함수를 사용해주시면 좋겠죠? 😃

SELECT 
  serverno 
  , SUBSTR(first_login_date,1,7) AS m
  , COUNT(DISTINCT game_account_id) AS usercnt
FROM 
  basic.users 
GROUP BY
  serverno
  , m
ORDER BY
  serverno
  , m
;

• Feedback 1:
  → MySQL의 경우 GROUP BY, HAVING, ORDER BY에서 SELECT의 alias를 사용할 수 있다. WHERE절에서는 SELECT의 alias를 사용할 수 없다.(별칭의 인식은 GROUP BY절 이후부터 가능)
  → 다른 RDBMS에서는 위와 같이 적으면 안 돌아갈 수 있으니까 GROUP BY, ORDER BY 적는 거 좀 더 신경쓰기
  튜터님은 아래와 같이 작성하셨음

group by serverno, substr(first_login_date,1,7)

• Feedback 2:
  ORDER BY는 없어도 된다...

• Feedback 3:
  SUBSTR 대신 DATE_FORMAT 써도 됨

SELECT 
  serverno 
  , DATE_FORMAT(first_login_date, '%Y-%m') AS m
  , COUNT(DISTINCT game_account_id) AS usercnt
FROM 
  basic.users 
GROUP BY
  serverno
  , m
;

해설:
• 중복값 없이 데이터를 추출해주는 distinct 함수활용 및 컬럼 형태 변경이 필요한 문제였습니다.
• distinct 함수를 사용하여 계정을 중복값 없이 세어주고, first_login_date 컬럼을 date_format 또는 substr을 활용하여 월로 바꾸어 추출 가능해야 합니다.
• 서버, 월 이라는 기준으로 계정수를 집계해야 하므로, group by 를 활용해야 합니다.

2. 조건1) group by 를 활용하여 첫 접속일자별 게임캐릭터수를 중복값 없이 구하고,
   조건2) having 절을 사용하여 그 값이 10개를 초과하는 경우의 첫 접속일자 및 게임캐릭터id 개수를 추출해주세요.

SELECT 
  first_login_date
  , COUNT(DISTINCT game_actor_id) AS actor_cnt
FROM 
  basic.users 
GROUP BY
  first_login_date 
HAVING 
  actor_cnt > 10
ORDER BY
  first_login_date 
;

• Feedback 1:
  다른 RDBMS에서는 위와 같이 적으면 안 돌아갈 수 있으니까(원칙적으로는 HAVING절도 alias 사용 불가임) 튜터님이 작성하신 것처럼 아래와 같이 적기

HAVING 
  count(distinct game_actor_id)>10

• Feedback 2:
  ORDER BY 없어도 됨

해설:
• 집계함수와 group by를 통한 기준별 데이터 추출, having 활용을 통한 group by 후 데이터 필터링이 필요한 문제였습니다.
• first_login_date를 기준으로 distinct 함수를 사용해 캐릭터 수를 중복값 없이 세어주고, 집계함수 count 를 제외한 컬럼을 group by 에 넣어주어야 합니다.
• group by 의 결과를 바탕으로 캐릭터 수가 10개 초과인 데이터를 having 구문으로 필터링 해야 합니다.

3. 조건1) group by 절을 사용하여 서버별, 유저구분(기존/신규) 게임캐릭터id수를 구해주세요. 중복값을 허용하지 않는 고유한 갯수로 추출해주세요.
   조건2) 기존/신규 기준→ 첫 접속일자가 2024-01-01 보다 작으면(미만) 기존유저, 그렇지 않은 경우 신규유저
   조건3) 또한, 서버별 평균레벨을 함께 추출해주세요.

SELECT 
  serverno 
  , IF(first_login_date < '2024-01-01', '기존유저', '신규유저') AS gb
  , COUNT(DISTINCT game_actor_id) AS actor_cnt
  , AVG(`level`) AS avg_level
FROM 
  basic.users 
GROUP BY
  serverno
  , gb
ORDER BY
  serverno 
;

• Feedback:
  여기도! 원칙적으로는 SQL문 문법 상 GROUP BY, ORDER BY에서 alias 사용 X라고 하니 차라리 숫자로 쓰는 게 나을 듯 & ORDER BY 필요없음

GROUP BY
  1
  , 2

ERROR 1055 (42000) at line 1: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
→ SQL 표준문법을 지키지 않아 생긴 문제, GROUP BY를 이용할 때 COUNT 외의 칼럼을 모두 기입해야 한다.

해설:
• case when 구문을 통해 기준에 따라 다른 결과값을 부여하고, 이를 기반으로 집계함수와 group by 구문의 활용이 필요한 문제였습니다.
• first_login_date이 2024-01-01 미만(더 이전의 날짜)인 경우 기존유저로 설정하고, 아닌 경우 else 구문을 통해서 신규유저로 구분해주시면 됩니다. 문제에서는 기존유저, 신규유저 조건이 두개였으므로 별도로 when 구문을 작성할 필요는 없겠습니다.
• 서버, 유저구분이라는 두개의 기준을 가지고, count 및 avg 두개의 집계함수가 사용되었습니다.
• 기준을 제외한 나머지 컬럼들은 모두 group by 뒤에 작성해야 합니다.

4. 2번 문제를 having 이 아닌 인라인 뷰 subquery 를 사용하여 추출해주세요.
   조건1) group by 를 활용하여 첫 접속일자별 게임캐릭터수를 중복값 없이 구하고
   조건2) 인라인 뷰 서브쿼리를 사용하여 그 값이 10개를 초과하는 경우의 첫 접속일자 및 게임캐릭터id 개수를 추출해주세요.
   힌트: 인라인 뷰 서브쿼리는 from 절 뒤에 위치하여, 마치 하나의 테이블 같은 역할을 했었습니다!

SELECT
  *
FROM (
  SELECT 
    first_login_date
    , COUNT(DISTINCT game_actor_id) AS actor_cnt
  FROM 
    basic.users 
  GROUP BY
    first_login_date 
) AS fld
WHERE 
  fld.actor_cnt > 10
ORDER BY 
  fld.first_login_date
;

• Feedback:
  ORDER BY 안 써도 됨

해설:
• 2번과 동일한 문제이지만, having 이 아닌 서브쿼리 활용이 필요한 문제였습니다.
• 인라인 뷰 서브쿼리는 from 뒤에서 마치 하나의 테이블처럼 그 역할을 수행합니다.
• 쿼리 내 서브쿼리가 여러개 있는경우, 가장 안쪽의 서브쿼리부터 수행합니다.
• first_login_date 를 기준으로 캐릭터 수를 중복값 없이 세는 부분이 먼저 수행됩니다.
• 그 다음 이를 서브쿼리로 감싸 a 로 명명해 주었습니다.
• 서브쿼리 결과 전체를 * (아스테리크)로 불러오되, 조건절 where에서 필터링을 진행해 주었습니다.

5. 조건1) 레벨이 30 이상인 캐릭터를 기준으로, 게임계정 별 캐릭터 수를 중복값 없이 추출해주세요.
   조건2) having 구문을 사용하여 캐릭터 수가 2 이상인 게임계정만 추출해주세요.
   조건3) 인라인 뷰 서브쿼리를 활용하여 캐릭터 수 별 게임계정 개수를 중복값 없이 추출해주세요.

SELECT
  atr.actor_cnt
  , COUNT(atr.game_account_id) AS accnt
FROM (
  SELECT 
    game_account_id 
    , COUNT(DISTINCT game_actor_id) AS actor_cnt
  FROM 
    basic.users 
  WHERE
    `level` >= 30
  GROUP BY
    game_account_id 
  HAVING
    actor_cnt >= 2
  ) AS atr
GROUP BY 
  atr.actor_cnt
ORDER BY
  atr.actor_cnt
;

• Feedback:
  ORDER BY 안 써도 됨
  SELECT에서 count(distinct game_account_id)as accnt라고 안 썼는데 이거 튜터님께 여쭤봤더니 이렇게 답해주셨음
  "서브쿼리에서 group by 를 사용해 주었으므로 해당 구문의 distinct 는 필수가 아닙니다."

해설:
• group by, having, subquery 가 혼합된 형태의 문제였습니다.
• 먼저, subquery가 실행됩니다. 레벨이 30 이상인 캐릭터는 전체 데이터에 대한 기준이므로 whrere 절 뒤에 적어주어야 합니다. (having은 group by 결과에 대한 필터링이므로, 차이점을 이해하셔야 해요.)
• 계정을 기준으로 캐릭터를 중복값없이 추출하고, 캐릭터 수가 2 이상인 게임계정을 추출해야 하므로 group by 결과를 바탕으로 데이터를 필터링 하는 having 절이 사용되어야 합니다.
• 그 다음, 이를 하나의 테이블처럼 저장하는 인라인 뷰 서브쿼리가 사용되었습니다.
• 마지막으로, 계정별 보유한 캐릭터 수에 따른 계정수를 추출해야 하고, count 라는 집계함수를 뺀 actor_cnt 를 group by 뒤에 작성해주어야 합니다.
• 해당 문제는 제가 현업에서 가장 자주 사용했던 쿼리 중 하나입니다.