1) 특정 user_id 하나를 콕 집어서 확인해보고, 전체로 적용 - 1명의 데이터 확인 (ex. WHERE user_id = 402) - Raw 데이터에서 직접 눈으로 계산해서 결과 예상 (ex. count하면 이 행은 30이라고 나오겠네) - (WHERE 걸었던) 1명 데이터의 예상 결과와 (WHERE 없앤) 전체 쿼리 결과가 동일한지 확인 - 혹시 모르니, 다른 user_id 3~4건 더 추가해서 확인 (여러 케이스가 존재할 수 있으니까) - 3~4개에서 동일한 결과가 나오면 WHERE 조건을 삭제
2) 샘플 데이터로 쿼리 맞는지 확인해보기 - WITH문으로 샘플 데이터를 생성 (UNION ALL 사용해서 수동으로 데이터 생성) - 결과를 예상하고 쿼리 작성 (=복잡한 데이터에서 하기 전에, 간단한 데이터로 쿼리 자체가 올바른지 확인할 때 사용) 출처 : 카일스쿨, 초보자를 위한 BigQuery(SQL) 입문
데이터 결과 검증 연습
👨🏫여러분은 포켓몬 트레이너들의 배틀 성적을 분석하는 작업을 맡게 되었습니다. 각 트레이너의 배틀 승리 비율을 계산해야 하며, 배틀에 참여한 횟수가 9회 이상인 경우만 계산합니다. 데이터 결과 검증을 시도하면서 문제를 풀어보세요
WITH battle_basic AS (
SELECT
id AS battle_id,
player1_id AS trainer_id,
winner_id
FROM `basic.battle`
UNION ALL
SELECT
id AS battle_id,
player2_id AS trainer_id,
winner_id
FROM `basic.battle`
), battle_with_result AS( --battle_with_result로 추가 테이블 지정
SELECT
*,
CASE --winner_id 기준으로
WHEN trainer_id = winner_id THEN "WIN" --trainer_id와 같으면 승리
WHEN winner_id IS NULL THEN "DRAW" --NULL이면 무승부
ELSE "LOSE" --trainer_id와 다르면 패배
END AS battle_result -- 그걸 하나의 컬럼으로 저장
FROM battle_basic
-- WHERE trainer_id = 7 (이제 주석 처리해서 전체 트레이너로 확장!)
)
SELECT
trainer_id,
COUNTIF(battle_result = "WIN") AS win_count, --승리 횟수
COUNT(battle_id) AS total_battle_count, --총 배틀 횟수
COUNTIF(battle_result = "WIN") / COUNT(DISTINCT battle_id) AS win_ratio --승리 비율
FROM battle_with_result
GROUP BY
trainer_id --트레이너마다 계산
HAVING
total_battle_count>=9; --전체 배틀 수 9 이상으로 추가 조건까지 걸면 완성!
--❎내 풀이 과정 --
SELECT -- 13번 트레이너의 배틀 횟수 = 10
-- id,
-- player1_id,
-- player2_id,
-- winner_id,
COUNT(id) AS battle_cnt
FROM `bigquery-studying.basic.battle`
WHERE
player1_id=13 OR player2_id=13;
SELECT -- 13번 트레이너의 승리 횟수 = 2
-- id,
-- player1_id,
-- player2_id,
-- winner_id,
COUNT(id) AS battle_win_cnt
FROM `bigquery-studying.basic.battle`
WHERE
winner_id=13
