[25.12.04] 내배캠 D+33

최다빈·2025년 12월 4일
sql자가레벨T

SQL

목록 보기
2/2

🚀 SQL 자가 레벨 테스트

오늘은 SQL 문제 풀이를 진행하며 겪었던 시행착오와 정답 쿼리를 분석하고, 그 과정에서 배운 핵심 개념들을 정리해보았다.

또한, 논리적 흐흠과 데이터 타입의 특성,
고급 함수 활용에 대해 깊이 있게 다뤄보고자 한다.

📚 Part 1. 데이터 가공과 고급 조회

날짜 데이터 포맷팅, 복잡한 문자열 기반의 시간 데이터 연산, 그리고 윈도우 함수를 이용한 랭킹 시스템 구현을 다뤘다.

📌 1번 문제: 날짜 데이터의 형식 변환

  • 주제: Date Function (DATE_FORMAT)
  • 문제 지문 (예측): games 테이블에서 2020 시즌(SEASON = 2020)의 경기 중 홈 팀 득점(PTS_home)이 110점 이상인 경기를 조회하라. 단, 경기 날짜(GAME_DATE_EST)는 'YYYY-MM-DD' 형식으로 출력하고, GAME_ID와 PTS_home도 함께 출력해야 한다.
  • 내가 쓴 쿼리:
    SELECT GAME_ID, 
       date_format(GAME_DATE_EST, '%Y-%m-%d') AS game_date,
       PTS_home
FROM games
WHERE season = 2020 
  AND PTS_home >= 110;
  • 분석 (✅ 정답):
    • DATE_FORMAT(컬럼, 형식) 함수를 정확히 이해하고 사용했다. SQL에서 날짜 데이터는 기본적으로 DATETIME이나 TIMESTAMP 타입을 가지는데, 이를 시각적으로 편한 문자열로 바꾸기 위해 포맷팅이 필수적이다.
    • %Y는 4자리 연도, %m은 숫자 월, %d는 일자를 의미한다.

📌 2번 문제: 패턴 매칭을 이용한 데이터 조회

  • 주제: String Pattern Matching (LIKE)
  • 문제 지문 (예측): games_details 테이블에서 선수 이름(PLAYER_NAME)이 'JAMES'로 끝나는 선수들의 PLAYER_ID와 PLAYER_NAME을 중복 없이 조회하라.
  • 내가 쓴 쿼리:
    SELECT PLAYER_ID, PLAYER_NAME
FROM games_details
WHERE PLAYER_NAME LIKE '%JAMES'
  • 분석 (✅ 정답):
    • LIKE 연산자와 와일드카드(%)의 활용이 적절했다. %JAMES는 앞에 어떤 문자가 오든 상관없이 끝이 'JAMES'인 문자열을 찾는다.
    • 심화 학습: 만약 'JAMES'가 포함된 모든 이름을 찾으려면 %JAMES%를 사용해야 하며, 정확히 'JAMES'인 경우만 찾으려면 LIKE 대신 = 연산자가 성능상 유리할 수 있다.

📌 3번 문제: 그룹화와 집계 결과 필터링

  • 주제: Aggregation & HAVING Clause
  • 문제 지문 (예측): games_details 테이블에서 각 팀(TEAM_ID)별 평균 득점(PTS)을 계산하고, 그 평균 득점이 105점 이상인 팀의 ID와 평균 득점을 조회하라.
  • 내가 쓴 쿼리:
    SELECT TEAM_ID, avg(PTS) AS '평균 득점'
FROM games_details
GROUP BY TEAM_ID 
HAVING avg(PTS) >= 105
  • 분석 (✅ 정답):
    • SQL의 실행 순서(FROM -> WHERE -> GROUP BY -> HAVING -> SELECT)를 잘 이해하고 있다. 집계 함수(AVG)의 결과에 대한 조건은 WHERE 절이 아닌 HAVING 절에서 처리해야 함을 정확히 적용했다.

