MySQL - 데이터 분석과 SQL 문법

 

데이터베이스 입문 수업을 듣고 중요한 내용을 정리했습니다.
개인 공부 후 자료를 남기기 위한 목적이므로 내용 상에 오류가 있을 수 있습니다.

 

데이터 분석과 SQL 문법(MySQL)

MySQL 구문으로 데이터를 분석하는 연습을 하고자 한다.

 

GROUP BY, COUNT, SUM, AVG, MAX, MIN, DISTINCT, AS, HAVING

 

 

• COUNT()

COUNT() 함수는 테이터 검색 결과에서 row가 몇 개인지를 알려준다.

 

기본 문법

 

     -  SELECT COUNT(*) FROM 테이블명

     -  SELECT COUNT(컬럼명) FROM 테이블명

 

SELECT COUNT(*) FROM items;

SELECT COUNT(ori_price) FROM items;

 

SELECT COUNT(*) FROM items 코드는 items 테이블 안에 있는 모든 row의 개수를 알려준다.

SELECT COUNT(ori_price) FROM items 코드는 items 테이블 안의 컬럼명이 ori_price인 row의 개수를 알려준다.

 

 

 

• SUM(), AVG(), MAX(), MIN()

SUM() 함수는 특정 컬럼안에 있는 데이터의 합계를 구해준다.

AVG() 함수는 특정 컬럼안에 있는 데이터의 평균을 구해준다.

MAX() 함수는 특정 컬럼안에 있는 데이터의 최대 값을 구해준다.

MIN() 함수는 특정 컬럼안에 있는 데이터의 최소 값을 구해준다. 

 

기본 문법

 

     -  SELECT SUM(컬럼명) FROM 테이블명

     -  SELECT AVG(컬럼명) FROM 테이블명

     -  SELECT MAX(컬럼명) FROM 테이블명

     -  SELECT MIN(컬럼명) FROM 테이블명

 

SELECT SUM(age) FROM people;

SELECT AVG(age) FROM people;

SELECT MAX(age) FROM people;

SELECT MIN(age) FROM people;

 

 

 

• GROUP BY

GROUP BY 절은 그룹을 지어서 데이터를 분석하고자 할 때 사용된다.

주로 COUNT(), SUM(), AVG(), MAX(), MIN() 함수와 함께 사용되며 각 그룹별 row 개수와 합계, 평균, 최대·최소 값

등을 구할 수 있다.

 

기본 문법

 

     -  SELECT SUM(컬럼명) FROM 테이블명 WHERE 조건 GROUP BY 그룹의 기준이 되는 컬럼명

     -  SELECT AVG(컬럼명) FROM 테이블명 WHERE 조건 GROUP BY 그룹의 기준이 되는 컬럼명

 

SELECT AVG(age) FROM people GROUP BY gender;

SELECT provider, COUNT(*) FROM items GROUP BY provider ORDER BY COUNT(*) DESC;

 

 

 

• DISTINCT()

DISTINCT() 함수는 특정 컬럼에 있는 데이터를 출력할 경우 중복된 값을 제외하고 출력한다.

즉, 해당 컬럼에서 데이터의 종류를 알고자 할 때 사용하는 함수이다.

 

기본 문법

 

     -  SELECT DISTINCT(컬럼명) FROM 테이블명

 

SELECT DISTINCT gender FROM people

 

 

 

• AS

AS 절은 특정 결과 값의 이름을 변경할 때 사용된다.

 

기본 문법

 

     -  SELECT COUNT(*) AS 변경하고자 하는 이름 FROM 테이블명

     -  SELECT COUNT(컬럼명) AS 변경하고자 하는 이름 FROM 테이블명

 

SELECT COUNT(*) AS total_count FROM people;

SELECT provider, COUNT(*) AS total_count FROM items GROUP BY provider;

 

 

 

• HAVING

HAVING 절은 집계함수를 가지고 조건을 비교할 때 사용되며, 주로 GROUP BY 절과 함께 사용된다.

 

기본 문법

 

     -  SELECT 컬럼명 FROM 테이블명 GROUP BY 컬럼명 HAVING 집계함수 + 조건

 

SELECT provider, COUNT(*) FROM items GROUP BY provider HAVING COUNT(*) >= 100;

SELECT provider, COUNT(*) AS total_products FROM items WHERE provider != '스마일배송' and provider != '' GROUP BY provider HAVING COUNT(*) >= 100 ORDER BY COUNT(*) DESC;

 

 

 

• 참고(복합검색) 

복합검색은 WHERE, GROUP BY, ORDER BY 등 다양한 SQL 문법을 복합적으로 사용하여 검색하는 것을 의미한다.

단, SQL 문법을 복합적으로 사용할 때에는 WHERE 절,  GROUP BY 절, ORDER BY 절 순으로 작성해야 한다.

 

SELECT provider, COUNT(dis_price) FROM items GROUP BY provider ORDER BY COUNT(dis_price) DESC;

 

 

 

JOIN

