[Bigquery] 초보자를 위한 BigQuery(SQL) 입문(1)

데이터 분석 특히 SQL관련 영상을 둘러보다가 유튜브 '카일스쿨'님의 영상을 몇 번보게 되었다.

SQL 초보자를 위한 9시간 짜리 강의를 무료로 올려주셨는데

좋아하는 포켓몬스터 데이터로 강의를 해주시기도 하고 초반 SQL을 입문하는데도 좋을 것 같아서 수강하게 되었다.

인프런에 동일한 강의가 올라가 있는데 자료도 있으니 인프런을 보면서 공부했다.

그래도 SQLD 자격증을 공부하면서 이미 알고 있는 내용들이 많아서 어렵지 않게 따라할만 했다

유튜브 : https://youtu.be/ohAsFH-YLoE?si=NaQE8qd1p27vQE-v

인프런 : https://inf.run/6ondu

[지금 무료]초보자를 위한 BigQuery(SQL) 입문| 카일스쿨 - 인프런 강의

현재 평점 5.0점 수강생 8,620명인 강의를 만나보세요. 초보자를 위한 BigQuery(SQL) 입문 강의입니다. 데이터를 가공하는 방법이 궁금하신 분에게 추천합니다. 백엔드 개발 관점보다는 데이터 분석,

www.inflearn.com

BigQuery 기초 지식

1. 데이터 저장 형태

- 데이터는 보통 데이터베이스 테이블 등에 저장

- Database : 데이터의 저장소

- Table : 데이터가 저장된 공간

- 저장된 데이터를 제품(앱, 웹)에서 사용

- ex) 음식 배달 서비스의 데이터 : 유저, 음식점, 음식, 라이더

2. 데이터가 저장되는 장소

- MySQL, Oracle, PostgreSQL 같은 데이터베이스에 주로 저장

- 회사 마다 주로 사용하는 데이터베이스가 다름

- 이러한 데이터베이스의 특징 : OLTP

3. OLTP란?

- OLTP(Online Transaction Processing)

- 거래를 하기 위해 사용되는 데이터베이스

- 보류, 중간 상태가 없음 -> 주문을 완료하거나 안하거나 : 데이터가 무결하다

- 데이터의 추가(INSERT), 데이터의 변경(UPDATE)이 많이 발생

- SQL을 사용해 데이터를 추출할 수 있으나 분석을 위해 만든 데이터베이스가 아니라 쿼리 속도가 느릴 수 있음

4. SQL

- SQL(Structured Query Language)

- 데이터베이스에서 데이터를 가지고 올 때 사용하는 언어

- 데이터베이스의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어

- 쿼리문, 쿼리 구문, 쿼리를 짠다, SQL 쿼리 등으로 표현

5. 테이블에 저장된 데이터의 형태

테이블에 저장된 형태는 엑셀 스프레드 시트와 유사

	Column 1	Column2	Column3	Column4
Row1
Row2
Row3

행(Row)

- → 새로운 row는 가로로 한 줄을 의미

- 하나의 row가 하나의 고유한 데이터

- ex) 거래 History에서 하나의 row 가 거래

※ Raw데이터 : 원본 데이터

열(Columns)

- ↓ column은 원형 기중이란 뜻, 세로로 연상

- 각 데이터의 특정 속성 값

- ex) 거래의 구매 시간, 거래의 구매자

6. OLAP와 데이터 웨어하수스(DW)의 등장

- OLTP로 데이터 분석을 하다가 속도, 기능 부족의 이슈로 OLAP가 등장함

OLAP (Online Analytical Processing)

- 분석을 위한 기능 제공

데이터 웨어하우스

- 데이터를 한 곳에 모아서 저장

- 여러 곳에 저장된 데이터 예시 : Database, 웹(크롤링), 파일, API의 결과 등

7. BigQuery 장점

1) 난이도

- SQL를 사용해 쉽게 데이터 추출 가능

2) 속도

- OLAP 도구이므로 속도가 빠름(단, 그만큼 돈을 지불)

3) Firebase, Google Analytics 4의 데이터를 쉽게 추출할 수 있음

- 사용 기기, 위치(시 단위까지 표현), OS 버전, 이벤트 행동 획득 가능(별도의 로깅 필요)