📌 4번 문제: 시간 데이터 연산과 JOIN의 정석 (핵심 오답)

  • 주제: Time Data Calculation & JOIN Keys
  • 문제 지문 (예측): games_details와 players 테이블을 조인하여, 각 선수(PLAYER_NAME)별 평균 출전 시간(MIN)을 구하라. (단, 데이터가 없는 경우 제외)
  • 내가 쓴 쿼리 (❌ 틀림):
    SELECT p.Player_name, gd.avg(min)
FROM games_details AS gd
JOIN players AS p
    ON gd.Team_ID = p.Team_ID
  • 정답 쿼리:
    SELECT 
    p.PLAYER_NAME,
    SEC_TO_TIME(AVG(TIME_TO_SEC(gd.MIN))) AS avg_playtime
FROM games_details AS gd
JOIN players AS p
    ON gd.PLAYER_ID = p.PLAYER_ID
WHERE gd.MIN IS NOT NULL 
  AND gd.MIN != ''
GROUP BY p.PLAYER_NAME;
  • 오답 분석 및 심층 학습:
    1. 데이터 타입의 오해 (String vs Time): 원본 데이터의 MIN 컬럼은 '25:30'과 같은 문자열(String) 형태일 가능성이 높다. 문자열에 바로 AVG()를 씌우면 SQL은 이를 제대로 된 숫자로 인식하지 못해 0이나 엉뚱한 값을 반환한다.
      • 해결책: TIME_TO_SEC()으로 문자열을 초(Second) 단위 정수로 변환 -> AVG()로 평균 초 계산 -> SEC_TO_TIME()으로 다시 시간 형식으로 변환하는 3단계 프로세스가 필요하다.
    2. 잘못된 JOIN 키: ON gd.Team_ID = p.Team_ID로 조인하면, 특정 팀의 경기 기록이 해당 팀의 모든 선수와 다대다(N:M)에 가깝게 잘못 매핑된다. 선수의 기록을 보려면 반드시 고유 식별자인 PLAYER_ID를 기준으로 조인해야 한다.
    3. 문법 오류: gd.avg(min)이라는 문법은 존재하지 않는다. 함수는 AVG(gd.min) 형태로 컬럼을 감싸야 한다.

📌5번 문제: 윈도우 함수와 CTE를 활용한 최신 기록 추출 (핵심 오답)

  • 주제: Window Function & CTE (Common Table Expression)
  • 문제 지문 (예측): 각 팀(TEAM_ID_home)별로 가장 최근에 치른 경기의 날짜, 홈 득점, 그리고 팀의 현재 승패(W, L)를 조회하라.
  • 내가 쓴 쿼리 (❌ 틀림):
    • (복잡도를 해결하지 못해 작성 중단)
  • 정답 쿼리:
    WITH latest_games AS (
    SELECT
        GAME_ID,
        TEAM_ID_home,
        GAME_DATE_EST,
        PTS_home,
        ROW_NUMBER() OVER (
            PARTITION BY TEAM_ID_home
            ORDER BY GAME_DATE_EST DESC
        ) AS rn
    FROM games
)
SELECT 
    lg.TEAM_ID_home,
    lg.GAME_DATE_EST,
    lg.PTS_home,
    r.W,
    r.L
FROM latest_games AS lg
JOIN ranking AS r
    ON lg.TEAM_ID_home = r.TEAM_ID
WHERE lg.rn = 1;
  • 오답 분석 및 심층 학습:
  1. 문제 해결 전략의 부재: 그룹별 최신 1건을 뽑는 문제는 일반적인 GROUP BY로는 해결하기 매우 까다롭다. 이때 윈도우 함수(Window Function)가 강력한 도구가 된다.
  2. ROW_NUMBER()의 마법: ROW_NUMBER() OVER (PARTITION BY 그룹컬럼 ORDER BY 정렬컬럼 DESC) 구문은 그룹별로 순위를 매긴다. 여기서 순위가 1인 것이 곧 최신 데이터가 된다.
  3. WITH 절의 활용: 서브쿼리를 메인 쿼리 안에 욱여넣으면 가독성이 떨어진다. WITH 절을 사용해 가상의 테이블(latest_games)을 먼저 정의하고, 메인 쿼리에서 이를 깔끔하게 호출하는 방식이 실무적인 쿼리 작성법이다.