JOIN은 두 개 이상의 테이블로부터 필요한 데이터를 연결하여 하나의 포괄적인 구조로 결합한 후에 데이터를

가져오거나 분석하는 연산으로 아래와 같이 세분화 할 수 있다.

 

* INNER JOIN(일반적인 JOIN)

   - 두 테이블에서 해당 컬럼 값이 매칭되는 데이터만을 가져온다.

 

*OUTER JOIN(참고)

   1. LEFT OUTER JOIN 

     - 왼쪽 테이블의 모든 데이터와 왼쪽 테이블의 데이터와 매칭되는 오른쪽 테이블의 데이터를 붙여서 가져온다.

   2. RIGHT OUTER JOIN

     - 오른쪽 테이블의 모든 데이터와 오른쪽 테이블의 데이터와 매칭되는 왼쪽 테이블의 데이터를 붙여서 가져온다.

 

 

• INNER JOIN

 

기본 문법

 

     -  SELECT * FROM 테이블명 INNER JOIN 조인할 테이블명 ON 조인 조건 WHERE 조건

     -  SELECT 테이블명.칼럼명 FROM 테이블명 INNER JOIN 조인할 테이블명 ON 조인 조건 WHERE 조건

     -  SELECT * FROM 테이블명 약어 INNER JOIN 조인할 테이블명 약어 ON 조인 조건(약어 사용) WHERE 조건

     -  SELECT 테이블명.칼럼명 FROM 테이블명 약어 INNER JOIN 조인할 테이블명 약어 ON 조인 조건(약어 사용)

         WHERE 조건

 

INNER JOIN은 특정 테이블에서 조인하는 테이블의 ON 절과 조건에 매칭되는 데이터만을 출력한다. 

 

SELECT * FROM items INNER JOIN ranking ON ranking.item_code = items.item_code WHERE ranking.main_category = 'ALL';

SELECT * FROM items i INNER JOIN ranking r ON r.item_code = i.item_code WHERE r.main_category = 'ALL';

 

참고로, 테이블명 뒤에 약어를 쓰면 ON 절과 WHERE 절에서 테이블명 대신 약어를 사용해서 보다 간결한 SQL 구문을

만들 수 있다.

 

 

활용 예제

SELECT items.provider, COUNT(*) FROM ranking INNER JOIN items ON items.item_code = ranking.item_code WHERE ranking.main_category = 'ALL' GROUP BY items.provider ORDER BY COUNT(*) DESC;

- G마켓 전체 베스트상품(ALL 메인 카테고리)에서 판매자별 베스트상품 개수 출력하기

 

SELECT items.provider, COUNT(*) FROM ranking INNER JOIN items ON items.item_code = ranking.item_code WHERE ranking.main_category = ‘패션의류’ GROUP BY items.provider HAVING COUNT(*) >= 5;

- G마켓 메인 카테고리가 패션의류인 서브 카테고리 포함, 패션의류 전체 베스트상품에서 판매자별 베스트 상품 개수가

   5개 이상인 판매자와 베스트상품 개수 출력하기

 

SELECT items.provider, COUNT(*) FROM ranking INNER JOIN items ON items.item_code = ranking.item_code WHERE ranking.main_category = ‘신발/잡화’ GROUP BY items.provider HAVING COUNT(*) >=10 ORDER BY COUNT(*) DESC;

- G마켓 메인 카테고리가 신발/잡화인 서브 카테고리 포함, 전체 베스트상품에서 판매자별 베스트상품 개수가 10개

   이상인 판매자와 베스트상품 개수 순으로 출력하기

 

SELECT AVG(i.dis_price) AS average, MAX(i.dis_price) AS maximum, MIN(i.dis_price) AS minimum FROM ranking r INNER JOIN items i ON i.item_code = r.item_code WHERE r.main_category = ‘화장품/헤어’;

- G마켓 메인 카테고리가 화장품/헤어인 서브 카테고리 포함, 전체 베스트상품의 평균, 최대, 최소 가격 출력하기

 

*참고

items TABLE

ranking TABLE

 

 

• OUTER JOIN

OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN으로 구분된다.

 

 

1. 기본 문법(LEFT OUTER JOIN)

     -  SELECT * FROM 테이블명 LEFT OUTER JOIN 조인할 테이블명 ON 조인 조건 WHERE 조건 등

 

SELECT * FROM customer_table C LEFT OUTER JOIN order_table O ON C.customer_id = O.customer_id

 

```

결과 :

```

 

SELECT * FROM order_table O LEFT OUTER JOIN customer_table C ON C.customer_id = O.customer_id

 

```

결과 :

```

 

 

2. 기본 문법(RIGHT OUTER JOIN)

     -  SELECT * FROM 테이블명 RIGHT OUTER JOIN 조인할 테이블명 ON 조인 조건 WHERE 조건 등

 

SELECT * FROM customer_table C RIGHT OUTER JOIN order_table O ON C.customer_id = O.customer_id

 

```

결과 :

```

 

참고로, OUTER JOIN은 매칭되는 데이터가 없는 경우 NULL 값을 출력한다. (NULL = NaN)