[패스트 캠퍼스] 데이터 분석 부트캠프(BDA) 11기 6-7주차

 

📊 5주차 - 파이썬 프로젝트 회고

🙋🏻‍♀️ 4주차가 끝나는 시점부터 6주차 시작하는 날까지 약 10일가량의 파이썬 프로젝트는 너무나도 힘들었어요. 부트캠프에서의 첫 프로젝트이기도 하고 데이터분석 첫 프로젝트여서 그런지 많은 부분에서 부족했고 아쉬운 점이 많았던 것 같아요. 저희 조는 미디어를 산업분야로 선택했는데 매체 선택을 잘못했는지.. 주제를 명확히 정하는 것부터 난관이었고 그렇게 프로젝트를 진행하는 동안 총 3번 주제가 바뀌었답니다 하하 역시 주제 선정이 가장 중요한 것이라는 걸 뼈저리게 느낀..! 

주제를 바꾼만큼 다른 조보다 시간은 더욱 짧았고 시간이 부족해서인지 혹은 제한된 데이터 & API 활용 한계 때문인지 좋은 인사이트를 도출해내지 못한 게 너무 아쉬웠답니다.. 프로젝트가 끝난 지 일주일이 지난 지금까지 과제를 제대로 끝내지 못했다는 이 찝집한 마음은 사라지지 않네요. 그래도 첫 프로젝트를 이 정도로 잘 마무리했다는 거에 나름(?) 만족감을 느끼고 +  좋은 점수를 받았던 것도 있고 팀원들과의 합도 너무 잘 맞았던 프로젝트여서 좋은 기억으로 남을 것 같아요!!😁

다음 프로젝트 때는 꼭 주제 선정 잘하리라..ㅎ 

 

---

 

출처: openlogic

 

 

📌SQL로 하는 데이터분석 

 

 

 

 

목차

1. 데이터베이스 기본 개념

2. 데이터베이스와 테이블 구축

3. 데이터 조회

4. 서브쿼리와 조인

5. 다양한 함수

 

 

< 데이터베이스 기본 개념 >

 

데이터 분석을 시작하기 전에, 핵심은  우선 데이터가 어떻게 구성되어 있는지 / 관리되고 있는지 파악하는 것 입니다.

방대한 데이터들은 데이터베이스(일정한 체계에 따라 구조화된 데이터의 집합) 안에 테이블이라 불리는 단위로 정보가 저장됩니다.

아래 사진처럼 여러 테이블이 하나의 데이터베이스에 존재할 수도 있습니다.

 

출처: Database.Guide

 

이렇게 서로 관련된 데이터를 하나의 테이블에 저장하고 여러 테이블 간에 관계를 형성함으로써 체계적으로 데이터를 관리할 수 있습니다. 데이터베이스 또한 여러 개를 생성할 경우 방대한 양의 데이터를 효과적으로 저장할 수 있습니다.

 

 

데이터베이스의 현황을 파악하려면 다음과 같은 사항을 알아야 합니다.

- 어떤 데이터베이스가 있는지

- 각 데이터베이스 안에 어떤 테이블이 있는지

- 각 테이블의 컬럼구조는 어떻게 되는지

- 테이블 간의 Foreign Key(외래키) 관계는 어떤지

 

 

 

 

 

< 데이터베이스와 테이블 구축>

 

그럼 먼저 데이터베이스와 테이블을 구축하는 방법부터 알아보려 합니다.

 

DATABASE (데이터베이스) 생성 • 조회 • 삭제

# 생성
CREATE DATABASE database_name;
-- 같은 sql문 반복 실행 안됨
-- 이미 존재해서 같은 이름의 데이터베이스 반복 생성 X
-- 대신 아래의 SQL문을 실행하면 에러가 아닌 경고
CREATE DATABASE IF NOT EXISTS database_name;

# 조회
SHOW DATABASES;

# 삭제
DROP DATABASE IF EXISTS database_name

 

• DBMS로 어떤 작업을 할 때는 가장 먼저 어떤 데이터베이스에서 작업을 할지 확실히 지정해줘야 함
• Schema 창에서 데이터베이스의 이름을 더블클릭 후 실행 할 수도 있고
• 또는 아래의 방법도 가능

USE database_name;
-- 데이터베이스를 지정하지 않고도 테이블을 생성할 수 있음

# ex)
SELECT * FROM mydatabase.mytable; # <-- 대신에 아래의 방법 사용 가능

USE mydatabase;
SELECT * FROM mytable;