4) 데이터 웨어하우스를 사용하기 위해 서버(컴퓨터)를 띄울 필요 없음

- 구글에서 인프라 관리함

8. BigQuery를 사용하는 이유

- 회사에서 앱이나 웹에서 Firebase, Google Analyics4를 활용할 경우

- 운영을 적은 비용(인력 등)으로 진행하기 위해

- 사용하는 회사 : 쏘카, 당근, 컬리, 하이퍼커넥트, 마이리얼트립, 버드뷰, 원티드랩, 매스프레소, 오피지지

BigQuery 환경 설정

1. Google Cloud 설정

- Google Cloud 가입

- Google Cloud 프로젝트 생성

- Google Cloud BigQuery Activate

- 구글 이메일로 로그인 후, Google Cloud Console로 이동

- https://console.cloud.google.com

이후 새 프로젝트를 만들어 주면 된다.

나는 강의에서 사용한 프로젝트 이름(inflearn-BigQuery)로 동일하게 만들었다.

이후 여러가지 과정들을 거쳐서 프로젝트의 BigQuery로 들어왔다.

https://console.cloud.google.com/bigquery

2. BigQuery의 환경 구성 요소

1) 프로젝트(Project)

- 하나의 큰 건물. 건물 안에는 여러 목적을 가진 창고가 존재

- 하나의 프로젝트에 여러 데이터셋이 존재할 수 있음

2) 데이터셋(Dataset)

- 프로젝트에 있는 창고. 각 창고 공간에 데이터 저장

- 판매 데이터, 고객 데이터 등 별도의 데이터를 저장할 수 있음

- 하나의 데이터셋에 다양한 테이블이 존재할 수 있음

3) 테이블(Table)

- 창고에 있는 선반

- 테이블 안엔 상품의 세부 정보가 저장

- 테이블 안엔 행과 열로 이루어진 데이터들이 저장

3. 테이블 생성

강의자료에 올라와져 있는 데이터 셋을 가지고 BigQuery에 테이블로 업로드 했다.

나머지는 동일한데 battle만 파티션을 설정 해서 그런지 로고 모양이 달랐다.

테이블에 모두 저장 된 후 쿼리에서 아래 SQL문을 작성하니 데이터가 잘 불러와졌다.

SELECT
	*
FROM `basic.pokemon`
ORDER BY id

여기서 basic pokemon을 불러올 때 당연히 ' 을 썼더니 오류가 나서 Q&A를 조회해보니

