[MySQL] SQL로 맛보는 데이터 전처리 분석(2-1)

본 내용은 [멀티캠퍼스] 데이터 분석&데이터 엔지니어링 취업캠프 28회차에서 실시한 수업 내용 중 일부입니다.

 

교재 홍보 (본 과정의 SQL 교재)

교재 구매: https://www.yes24.com/Product/Goods/86544423

SQL로 맛보는 데이터 전처리 분석 - 예스24

 

2장

SQL이란?

SQL은 Structured Query Language의 약어로, 데이터베이스 관리 시스템 (DBMS)과 상호 작용하고 데이터베이스에서 데이터를 관리하기 위한 표준화된 프로그래밍 언어이다. SQL은 데이터의 조회, 삽입, 수정, 삭제 및 데이터베이스 관리 작업을 수행하는 데 사용된다. SQL은 데이터베이스 시스템의 종류에 관계없이 널리 사용되며, 관계형 데이터베이스 시스템 (RDBMS)에서 특히 많이 사용된다.

 

그 중에서 해당 포스팅에서 설명할 내용은 데이터 조회 기능을 하는 SELECT문이다.

 

SELECT절이란?

SQL을 사용하여 데이터베이스에서 원하는 정보를 조회할 수 있다. SELECT 문을 사용하여 특정 열 또는 테이블 전체에서 데이터를 검색하고, 조건을 지정하여 검색 결과를 필터링할 수 있다.

 

데이터 분석가에게는 SQL의 여러 기능 중 가장 자주 사용하는 기능으로 알려져 있다.

 

해당 포스팅에서는 classicmodels 데이터베이스를 사용하여 실습을 진행한다. 자세한 내용은 다음 링크 참고: https://hsyhrae.tistory.com/30

[MySQL] 실습하기 전 데이터 다운로드 및 불러오기

 

형태:

SELECT 컬럼, 계산 값

FROM 테이블 명

WHERE 조건

GROUP BY 컬럼(그룹화)

HAVING 그룹화에 사용되는 조건

ORDER BY 컬럼(순서 정렬)

 

실습에 앞서 우리가 다룰 데이터의 ERD를 먼저 숙지하자.

 

1) 컬럼 조회: SELECT 컬럼명 FROM DB명. 테이블명; 

SELECT 
	customerNumber 
FROM 
	classicmodels.customers
;

 

2)  집계 함수: SELECT 집계함수 FROM DB명. 테이블명;

• 집계 함수 종류
  • COUNT: 지정된 컬럼 또는 행의 수를 계산합니다.
  • SUM: 지정된 컬럼의 합계를 계산한다.
  • AVG: 지정된 컬럼의 평균 값을 계산한다.
  • MIN: 지정된 컬럼의 가장 작은 값을 찾는다.
  • MAX: 지정된 컬럼의 가장 큰 값을 찾는다.

일반적으로 집계함수는 계산할때 NULL값, 결측치를 포함하지 않는다.

SELECT 
	COUNT(checkNumber) 
FROM 
	classicmodels.payments
;

 

 

 

3) * : SELECT * FROM DB명.테이블명;

해당 테이블의 모든 컬럼을 출력한다.

SELECT 
	* 
FROM 
	classicmodels.payments
;

 

여러 컬럼 조회: SELECT 컬럼1, 컬럼2 FROM DB명, 테이블명;

','으로 구분한다.

SELECT 
	productname, productline
FROM 
	classicmodels.products
;

 

4) AS: SELECT 컬럼명1 AS 변경 컬럼명 FROM DB명.테이블명;

컬럼이 너무 길거나 의미가 명확하지 않는 등 컬럼명을 변경하고자 할 때 사용한다.

SELECT 
	COUNT(productcode) AS n_products
	, COUNT(productcode) n_products
    	, COUNT(productcode)
    	, COUNT(productcode) AS 갯수
FROM 
	classicmodels.products
;

5) DISTINCT: SELECT DISTINCT 컬럼명 FROM DB명.테이블명;

컬럼에서 중복을 제외하고 조회한다.

SELECT
	DISTINCT ordernumber
FROM
	classicmodels.orderdetails
;

 

FROM절이란?

데이터를 쿼리하는 데 사용할 데이터 소스, 즉 하나 이상의 테이블 또는 뷰를 지정하는 부분이다. FROM 절은 SQL 쿼리에서 필수적인 구문 중 하나이며, 데이터를 검색하거나 조작하기 위해 사용할 데이터 소스를 정의한다.

 

사용법1

SELECT 계산식 또는 컬럼명 FROM DB명.테이블명;

 

사용법2

USE DB명;

SELECT 계산식 또는 컬럼명 FROM DB명.테이블명;

매번 DB명을 입력하는 것은 번거롭기 때문에 이후 쿼리에서는 USE를 사용했다고 가정한다.

 

WHERE절이란?

