[성동1기 전Z전능 데이터 분석가] 19일차 - SQL.1

목차
1. Query 함수로 SQL 감 잡기
2. 데이터 베이스와 SQL
3. SQL로 데이터 읽어오기
4. SQL로 데이터 연결하기

 

1. Query 함수로 SQL 감 잡기

SQL이란?

Structure Query Language

SQL은 저장된 데이터 중에 내가 보고 싶은 데이터를 가져올 때 쓰는 명령어다.

가져오는 것 뿐만 아니라 새로운 데이터를 저장, 기존 데이터를 수정, 삭제하는 것도 가능하다.

SQL 활용처는 개발자(개발, 엔지니어링)와 비개발자(기획, 마케팅, 운영, 분석가)가 다르다.

개발자는 사용자에게 보여줄 데이터를 끄집어 내고 생성되는 데이터를 저장, 업데이트, 삭제하는 것이 목적이다.

비개발자는 분석에 필요한 데이터를 끄집어 내는 것이 목적이다.

 

본격적으로 Query 함수로 SQL 감 잡아보자

Query 함수는 구글 스프레드 시트의 데이터에서 내가 원하는 값만 뽑아 보고(필터링), 나눠보고(그룹핑), 평균,합산 등을 하는 함수다. SQL과 유사하고, 별다른 설치 없이 크롬 등의 브라우저에서 바로 쓸 수 있어 Query 함수로 쿼리 짜는 연습을 통해 SQL을 더 빠르고 쉽게 이해할 수 있다.

 

Select: 테이블에서 특정 열의 데이터를 가져오는 명령어

• select A: A열을 가져와라
• select A, B: A,B열을 가져와라
• select *: 모든 열을 가져와라
• select AVG(A): A열의 평균을 가져와라 (sum, avg, count, min, max 가능)
• select A+B: A와 B를 더한 열을 가져와라 (*-+/% 가능)

LABEL: select으로 불러오는 열의 이름을 변경

query( A1:E21 , "select A,B label B 'BBB'")

: A1:E21까지의 데이터 중에 A열과 B열을 가져오되 B열의 이름을 BBB로 바꿔라

 

limit: 데이터를 가져올 때 행의 수를 제한하는 명령어

query( A1:E21, "select A limit 10"): A열을 10행만 가져와라

 

offset: select로 열을 불러올 때 (건너뛰고) 가져올 첫번째 행을 지정

query( A1:E21, "select A offset 10"): A열 중 10행 이후부터 가져와라

 

order by: 특정 열을 내림차순이나 오름차순으로 정렬하는 명령어

query( A1:E21, "select A order by A DESC"): A열을 내림차순으로 정렬해라

query( A1:E21, "select B,C order by C ASC"): C열을 기준으로 오름차순으로 B, C열을 정렬해라

=QUERY(A1:E21,"select A, B, C, D, (B+C+D)/3 order by (B+C+D)/3 ASC limit 5 label (B+C+D)/3 'avg'")

- 순서도 정해져 있다.

 

where: selct문으로 가져오는 데이터 중에 특정 조건에 해당하는 데이터를 가져오는 명령어

query( A1:E21, "select A, D where E='promotion'"):  promotion에 해당하는 A와 D열을 가져와라

= 같음 <> 같지 않음 != 같지 않음 >크다 <적다 >= 크거나 같다 <= 작거나 같다

and 모든 조건이 참일때, or 하나 이상의 조건이 아닐 때 not 조건이 참이 아닐 때

 

Group by: sum, count, avg 등과 함께 사용되며 지정한 열의 값에 따라 그룹화  해서 sum, count, avg 등을 계산

=QUERY(A4:E24,"select E,avg(B) group by E") E열에 들어있는 값을 읽어 경우의 수로 그룹을 나눠서 B의 평균값을 보임

=QUERY(A4:E24,"select E, sum(B)+sum(C)+sum(D) group by E" ) 프로모션별 전체 합계계산

피봇테이블을 연상하면서 하면 편하다. B+C+D이런 식은 안 된다. sum(B)+ sum(C)+ sum(D)로 해야된다.

 

=QUERY(A4:E24,"select A, D, B+C where D>(B+C)*4") -> 4*(B+C) 는 안 된다. 약속이다.

 

실습을 해보자

 

이 데이터를 가지고 실습해보고자 한다.