` 을 써야 제대로 정상 작동 한다고 한다..

데이터 탐색 - 조건, 추출, 요약

데이터 활용 Overview

어떤 일을 해야 한다 > 원하는 것을 정한다 > 데이터 탐색 > 데이터 결과 검증 > 피드백/활용

업무를 해야할 때 위의 과정을 한다고 할 때 SQL에서는 이중 데이터 탐색 > 데이터 결과 검증 이 부분을 담당한다.

데이터 탐색의 경우 단일 자료인 경우가 있꼬 다량의 자료인 경우가 있는데 만약 다량의 자료인 경우 연결(Join)을 하고 조건(필터리), 추출 변환 요약을 한다.

이 것 또한 SQL에서 진행할 수 있다.

저장된 데이터 확인하기

1. SQL 쿼리를 작성하기 전에

- 데이터가 어떻게 저장되어 있는가?에 대해 생각해보기

- 어떤 데이터가 저장되어 있는가?

- 컬럼의 의미는 무엇인가?

데이터를 제대로 이해해야 올바른 데이터를 추출할 수 있음

(구체적인 정의를 항상 확인하면서 쿼리를 작성해야 함)

2. 데이터가 저장되는 형태를 알려면?

- ERD(Entity Relationship Diagram) : 데이터베이스 구조를 한눈에 알아보기 위해 사용

- 회사마다 있을 수도 있고, 없을 수도 있음

- 회사의 개발자, DBA 분들에게 물어보기

- ERD가 없다면 모든 데이터베이스를 직접 보면서 탐색해보기

-> 어떤 테이블이 존재하는가?

-> 어떤 컬럼이 존재하는가?

-> 다른 테이블과 연결할 때 어떤 컬럼을 사용하는가?

-> 컬럼의 값들은 어떤 의미를 가지는가?

- 스프레드시트나 문서로 따로 정리해두기

3. 포켓몬 세상을 데이터로 생각해보기

1) 포켓몬 세상엔 어떤 데이터가 존재할까?

- 포켓몬

- 트레이너

- 트레이너가 잡은 포켓몬

- 트레이너가 도전한 유저 배틀

- 트레이너가 도전한 체육관 배틀

- NPC

- 상점

- 상점 별로 판매하는 제품

2) 이커머스 산업엔 어떤 데이터가 존재할까?

- 포켓몬 : 상품

- 트레이너 : 유저

- 트레이너가 잡은 포켓몬 : 주문

- 장바구니에 담은 물건

- 웹페이지 접근 수

- 웹페이지의 버튼 클릭 수

4. ERD 예시 - pokemon

카일스쿨 데이터분석가를 위한 BigQuery 사용법, SQL 강의 기초 캡쳐본

데이터 탐색 : 조건과 추출

1. 포켓몬으로 SELECT 이해하기

포켓몬스터

- 포켓몬 트레이너와 포켓몬이 함께 여행을 떠남

- 체육관에 도전해 뱃지를 취득하기도 함

- 포켓몬의 레벨 존재

- 포켓몬 배틀을 하기도 함

포켓몬 여행시 시작할 때 함께 할 포켓몬 고민 :

포켓몬 이름, 공격력 높은 포켓몬, 타입 등

각각의 데이터 소스를 아래와 같이 표현

이름	타입	공격력	특수 공격력	...
피카츄	전기	55	50
꼬부기	물	48	50
이상해씨	풀	49	65
파이리	불	52	60

2. SQL 쿼리 구조

SQL 쿼리문은 아래와 같은 문법으로 작성

우선 SELECT, FROM, WHERE

SELECT
 COL1 AS new_name,
 COL2,
 COL3
FROM Dataset.Table
WHERE
 COL1= 1;

FROM : 어떤 테이블에서 데이터를 확인할 것인가?

WHERE : 만약 원하는 조건이 있다면 어떤 조건인가?

SELECT : 테이블의 어떤 컬럼을 선택(출력)할 것인가?

ex) 포켓몬 타입이 불인 포켓몬을 찾는 SQL 쿼리

SELECT
 *
FROM basic.pokemon
WHERE
 type1 = 'Fire';

* : 모든 컬러을 출력한다.

SELECT

* EXCEPT(제외할 컬럼)

이런 형태도 가능

3. SQL 작성해보기

먼저 pokemon 테이블을 쿼리로 연결 하여 열먼 아래와 같이 나온다.

SELECT
FROM `inflearn-bigquery-498007.basic.pokemon` LIMIT 1000

- inflearn-bigquery : 프로젝트 id

- basic : dataset

- pokemon : table

- `<프로젝트 id>.<데이터셋>.<테이블>

- 프로젝트 id는 꼭 명시할 필요는 없을 수도 있음(트로젝트가 단일이라면!)

- 프로젝트를 여러개 사용한다면 명시하는 것이 좋음 => 쿼리를 실행할 때 어떤 프로젝트인지 확인하는 과정이 존재