📚 Part 2. 문자열 처리와 스칼라 서브쿼리

이 파트에서는 단순 조회처럼 보이지만 디테일이 필요한 문자열 처리와, 서브쿼리의 종류 중 하나인 스칼라 서브쿼리에 대해 다뤘다.

📌 1번 문제: 정렬의 기초

  • 주제: Order By
  • 문제 지문 (예측): 원정 팀 득점(PTS_away)이 100점 이상인 경기를 찾아, 득점이 높은 순서대로 정렬하여 조회하라.
  • 내가 쓴 쿼리:
    SELECT GAME_ID, VISITOR_TEAM_ID, PTS_away
FROM games
WHERE PTS_away >= 100
ORDER BY PTS_away DESC
  • 분석 (✅ 정답):
    • ORDER BY 컬럼 DESC(내림차순) 구문을 정확하게 사용했다.

📌 2번 문제: 문자열 내 특정 문자 포함 여부 (오답)

  • 주제: String Function (INSTR vs LIKE)
  • 문제 지문 (예측): 이름(PLAYER_NAME)에 공백(' ')이 포함된 선수들을 조회하라. (즉, 이름과 성이 모두 있는 선수 등을 찾으라는 의도)
  • 내가 쓴 쿼리 (❌ 틀림):
    SELECT PLAYER_ID, instr(PLAYER_NAME, ' ')
FROM players
  • 정답 쿼리:
    SELECT PLAYER_ID, PLAYER_NAME
FROM players
WHERE PLAYER_NAME LIKE '% %';
-- 또는
-- WHERE INSTR(PLAYER_NAME, ' ') > 0;
  • 오답 분석 및 심층 학습:
    1. SELECT와 WHERE의 역할 혼동: 내가 쓴 쿼리는 공백의 위치(숫자)를 조회(SELECT)하는 것이지, 공백이 있는 사람만 필터링(WHERE)하는 것이 아니다. 원하는 데이터를 남기려면 반드시 WHERE 절에 조건을 명시해야 한다.
    2. 효율적인 방법: INSTR 함수는 위치 인덱스를 반환하므로 > 0 조건을 걸어야 한다. 하지만 단순히 포함 여부만 따질 때는 LIKE '% %'가 훨씬 직관적이고 표준적인 SQL 작성법이다.

📌 3번 문제: 복합 컬럼의 집계

  • 주제: Aggregation with Arithmetic
  • 문제 지문 (예측): 팀별로 공격 리바운드(OREB)와 수비 리바운드(DREB)를 합친 '총 리바운드'의 합계가 1000개 이상인 팀을 조회하라.
  • 내가 쓴 쿼리:
    SELECT TEAM_ID, SUM(OREB + DREB) AS total_rebounds
FROM games_details
GROUP BY TEAM_ID
HAVING SUM(OREB + DREB) >= 1000;
  • 분석 (✅ 정답):
    • SUM() 함수 내부에서 컬럼 간의 연산(OREB + DREB)이 가능함을 잘 알고 활용했다.

📌 4번 문제: JOIN과 집계의 결합

  • 주제: JOIN & COUNT
  • 문제 지문 (예측): 각 선수별로 참여한 경기 수를 계산하고, 선수 이름을 기준으로 오름차순 정렬하라.
  • 내가 쓴 쿼리:
    SELECT gd.PLAYER_ID, p.PLAYER_NAME, count(*) AS game_count
FROM games_details AS gd
JOIN players AS p
    ON gd.PLAYER_ID = p.PLAYER_ID
GROUP BY gd.PLAYER_ID, p.PLAYER_NAME
ORDER BY PLAYER_NAME ASC;
  • 분석 (정답✅ ):
    • JOIN을 통해 이름을 가져오고, GROUP BY로 선수별 데이터를 묶은 뒤 COUNT(*)로 행의 개수를 세는 표준적인 집계 패턴을 완벽하게 수행했다.

