1. 학습 준비
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| # 데이터와 라이브러리가 준비 되었다면 아래 코드로 데이터가 잘 불러지는지 확인해보자.
import os
import pandas as pd
from pandasql import sqldf
# pandasql을 전역 환경으로 지정
mysql = lambda q: sqldf(q, globals())
data_dir = 'C:/Users/daydr/data/sql_basic/'
bike = pd.read_csv(os.path.join(data_dir, 'bike_dataset.csv'))
orders = pd.read_csv(os.path.join(data_dir, 'orders.csv'))
customers = pd.read_csv(os.path.join(data_dir, 'customers.csv'))
employees = pd.read_csv(os.path.join(data_dir,'employees.csv'))
ds_2020 = pd.read_csv(os.path.join(data_dir, 'DS_JobsSalaries_2020.csv'))
ds_2021 = pd.read_csv(os.path.join(data_dir, 'DS_JobsSalaries_2021.csv'))
print("데이터 준비 완료!")
|
2. 특정 데이터 조건과 별칭
2-1. 특정 데이터 조건 주기(LIKE, Wildcard)
LIKE
- 조회 조건 값이 명확하지 않을 때 특정 내용을 찾을 수 있음.
- 조건에 문자나 숫자를 포함할 수 있다.
- 주로 Wildcard와 함께 사용
Wildcard
% : 조건을 포함하는 모든 문자를 의미한다. (0개 이상의 문자)_ : 한 글자를 의미한다._ 의 개수에 따라 뒤에 오는 문자열의 개수를 정할 수 있다.- ex.
to_ 라고 지정하면 tom은 추출되지만 tomi는 추출되지 않음
1
2
3
4
5
6
7
8
9
10
11
| # hr이 0이고, weekday에 'ur'이란 단어가 들어간 데이터를 추출해보자.
mysql("""
SELECT *
FROM bike
WHERE hr = 0
AND weekday LIKE '%ur%'
LIMIT 3;
""")
|
| dteday | season | yr | mnth | hr | holiday | weekday | workingday | weathersit | temp | atemp | hum | windspeed | casual | registered | cnt |
|---|
| 0 | 2011-01-01 | spring | 2011 | Jan | 0 | 0 | Saturday | 0 | Clear | 0.24 | 0.2879 | 0.81 | 0.0000 | 3 | 13 | 16 |
|---|
| 1 | 2011-01-06 | spring | 2011 | Jan | 0 | 0 | Thursday | 1 | Clear | 0.18 | 0.2424 | 0.55 | 0.0000 | 0 | 11 | 11 |
|---|
| 2 | 2011-01-08 | spring | 2011 | Jan | 0 | 0 | Saturday | 0 | Cloudy | 0.18 | 0.1970 | 0.51 | 0.1642 | 1 | 24 | 25 |
|---|
- weathersit이 ‘Cl’로 시작하는 데이터를 추출해 보자.
AND weathersit LIKE 'Cl%'
- mnth이 ‘ma?’인 데이터를 확인해 보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
| # 응용해보자.
# hr이 0이고, mnth이 'ma?'이며 weekday에 'ur'이란 단어가 들어간 데이터를 추출해 보자.
mysql("""
SELECT *
FROM bike
WHERE hr = 0
AND mnth LIKE 'ma_'
AND weekday LIKE '%ur%'
LIMIT 5;
""")
|
| dteday | season | yr | mnth | hr | holiday | weekday | workingday | weathersit | temp | atemp | hum | windspeed | casual | registered | cnt |
|---|
| 0 | 2011-03-03 | spring | 2011 | Mar | 0 | 0 | Thursday | 1 | Clear | 0.24 | 0.1970 | 0.30 | 0.4627 | 3 | 10 | 13 |
|---|
| 1 | 2011-03-05 | spring | 2011 | Mar | 0 | 0 | Saturday | 0 | Cloudy | 0.28 | 0.2879 | 0.81 | 0.1045 | 4 | 15 | 19 |
|---|
| 2 | 2011-03-10 | spring | 2011 | Mar | 0 | 0 | Thursday | 1 | LightRain | 0.34 | 0.3182 | 0.00 | 0.2537 | 3 | 0 | 3 |
|---|
| 3 | 2011-03-12 | spring | 2011 | Mar | 0 | 0 | Saturday | 0 | Clear | 0.26 | 0.2727 | 0.60 | 0.1343 | 4 | 30 | 34 |
|---|
| 4 | 2011-03-17 | spring | 2011 | Mar | 0 | 0 | Thursday | 1 | Clear | 0.38 | 0.3939 | 0.66 | 0.2537 | 4 | 19 | 23 |
|---|
2-2. 별칭 사용하기 (Alias)
- 임시로 컬럼명을 변경할 때 별칭을 사용할 수 있음.
⚠️ WARNING
기존의 컬럼명이 물리적으로 변경되는 것은 아님.
- 별칭은 컬럼명 바로 뒤에 작성함.
- 컬럼명과 별칭 사이에는 AS를 작성합니다. AS는 생략할 수 있음.
- 특수문자, 대소문자, 공백을 사용하려면 따옴표(“ “ 또는 ‘ ‘)로 묶어주어야 함.
- SELECT, FROM절에서 사용함.
- 조인과 서브쿼리에서 테이블과 서브쿼리를 호출하기 위해 필수로 사용됨.
1
2
3
4
5
6
7
8
9
10
| # bike테이블 이름을 'b'로 지정하고, b 테이블의 temp와 hum를 추출해보자.
# hum에 'humidity'이란 별칭을 주어보자.
mysql("""
SELECT temp, hum AS 'humidity'
FROM bike AS b
LIMIT 5
""")
|
| temp | humidity |
|---|
| 0 | 0.24 | 0.81 |
|---|
| 1 | 0.22 | 0.80 |
|---|
| 2 | 0.22 | 0.80 |
|---|
| 3 | 0.24 | 0.75 |
|---|
| 4 | 0.24 | 0.75 |
|---|
3. 데이터 정렬과 중복 제거
3-1. 데이터 정렬하기(ORDER BY)
작성 순서
1
2
3
4
5
| SELECT
FROM
WHERE
ORDER BY
LIMIT
|
- SQL 구문 하단에 작성
- 데이터 추출 시, 테이블에 입력된 순서대로 출력되는 것이 기본
- 데이터가 오름차순, 내림차순으로 정렬된 형태가 필요할 때가 있음.
- 오름차순 : 컬럼명 뒤에
ASC 적기.
ORDER BY 컬럼명
ORDER BY 컬럼명 DESC
SELECT 구문에 지정한 컬럼의 순서를 입력해도 실행 가능
` [예시] ORDER BY 1 DESC, 2 `
- [해설]
SELECT에 작성한 첫번째 컬럼 기준으로 내림차순으로 정렬하고, 같은 값이 있다면 두번째 컬럼 기준으로 정렬됨.
1
2
3
4
5
6
7
8
9
10
11
| # dteday, temp, cnt 컬럼을 추출하고, cnt가 높은 순서대로 100개만 정렬 해보자.
# 순서가 같은 것이 있다면 temp가 낮은 순서대로 정렬하자
mysql("""
SELECT dteday, temp, cnt
FROM bike
ORDER BY cnt DESC, temp
LIMIT 100
""")
|
| dteday | temp | cnt |
|---|
| 0 | 2012-09-12 | 0.66 | 977 |
|---|
| 1 | 2012-09-20 | 0.64 | 976 |
|---|
| 2 | 2012-09-11 | 0.70 | 970 |
|---|
| 3 | 2012-09-10 | 0.62 | 968 |
|---|
| 4 | 2012-09-25 | 0.66 | 967 |
|---|
| ... | ... | ... | ... |
|---|
| 95 | 2012-06-11 | 0.80 | 831 |
|---|
| 96 | 2012-03-23 | 0.70 | 830 |
|---|
| 97 | 2012-07-11 | 0.80 | 830 |
|---|
| 98 | 2012-10-11 | 0.50 | 827 |
|---|
| 99 | 2012-05-31 | 0.76 | 827 |
|---|
100 rows × 3 columns
1
2
3
4
5
6
7
8
9
| # 컬럼 순서대로 입력하여 정렬
mysql("""
SELECT dteday, temp, cnt
FROM bike
ORDER BY 3 DESC, 2
LIMIT 100
""")
|
| dteday | temp | cnt |
|---|
| 0 | 2012-09-12 | 0.66 | 977 |
|---|
| 1 | 2012-09-20 | 0.64 | 976 |
|---|
| 2 | 2012-09-11 | 0.70 | 970 |
|---|
| 3 | 2012-09-10 | 0.62 | 968 |
|---|
| 4 | 2012-09-25 | 0.66 | 967 |
|---|
| ... | ... | ... | ... |
|---|
| 95 | 2012-06-11 | 0.80 | 831 |
|---|
| 96 | 2012-03-23 | 0.70 | 830 |
|---|
| 97 | 2012-07-11 | 0.80 | 830 |
|---|
| 98 | 2012-10-11 | 0.50 | 827 |
|---|
| 99 | 2012-05-31 | 0.76 | 827 |
|---|
100 rows × 3 columns
3-2. 중복제거하기(DISTINCT)
- 출력에서 고유한 값을 반환하기 위한 키워드
DISTINCT 키워드는 옆에 온 모든 컬럼을 고려하여 중복을 제거함.- 집계함수에도
DISTINCT를 사용할 수 있다.- [참고] 집계함수 :
COUNT, SUM, AVG, MAX, MIN
1
2
3
4
5
6
7
8
9
| # weekday, weathersit 컬럼에서 중복을 제거해보자.
mysql("""
SELECT DISTINCT weekday, weathersit
FROM bike
LIMIT 5;
""")
|
| weekday | weathersit |
|---|
| 0 | Saturday | Clear |
|---|
| 1 | Saturday | Cloudy |
|---|
| 2 | Saturday | LightRain |
|---|
| 3 | Sunday | Cloudy |
|---|
| 4 | Sunday | LightRain |
|---|
4. 조건문 활용하기(IF, CASE WHEN ~ THEN ~)
4-1. IF(조건, 참일 때 반환 값, 거짓일 때 반환 값)
- 단일 조건문
SELECT, WHERE 절에서 사용 가능합니다.IF 내에 조건을 줄 때 함수와 함께 사용 가능합니다.- [참고] pandasql에서는
if 함수를 iif로 표현합니다.
1
2
3
4
5
6
7
8
9
10
| #1990년 이전과 이후에 태어난 고객을 분리해 보자.
mysql("""
SELECT customerName AS "고객명"
, birthday AS "생년월일"
, iif(birthday >= '1990-01-01' , '이후', '이전') AS "1990년 기준"
FROM customers
""")
|
| 고객명 | 생년월일 | 1990년 기준 |
|---|
| 0 | Unai Bustinza | 1992. 2. 2 | 이후 |
|---|
| 1 | Igor Bubnjic | 1992. 7. 17 | 이후 |
|---|
| 2 | Gideon Jung | 1994. 9. 12 | 이후 |
|---|
| 3 | Alberto Aquilani | 1984. 7. 7 | 이전 |
|---|
| 4 | Nicola Strambelli | 1988. 9. 6 | 이전 |
|---|
| ... | ... | ... | ... |
|---|
| 118 | Jeroen Vanthournout | 1989. 6. 29 | 이전 |
|---|
| 119 | Raul Rusescu | 1988. 7. 9 | 이전 |
|---|
| 120 | Konstantinos Fortounis | 1992. 10. 16 | 이후 |
|---|
| 121 | Sami | 1988. 12. 18 | 이전 |
|---|
| 122 | Szymon Drewniak | 1993. 7. 11 | 이후 |
|---|
123 rows × 3 columns
4-2. CASE WHEN ~ THEN
- 다중 조건문
- 조건에 따른 값을 다르게 출력하고 싶은 경우 사용
- 조건에 따라 기존에 있는 컬럼을 이용해 새로운 컬럼을 만들기도 함
- 작성 순서
1
2
3
4
| CASE WHEN '조건1' THEN '조건1 반환값'
WHEN '조건2' THEN '조건2 반환값'
ELSE '만족하는 조건이 없을 때 반환 값'
END
|
- CASE문은
WHEN의 조건을 순차적으로 체크하고 조건이 충족되면 THEN에 명시된 값을 반환함.WHEN과 THEN은 한 쌍으로 존재함.WHEN과 THEN은 여러 개가 존재할 수 있음.ELSE는 앞의 조건들에 모두 해당하지 않았을 때 정해줄 기본 값이다.ELSE가 없고 조건에 맞는 값이 없으면 NULL값을 반환함.
💡 TIP
CASE WHEN, THEN, END를 먼저 작성한 후에 조건을 적으면 문법 실수를 줄일 수 있음.
1
2
3
4
5
6
7
8
9
10
11
| # 고객 한도를 5만원, 십만원 단위로 분리해 보자.
mysql("""
SELECT customerName, creditLimit
, CASE WHEN creditLimit <= 50000 THEN '5만원이하'
WHEN creditLimit <= 100000 THEN '10만원이하'
ELSE '10만원초과'
END AS '고객한도분리'
FROM customers
""")
|
| customerName | creditLimit | 고객한도분리 |
|---|
| 0 | Unai Bustinza | 21000 | 5만원이하 |
|---|
| 1 | Igor Bubnjic | 71800 | 10만원이하 |
|---|
| 2 | Gideon Jung | 117300 | 10만원초과 |
|---|
| 3 | Alberto Aquilani | 118200 | 10만원초과 |
|---|
| 4 | Nicola Strambelli | 81700 | 10만원이하 |
|---|
| ... | ... | ... | ... |
|---|
| 118 | Jeroen Vanthournout | 60300 | 10만원이하 |
|---|
| 119 | Raul Rusescu | 43300 | 5만원이하 |
|---|
| 120 | Konstantinos Fortounis | 85100 | 10만원이하 |
|---|
| 121 | Sami | 110000 | 10만원초과 |
|---|
| 122 | Szymon Drewniak | 88900 | 10만원이하 |
|---|
123 rows × 3 columns
5. 데이터 타입 변환하기 (CAST)
5-1. CAST
CAST(컬럼 AS 변환할 타입)
- 변환할 타입에는 int, float, varchar(문자열), datetime(날짜) 등이 있음.
- [예시]
SELECT CAST(14.58 AS int) //14(결과값)
1
2
3
| # 데이터 bike의 정보를 확인하자.
bike.info()
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| <class 'pandas.core.frame.DataFrame'>
RangeIndex: 17379 entries, 0 to 17378
Data columns (total 16 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 dteday 17379 non-null object
1 season 17379 non-null object
2 yr 17379 non-null int64
3 mnth 17379 non-null object
4 hr 17379 non-null int64
5 holiday 17379 non-null int64
6 weekday 17379 non-null object
7 workingday 17379 non-null int64
8 weathersit 17378 non-null object
9 temp 17379 non-null float64
10 atemp 17379 non-null float64
11 hum 17379 non-null float64
12 windspeed 17379 non-null float64
13 casual 17379 non-null int64
14 registered 17379 non-null int64
15 cnt 17379 non-null int64
dtypes: float64(4), int64(7), object(5)
memory usage: 2.1+ MB
|
yr은 int 타입!
1
2
3
4
5
6
7
8
| # 먼저 2011년 데이터 5개만 추출해 보자.
mysql("""
SELECT *
FROM bike
WHERE yr = 2011
LIMIT 5
""")
|
| dteday | season | yr | mnth | hr | holiday | weekday | workingday | weathersit | temp | atemp | hum | windspeed | casual | registered | cnt |
|---|
| 0 | 2011-01-01 | spring | 2011 | Jan | 0 | 0 | Saturday | 0 | Clear | 0.24 | 0.2879 | 0.81 | 0.0 | 3 | 13 | 16 |
|---|
| 1 | 2011-01-01 | spring | 2011 | Jan | 1 | 0 | Saturday | 0 | Clear | 0.22 | 0.2727 | 0.80 | 0.0 | 8 | 32 | 40 |
|---|
| 2 | 2011-01-01 | spring | 2011 | Jan | 2 | 0 | Saturday | 0 | Clear | 0.22 | 0.2727 | 0.80 | 0.0 | 5 | 27 | 32 |
|---|
| 3 | 2011-01-01 | spring | 2011 | Jan | 3 | 0 | Saturday | 0 | Clear | 0.24 | 0.2879 | 0.75 | 0.0 | 3 | 10 | 13 |
|---|
| 4 | 2011-01-01 | spring | 2011 | Jan | 4 | 0 | Saturday | 0 | Clear | 0.24 | 0.2879 | 0.75 | 0.0 | 0 | 1 | 1 |
|---|
1
2
3
4
5
6
7
| # 현재 int 타입인 yr을 varchar 타입으로 변경해 보자.
mysql("""
SELECT CAST(yr AS varchar) AS yr
FROM bike
LIMIT 5
""")
|
| yr |
|---|
| 0 | 2011 |
|---|
| 1 | 2011 |
|---|
| 2 | 2011 |
|---|
| 3 | 2011 |
|---|
| 4 | 2011 |
|---|
1
2
3
4
5
6
7
8
| # yr을 문자열타입으로 변환한 후 2011년 데이터 5개만 추출해 보자.
mysql("""
SELECT CAST(yr AS varchar) AS yr
FROM bike
WHERE CAST(yr AS varchar)= '2011'
LIMIT 5
""")
|
| yr |
|---|
| 0 | 2011 |
|---|
| 1 | 2011 |
|---|
| 2 | 2011 |
|---|
| 3 | 2011 |
|---|
| 4 | 2011 |
|---|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| <class 'pandas.core.frame.DataFrame'>
RangeIndex: 17379 entries, 0 to 17378
Data columns (total 16 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 dteday 17379 non-null object
1 season 17379 non-null object
2 yr 17379 non-null int64
3 mnth 17379 non-null object
4 hr 17379 non-null int64
5 holiday 17379 non-null int64
6 weekday 17379 non-null object
7 workingday 17379 non-null int64
8 weathersit 17378 non-null object
9 temp 17379 non-null float64
10 atemp 17379 non-null float64
11 hum 17379 non-null float64
12 windspeed 17379 non-null float64
13 casual 17379 non-null int64
14 registered 17379 non-null int64
15 cnt 17379 non-null int64
dtypes: float64(4), int64(7), object(5)
memory usage: 2.1+ MB
|
-> 하지만 변경은 되지 않았다. 왜?
ℹ️ INFO
SQL에서 CAST() 함수는 조회(Query) 시점에서만 데이터를 변환하는 것 변경된 타입으로 결과를 보여줄 뿐, 원본 테이블 안에 있는 데이터 자체는 변하지 않음.