SQL 쿼리 문법을 활용하여 데이터 분석을 진행하기(1)

SQL과 RDB란 무엇인가

 데이터 분석을 공부할 때 SQL은 기본이며 실무에서도 매우 중요하다. SQL을 학습하면 원하는 형태로 데이터를 가져올 수 있다. 그리고 효율적으로 데이터를 가져올 수 있으며 간단한 데이터 분석을 수행할 수 있다. 

  데이터베이스는 데이터를 쌓고 관리하는 방법에 따라 크게 두 가지로 구분되는데 바로 관계형 데이터베이스(RDBMS)와 비관계형 데이터베이스(NoSQL)이다. 

구분	관계형 데이터베이스	비관계형 데이터베이스
사용 목적	- 정형데이터: 테이블/행/열로 이루어진 형태 - Transaction 처리: 데이터의 일관성과 무결성이 중요한 서비스 - 예) 은행의 금융서비스, 쇼핑몰 주문/정산	- 비정형/반정형 데이터: JSON, XML 등 다양한 형태 - 대량의 데이터와 빠른 읽기/쓰기: 대규모/분산 데이터 처리가 필요한 서비스에서 사용
데이터 일관성	- ACID(Atomicity, Consistency, Isolation, Durability) 특성을 준수하여 데이터 일관성 보장	- DB마다 다르고, ACID 특성을 보장하지 않는 경우도 있음 - 중복이 발생
스키마	- 고정된 스키마 사용, 데이터 구조 변경이 어려울 수 있음	- 스키마 없는(또는 유연한 스키마) 데이터 모델을 사용하여 데이터 구조를 자유롭게 변경할 수 있음
쿼리 언어	- SQL(Structured Query Language)을 사용하여 데이터를 가져오고 관리	- DB마다 고유한 쿼리 언어 또는 API를 제공하며, SQL과는 다른 방식으로 데이터를 질의
수평 확장	- 수평 확장이 복잡하고 어려움	- 수평 확장이 상대적으로 용이

 

데이터 조회: SELECT, FROM, WHERE

SELECT name, price
FROM products
WHERE price > 5000

 위 쿼리문에서 SELECT는 어떤 열을 가져올지, FROM은 가져올 테이블은 무엇인지, WHERE은 어떤 조건으로 행을 가져올지를 의미한다.

 

SELECT *
FROM products
WHERE price < 2000

 SELECT 뒤에 * 를 쓰면 모든 열을 선택한다. 단, 테이블의 크기가 매우 클 경우 불필요하게 많은 리소스를 사용하기 때문에 미리 스키마를 확인하여 필요한 열만 사용하는 것이 권장된다. 

 

SELECT product_id, category
FROM products
WHERE 1=1
AND category = '주방용품'

 보통 WHERE 옆에 1=1 과 같은 항상 참인 조건을 써놓곤 한다. 왜냐하면 WHERE 옆에는 항상 조건이 있어야 하므로 다른 조건들을 지우거나 살릴 때 계속 WHERE 옆에 조건을 추가해야 되는 번거로움이 있기 때문이다. 그래서 WHERE 옆에 항상 참인 조건이 있으면 AND 옆에 있는 조건만 주석 처리하는 방식으로 조건을 지울 수 있다.    

 

SELECT name, managing
FROM managers
WHERE 1=1
AND managing = '키즈'

 FROM 옆은 불러올 테이블 이름이며 조건에 따라 managing이 키즈인 행만 가져온다.

 

SELECT product_id, name
FROM products
WHERE 1=1
AND price < 30000
LIMIT 2

 LIMIT 2 는 상위 2개 행만 필터링하여 가져오게 한다. LIMIT는 사용하는 DB종류에 따라 구문이 다소 상이하다. 

 

 

비교 연산자와 논리 연산자

SELECT product_id, category
FROM products
WHERE 1=1
AND category = '주방용품'

 = 은 양쪽 값을 비교한다. 위의 쿼리 경우 category가 주방용품인 행만 불러온다. 

 

SELECT name, price
FROM products
WHERE price > 5000

 부등호도 등호와 마찬가지로 값을 비교하여 조건에 맞는 행을 불러온다.

 

SELECT product_id, name
FROM products
WHERE 1=1
AND category <> '키즈'

 <> 또는 != 은 같지 않다는 뜻이며 위의 경우 category가 키즈가 아닌 행을 불러온다. 

 

SELECT name, price
FROM products
WHERE 1=1
AND price < 10000
AND (category = '주방용품' OR name = '손목보호대')

 논리연산자 AND와 OR은 앞서 배운 엑셀과 같으며 괄호가 있는 조건을 먼저 처리한다.

 

SELECT *
FROM products
WHERE 1=1
AND NOT category = '키즈'

 NOT은 조건을 뒤집으며 category가 키즈가 아닌 행을 불러온다.

 

SELECT product_id, name
FROM products
WHERE 1=1
AND category IN ('스포츠', '디지털', '식품')

 IN은 괄호 안에 있는 값들을 필터링한다. 반대로 NOT IN은 괄호 안에 있는 값이 아닌 값들을 필터링한다.  

 

SELECT product_id, name
FROM products
WHERE 1=1
AND name LIKE '%보호%'

 LIKE는 문자열의 패턴을 검색하는 연산자이다. %는 모든 문자열이라는 의미이다. 예시처럼 앞뒤로 %가 붙으면 앞뒤에 어느 문자가 와도 보호가 있는 조건을 필터링한다.

 