Date	Cakes	Pies	Cookies	Promotion
2021-01-01	79	46	518	none
2021-01-02	91	50	539	none
2021-01-03	47	60	222	promotion
2021-01-04	89	64	734	none
2021-01-05	112	73	764	none
2021-01-06	89	57	922	promotion
2021-01-07	70	50	476	none
2021-01-08	70	48	496	none
2021-01-09	59	37	587	promotion
2021-01-10	71	36	488	none
2021-01-11	74	50	645	none
2021-01-12	119	71	438	none
2021-01-13	90	51	568	none
2021-01-14	96	48	585	promotion
2021-01-15	62	56	536	none
2021-01-16	48	33	336	promotion
2021-01-17	58	67	404	none
2021-01-18	74	74	533	none
2021-01-19	94	45	470	promotion
2021-01-20	75	85	400	none

 

1. 프로모션 별 케이크 파이 쿠키 판매평균 계산 =QUERY(A4:E24,"select E,avg(B) group by E")

Promotion	avg Cakes	avg Pies	avg Cookies
none	81	58.64285714	538.5
promotion	72.16666667	46.66666667	520.3333333

 

2. 프로모션 별 전체 합계 계산 =QUERY(A4:E24,"select E, sum(B)+sum(C)+sum(D) group by E" )

Promotion	sum(sum(sum Cakessum Pies)sum Cookies)
none	9494
promotion	3835

 

3. 프로모션 별 최대 파이 판매량 =QUERY(A4:E24,"select E, max(C) group by E")  

Promotion	max Pies
none	85
promotion	60

 

4.프로모션을 한 날 중 케이크의 평균 판매량은?   =QUERY(A4:E24,"select E,avg(B) group by E")

Promotion	avg Cakes
none	81
promotion	72.16666667

 

5. 프로모션을 한 날 중, 케이크, 파이, 쿠키 판매량 총합이 세번째로 큰 날은 언제인가요?

=QUERY(A4:E24,"select A,B+C+D where E='promotion' order by B+C+D DESC")

Date	sum(sum(CakesPies)Cookies)
2021-01-06	1068
2021-01-14	729
2021-01-09	683
2021-01-19	609
2021-01-16	417
2021-01-03	329

 

6. 프로모션을 하지 않은 날 중, 쿠키 판매량이 700 이상인 날은 언제인가요?

=QUERY(A4:E24,"select A, D where D>=700 and E <> 'promotion'")

Date	Cookies
2021-01-04	734
2021-01-05	764

7. 프로모션을 한 날 중, 케이크, 파이의 합이 200 이하인 건수는 며칠인가요?

=QUERY(A4:E24,"select A, B+C where B+C <=200 and E = 'promotion'")

Date	sum(CakesPies)
2021-01-03	107
2021-01-06	146
2021-01-09	96
2021-01-14	144
2021-01-16	81
2021-01-19	139

 

8. 쿠키의 판매량이 케이크와 파이의 판매량의 합의 4배를 초과한 날짜 찾기

=QUERY(A4:E24,"select A, D, B+C where D>(B+C)*4")

Date	Cookies	sum(CakesPies)
2021-01-01	518	125
2021-01-04	734	153
2021-01-05	764	185
2021-01-06	922	146
2021-01-08	496	118
2021-01-09	587	96
2021-01-10	488	107
2021-01-11	645	124
2021-01-13	568	141
2021-01-14	585	144
2021-01-15	536	118
2021-01-16	336	81

 

9. 케이크의 판매량이 쿠키 판매량보다 낮은 날짜 중, 상위 3개의 쿠키 판매량을 가잔 날짜 찾기

=QUERY(A4:E24,"select A, B,D where B <D order by D DESC limit 3")

Date	Cakes	Cookies
2021-01-06	89	922
2021-01-05	112	764
2021-01-04	89	734

 

10. 프로모션을 한 날 중, 케이크 판매량이 80 이상이며, 쿠키 판매량이 600이상인 날짜 찾기

=QUERY(A4:E24,"select A, B,D where E='promotion' and B>=80 and D>=600")

Date	Cakes	Cookies
2021-01-06	89	922

 

11. 프로모션이 없는 날 중에서 케이크와 쿠키의 판매량의 차이가 가장 큰 날짜 찾기

=QUERY(A4:E24,"select A, D-B where E<>'promotion' order by D-B DESC limit 1")

Date	difference(CookiesCakes)
2021-01-05	652

 

12. 프로모션 유무별로 케이크의 최대 판매량과 최소 판매량의 차이를 구하시오

=QUERY(A4:E24,"select E,max(B),min(B), max(B)-min(B) group by E")

Promotion	max Cakes	min Cakes	difference(max Cakesmin Cakes)
none	119	58	61
promotion	96	47	49

 

배운점 및 적용할점

스프레드 시트의 Query함수를 사용하여 SQL에 대한 기본적인 것을 배웠다. 

확실히 이미 익숙한 스프레드 시트로 연습하니 큰 어려움은 없었다. 오늘 배운 것을 잘 기억해서 진짜 SQL을 쓰게 되었을 때 조금 더 손쉽게 다룰 수 있도록 해보겠다.