스파르타 내일배움캠프_본캠프_data11기 김선영_TIL_Day 3

<오늘의 학습>

• SQL 라이브 세션 2회차
• SQL 라이브 세션: 과제
• SQL 사전캠프 퀘스트 나머지

<학습내용 정리>

1. SQL 라이브 세션 2회차

• limit n : 데이터를 불러오는 행 수를 제한
  항상 쿼리 맨 마지막에 위치
• select, from 등 구문시작 문법 적을 때 가능하면 줄 나눠서 적기
• DB 안에 Table을 가져올 때는 => DB.Table

select logid as id,
       device as dv,
       country as ct
from basic.game_logs
limit 15;

• 날짜값은 문자열 (yyyy-mm-dd) : '-'를 포함하고 있기 때문에
  단, 문자열 안에 숫자를 포함하고 있기 때문에 연산 가능.
  sql에서 저 포맷을 날짜값이라고 자동으로 인식
• where 절에 and와 or 모두 있을 시, and가 먼저 해석됨.

// 괄호 없는 버전
SELECT action_type, country
FROM basic.game_logs
WHERE action_type='login'
OR action_type='purchase'
AND country='KR';
-- action_type='login'
-- action_type='purchase' and country='KR'
-- and를 기준으로 앞뒤 조건이 우선 고려됨.

//괄호 있는 버전
SELECT action_type, country
FROM basic.game_logs
WHERE (action_type='login'OR action_type='purchase')
AND country='KR';
-- action_type='login' OR action_type='purchase'
-- country='KR'

• IN(조건1, 조건2)
  and X / or의 개념으로 사용됨.
  ex. login 이거나 logout인 행

SELECT logid, country, device, action_type
FROM basic.game_logs
WHERE country IN ('KR','JP') --'KR'이거나 'JP'인 데이터
LIMIT 10;

• Like '찾을 패턴'
  - %: 글자 0개 이상 / 길이 제한 X
  - _: 글자 1개 / 정확히 한 글자 (다섯 글자를 찾을 때는 '_____')
• NULL <> 0, 빈 문자열
  : 값이 없음 / 미정
  - where 절에 "is null / in not null" 형태로 사용함.
  - 💡정렬 시 NULL이 어떻게 배치되는지는 (ex. 오름차순: 맨 아래 / 내림차순: 맨 위), 정렬 결과를 보고 꼭 확인하는 습관이 좋습니다.
      **null값이라서 특정 위치로 정렬된 것을 의미 있는 데이터로 착각하지 않기!
• 모든 집계 함수는 Null 제외하고 계산 (*Count(*) 제외)
  - ex. SUM(score) → 100 + 80 + 60 = 240 (NULL 제외)
          AVG(score) → (100 + 80 + 60) / 3 = 80 (NULL 제외, 3명 기준)
  - Null을 0으로 처리?
     AVG(ifnull(score, 0)) → (100 + 80 + 0 + 60) / 4 = 60
    IFNULL(컬럼명, '대체할 값')
• order by
  - 정렬기준으로 설정하는 컬럼이 여러 개일 경우, 컬럼 모두에 정렬기준(asc, desc) 설정.
  - 앞에 온 컬럼의 정렬순위가 가장 높음.

SELECT logid, log_date, game_account_id, level, exp
FROM basic.game_logs
ORDER BY level DESC, exp asc -- level은 내림차순 / 같은 level 안에서 exp가 올림차순
LIMIT 10;

• SELECT에서 만든 컬럼 별칭(예: level_group)은 ORDER BY에서는 쓸 수 있지만, WHERE에서는 쓸 수 없습니다.
  왜냐면 WHERE가 실행될 때는 그 별칭 컬럼 값이 아직 확정되지 않았기 때문이에요.

<쿼리 실행 순서>
FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

---

2. SQL 라이브 세션
# 데일리 과제

문제1)

login 이벤트 중에서 country = 'KR' 인 데이터의 logid, log_date, game_account_id, device, referrer 를 출력하고, logid 오름차순 정렬해서 20개만 보여주세요.

select logid,
       log_date,
       game_account_id,
       device,
       referrer
from basic.game_logs
where action_type = 'login' and country='KR'
order by logid asc
limit 20;

 

문제2)

item_gain 중 item_qty >= 10 인 로그를 찾고, logid, log_date, game_account_id, item_name, item_qty 를 출력하세요. 정렬은 item_qty DESC, log_date DESC 후 상위 30개를 보여주세요.