SELECT product_id, name
FROM products
WHERE 1=1
AND name LIKE '_그릇'

 _ 는 하나의 문자열을 의미한다. 예시에서 밥그릇을 해당되지만 사기그릇은 해당되지 않는다.

 

SELECT name, price
FROM products
WHERE 1=1
AND price between 5000 and 15000

 between 은 양쪽 끝 값을 포함한 사이의 값을 추출한다. 숫자뿐만 아니라 문자열에서도 쓸 수 있다.

 

SELECT *
FROM orders
WHERE 1=1
AND purchase_date IS NULL

 IS NULL은 값이 비어있는지를 확인하는 연산자이다. IS NOT NULL을 활용하여 비어있지 않은 값만 추출할 수 있다. 

 

 

정렬과 집계

SELECT price, name
FROM products_v2
WHERE 1=1
ORDER BY 1 DESC, 2

 ORDER BY는 값을 오름차순으로 정렬시켜주며 뒤에 DESC를 쓰면 내림차순으로 정렬한다. column명을 입력해 줘도 되지만 예시와 같이 숫자로 하면 1은 결괏값 첫 번째 column인 price가 내림차순으로 정렬되고 2는 결괏값 두 번째 column인 name이 가나다순으로 정렬된다. 

 

SELECT sum(price) as sum_price, avg(price) as avg_price
FROM products_v2
WHERE 1=1

 sum(price)는 price의 합계이며 as 뒤의 이름으로 보여진다. avg는 평균값이다. 

 

SELECT count(1) as cnt
FROM products_v2
WHERE 1=1

count는 조건에 맞는 행의 수를 반환한다. 조건이 항상 참이므로 전체 행의 수가 반환된다. count(1) 또는 count(*)은 null값이 있어도 카운트에 포함한다. 만약 count(price)이면 price의 null값을 제외하고 카운트한다. 또, count(distinct price)와 같이 distinct를 넣으면 중복을 제외 후 카운트한다. 

 

SELECT category, sale_yn, avg(price) as avg_price
FROM products_v3
WHERE 1=1
GROUP BY 1, 2
HAVING avg_price > 3000

GROUP BY는 해당 열을 그룹화하여 나타내주며 2개 이상도 가능하다. HAVING은 집계 후 결과 값을 필터링해 준다. category와 sale_yn에 따라 그룹화 한 뒤 그룹들의 평균값 중 3000 이상만 나타내준다. 

 

 

기초 SQL 함수

SELECT CONCAT(category, '-', name) as comb_name
FROM products
WHERE 1=1
LIMIT 3

 CONCAT은 문자열을 합치는 함수이다. category와 name의 값을 - 으로 연결한 뒤 comb_name으로 상위 3개 값이 보인다.

 

SELECT SUBSTRING(name, 1, 2) as sub_string
FROM products
WHERE 1=1

 SUBSTRING은 문자열 부분을 추출하는 함수이다. 괄호 안 첫 번째는 추출할 열, 두 번째는 몇 번째 문자부터 추출할지, 세 번째는 추출할 문자 길이이다. SUBSTRING 대신 LEFT(name, 3)이라 한다면 왼쪽에서 3번째까지 추출, RIGHT(name, 4)라면 오른쪽에서 4번째까지 추출한다. 추가적으로 UPPER, LOWER는 각각 영어 대문자와 소문자로 변환하는 함수이다. 

 

SELECT CHAR_LENGTH(name) as name_length
FROM products

 CHAR_LENGTH는 문자열의 길이를 반환하는 함수이다. 

 

SELECT ROUND(avg(price)) as avg_price
FROM products_v2
WHERE 1=1

 ROUND 함수는 값을 반올림한다. 참고로 올림은 CEIL이고 내림은 FLOOR이며 TRUNCATE는 일정 자릿수 이하를 잘라서 버리는 함수이다. 

 

SELECT ABS(-1,0) as res, ABS(1) as res_2, ABS(price) as abs_price
FROM products_v2
WHERE 1=1
LIMIT 3

 ABS는 절댓값을 나타내주는 함수이다.

 

SELECT MOD(price, product_id) as mods
FROM products_v2

 MOD는 나머지를 구하는 함수이다. POW는 두 인자(x, y)를 받아 x의 y제곱을 하며 GREATEST는 최댓값,  LEAST는 최솟값이다. 

 

SELECT COALESCE(purchase_date, 'Unknown') as purchase_date
FROM orders

 COALESCE는 병합하다는 뜻으로 주로 null값을 채워 넣기 위해 사용한다. 

 

 

(Optional) DDL, DML

 DDL(Data Definition Language)는 데이터베이스 구조를 정의하고 관리한다. DDL은 크게 CREATE(테이블/뷰/인덱스 생성), ALTER(테이블/뷰/인덱스 구조 변경), DROP(테이블/뷰/인덱스 삭제)가 있다. DML(Data Manipulation Language)는 데이터 쿼리하거나 조작하며 SELECT(질의), INCERT(삽입), UPDATE(수정), DELETE(삭제)할 수 있다. 

 

공부하며 어려웠던 내용

 SQL 기본 구문부터 배우기 시작했다. SQL을 처음 배워 낯설었지만 구문 자체는 타 프로그래밍 언어와 비슷해 비교적 쉽게 이해하였다. DDL, DML은 앞으로 공부해 가면서 이해하기를 시도해야겠다.