• 하나의 DBMS 안에는 여러 개의 데이터베이스가 있기 때문에 위의 방법으로 먼저 데이터베이스를 지정해주고 나면 그 데이터베이스를 작업 중인 데이터베이스로 인식하게 되어 굳이 그 이름을 적어주지 않아도 됨

 

---

 

 

TABLE (테이블) 생성 • 조회 • 삭제

 

# 데이터베이스를 지정해줬다는 가정 하에

# 생성
CREATE TABLE table_name (
	column_name data_type,
        column_name data_type,
        ...,
        PRIMARY KEY (col_name));

# 테이블/뷰 파악
SHOW TABLES; 
SHOW FULL TABLES IN database_name; # <-- 이것도 가능

# 테이블의 컬럼 구조 파악
DESC table_name;

# 테이블 삭제 
DROP TABLE IF EXISTS table_name;

• 테이블 생성은 해당 데이터베이스 안에 다음 사진과 같이 테이블 아이콘을 눌러 생성할 수도 있음

table 생성 아이콘을 바로 클릭하면 'No Schema Selected'라는 경고 메세지 발생, 꼭 데이터베이스 지정 후 사용

 

 

 

TABLE (테이블)에 들어갈 Column data type

• Numeric types
• Date and Time types
• String types

> Numeric types, 숫자형 타입

말 그대로 숫자를 나타내기 위해 사용되는 데이터 타입입니다. 

정수형 타입, 실수형 타입이 있습니다.

 

 

> Date and Time types, 날짜 및 시간 타입

날짜 및 시간을 나타내는 데이터 타입입니다.

• TIME : 시간을 나타내는 데이터 타입. ex) ’ 09:27:31’
• DATE : 날짜를 저장하는 데이터 타입. ex)‘2020-03-26’

TIME과 DATE 외에도 다양한 데이터 유형이 있는데요.

• DATETIME : 날짜와 시간을 저장. ex) ’2020-03-26 09:30:27’
• TIMESTAMP : 날짜와 시간을 저장. 타임존(time_zone) 정보도 함께 저장

 

> String types, 문자열 타입

텍스트로 구성된 문자형 데이터 타입입니다.

 

 

---

 

 

TABLE (테이블) 구조 수정 

먼저 테이블 관련 쿼리문법을 살펴보겠습니다.

# 테이블 이름 변경 
RENAME TABLE table_name TO new_table_name;;
-- 또는
ALTER TABLE table_name RENAME new_table_name;

# 데이터 삽입
INSERT INTO table_name
	col1, col2, col3
        VALUES (val1, val2, val3);
        
# 데이터 수정
UPDATE table_name
	SET col1 = new_value, col2 = new_value2
    WHERE conditions;

# 데이터 삭제
-- 테이블에서 모든 데이터(row) 삭제 (ROLLBACK 가능)
DELETE FROM table_name;
-- 조건에 맞는 데이터(row) 삭제
DELETE FROM table_name WHERE conditions;
-- 모든 데이터(row)를 한번에 삭제(구조만 남음, ROLLBACK 불가하며 실행 후 복구X)
TRUNCATE TABLE table_name;

데이터를 삭제할 때는 주의할 점이 있습니다. 

• DELETE 문 같은 경우 row를 바로 삭제해 버리기 때문에 (ROLLBACK 가능하지만) 소중한 데이터가 사라질 수 있는 위험이 있습니다. 그래서 "논리적 삭제"인 삭제해야 할 row를 삭제하지 않고 삭제여부를 나타내는 별도의 column을 두고 거기에 삭제되었음을 나타내는 값을 넣는 방법을 사용하기도 합니다. (ex. is_cancelled, is_deleted)
• 같은 테이블에서 처음부터 다시 시작하고 싶을 때 주로 사용하는 TRUNCATE 같은 경우는 기존 테이블의 데이터를 전부 다 삭제하고 구조만 남는 쿼리문인데요. DELETE 문에 비해 ROLLBACK이 안되니 주의해야 합니다. 

 

 

다음은 테이블의 Column(열) 관련 쿼리문법입니다.

# column 이름 변경 
ALTER TABLE table_name RENAME COLUMN col_name TO new_col_name;

# column 추가
ALTER TABLE table_name ADD COLUMN col_name CHAR(1) NULL;

# column 타입 변경 
ALTER TABLE table_name MODIFY COLUMN col_name new_data_type NOT NULL;

# column 이름과 타입 변경 
ALTER TABLE table_name CHANGE COLUMN col_name new_col_name; # 이름 변경
ALTER TABLE table_name CHANGE COLUMN col_name new_col_name new_data_type; # 둘다 변경

