🖇 쿼리문 작성과 데이터 필터링
🖇 비교 연산자
🖇 논리 연산자
🖇 산술 연산자
데이터는 저장만으로는 의미가 없다.
진짜 중요한 것은 어떤 데이터를 어떻게 불러오고, 어떻게 추려낼 것인가 이다.
SQL은 데이터를 분석하는 가장 기본적이고 강력한 도구이다.
이 글에서는 JupyterNotebook 환경에서 pandasql을 사용해 파이썬 환경에서 SQL 쿼리문을 실습해보고
SELECT, WHERE, AND, OR, IN, BETWEEN 등 데이터 필터링에 자주 쓰이는 SQL 구문을 직접 다뤄보았다.
🖇 쿼리문 작성과 데이터 필터링
실습을 통해 다룰 데이터를 설명하겠다.
해당 데이터는 자전거 대여 수요 분석에 많이 사용되는 유명한 공개 데이터셋인 Capital Bikeshare 데이터를 기반으로 만들어진 전처리된 버전이다.
bike 테이블(bike_dataset.csv)은 아래와 같은 구조로 되어있으며
dteday, season, yr, mnth, hr, holiday, weekday, workingday, weathersit, temp, atemp, hum, windspeed, casual, registered, cnt 는
각각 날짜, 계절, 연도, 월, 시간, 휴일 여부, 요일, 근무일 여부, 날씨, 온도, 체감온도, 습도, 풍속, 비회원 대여 수, 회원 대여 수, 전체 대여 수를 나타낸다.
먼저
pandsql 을 사용하기 위해 필요한 라이브러리와 데이터를 불러와야 한다.
/* 파이썬의 표준 라이브러리 os(Operating System)
파일 경로를 조립하거나, 현재 디렉토리 확인
os.path.join()이나 os.getcwd() 같은 함수 사용 가능 */
import os/* 파이썬 기반 sql 사용 -> pandas 가져오기
데이터프레임(DataFrame)을 활용한 데이터 분석에 가장 많이 사용되는 라이브러리 */
import pandas as pd /* sql을 사용하기 라이브러리 가져오기
pandas의 데이터프레임(테이블)에 SQL 문법으로 질의(Query)를 날릴 수 있게 해준다
pandasql 라이브러리에서 sqldf 함수 가져오기 */
from pandasql import sqldf/* pandasql을 전역 환경으로 지정
-> 나중에 mysql("SELECT * FROM bike")처럼 SQL 문법으로 손쉽게 질의할 수 있게 하기 위함 */
mysql = lambda q: sqldf(q, globals())/* 데이터 불러오기
데이터 파일들이 들어 있는 폴더 경로를 변수로 저장
절대 경로: 루트 디렉토리(/)를 기준으로 전체 경로를 나타낸다 */
data_dir = '/aiffel/aiffel/sql_basic'/* 'bike_dataset.csv' 파일을 bike라는 데이터프레임으로 읽어오기
os.path.join()은 경로를 시스템에 맞게 자동으로 연결한다
pd.read_csv()는 CSV 파일을 읽어 데이터프레임으로 변환한다 */
bike = pd.read_csv(os.path.join(data_dir, 'bike_dataset.csv'))
...쿼리문 작성하기(SELECT, FROM)
어떤 컬럼을 어떤 테이블에서 가져올 것인지를 작성하는 구문
테이블을 지정하여(FROM) 데이터를 조회(SELECT)할 수 있다.
전체 컬럼 조회하기
SELECT구문에*기호- 해당 테이블의 모든 컬럼을 조회
SELECT 컬럼(*)
FROM 테이블명-- bike 테이블에서 전체 컬럼 조회하기
SELECT *
FROM bike
-- bike 테이블에서 날짜, 계절, 날씨 컬럼을 추출하기
SELECT dteday, season, weathersit
FROM bike
전체 데이터 필터링하기(WHERE)
WHERE 조건을 줘서 데이터를 확인한다.
- SQL에서 쿼리 결과 필터링은 중요한 기술
WHERE는FROM절 다음에 위치 → 순서가 중요WHERE절에는 연산자를 같이 사용해야 한다.
SELECT 원하는 컬럼
FROM 테이블명
WHERE 조건절-- 2012년을 기준으로 데이터 확인하기
SELECT *
FROM bike
WHERE yr = 2012
🖇 비교 연산자
비교 연산자
| 연산자 | 의미 |
|---|---|
A = 'value' | A와 'value'는 같다 |
A != 'value' | A와 'value'는 같지 않다 |
A > n | A가 n보다 크다 |
A >= n | A가 n보다 크거나 같다 |
A < n | A가 n보다 작다 |
A <= n | A가 n보다 작거나 같다 |
=
- 특정 데이터를 선택할 때 사용
-- 2012년을 기준으로 날짜, 계절, 날씨 컬럼 확인하기
-- LIMIT으로 전체 데이터 중 100개만 확인하기
-- -> sql 구문 가장 하단에 작성해야 한다
SELECT dteday, season, weathersit
FROM bike
WHERE yr = 2012
LIMIT 100
-- 계절이 봄인 전체 데이터를 확인하기
SELECT *
FROM bike
WHERE season = 'spring'
!= 또는 <>
- 같지 않다'는 뜻
- 특정 데이터를 제외하여 선택할 때 사용
-- 계절이 봄이 아닌 전체 데이터 확인하기
SELECT *
FROM bike
WHERE season != 'spring'
------------------------
SELECT *
FROM bike
WHERE season <> 'spring'
>, >=
- 컬럼을 기준으로 데이터 값보다 큰 경우에 사용
- 숫자 타입의 금액이나 날짜 타입의 날짜를 데이터 값으로 사용할 때
-- 23시 이후 대여건에 대한 전체 데이터 확인하기
SELECT *
FROM bike
WHERE hr >= 23
LIMIT 100
<, <=
- 컬럼을 기준으로 데이터 값보다 작은 경우에 사용
- 숫자 타입의 금액이나 날짜 타입의 날짜를 데이터 값으로 사용할 때
-- 온도가 0.2 미만인 전체 데이 추출하기
SELECT *
FROM bike
WHERE temp < 0.2
LIMIT 100
-- 전체 대여 건수가 100건보다 작은 데이터를 추출하기
SELECT *
FROM bike
WHERE cnt < 100
🖇 논리 연산자 사용하기
여러가지 조건을 주고 싶을 때 사용
논리 연산자를 계속 추가하면 복잡한 조건도 쉽게 필터링할 수 있다.
AND
- 앞의 조건과 뒤의 조건 둘 다를 만족해야 참(True)
- 조건1과 조건2 둘 다 만족한 값을 필터링한다.
- 조건을 계속 추가해야할 때 사용
WHERE [조건1] AND [조건2]-- bike 테이블에서 2011년도 겨울 시즌의 데이터 확인하기
SELECT *
FROM bike
WHERE yr = 2011
AND season = 'winter'
LIMIT 100
-- bike 테이블에서 2011년도 봄시즌의 22시에 발생한 데이터 확인하기
SELECT *
FROM bike
WHERE yr = 2011
AND season = 'spring'
AND hr = 22
OR
- 앞의 조건을 만족하거나 뒤의 조건을 만족하거나, 한쪽이라도 만족해야 참(True)
- 조건1과 조건2 둘 중 하나라도 만족시킨 값을 필터링한다.
- 조건을 계속 추가해야 할 때 사용
WHERE [조건1] OR [조건2]-- bike 테이블에서 2011년 또는 봄시즌의 데이터 확인하기
SELECT *
FROM bike
WHERE yr = 2011
OR season = 'spring'
🧐 Q. hr이 10이거나 11인 행을 추출하고 싶은 경우 맞게 작성된 쿼리는 무엇일까?
1번. WHERE hr = 10 OR hr = 11
2번. WHERE hr = 10 OR 11
정답은 1번
-> 2번 'WHERE hr = 10 OR 11' 같은 경우는 원하는 값을 추출할 수 없다.
-> 11은 단순한 숫자 값이지만, 이 조건에서는 항상 참(True)으로 평가되어 모든 행을 반환하게 된다.
-> 0은 False,그 외의 모든 숫자를 참(True)으로 간주하기 때문이다.IN
- 컬럼이 어떤 값들의 집합에 속할 경우 사용
- 조회하고자 하는 데이터 값이 여러 개일 때 사용
=과 유사하지만=은 데이터 값 하나를 조건으로 줄 수 있는 반면,IN은 데이터 값을 여러 개의 list로 지정할 수 있다.- 여러 개 값의 리스트 중 하나의 값이라도 만족하면 해당하는 결과값을 출력한다.
-- 1월(Jan), 3월(Mar), 5월(May)에 대한 전체 데이터 추출하기
SELECT *
FROM bike
WHERE mnth IN ('Jan', 'Mar', 'May')
-- cf. OR로도 동일한 데이터를 추출할 수 있지만
-- 하나의 컬럼에서 같은 인자를 조건으로 줄 때 IN을 사용하는 것이 효율적이다
SELECT *
FROM bike
WHERE mnth = 'Jan'
OR mnth = 'Mar'
OR mnth = 'May'
NOT
- 논리 연산자의 의미를 반전시킨다.
- 조건을 부정으로 만들어 부정 연산자라고도 부른다.
- 선언한 조건에 대해 반대 결과를 반환
-- 1월(Jan), 3월(Mar), 5월(May)이 아닌 전체 데이터 추출하기
SELECT *
FROM bike
WHERE mnth NOT IN ('Jan', 'Mar', 'May')
-- 1월(Jan), 3월(Mar), 5월(May)이 아니고, 전체 대여 건수가 10보다 큰 전체 데이터 추출하기
SELECT *
FROM bike
WHERE mnth NOT IN ('Jan', 'Mar', 'May')
AND cnt > 10
NULL
- 값이 지정되지 않아 알 수 없는 값
- NULL은 0이나 공백과는 다르다
- 0 : 숫자
- '' "(공백) : 문자열
-- 날씨 데이터의 값이 NULL값인 데이터 추출하기
SELECT *
FROM bike
WHERE weathersit IS NULL
-- 날씨 데이터의 값이 NULL값이 아닌 전체 데이터 추출하기
SELECT *
FROM bike
WHERE weathersit IS NOT NULL
Between A and B
- A와 B 사이에 값이 존재할 때 사용
- [주의] A와 B를 포함한다는 점!
- 두 값의 범위에 해당하는 행을 출력할 때 사용
- 주로 날짜 사이 간격을 줄 때 자주 사용하는 편이다.
- 최소값과 최대값을 지정한 값의 범위 내에 있는 값들을 검색하기 위해 사용
e.g.,number Between 3 AND 5와number>=3 AND number<=5는 동일한 결과를 출력한다.
-- 2011년 3월 30일 ~ 31일 사이의 데이터를 확인하기
SELECT *
FROM bike
WHERE dteday Between '2011-03-30' and '2011-03-31'
-- cf. 비교 연산자 사용하여 확인하기
SELECT *
FROM bike
WHERE dteday >= '2011-03-30'
AND dteday <= '2011-03-31'
🖇 산술 연산자 사용하기
데이터값을 계산하고자 할 때 사용
+,-,*,/등이 있다.- 우선 순위:
()>*/>+-
- 우선 순위:
SELECT,WHERE문에서 사용- 문자열 혹은 날짜타입 데이터에 산술연산자를 적용하면 원하지 않는 값을 얻을 수 있다.
- 실무에서 많이 쓰이며 비즈니스를 위한 다양한 리포트를 작성할 때 필수적으로 사용된다.
# dteday 값에 2를 더하고, weekday에 3을 더하기
SELECT dteday,
dteday + 2,
weekday,
weekday + 3
FROM bike
=> DBMS(Database Management system)에 따라
실행했을 때 에러가 날 수 있기 때문에
날짜나 문자 타입의 경우에는 산술 연산자를 사용하지 않는 것이 좋다. [주의]
이때 dteday + 2, weekday + 3는
산술 연산자를 사용해 계산해 출력한 결과일 뿐
새로운 컬럼을 만들어 데이터베이스에 추가한 것이 아니다.
# 온도(temp)에 +2, -2, *2, /2 추가하기
SELECT temp,
temp + 2,
temp - 2,
temp * 2,
temp / 2
FROM bike
AI에 관심을 가지고, 데이터로 가치를 만들어 나가는 과정을 기록합니다.