- 프로젝트 id를 제거하고 작성할 때는 ` 가 없어도 가능

SELECT
  id AS pokemon_id,
  kor_name,
  type1,
  total
FROM basic.pokemon
WHERE
  type1 = 'Fire';

pokemon 테이블에서 첫 번째 타입이 불 타입인 포켓몬만 id(을 pokemon_id 명칭으로), kor_name, type1, total으로 조회하라

위와 같이 코드를 작성하면 아래와 같이 결과가 나온다.

4. SQL 문법 핵심 정리

1) FROM

- 데이터를 확인할 Table 명시

- 이름이 넘 길다면 AS'별칭'으로 별칭 지정 가능

- FROM Table1 AS t1

2) WHERE

- FROM에 명시된 Table에 저장된 데이터를 필터링(조건 설정)

- Table에 있는 컬럼을 조건 설정

3) SELECT

- Table에 저장되어 있는 컬럼 선택

- 여러 컬럼 명시 가능

- col1 AS '별칭'으로 컬럼의 이름도 별칭 지정 가능

SELECT 연습 문제

Q1. trainer 테이블에 있는 모든 데이터를 보여주는 SQL 쿼리를 작성해주세요.

SELECT
  *
FROM basic.trainer

Q2. trainer 테이블에 있는 트레이너의 name을 출력하는 쿼리를 작성해주세요

SELECT
  name
FROM basic.trainer

Q3. trainer 테이블에 있는 트레이너의 name, age를 출력하는 쿼리를 작성해주세요

SELECT
  name,
  age
FROM basic.trainer

Q4. trainer 테이블에서 id가 3인 트레이너의 name, age, hometown을 출력하는 쿼리를 작성해주세요.

SELECT
  name,
  age,
  hometown
FROM basic.trainer
WHERE
  id = 3

Q5. pokemon 테이블에서 '피카츄'의 공격력 과 체력을 확인할 수 있는 쿼리를 작성해주세요

SELECT
  kor_name,
  attack,
  hp
FROM basic.pokemon
WHERE
  kor_name = '피카츄'

데이터 탐색 : 요약(집계, 그룹화)

1. 집계와 그룹화

집계하다 : 모을 집, 계산할 계

모아서 계산하다

(=그룹화)해서 계산하다

계산

- 더하기, 빼기

- 최대값, 최소값

- 평균

- 갯수 세기 등

2. 집계 : GROUP BY

- GROUP BY : 같은 값끼리 모아서 그룹화한다.

ex) 색상 기준으로 모은다

특정 컬럼을 기준으로 모으면서 다른 컬럼에선 집계 가능(합, 평균, MAX, MIN 등)

도형 ID	색상	가로	세로
1	RED	10	7
2	RED	13	21
3	BLUE	30	21
4	BLUE	5	3
5	GREEN	5	3
6	PINK	7	7

색상	가로의 합	세로의 합
RED	23	28
BLUE	35	24
GREEN	5	3
PINK	7	7

ex) 타입을 기준으로 그룹화 해서 '평균 공격력'과 포켓몬 수를 집계하라

강의에서는 설명 이후에 나오지만 내 나름대로 먼저 SQL문을 작성해봤다.

강의에서는 전기, 물, 풀, 불 만 나왔기 때문에 필터링도 해봤다.

SELECT
  type1,
  ROUND(AVG(attack),0) AS AVG_attack,
  COUNT(*) AS NUM_type
FROM basic.pokemon
WHERE
  type1 IN ('Electric', 'Water','Grass','Fire')
GROUP BY type1
ORDER BY AVG_attack DESC

type1	AVG_attack	NUM_type
Fire	81	20
Water	69	46
Grass	65	21
Electric	61	15

Q. 타입 당 포켓몬 수가 10마리 이상인 데이터만 추출하고 싶은 경우

SELECT
  type1,
  ROUND(AVG(attack),0) AS AVG_attack,
  COUNT(*) AS NUM_attack
FROM basic.pokemon
GROUP BY type1
HAVING
  COUNT(type1) >= 10
ORDER BY AVG_attack DESC

3. DISTINCT : 고유값을 알고 싶은 경우

- DISTINCT의 뜻 : 별개의

- 여러 값 중에 Unique한 것만 보고 싶은 경우 사용

- ex)

1,2,3,3,4 => Distinct 하면 => 1,2,3,4

- DISTINCT 는 중복을 제거하는 것

4. 조건을 설정하고 싶은 경우

1) WHERE

- Table에 바로 조건을 설정하고 싶은 경우

- Raw Data인 테이블 데이터에서 조건 설정

2) HAING

- GROUP BY한 후 조건을 설정하고 싶은 경우 사용

5. 서브쿼리

- SELECT 문 안에 존재하는 SELECT 쿼리

- FROM 절에 또 다른 SELECT 문을 넣을 수 있음

- 괄호로 묶어서 사용

- 서브 쿼리를 작성하고, 서브 쿼리 바깥에서 WHERE 조건 설정하는 것

= 서브 쿼리에서 HAVING으로 하는 것

6. 정렬하기 : ORDER BY

- ORDER BY 는 쿼리의 맨 마지막(아래)에 두고,

쿼리의 맨 마지막에만 작성하면 됨 (중간에 필요없음)

- 순서 : DESC(내림차순) , ASC(오른차순 - 보통 Default)

7. 출력 개수 제한하기 : LIMIT

- 쿼리문의 결과 Row 수를 제한하고 싶은 경우 LIMIT 사용

-쿼리문의 제일 마지막에 작성

8. GROUP BY 연습문제

Q1. pokemon 테이블에 있는 포켓몬의 수를 구하는 쿼리를 작성해주세요

SELECT
  COUNT(id)
FROM basic.pokemon

Q2. 포켓몬의 수가 세대별로 얼마나 있는지 알 수 있는 쿼리를 작성해주세요

SELECT
  generation,
  count(generation)
FROM basic.pokemon
GROUP BY
  generation

강의에서는 count(id) AS cnt 로 적었는데 count 뒤에 어떤 걸 적으면 아직 좋을지는 잘 모르겠다.

Q3. 포켓몬의 수를 타입 별로 집계하고, 포켓몬의 수가 10이상인 타입만 남기는 쿼리를 작성해주세요. 포켓몬의 수가 많은 순으로 정렬해주세요

SELECT
  type1,
  COUNT(id) AS cnt
FROM basic.pokemon
GROUP BY type1
HAVING cnt >= 10
ORDER BY cnt DESC

데이터 탐색 연습 문제

어떤 테이블이고 어떤 의도로 작성하셨는지를 적는 연습을 위해서 조건들을 정리하면서 문제를 풀어보려고 한다.

Q1. 포켓몬 중에 type2가 없는 포켓몬의 수를 작성하는 쿼리를 작성해주세요.

-- 어떤 테이블 : pokemon
-- 어떤 컬럼 : count 컬럼만들기
-- 조건 :type2가 없는
-- 어떻게 집계 : 포켓몬의 수 => count

SELECT
  COUNT(id) AS cnt
FROM basic.pokemon
WHERE 
  type2 IS NULL

NULL

- 아무것도 없는 값. 값이 존재하지 않을 때 NULL

- 0이랑 다르고 " " 과도 다름 => 값이 없는 상태

- IS NULL : NULL인 값 찾기

- IS NOT NULL : NULL이 아닌 값 찾기

Q2. type2가 없는 포켓몬의 type1과 type1의 포켓몬 수를 알려주는 쿼리를 작성해주세요.

단, type1의 포켓몬 수가 큰 순으로 정렬해주세요

-- 테이블 : pokemon
-- 컬럼 : type1, 포켓몬 수
-- 조건 : type2가 없음
-- 집계 : 포켓몬 수 => count
-- 정렬 : type1의 포켓몬 수가 큰순

SELECT 
  type1,
  COUNT(id) AS cnt
FROM basic.pokemon
WHERE
  type2 IS NULL
GROUP BY 
  type1
ORDER BY cnt DESC

Q3. type2 상관없이 type1의 포켓몬 수를 알 수 있는 쿼리를 작성해주세요.

-- 테이블 : pokemon
-- 컬럼 : type1, type1의 포켓몬 수
-- 조건 : 없음 (상관없음 = 조건 없음)
-- 집계 : 포켓몬 수 => count
-- 정렬 : 없음

SELECT 
  type1,
  COUNT(id) AS cnt
FROM basic.pokemon
GROUP BY 
  type1

Q4. 전설 여부에 따른 포켓몬 수를 알 수 있는 쿼리를 작성해주세요

-- 테이블 : pokemon
-- 컬럼 : is_legendary, 전설 여부 포켓몬 수
-- 조건 : 없음
-- 그룹 : is_legendary
-- 집계 : 포켓몬 수 => count
-- 정렬 : 없음

SELECT 
  is_legendary,
  COUNT(id) AS cnt
FROM basic.pokemon
GROUP BY 
  is_legendary

GROUP BY : is_legendary 가 길다. 이후에는 GROUP BY에 컬럼이 많이 있을 수 있음(5개+COUNT)

GROUP BY 1 => SELECT의 첫 컬럼을 의미함

ORDER BY 1 도 가능

1, 2 => 쿼리를 빠르게 작성하고, 결과를 보는 과정임

완성된 쿼리문에서는 1,2 같은 표현보단 명확하게 컬럼을 명시하는 것이 좋음(가독성 관점)

Q5. 동명 이인이 있는 이름은 무엇일까요?

-- 테이블 : trainer
-- 컬럼 : name
-- 조건 : 동명이인(이름 그룹으로 나눴을 때 가 그룹별 이름의 갯수가 1보다 큼)
-- 집계 : 이름의 => count
-- 정렬 : 없음

SELECT
  name
FROM basic.trainer
Group by
  name
HAVING
  COUNT(name) > 1

Q6. trainer 테이블에서 'Iris' 트레이너의 정보를 알 수 있는 쿼리를 작성해주세요.

-- 테이블 : trainer
-- 컬럼 : 모두(정보)
-- 조건 : 이름이 Iris
-- 집계 : 없음
-- 정렬 : 없음

SELECT
  *
FROM basic.trainer
WHERE
  name = 'Iris'

Q7. trainer 테이블에서 'Iris', 'Whitney', 'Cynthia' 트레이너의 정보를 알 수 있는 쿼리를 작성해주세요

-- 테이블 : trainer
-- 컬럼 : 모두(정보)
-- 조건 : 이름이 Iris, Whitney, Cynthia 인 사람 모두
-- 집계 : 없음
-- 정렬 : 없음

SELECT
  *
FROM basic.trainer
WHERE
  name IN ('Iris', 'Whitney', 'Cynthia')

Q8. 전체 포켓몬 수는 얼마나 되나요?

-- 테이블 : pokemon
-- 컬럼 : 포켓몬 수
-- 조건 : 없음
-- 집계 : 포켓몬 수 => COUNT
-- 정렬 : 없음

SELECT
  COUNT(id)
FROM basic.pokemon

Q9. 세대별로 포켓몬 수가 얼마나 되는지 알 수 있는 쿼리를 작성해주세요.

-- 테이블 : pokemon
-- 컬럼 : 세대(generation), 포켓몬 수
-- 조건 : 없음
-- 집계 : 포켓몬 수 =>COUNT
-- 정렬 : 없음

SELECT
  generation,
  COUNT(id) AS cnt
FROM basic.pokemon
GROUP BY
  generation

Q10. type2가 존재하는 포켓몬의 수는 얼마나 되나요?

-- 테이블 : pokemon
-- 컬럼 : 포켓몬 수
-- 조건 : type2가 존재(IS NOT NULL)
-- 집계 : 포켓몬 수 =>COUNT
-- 정렬 : 없음

SELECT
  COUNT(id) AS cnt
FROM basic.pokemon
WHERE
  type2 IS NOT NULL

Q11. type2가 있는 포켓몬 중에 제일 많은 type1은 무엇인가요?

-- 테이블 : pokemon
-- 컬럼 : type1
-- 조건 : type2가 존재(IS NOT NULL)
-- 집계 : 포켓의 수 => COUNT
-- 정렬 : type1의 수가 가장 많음

SELECT
  type1
FROM basic.pokemon
WHERE
  type2 IS NOT NULL
GROUP BY
  type1
ORDER BY COUNT(id) DESC
LIMIT 1

Q12. 단일(하나의 타입만 있는) 타입 포켓몬 중 많은 type1은 무엇일까요?

-- 테이블 : pokemon
-- 컬럼 : type1
-- 조건 : type2가 미존재 (IS NULL)
-- 집계 : 포켓몬 수 => COUNT
-- 정렬 : type1의 수가 가장 많음

SELECT
  type1
FROM basic.pokemon
WHERE
  type2 IS NULL
GROUP BY
  type1
ORDER BY COUNT(id) DESC
LIMIT 1

Q13. 포켓몬의 이름에 '파'가 들어가는 포켓몬은 어떤 포켓몬이 있을까요?

-- 테이블 : pokemon
-- 컬럼 : kor_name
-- 조건 : 이름(kor_name)에 파가 들어감
-- 집계 : 없음
-- 정렬 : 없음

SELECT
  kor_name
FROM basic.pokemon
WHERE
  kor_name Like '%파%'

컬럼 LIKE '특정단어%' , %는 앞에도 붙을 수 있고, 뒤에도 붙을 수 있음

'%파' : 파로 끝나는 단어, '%파' : 파로 시작하는 단어

'%파%' 파가 들어가는 단어(앞 뒤 상관 X)

Q14. 뱃지가 6개 이상인 트레이너는 몇 명이 있나요?

-- 테이블 : trainer
-- 컬럼 : 트레이너의 수
-- 조건 : badge의 수가 6개 이상
-- 집계 : 트레이너의 수 => COUNT
-- 정렬 : 없음

SELECT
  COUNT(id)
FROM basic.trainer
WHERE
  badge_count >=6

Q15. 트레이너가 보유한 포켓몬(trainer_pokemon)이 제일 많은 트레이너는 누구일까요?

-- 테이블 : trainer_pokemon
-- 컬럼 : trainer_id
-- 조건 : 없음
-- 집계 : 포켓몬의 수 => COUNT
-- 정렬 : 가장 많은 포켓몬 수

-- trainer_pokemon 테이블 안에서 trianer_id만 반환하는 경우
SELECT
  trainer_id,
FROM basic.trainer_pokemon
GROUP BY
  trainer_id
ORDER BY
  COUNT(pokemon_id) DESC
LIMIT 1;

-- trainer_pokemon테이블에서 찾은 id로 trainer테이블에서 이름을 반환하는 경우
SELECT 
  name
FROM basic.trainer
WHERE
  ID = (
  SELECT
    trainer_id,
  FROM basic.trainer_pokemon
  GROUP BY
    trainer_id
  ORDER BY
    COUNT(pokemon_id) DESC
  LIMIT 1 );

Q16. 포켓몬을 많이 풀어준 트레이너는 누구일까요?

-- 테이블 : trainer_pokemon
-- 컬럼 : trainer_id
-- 조건 : 포켓몬 상태(status)가 Released인 것
-- 집계 : 포켓몬의 수 => COUNT
-- 정렬 : 가장 많은 포켓몬 수

-- trainer_pokemon 테이블 안에서 trianer_id만 반환하는 경우
SELECT
  trainer_id,
FROM basic.trainer_pokemon
WHERE
  status = 'Released'
GROUP BY
  trainer_id
ORDER BY
  COUNT(pokemon_id) DESC
LIMIT 1;

-- trainer_pokemon테이블에서 찾은 id로 trainer테이블에서 이름을 반환하는 경우
SELECT 
  name
FROM basic.trainer
WHERE
  ID = (
  SELECT
    trainer_id,
  FROM basic.trainer_pokemon
  WHERE
    status = 'Released'
  GROUP BY
    trainer_id
  ORDER BY
    COUNT(pokemon_id) DESC
  LIMIT 1 );

Q17. 트레이너 별로 풀어준 포켓몬의 비율이 20%가 넘는 포켓몬 트레이너는 누구일까요?

풀어준 포켓몬의 비율 = (풀어준 포켓몬 수 / 전체 포켓몬의 수)

-- 테이블 : trainer_pokemon
-- 컬럼 : trainer_id
-- 조건 : 풀어준 포켓몬의 비율이 20%가 넘는 포켓몬 트레이너의 수
-- 집계 : 포켓몬의 수 => COUNT
-- 정렬 : 없음

SELECT
  trainer_id,
  COUNT(pokemon_id) AS pokemon_num,
  COUNTIF(status = 'Released') AS released_num,
  COUNTIF(status = 'Released')/COUNT(pokemon_id) AS released_ratio
FROM basic.trainer_pokemon
GROUP BY
  trainer_id
HAVING
  released_ratio >= 0.2

SQL 쿼리 잘 작성하기

SQL 쿼리 작성하는 흐름

쿼리 작성 템플릿

템플릿

# 쿼리를 작성하는 목표, 확인할 지표 :

# 쿼리 계산 방법 :

# 데이터의 기간 :

# 사용할 테이블 :

# Join KEY L

# 데이터의 특정 :

SELECT

FROM

WHERE

이렇게 글로 작성하면 쿼리 작성이 더 수월

생산성 도구 : 템플릿 쉽게 사용하기

템플릿을 사용하자라고 제시하면 생기는 일

템플릿을 사용하는 것을 까먹음 => 습관 형성이 되지 않음

이 부분을 개선하기 위해 생산성 도구를 활용

생산성 도구 : Espanso

https://espanso.org/

- 윈도우10, Mac, Linux 모두 사용 가능한 프로그램(무료)

- 특정 단어를 입력하면 원하는 문장(템플릿)으로 변경

나는 따로 사용하지 않을 것이기에 이후 해당 프로그램을 저장하지 않을 거지만 혹시라도 사용하고 싶은 분은 강의를 들으면서 다운로드 해도 괜찮을 것 같다.

오류를 디버깅하는 방법

SQL 쿼리를 작성하다가 만나는 다양한 오류

- 쿼리를 작성하면서 실행이 안되는 경우를 경험할 것

오류(Error)의 정의

- '방황하다, 길을 잃다' 라는 뜻의 라틴어에서 유래한 단어

- 부정확하거나 잘못된 행동을 의미

- 실수와 동의어인 경우도 있음

오류 메시지가 알려주고자 하는 것

- 현재 작성한 방식으로 하면 답을 얻을 수 없어요 (길잡이 역할)

- 이 부분에 문제가 되어요 (문제 진단)

오류를 바라보는 관점

오류가 발생하면 : 아 길잡이가 나를 더 좋은 길로 나아가게 하려는 구나!

좋은 길로 어떻게 갈까? : 오류 메시지를 보면서 더 좋은 길로 나가자

BigQuery Error

대표적인 오류 카테고리 : Syntax Error (문법 오류)

- 문법을 지키지 않아 생기는 오류

- Error Message를 보고 번역 또는 해석한 후, 해결 방법 찾아보기

- 구글에 검색

- ChatGPT에 질문

- 지인에게 질문(커뮤니티 등)

SELECT list must not be empty at [10:1]

- SELECT 목록은 [10:1]에서 비어 있으면 안 됩니다.

Number of argyments does not match for aggregate function COUNT

SELECT
  COUNT(id, kor_name)
FROM basic.pokemon

- 집계 함수 COUNT의 인자 수가 일치하지 않습니다.

COUNT(kor_name, eng_name) => X

SELECT list expression references column type1 which is neither grouped nor aggregated

SELECT
  type1,
  COUNT(id) AS cnt
FROM basic.pokemon

- SELECT 목록 식은 다음에서 그룹화되거나 집계되지 않은 열을 참조합니다.

- GROUP BY에 적절한 컬럼을 명시하지 않았을 경우 발생하는 오류

Syntax error: Expected end of input but got keyword SELECT

SELECT
  type1,
  COUNT(id) AS cnt
FROM basic.pokemon
GROUP bY
  type1

SELECT
  *
FROM basic.trainer

- 입력이 끝날 것으로 예상되었지만 SELECT 키워드가 입력되었습니다.

- SELECT 근처 확인하기

- 하나의 쿼리엔 SLEECT가 1개만 있어야 함

- 혹은 쿼리가 끝나는 부분에 ; 붙이고 실행할 부분만 드래그 앤 드랍해서 실행하기

Syntax error: Expected end of input but got keyword WHERE at [4:1]

SELECT
  *
FROm basic.trainer LIMIT 10
WHERE
  id = 3

- 입력이 끝날 것으로 예상되었지만 [4:1]에서 키워드 WHERE를 얻었습니다.

- LIMIT는 맨 마지막에 입력

Syntax error: Expected ")" but got end of script at [8:11]

SELECT
  name,
FROM (
  SELECT
    *
  FROM basic.trainer
  WHERE
    id = 3

- ")"가 예상되지만 [8:11]에 스크립트가 끝났습니다.

- 괄호를 작성하지 않은 경우

더 많은 오류 해결 방법을 알고 싶다면

https://zzsza.github.io/bigquery

본인의 깃헙 블로그에서 계속 업데이트하실 예정이시라고 합니다.

오늘은 이렇게 20강까지 공부해봤는데

연습 문제들을 많이 풀 수 있어서 매우 좋았다

'데이터분석 > 스터디' 카테고리의 다른 글

[Bigquery] 초보자를 위한 BigQuery(SQL) 입문(2) (0)	2026.06.14