# column 삭제 
ALTER TABLE table_name DROP COLUMN col_name;

# column 가장 앞으로 당기기 
ALTER TABLE table_name MODIFY col_name data_type FIRST;

# column간의 순서 바꾸기 AFTER
ALTER TABLE table_name MODIFY col2 data_type AFTER col3; # col2를 col3 뒤로

 

• column의 이름을 변경하는 ALTER TABLE - RENAME COLUMN 문법은 컬럼의 이름만 변경하고, 데이터 타입이나 제약 조건 등은 변경되지 않는 특징이 있습니다. 
• column을 추가하는 ADD COLUMN 문법을 작성할 때는 column의 이름과 데이터 타입을 같이 입력해줘야 합니다.
• 타입을 변경하는 MODIFY  문법 사용 시 주의할 점은 기존의 데이터타입과 변경하려는 데이터 타입이 다를 때 에러가 발생할 수 있다는 점입니다. 이는 기존의 값들을 바꾸려는 데이터타입의 임의의 값으로 변경 후 진행하여 에러를 방지할 수 있습니다. 
• CHANGE 문은 column의 이름뿐만 아니라 데이터 타입, 제약 조건등을 변경할 수 있습니다. 이를 이용하면 RENAME과 MODIFY를 따로 실행해주지 않고 하나의 쿼리문으로 작성할 수 있다는 장점이 있습니다. 

 

 

 

< 데이터 조회 >

✅SELECT...FROM

 

SELECT는 테이블의 데이터를 조회할 때 사용하는 구문입니다. 

SELECT * FROM dbname.table_name;

-- 또는
USE dbname;
SELECT * FROM table_name;

 

SELECT 문 작성 순서: SELECT →  FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Query의 실행 순서: FROM →  WHERE/JOIN →  GROUP BY →  HAVING →  SELECT →  ORDER BY →  LIMIT

쿼리 실행 순서
FROM	어떤 테이블을 대상으로 할 것인지 결정
WHERE	해당 테이블에서 특정 조건들을 만족하는 row 선별
GROUP BY	row를 그룹 기준대로 그룹화. 하나의 그룹은 하나의 row
HAVING	그룹화 후 생성된 여러 그룹들 중 특정 조건을 만족하는 그룹 선별
SELECT	모든 column or 특정 column 조회, alias 사용 가능
ORDER BY	각 row를 특정 기준에 따라 정렬
LIMIT	이전 단계까지 조회된 row들 중 row의 수 제한

 

SELECT문으로 조회된 column에는 별칭(ALIAS)을 지정할 수 있습니다. 

SELECT 
    name,
    COUNT(*) AS StudentCounts # <-- AS 없어도 빈칸 한칸이면 가능
FROM student;

 

중복된 값을 제외하고 고유의 값만 가져오고 싶은 경우 DISTINCT 키워드를 사용합니다.  DISTINCT를 사용하여 데이터를 조회하는 경우 SELECT에서 조회하는 모든 column에 영향을 미칩니다.

SELECT DISTINCT(gender) FROM customer;

-- GROUP BY를 사용할 때 ORDER BY를 같이 사용하지 않으면 정렬되지 않음
-- 다음 두 쿼리는 같은 결과를 나타냄
SELECT DISTINCT class FROM school;
SELECT class FROM school GROUP BY class;

 

 

 

---

 

 

✅WHERE

 

WHERE문은 테이블에서 특정 데이터를 조회할 때 조건을 설정하기 위한 구문입니다.

# id가 3인 학생의 이름 가져오기
SELECT name FROM student WHERE id = 3;

# 키가 160이상이고 몸무게가 50이상인 학생의 이름 가져오기
SELECT name FROM student WHERE height > 160 AND weight >= 50;

# 등급이 A가 아닌 학생 정보 모두 가져오기
SELECT * FROM student WHERE grade != 'A';

# 등급이 A,B,C인 학생들의 이름과 반 가져오기
SELECT name, class FROM student
WHERE grade IN ('A', 'B', 'C');

# 성이 김씨인 학생들의 이름 가져오기
SELECT name FROM student
WHERE name LIKE '김%';

 

WHERE문에서는 연산자가 사용 가능합니다.

 

문자열을 다루는 LIKE 연산자에서 %와 _의 사용