5번 문제: 스칼라 서브쿼리의 활용 (개념 정리)

  • 주제: Scalar Subquery
  • 문제 지문 (예측): 2020 시즌의 경기 중, 해당 시즌의 '평균 홈 득점'보다 더 많은 득점을 기록한 경기를 조회하라.
  • 내가 쓴 쿼리 (🔺 애매):
    SELECT GAME_ID, GAME_DATE_EST, PTS_home
FROM games
WHERE SEASON = 2020 
    AND pts_home > (
        SELECT avg(PTS_home)
        FROM games
        WHERE SEASON = 2020
        );
  • 분석 (✅ 정답 & ✓ 개념 보완):
    • 정답 처리는 되었으나, 스칼라 서브쿼리(Scalar Subquery)의 개념을 확실히 짚고 넘어가야 한다.
    • 괄호 안의 서브쿼리는 2020 시즌 평균이라는 단 하나의 숫자(1행 1열)를 반환한다. 이렇게 단일 값을 반환하는 서브쿼리만이 비교 연산자(>, < 등)의 오른쪽에 올 수 있다. 만약 서브쿼리가 여러 행을 반환했다면 에러가 발생했을 것이다.

📚 Part 3. 서브쿼리 심화와 논리적 비교 (Subquery Set)

이 파트에서는 서브쿼리가 단순 값을 리턴하는 것을 넘어, 집계 결과와 비교될 때 어떻게 처리해야 하는지, 즉 HAVING 절에서의 서브쿼리 활용을 다뤘다.

📌 1번 문제: 평균 집계 및 정렬

  • 주제: Average & Sorting
  • 문제 지문 (예측): 팀 평균 득점이 20점 이상인 팀을 찾고, 평균 득점이 높은 순서대로 조회하라.
  • 내가 쓴 쿼리:
    SELECT TEAM_ID, AVG(PTS)
FROM games_details
GROUP BY TEAM_ID
HAVING AVG(PTS) >= 20
ORDER BY AVG(PTS) DESC
  • 분석 (✅ 정답):
    • 집계(GROUP BY + AVG), 필터링(HAVING), 정렬(ORDER BY)의 삼박자가 잘 맞았다.

📌 2번 문제: JOIN을 이용한 조건 필터링

  • 주제: JOIN & WHERE
  • 문제 지문 (예측): 연고지가 'Los Angeles'인 팀에 속한 선수들의 이름을 조회하라.
  • 내가 쓴 쿼리:
    SELECT p.player_name, t.CITY
FROM players AS p
JOIN teams AS t
    ON p.TEAM_ID = t.TEAM_ID
WHERE t.CITY = 'Los Angeles'
  • 분석 (✅ 정답):
    • players 테이블에는 도시 정보가 없으므로 teams 테이블과 조인해야 한다는 사실을 잘 파악했다.

📌 3번 문제: 다중 조인 및 컬럼 간 비교

  • 주제: Self/Multi Join logic
  • 문제 지문 (예측): 홈 팀이 원정 팀보다 더 많은 득점을 한(승리한) 경기의 선수 ID와 경기 ID를 조회하라.
  • 내가 쓴 쿼리:
    SELECT gd.PLAYER_ID, gd.TEAM_ID, g.GAME_ID
FROM games_details AS gd 
JOIN games AS g
    ON gd.GAME_ID = g.GAME_ID
WHERE g.PTS_home > g.PTS_away;
  • 분석 (✅ 정답):
    • games_details에는 점수 정보가 없거나 불충분할 수 있어 games 테이블과 조인하고, games 테이블 내의 두 컬럼(PTS_home, PTS_away)을 비교하는 조건을 잘 작성했다.

📌 4번 문제: 전체 평균과의 비교

  • 주제: Scalar Subquery (Global Average)
  • 문제 지문 (예측): 역대 모든 경기의 평균 홈 득점보다 높은 득점을 기록한 경기를 조회하라.
  • 내가 쓴 쿼리:
    SELECT GAME_ID, GAME_DATE_EST, PTS_home