데이터베이스에서 특정 조건을 만족하는 행(레코드)을 필터링하는 데 사용된다. WHERE 절은 SQL 쿼리에서 선택적으로 사용되며, 주로 데이터를 검색하거나 조작할 때 특정 조건을 적용하고 원하는 결과만 반환하기 위해 활용된다.

 

1) BETWEEN: SELECT * FROM WHERE 컬럼명 BETWEEN 시작점 AND 끝점;

시작점과 끝점 사이의 값들을 가진 행만 조회한다.

SELECT *
FROM orderdetails
WHERE priceeach BETWEEN 30 AND 50;

 

2) 대소관계 연산자: SELECT * FROM WHERE 컬럼명 대소관계 연산자 특정값;

• 대소관계 연산자
  • =: 동일
  • >: 초과
  • >=: 이상
  • <: 미만
  • <=: 이하
  • <>: 같지 않다.

priceeach가 30 이하인 행들만 조회

SELECT *
FROM orderdetails
WHERE priceeach < 30;

 

priceeach가 30 이하인 것의 수를 세기

SELECT COUNT(*)
FROM orderdetails
WHERE priceeach <= 30;

 

3) IN: SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 IN (값1, 값2);

IN을 배우는 것에 앞서 AND와 OR에 대해 먼저 알아보자.

AND: 서로 다른 여러 조건을 '그리고'로 묶어준다. 즉, 모든 조건을 만족하는 데이터만 조회한다.

SELECT
    customernumber
    , country
    , city
FROM customers
WHERE country = 'USA'
	  AND city = 'NYC'
;

 

OR: 서로 다른 여러 조건을 '또는'으로 묶어준다. 즉, 한 조건이라도 만족한다면 만족하는 데이터를 조회한다.

SELECT 
    customernumber
    , country
FROM customers
WHERE country = 'USA' OR country = 'Canada' OR country = 'France'
;

 

위의 OR예제에서도 알 수 있듯 조건이 늘어날 수록 쿼리를 작성하는 것이 번거롭다. 

따라서 이때 IN을 쓴다면 편하게 조회할 수 있다.

SELECT 
    customernumber
    , country
FROM customers
WHERE country IN ('USA', 'Canada', 'France')
;

더 편리하게 조회 가능

 

추가로 IN 앞에 NOT를 붙이면 괄호 안에 있는 것을 제외한 데이터를 조회한다.

SELECT 
    customernumber
    , country
FROM customers
WHERE country NOT IN ('USA', 'Canada', 'France')
;

 

4) NULL: SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 IS NULL;

특정 컬럼의 결측치인 데이터를 조회한다.

SELECT 
    employeenumber
    , reportsto
FROM employees
WHERE reportsto IS NULL
;

 

추가로 IS NOT을 붙이면 결측치가 아닌 데이터만 조회한다.

SELECT 
    employeenumber
    , reportsto
FROM employees
WHERE reportsto IS NOT NULL
;

 

5) SELCT * FROM  테이블명 WHERE 컬럼명 LIKE '%문자%';

특정 문자가 포함된 데이터를 조회한다. '%'는 모든 문자를 의미한다.

MySQL는 일반적으로 대소문자를 구분하지 않는다.

'%문자'는 앞에 어떤 문자가 와도 된다는 것이고, 특정 문자로 끝이 나야한다.

SELECT 
	addressline1
FROM customers
WHERE addressline1 LIKE '%ST';

ST로 끝나는 데이터가 없다.

 

'문자%'는 뒤에 어떤 문자가 와도 된다는 것이고, 특정 문자로 시작해야 한다.

SELECT 
	addressline1
FROM customers
WHERE addressline1 LIKE 'ST%';

 

'%문자%'는 앞뒤로 어떤 문자가 와도 된다는 것이다.

SELECT 
    addressline1
FROM customers
WHERE addressline1 LIKE '%ST%'
;

 

GROUP BY절이란?

테이블의 데이터를 그룹화하고 그룹 단위로 집계 함수를 적용하기 위해 사용된다. GROUP BY 절은 데이터를 그룹화하는 데 사용할 열(또는 열들)을 지정하며, 이러한 그룹 단위로 데이터를 요약하거나 계산할 수 있다.

 

1) SELECT 컬럼명1, 집계함수(컬럼명2) FROM 테이블명 GROUP BY 컬럼명1;

집계함수를 적용하지 않은 컬럼에 대해서 GROUP BY를 사용해 줘야 한다.

국가별, 도시별 고객수를 조회한다.

SELECT
    country
    , city
    , COUNT(customernumber) AS n_customers
FROM customers
GROUP BY country, city
;

 

고객 번호별, 체크 번호별 총량에 대해 조회한다.

SELECT 
    customernumber
    , checknumber
    , SUM(amount)
FROM 
	payments
GROUP BY customernumber, checknumber;