• ‘cat%’ : cat으로 시작하는 값
• ‘%dog%’: dog을 포함하는 값
• ‘%apple’: apple로 끝나는 값
• '_a' : a로 끝나고 a앞에 1개 이 문자가 있는 값
• '%_a' : a로 끝나고 a 앞에 1개 이상의 문자가 있는 값
• '%_a_%' : a를 포함하고 a 앞뒤로 1개 이상의 문자가 있는 값
• LIKE BINARY ‘%a%’ : 소문자 a만 조회

NULL 데이터를 다루는 IS NULL 연산자

• 데이터 값이 존재하지 않는다는 표현
• 0이나 공백이 아닌 알 수 없는 값을 의미
• IS NULL은 데이터가 NULL인지 아닌지를 확인하는 연산자
• col_name = NULL 또는 col_name != NULL은 잘못된 표현

 

 

---

 

 

 

✅GROUP BY - HAVING

 

GROUP BY문은 특정 컬럼 기준의 데이터를 그룹화하는 구문입니다.

HAVING은 이미 조회된 데이터를 그룹화했을 때 생성된 그룹들 중, 다시 필터링을 할 때 쓰는 구문입니다.

그룹화가 된 데이터를 다루기 때문에 꼭 GROUP BY뒤에 와야 합니다. 

/*이름의 길이가 5보다 큰 학생들을 class 기준으로 그룹화하고 
키의 평균이 160 이상인 그룹의 class와 키의 평균 가져오기*/
SELECT class, AVG(height) FROM student
WHERE LENGTH(name) > 5
GROUP BY class
HAVING AVG(height) >= 160;

 

GROUP BY가 쓰인 쿼리의 SELECT 절에는 

 

  1. GROUP BY 뒤에서 사용한 columns 또는

  2. COUNT(), MAX() 등과 같은 집계함수만 쓸 수 있습니다

 

 

**집계함수(Aggregation function)

: 그룹화를 통해 생성된 각 그룹의 수치적인 특성을 구하는 함수입니다. 

• SUM, AVG, MIN, MAX, COUNT, COUNT(DISTINCT)
  • COUNT( ): null의 개수 제외
  • COUNT(*) 연산은 모든 row를 대상으로 이루어지기 때문에 NULL값이 포함되어 있어도 카운트됨
• MOD(나머지), STD(표준편차), ABS(절댓값), SQRT(제곱근), CEILING(올림), FLOOR(내림), ROUND(반올림)..

 

 

---

 

 

✅ORDER BY

 

ORDER BY는 조회된 데이터를 특정 기준에 따라 정렬할 때 사용하는 구문입니다. 

기본 정렬은 ASC (ascending order) 오름차순이며 내림차순이고

내림차순 정렬을 할 때는 DESC (descending order) 키워드를 추가해 줍니다. 

여러 컬럼으로 정렬도 가능하며, 키워드 뒤에 컬럼이름을 여러 개 입력하면 됩니다. => 위치한 순서대로 정렬

# 오름차순
SELECT col1, col2 
FROM table_name
WHERE condition
ORDER BY col1, col2 ASC; # <-- ASC 생략 가능

# 내림차순
SELECT col1, col2 
FROM table_name
WHERE condition
ORDER BY col1, col2 DESC;

**순위 함수(RANK)
RANK() 함수는 데이터를 정렬해 순위를 만들어주는 함수입니다. 

항상 ORDER BY와 사용하며 SELECT 절에 사용하여 정렬된 순서에 순위를 붙인 새로운 column을 보여줍니다.

# 오름차순
SELECT col_name, ..., RANK() OVER (ORDER BY col_name)
FROM table_name
WHERE condition;

# 내림차순
SELECT col_name, ..., RANK() OVER (ORDER BY col_name DESC)
FROM table_name
WHERE condition;

 

RANK() 비슷하게 사용되는 함수로는 DENSE_RANK()와 ROW_NUMBER()가 있습니다. 

• RANK()는 공동 순위가 있으면 다음 순서로 건너뜀
• DENSE_RANK()는 공동 순위가 있어도 다음 순위를 뛰어넘지 않음
• ROW_NUMBER()는 공동 순위를 무시함

 

 

---

 

 

 

 

✅LIMIT

 

LIMIT은 출력 개수를 제한하는 구문입니다. 

가장 마지막에 실행되며 조건에 도달하면 쿼리를 멈춰버립니다.

SELECT * FROM mytable LIMIT 10; # 최상위 10개 가져오기
SELECT * FROM mytable LIMIT 8, 2; # 8번 다음 row부터 2개 가져오기
SELECT * FROM mytable 
WHERE condition LIMIT 1; # 특정 조건에 맞는 최상위 1개 가져오기