select logid,
       log_date,
       game_account_id,
       item_name,
       item_qty
from basic.game_logs
where action_type='item_gain'
      and item_qty>=10
order by item_qty desc, log_date desc
limit 30;

 

문제3)

`action_type` 의 `purchase` 이벤트 중 `country IN ('KR', 'JP')` 인 데이터를 대상으로 아래 컬럼을 출력하세요.

• `logid, game_account_id, country, item_name, item_qty`
• `referrer`가 `NULL`이면 `'unknown'`, 아니면 원래 `referrer` 값을 쓰는 `referrer_group` 컬럼을 **CASE WHEN**으로 만들어 함께 출력
• 정렬: `log_date DESC`, `logid DESC` / `LIMIT 50`

select logid,
       game_account_id,
       country, item_name,
       item_qty,
       case when referrer is null then 'unknown'
            else referrer
       end as referrer_group
from basic.game_logs
where action_type='purchase'
      and country in ('KR', 'JP')
order by log_date DESC, logid DESC
LIMIT 50;

 

문제4)

“파티 플레이 로그”만 뽑고 싶습니다. party_id IS NOT NULL 인 행 중에서, action_type IN ('quest_complete', 'item_gain') 인 데이터만 출력하세요.

• 컬럼: logid, log_date, game_account_id, action_type, party_id, party_members
• 정렬: party_members DESC, logid ASC / LIMIT 30

select logid, log_date, game_account_id, action_type, party_id, party_members
from basic.game_logs
where party_id is not NULL
      and action_type in ('quest_complete', 'item_gain')
order by party_members desc, logid asc
LIMIT 30;

 

# 데일리 퀴즈

## Q1. SELECT가 주로 선택하는 것은?
A. 행(row)
B. 열(column)
C. 테이블
D. DBMS

 

## Q2 NULL 비교로 올바른 것은?
A. `col = NULL`  
B. `col != NULL`  
C. `col IS NULL`  
D. `col <> NULL`

 

## Q3. 다음 중 일반적으로 우선순위가 높은 것은? (MySQL 기준)
A. OR  
B. AND

## Q4. `LIKE '_____'` 는 무엇을 의미하나요?
A. 5글자 문자열과 매칭  
B. 5글자 이상  
C. `_` 문자 포함  
D. 공백 포함

 

Q5. 별칭(alias) AS로 만든 컬럼은 WHERE에서 사용할 수 있다 (O / X)

---

3. SQL 사전캠프 퀘스트

//21.lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)
//📌오답
select *
from lol_users
order by region asc, rating desc
//💡해설
select id,
       name,
       region,
       rating,
       rank() over(order by rating desc) as lol_ranking --rank() over(partition by(선택) order by(필수))
from lol_users;

//22.lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요!
//정답
select name,
       join_date
from lol_users
where join_date is not null
order by join_date desc
limit 1;

//23.lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!
//정답
select *
from lol_users
order by region, rating desc;

//24.lol_users 테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!
//정답
select region,
       avg(rating) as avg_rating
from lol_users
group by region;

//25. lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!
--정답
select id, user_name, satisfaction_score, feedback_date
from lol_feedbacks
order by satisfaction_score desc;

//26.lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!
--정답
select user_name,
       max(feedback_date) as latest_feedback_date
from lol_feedbacks
group by user_name;

//27.lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!
--정답
select count(*) as feedback_cnt
from lol_feedbacks
where satisfaction_score=5;

//28.lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!
--📌오답
select user_name,
       count(user_name) as cnt_fb
from lol_feedbacks
order by count(user_name) desc
limit 3;
--💡해설
select user_name,
       count(user_name) as cnt_fb
from lol_feedbacks
group by user_name --분류 기준 꼭 적기
order by count(user_name) desc --order by cnt_fb과 같이 별칭 사용 가능
--order by가 select 보다 뒤에 실행되기 때문에 select의 별칭을 불러올 수 있음.
limit 3;

//29.lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!
--정답
select feedback_date,
       avg(satisfaction_score) as avg_scor
from lol_feedbacks
group by feedback_date
order by avg_score desc
limit 1;

<오답노트>
21. 랭킹 집계 함수
     rank() over(partition by 순위를 나눠서 매길 기준(선택) order by 순위 기준(필수))
     - 동점 허용 / 같은 순위로 판단
     - partition by : 해당 기준마다 순위 리셋 (ex.각 국가 내에서 경쟁할 때)