FROM games 
WHERE pts_home > (
    SELECT avg(PTS_home)
    FROM games);
  • 분석 (✅ 정답):
    • 앞선 2-5번 문제와 유사한 패턴으로, 전체 데이터를 대상으로 하는 스칼라 서브쿼리를 올바르게 사용했다.

📌 5번 문제: 집계 결과와 서브쿼리의 비교 (핵심 오답)

  • 주제: Aggregation Comparison Subquery
  • 문제 지문 (예측): 'LeBron James' 선수의 총 리바운드 수보다 더 많은 리바운드를 기록한 선수의 ID와 총 리바운드 수를 조회하라.
  • 내가 쓴 쿼리 (❌ 틀림):
    SELECT PLAYER_ID, count(*) >
    (SELECT sum(RED) FROM games_details)
FROM games_details
GROUP BY PLAYER_ID
  • 정답 쿼리:
    SELECT 
    PLAYER_ID,
    SUM(REB) AS total_rebounds
FROM games_details
GROUP BY PLAYER_ID
HAVING SUM(REB) > (
    SELECT SUM(REB)
    FROM games_details
    WHERE PLAYER_NAME = 'LeBron James'
);
  • 오답 분석 및 심층 학습:
    1. 비교의 위치 (SELECT vs HAVING): 나의 오답 쿼리는 SELECT 절에서 비교를 시도했다. SQL에서 SELECT는 데이터를 '보여주는' 곳이지 데이터를 '거르는' 곳이 아니다. 집계된 결과(SUM(REB))를 기준으로 데이터를 남길지 말지 결정하려면 반드시 HAVING 절을 사용해야 한다.
    2. 비교 대상의 불일치: 문제의 의도는 '리바운드 수'를 비교하는 것이다. 하지만 내 쿼리는 count(*)(경기 수)와 비교하려 했다. 논리적 정합성을 위해 SUM(REB)끼리 비교해야 한다.
    3. 서브쿼리의 정확성: 비교 기준이 되는 값은 '전체 리바운드 합계'가 아니라 '르브론 제임스라는 특정 선수의 리바운드 합계'여야 한다. 따라서 서브쿼리 내에 WHERE PLAYER_NAME = 'LeBron James' 조건이 필수적으로 들어가야 한다.

📝 오늘의 SQL 핵심 요약 (Cheat Sheet)

오늘의 시행착오를 통해 다음 세 가지 원칙을 뼈저리게 느꼈다. 이 원칙들은 앞으로의 쿼리 작성에 있어 강력한 가이드라인이 될 것이다.

  1. 시간 데이터는 숫자로 변환 후 연산한다:
    • 문자열('10:00') -> TIME_TO_SEC -> 숫자 연산 -> SEC_TO_TIME -> 문자열
  2. 순위나 최신값 추출은 윈도우 함수가 답이다:
    • 복잡한 GROUP BY와 JOIN 대신 ROW_NUMBER() OVER(PARTITION BY... ORDER BY...)를 사용하면 쿼리가 훨씬 우아해진다.
  3. 집계 후 필터링은 무조건 HAVING이다:
    • WHERE는 원본 데이터를 거르고, HAVING은 GROUP BY로 요약된 데이터를 거른다. 집계 함수(SUM, AVG)와 서브쿼리를 비교할 때는 HAVING 절을 확인하자.

이 과정을 통해 SQL 레벨이 한 단계 성장했음을 느낀다. 단순한 문법 암기가 아니라, 데이터가 어떻게 흐르고 처리되는지 그 구조를 이해하는 것이 쿼리 최적화와 정답률 상승의 지름길이다.

마무리하며

SQL에 소홀해지다 보니, 이렇게 간단한 문제들도 틀려버린다.

튜터님들께서 자주
"앞으로 SQL을 자주 사용하지 않으니, 코트카타 열심히" 라고 말씀하시는데,
정말 지당한 말씀이라고 생각이 든다..

앞으로 코트카타 더 열심히 하고, 시간 날 때 개념 한 번씩 훑어보자.

profile
최다빈
Running on hopes and tiny skills...
이전 포스트

[251118] 내배캠 D+21

0개의 댓글