study record

CREATE TABLE AS SELECT

- city 테이블과 똑같은 city2 테이블 생성

CREATE TABLE city2
AS SELECT *
FROM city;

CREATE DATABASE

- 새로운 데이터베이스를 생성

- USE문으로 새 데이터베이스를 사용

- Navigator, Schemas에서 마우스 우클릭 'Refresh All' → 만든 데이터베이스가 보임 

CREATE DATABASE suan;

USE suan;

CREATE TABLE

- GUI로 만들수 있음

- 코드로 만들기

CREATE TABLE test2 (
    id INT NOT NULL PRIMARY KEY,
    col1 INT NULL,
    col2 FLOAT NULL,
    col3 VARCHAR(45) NULL
);

ALTER TABLE

- ALTER TABLE문과 함께 ADD문을 사용하면, 테이블에 컬럼을 추가할 수 있음

ALTER TABLE test2
ADD col4 INT NULL;

- ALTER TABLE문과 함게 MODIFY문을 사용하면, 테이블의 컬럼 타입을 변경할 수 있음

ALTER TABLE test2
MODIFY col4 VARCHAR(20) NULL;

- ALTER TABLE문과 함께 DROP문을 사용하면, 테이블에 컬럼을 제거할 수 있음

ALTER TABLE test2
DROP col4;

인덱스

- 테이블에서 원하는 데이터를 빠르게 찾기 위해 사용

- 일반적으로 데이터를 검색할 때 순서대로 테이블 전체를 검색하므로 데이터가 많으면 많을수록 탐색하는 시간이 늘어남.하지만 인덱스를 사용할 경우 검색과 질의를 할 때 테이블 전체를 읽지 않기 때문에 빠름

- 설정된 컬럼 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 함. 그래서 인덱스가 있는 테이블은 처리 속도가 느려질 수 있으므로 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 좋음

CREATE INDEX

CREATE INDEX문을 사용하여 인덱스를 생성

CREATE INDEX Col1idx
ON test (col1);

SHOW INDEX

인덱스 정보 보기

SHOW INDEX FROM test;

CREATE UNIQUE INDEX

중복 값을 허용하지 않는 인덱스를 생성

CREATE UNIQUE INDEX Col2idx
ON test (col2);

FULLTEXT INDEX

일반적인 인덱스와는 달리 매우 빠르게 테이블의 모든 텍스트 컬럼을 검색

ALTER TABLE test
ADD FULLTEXT Col3idx(col3);

INDEX 삭제

- ALTER: ALTER 문을 사용하여 테이블에 추가된 인덱스 삭제

ALTER TABLE test
DROP INDEX Col3idx;

- DROP INDEX: DROP 문을 사용하여 해당 테이블에서 명시된 인덱스를 삭제

DROP INDEX Col2idx ON test;

- 둘 중 하나 사용

VIEW

- 뷰는 데이터베이스에 존재하는 일종의 가상 테이블

- 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하진 않음

- MySQL에서 뷰는 다른 테이블이나 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만 수행

- 뷰를 사용하면 여러 테이블이나 뷰를 하나의 테이블처럼 볼 수 있음

- 장점

1) 특정 사용자에게 테이블 전체가 아닌 필요한 컬럼만 보여줄 수 있음

2) 복잡한 쿼리를 단순화해서 사용

3) 쿼리 재사용 가능

- 단점

1) 한 번 정의된 뷰는 변경할 수 없음

2) 삽입, 삭제, 갱신 작업에 많은 제한 사항을 가짐

3) 자신만의 인덱스를 가질 수 없음

CREATE VIEW

CREATER VIEW 문을 사용하여 뷰 생성

CREATE VIEW testView AS
SELECT Col1, Col2
FROM test;

SELECT * FROM testView;

ALTER VIEW

ALTER 문을 사용하여 뷰를 수정

ALTER VIEW testView AS
SELECT Col1, Col2, Col3
FROM test;

SELECT * FROM testView;

DROP VIEW

DROP 문을 사용하여 생성된 뷰를 삭제

DROP VIEW testView;

INSERT

- INSERT INTO 문을 사용하여 테이블에 새로운 레코드를 추가할 수 있음

- 문법

1. INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...)

   VALUES (데이터값1, 데이터값2, 데이터값3, ...)

2. INSERT INTO 테이블이름

   VALUES (데이터값1, 데이터값2, 데이터값3, ...)

- 테이블이름 다음에 나오는 열 생략 가능

- 생략할 경우에 VALUES 다음에 나오는 값들의 순서 및 개수가 테이블이 정의된 열 순서 및 개수와 동일해야 함

INSERT INTO test
VALUES(1, 123, 1.1, "Test");

3. Workbench

Workbench에서 바로 입력 후 Apply 

INSERT INTO SELECT

- test 테이블에 있는 내용을 test2 테이블에 삽입(테이블의 데이터를 우리가 복사하고자 하는 대상 테이블로 복사하는 기능을 가진 구문)

INSERT INTO test2 SELECT * FROM test;

UPDATE

- 기존에 입력되어 있는 값을 변경하는 구문

- WHERE절 생략 가능하나 테이블의 전체 행의 내용 변경

UPDATE test
SET col1 = 1, col2 = 1.0, col3 = 'test'
WHERE id = 1;

DELETE

- 행 단위로 데이터 삭제하는 구문

- DELETE FROM 테이블이름 WHERE 조건;

- WHERE절을 안쓰면 전체 다 날라

- 데이터는 지워지지만 테이블 용량은 줄어들지 않음

- 원하는 데이터만 지울 수 있음

- 삭제 후 잘못 삭제한 것을 되돌릴 수 있음

DELETE FROM test
WHERE id = 1;

TRUNCATE

- 용량이 줄어들고, 인덱스 등도 모두 삭제

- 테이블은 삭제하지는 않고, 데이터만 삭제(테이블의 데이터를 전부 삭제하고 사용하고 있던 공간을 반납)

- 삭제 후 절대 되돌릴 수 없음

TRUNCATE TABLE test;

DROP TABLE

- 테이블 자체를 삭제

- 삭제 후 절대 되돌릴 수 없음

DROP TABLE test;

DROP DATABASE

- DROP DATABASE 문은 해당 데이터베이스를 삭제

DROP DATABASE suan;

Reference)

유튜브 '이수안컴퓨터연구소 MySQL 데이터베이스 한번에 끝내기'

유튜브 '코딩애플 index'

MySQL 내장함수

- 사용자의 편의를 위해 다양한 기능의 내장 함수를 미리 정의하여 제공

- 대표적인 내장 함수의 종류

문자열 함수, 수학 함수, 날짜와 시간 함수

- 실습은 특정 숫자나 문자를 넣었지만, 분석을 할때는 해당 자리에 본인이 알고자 하는 컬럼들을 넣음

SELECT LENGTH(Name)
FROM city;

LENGTH()

- 전달받은 문자열의 길이를 반환

SELECT LENGTH('123456789');

CONCAT()

- 전달받은 문자열을 모두 결합하여 하나의 문자열로 반환

- 전달받은 문자열 중 하나라도 NULL이 존재하면 NULL을 반환

SELECT CONCAT('My', 'sql Op', 'en Source');

LOCATE()

- SELECT LOCATE(substr, str, [pos]);

- 문자열 내에서 찾는 문자열이 처음으로 나타나는 위치를 찾아서 해당 위치를 반환

- 찾는 문자열이 문자열 내에 존재하지 않으면 0을 반환

- python과 달리 MySQL에서는 문자열의 시작 인덱스를 1부터 계산(python은 인덱스를 매길 때 0부터 시작하지만, MySQL은 1부터 시작)

- pos는 선택적으로 넣을수 있는 값인데, 해당 인자에 값을 주게 되면 해당 인자의 위치에서 부터 검색을 시작

SELECT LOCATE('abc', 'abababcbacdABCDabcAB');
>> 5

SELECT LOCATE('abc', 'abababcbacdABCDabcAB', 7);
>> 12
# 소문자와 대문자 구분없는듯

LEFT(), RIGHT()

- LEFT(): 문자열의 왼쪽부터 지정한 개수만큼의 문자를 반환

- RIGHT(): 문자열의 오른쪽부터 지정한 개수만큼의 문자를 반환

SELECT LEFT('MySQL is database management system', 5);
>> MySQL
SELECT RIGHT('MySQL is database management system', 6);
>> system

LOWER(), UPPER()

- LOWER(): 문자열의 문자를 모두 소문자로 변경

- UPPER(): 문자열의 문자를 모두 대문자로 변경

SELECT LOWER('MySQL is database management system');
>> mysql is database management system
SELECT UPPER('MySQL is database management system');
>> MYSQL IS DATABASE MANAGEMENT SYSTEM

REPLACE()

- 문자열에서 특정 문자열을 대체 문자열로 교체

SELECT REPLACE('MSSQL', 'MS', 'My');
>> MySQL

TRIM()

- 문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거

- TRIM() 함수에서 사용할 수 있는 지정자

BOTH: 전달받은 문자열의 양 끝에 존재하는 특정 문자를 제거(기본 설정)

LEADING: 전달받은 문자열 앞에 존재하는 특정 문자를 제거

TRAILING: 전달받은 문자열 뒤에 존재하는 특정 문자를 제거

- 제거할 문자를 명시하지 않으면, 자동으로 공백을 제거

SELECT TRIM('    MySQL    '),
TRIM(LEADING '#' FROM '###MySQL##'),
TRIM(TRAILING '#' FROM '###MySQL##');
>> MySQL, MySQL##, ###MySQL

FORMAT()

- 숫자 타입의 데이터를 세 자리마다 쉼표(,)를 사용하는 '#,###,###.##' 형식으로 변환

- 반환되는 데이터의 형식은 문자열 타입

- 두 번째 인수는 반올림할 소수 부분의 자릿수

SELECT FORMAT(123123123123.123123123, 4);
>> 123,123,123,123.1231

FLOOR(), CEIL(), ROUND()

- FLOOR(): 내림

- CEIL(): 올림

- ROUND(): 반올림(반올림 기준 정할수 있음: 지정하지 않을 경우 소수점 첫번째 자리를 사용, '소수점 몇번째 자리까지 나타냄 → 지정한 자리수 뒤에 숫자를 보고 반올림 처리')

SELECT FLOOR(21.35);
>> 21
SELECT CEIL(21.35);
>> 22
SELECT ROUND(10.349);
>> 10
SELECT ROUND(10.349, 1);
>> 10.3

SQRT(), POW(), EXP(), LOG()

- SQRT(): 양의 제곱근

- POW(): 첫 번째 인수로는 밑수를 전달하고, 두 번째 인수로는 지수를 전달하여 거듭제곱 계산

- EXP(): 인수로 지수를 전달받아, e의 거듭제곱을 계산

- LOG(): 자연로그 값을 계산

SELECT SQRT(4), POW(2,3), EXP(3), LOG(3);

SIN(), COS(), TAN()

- SIN(): 사인값 반환

- COS(): 코사인값 반환

- TAN(): 탄젠트값 반환

SELECT SIN(PI()/2), COS(PI()), TAN(PI()/4);
>> 1, -1, 0.99999999999999

ABS(), RAND()

- ABS(): 절대값을 반환

- RAND(): 0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 무작위로 생성(매 실행마다 달라짐)

- RAND(시드번호): 시드번호를 부여하면 매 실행마다 동일한 결과

SELECT ABS(-3), RAND(1), ROUND(RAND(1)*100, 0);
>> 3, 40.54035371219772, 41

NOW(), CURDATE(), CURTIME()

- NOW(): 현재 날짜와 시간을 반환, 반환되는 값은 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 형태로 반환

- CURDATE(): 현재 날짜를 반환, 이때 반환되는 값은 'YYYY-MM-DD' 또는 YYYYMMDD 형태로 반환

- CURTIME(): 현재 시각을 반환, 이때 반환되는 값은 'HH:MM:SS' 또는 HHMMSS 형태로 반환

SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

DATE(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

- DATE(): 전달받은 값에 해당하는 날짜 정보를 반환

- MONTH(): 월에 해당하는 값을 반환하며, 0부터 12 사이의 값을 가짐

- DAY(): 일에 해당하는 값을 반환하며, 0부터 31 사이의 값을 가짐

- HOUR(): 시간에 해당하는 값을 반환하며, 0부터 23 사이의 값을 가짐

- MINUTE(): 분에 해당하는 값을 반환하며, 0부터 59 사이의 값을 가짐

- SECONDE(): 초에 해당하는 값을 반환하며, 0부터 59 사이의 값을 가짐

SELECT
NOW(),
DATE(NOW()),
MONTH(NOW()),
DAY(NOW()),
HOUR(NOW()),
MINUTE(NOW()),
SECOND(NOW());

MONTHNAME(), DAYNAME()

- MONTHNAME(): 월에 해당하는 이름을 반환

- DAYNAME(): 요일에 해당하는 이름을 반환

SELECT
NOW(),
MONTHNAME(NOW()),
DAYNAME(NOW());

DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()

- DAYOFWEEK(): 일자가 해당 주에서 몇 번째 날인지를 반환, 1부터 7 사이의 값을 반환(일요일=1, 토요일=7)

                             요일에 해당하는 정수를 반환

- DAYOFMONTH(): 일자가 해당 월에서 몇 번째 날인지를 반환, 0부터 31 사이의 값을 반환

- DAYOFYEAR(): 일자가 해당 연도에서 몇 번째 날인지를 반환, 1부터 366사이의 값을 반환

SELECT
DAYOFMONTH('2023-05-01'),
DAYOFWEEK('2023-05-01'),
DAYOFYEAR('2023-05-01');

DATE_FORMAT()

- DATE_FORMAT(날짜 , 형식)

- 전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환(날짜를 지정한 형식으로 출력)

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
>> 2023-05-20

- https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

Reference)

유튜브 '이수안컴퓨터연구소 MySQL 데이터베이스 한번에 끝내기'

SHOW DATABASES

현재 서버에 어떤 DB가 있는지 보기

USE

• 사용할 데이터베이스 지정
• 지정해 놓은 후 특별히 다시 USE문 사용하거나 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL문은 지정 DB에서 수행

USE database_name;

• Workbench에서 직접 선택해 사용 가능

[Navigator] → [SCHEMAS] → 데이터베이스 선택

SHOW TABLES

• 지정한 데이터베이스의 테이블 이름 보기

SHOW TABLE STATUS

• 지정한 데이터베이스의 테이블 정보 조회(row 개수 등등 정보를 보여줌)

DESCRIBE(DESC)

• 해당 테이블에 무슨 컬럼이 있는지 확인

DESCRIBE city;
DESC city;

SELECT

• <SELECT... FROM>
• 요구하는 데이터를 가져오는 구문
• 일반적으로 가장 많이 사용되는 구문
• 데이터베이스 내 테이블에서 원하는 정보를 추출
• SELECT 구문 형식

SELECT select_expr
               [FROM table_references]
               [WHERE where_condition]
               [GROUP BY {col_name | expr | position}]
               [HAVING where_condition]
               [ORDER BY {col_name | expr | position}]

SELECT *

해당 테이블의 전체 데이터들을 보여줌(모든 열)

SELECT *
FROM city;

SELECT 열 이름

• 테이블에서 필요로 하는 열만 가져옴
• 여러 개의 열을 가져오고 싶을 때는 콤마로 구분
• 열 이름의 순서는 출력하고 싶은 순서대로 배열 가능

SELECT Name, Population
FROM city;

SELECT FROM WHERE

• 조회하는 결과에 특정한 조건으로 원하는 데이터만 보고 싶을 때 사용
• SELECT 필드이름 FROM 테이블이름 WHERE 조건식;

SELECT *
FROM city
WHERE Population >= 8000000;

• 연산자의 사용

- 조건 연산자(=, <, >, <=, >=, <>, != 등)

- 관계 연산자(AND, OR, NOT 등)

- 연산자의 조합으로 데이터를 효율적으로 추출

SELECT *
FROM city
WHERE Population > 7000000 AND Population < 8000000;

• BETWEEN

데이터가 숫자로 구성되어 있어 연속적인 범위를 지정할 때 BETWEEN...AND 사용 가능(이상, 이하의 결과를 추출)

SELECT *
FROM city
WHERE Population BETWEEN 7000000 AND 8000000;

# 동일한 결과(AND 연산자 사용)
SELECT *
FROM city
WHERE Poulation >= 7000000 AND Population <= 8000000;

# 위 검색내용과 반대되는 조건문
SELECT *
FROM city
WHERE Population NOT BETWEEN 7000000 AND 8000000;

• IN

이산적인 값의 조건에서는 IN()사용 가능(IN안에 들어가는 값에 맞는 결과 추출)

SELECT *
FROM city
WHERE Name IN('Seoul', 'New York', 'Tokyo');

# 마찬가지로 NOT 이용 가능(반대되는 조건문)

• LIKE

-  문자열의 내용 검색하기 위해 LIKE 연산자 사용(부분적으로 일치하는 데이터를 찾을때 사용)

-  _: 글자숫자를 정해줌(EX 컬럼명 LIKE '홍_동'), 한 글자와 매치

-  %: 글자숫자를 정해주지않음(EX 컬럼명 LIKE '홍%'), 모든 글자와 매치

# A로 시작하는 문자를 찾기
SELECT 컬럼명
FROM 테이블
WHERE 컬럼명 LIKE 'A%';

# A로 끝나는 문자 찾기
SELECT 컬럼명
FROM 테이블
WHERE 컬럼명 LIKE '%A';

# A를 포함하는 문자 찾기
SELECT 컬럼명
FROM 테이블
WHERE 컬럼명 LIKE '%A%';

# A로 시작하는 두 글자 문자 찾기
SELECT 컬럼명
FROM 테이블
WHERE 컬럼명 LIKE 'A_';

# 첫 번째 문자가 'A'가 아닌 모든 문자열 찾기
SELECT 컬럼명
FROM 테이블
WHERE 컬럼명 LIKE '[^A]';

# 첫 번째 문자가 'A' 또는 'B' 또는 'C'인 문자열 찾기
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[ABC]';
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[A-C]';

• Sub Query(서브 쿼리)

- 쿼리문 안에 또 쿼리문이 들어 있는 것

- 서브 쿼리의 결과가 둘 이상이 되면 에러 발생

- 서브쿼리를 쓰는 이유는 알려지지 않은 기준을 이용한 검색을 하기 위해서

SELECT *
FROM city
WHERE CountryCode = (    SELECT CountryCode
                                             FROM city
                                             WHERE Name = 'Seoul'    );

서브 쿼리만 실행하면 city 테이블에서 Name이 'Seoul'인 CountryCode가 출력되고('KOR') 메인 쿼리와 매칭시켜 결과 출력

# 서브 쿼리의 결과가 둘 이상이 되면 에러 발생
SELECT *
FROM city
WHERE CountryCode = (    SELECT CountryCode
                                             FROM city
                                             WHERE Name IN('Seoul', 'Kabul')    );

• ANY(서브쿼리 안의 값이 여러개일때)

- 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 가능(하나의 조건만 만족하면 True)

- SOME은 ANY와 동일한 의미로 사용

SELECT *
FROM city
WHERE Population > ANY(    SELECT Population
                                                FROM city
                                                WHERE District = 'New York'    );
#
> ANY : 최소값보다 크면
>= ANY : 최소값보다 크거나 같으면
< ANY : 최대값보다 작으면
<= ANY : 최대값보다 작거나 같으면
= ANY : IN과 같은 기능
!= ANY : NOT IN과 같은 기능

• ALL(서브쿼리 안의 값이 여러개일때)

- 서브쿼리의 여러 개의 결과를 모두 만족시켜야 함(모든 값이 조건 만족하면 True)

SELECT *
FROM city
WHERE Population > ALL(    SELECT Population
                                               FROM city
                                               WHERE District = 'New York'    );
#
> ALL : 최대값보다 크면
>= ALL : 최대값보다 크거나 같으면
< ALL : 최소값보다 작으면
<= ALL : 최소값보다 작거나 같으면
= ALL : 서브 쿼리의 결과가 1건이면 괜찮지만 여러 건이면 오류가 발생
!= ALL : 서브 쿼리의 결과가 1건이면 괜찮지만 여러 건이면 오류가 발생

ORDER BY

- 결과가 출력되는 순서를 조절하는 구문

- default는 ASC(오름차순)으로 생략 가능

- 내림차순 정렬(열 이름 뒤에 DESC 적어줄 것)

SELECT *
FROM city
ORDER BY Population DESC;

- 혼합해 사용하는 구문도 가능

SELECT *
FROM city
ORDER BY CountryCode ASC, Population DESC;
# CountryCode 컬럼을 오름차순으로 정렬 후, Population 컬럼을 내림차순 정렬

DISTINCT

- 중복된 것은 1개씩만 보여주면서 출력

- 테이블의 크기가 클수록 효율적

SELECT DISTINCT CountryCode
FROM city;

LIMIT

- 출력 개수를 제한

- 상위의 N개만 출력하는 'LIMIT N' 구문

- 서버의 처리량을 많이 사용해 서버의 전반적인 성능을 나쁘게 하는 악성 쿼리문 개선할 때 사용

- Workbench에서 Limit을 조절하는 옵션이 있음

SELECT *
FROM city
ORDER BY Population DESC
LIMIT 10;

GROUP BY

- 그룹으로 묶어주는 역할(효율적인 데이터 그룹화)

- 집계 함수를 함께 사용

AVG(): 평균

MIN(): 최소값

MAX(): 최대값

COUNT(): 행의 개수

COUNT(DISTINCT): 중복 제외된 행의 개수

STDEV(): 표준편차

VARIANCE(): 분산

- 읽기 좋게 하기 위해 별칭 사용: AS

SELECT CountryCode, AVG(Population) AS 'Average'
FROM city
GROUP BY CountryCode;

Question: 도시는 몇개인가?, 도시들의 평균 인구수는?

SELECT COUNT(*)
FROM city;

SELECT AVG(Population)
FROM city;

HAVING

- WHERE과 비슷한 개념으로 조건 제한

- 집계 함수에 대해서 조건 제한하는 편리한 개념

- HAVING절은 반드시 GROUP BY절 다음에 나와야 함

SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 8000000;

ROLLUP

- 총합 또는 중간합계가 필요할 경우 사용

- GROUP BY절과 함께 WITH ROLLUP문 사용

SELECT CountryCode, Name, SUM(Population)
FROM city
GROUP BY CountryCode, Name WITH ROLLUP;

JOIN

- 데이터베이스 내의 여러 테이블을 하나의 테이블이나 결과 집합으로 결합

- python에서 데이터프레임을 결합하는 merge와 비슷

SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code;

Question: city, country, countrylanguage 테이블 3개를 JOIN 하기

SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode;

Reference)

유튜브 '이수안컴퓨터연구소 MySQL 데이터베이스 한번에 끝내기'

DDL(Data Definition Language, 데이터 정의어)

데이터베이스를 정의하는 언어이며, 데이터를 생성, 수정, 삭제하는 등 데이터의 전체 골격을 결정하는 역할의 언어이다.

• CREATE: 데이터베이스, 테이블 등을 생성하는 역할
• ALTER: 테이블을 수정하는 역할
• DROP: 데이터베이스, 테이블을 삭제하는 역할
• TRUNCATE: 테이블을 초기화 시키는 역할

DML(Data Manipulation Language, 데이터 조작어)

정의된 데이터베이스에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할을 하는 언어

(쉽게 말해, 테이블에 있는 행과 열을 조작하는 언어라고 생각)

→ 데이터베이스 사용자가 질의어를 통해 저장된 데이터를 실질적으로 처리하는데 사용하는 언어

• SELECT: 데이터를 조회하는 역할
• INSERT: 데이터를 삽입하는 역할
• UPDATE: 데이터를 수정하는 역할
• DELETE: 데이터를 삭제하는 역할

DCL(Data Control Language, 데이터 제어어)

데이터베이스에 접근하거나 객체에 권한을 주는 등의 역할을 하는 언어

데이터의 보안, 무결성, 회복 등을 정의하는데 사용

• GRANT: 특정 데이터베이스 사용자에게 특정 작업에 대한 수행권한 부여
• REVOKE: 특정 데이터베이스 사용자에게 특정 작업에 대한 권한을 박탈, 회수
• COMMIT: 트랜잭션의 작업이 정상적으로 완료되었음을 관리자에게 알려줌
• ROLLBACK: 트랜잭션의 작업이 비정상적으로 종료 되었을 때 원래의 상태로 복구

정리를 하자면,

DDL: 데이터베이스와 테이블을 생성 및 변경, 제거

DML: 생성된 테이블 내에 있는 데이터들(행과 열)을 입력, 변경, 수정

DCL: 데이터베이스의 접속 권한 등을 수정

REFERENCE)

https://cbw1030.tistory.com/71

○ 선형 회귀 모델은 일반적으로 피처와 타겟값 간에 선형의 관계가 있다고 가정하고 이러한 최적의 선형 함수를 찾아내 결과값을 예측함

○ 선형 회귀 모델은 피처값과 타겟값의 분포가 정규분포 형태를 선호

- 피처값과 타겟값의 분포가 왜곡(skew)된 분포일 경우 예측 성능에 부정적인 영향을 미칠 수 있음

→ 선형 회귀 모델을 적용하기 전에 먼저 데이터에 대한 스케일링/정규화 작업을 수행하는 것이 일반적이다. 하지만 이러한 스케일링/정규화 작업을 선행한다고 해서 무조건 예측 성능이 향상되는 것은 아니다. 

<'피처값 변환 - 로그변환' 부가적인 설명>

로그변환은 매우 유용한 변환이며, 원래 값에 log 함수를 적용하면 보다 정규분포에 가까운 형태로 값이 분포된다. 

실제로 스케일링, 다항 특성 변환 방법보다 로그변환이 훨씬 많이 사용되는 변환 방법이다. 왜냐하면 스케일링 방법의 경우 예측 성능 향상을 크게 기대하기 어려운 경우가 많으며, 다항 특성 변환 방법의 경우 피처의 개수가 매우 많을 경우에는 다항 변환으로 생성되는 피처의 개수가 기하급수로 늘어나서 과적합의 이슈가 발생할 수 있기 때문이다.

from sklearn.preprocessing import StandardScaler, MinMaxScaler, PolynomialFeatures

# 보스턴 주택가격 타겟 데이터 세트는 이미 정규분포화 되어 있음
bostonDF['PRICE'].hist()

로그변환은 아래 코드에서 np.log()가 아니라 np.log1p()를 이용한다. 일반적으로 log() 함수를 적용하면 언더 플로우가 발생하기 쉬워서 1+log() 함수를 적용하는데 이를 구현한 것이 np.log1p()이다.

# method는 표준정규분포 변환(Standard), 최대값/최소값 정규화(MinMax), 로그변환(Log) 결정
# p_degree는 다항식 특성을 추가할 때 적용. p_degree는 2 이상 부여하지 않음
def get_scaled_data(method='None', p_degree=None, input_data=None):
    if method == 'Standard':
        scaled_data = StandardScaler().fit_transform(input_data)
    elif method == 'MinMax':
        scaled_data = MinMaxScaler().fit_transform(input_data)
    elif method == 'Log':
        scaled_data = np.log1p(input_data)
    else:
        scaled_data = input_data
        
    
    if p_degree != None:
        scaled_data = PolynomialFeatures(degree=p_degree, includ_bias=False).fit_transform(scaled_data)
        
    return scaled_data

Ridge 클래스의 alpha 값을 변화시키면서 피처 데이터 세트를 여러 가지 방법으로 변환한 데이터 세트를 입력받을 경우에 rmse값이 어떻게 변하는지 살펴보자. (앞에서 생성한 get_linear_reg_eval() 함수 이용)

피처 데이터의 변환 방법은 모두 5가지로 아래와 같다.

• (None, None) : 아무런 변환을 하지 않은 원본 데이터
• ('Standard', None) : 표준정규분포
• ('Standard', 2) : 표준정규분포를 다시 2차 다항식 변환
• ('MinMax', None) : 최소값/최대값 정규화
• ('MinMax', 2) : 최소값/최대값 정규화를 다시 2차 다항식 변환
• ('Log', None) : 로그변환

# Ridge의 alpha값을 다르게 적용하고 다양한 데이터 변환 방법에 따른 rmse 추출
alphas = [0.1, 1, 10, 100]

scale_methods = [(None, None), ('Standard', None), ('Standard', 2),
               ('MinMax', None), ('MinMax', 2), ('Log', None)]
for scale_method in scale_methods:
    X_features_scaled = get_scaled_data(method=scale_method[0], p_degree=scale_method[1], input_data=X_features)
    print('\n## 변환 유형:{0}, Polylnomial Degree:{1}'.format(scale_method[0], scale_method[1]))
    get_linear_reg_eval('Ridge',params=alphas, X_features_n=X_features_scaled, y_target_n=y_target,
                       verbose=False, return_coeff=False)

## 변환 유형:None, Polylnomial Degree:None
alpha 0.1일 때 5 폴드 세트의 평균 rmse:5.788
alpha 1일 때 5 폴드 세트의 평균 rmse:5.653
alpha 10일 때 5 폴드 세트의 평균 rmse:5.518
alpha 100일 때 5 폴드 세트의 평균 rmse:5.330

## 변환 유형:Standard, Polylnomial Degree:None
alpha 0.1일 때 5 폴드 세트의 평균 rmse:5.826
alpha 1일 때 5 폴드 세트의 평균 rmse:5.803
alpha 10일 때 5 폴드 세트의 평균 rmse:5.637
alpha 100일 때 5 폴드 세트의 평균 rmse:5.421

## 변환 유형:Standard, Polylnomial Degree:2
alpha 0.1일 때 5 폴드 세트의 평균 rmse:8.827
alpha 1일 때 5 폴드 세트의 평균 rmse:6.871
alpha 10일 때 5 폴드 세트의 평균 rmse:5.485
alpha 100일 때 5 폴드 세트의 평균 rmse:4.634

## 변환 유형:MinMax, Polylnomial Degree:None
alpha 0.1일 때 5 폴드 세트의 평균 rmse:5.764
alpha 1일 때 5 폴드 세트의 평균 rmse:5.465
alpha 10일 때 5 폴드 세트의 평균 rmse:5.754
alpha 100일 때 5 폴드 세트의 평균 rmse:7.635

## 변환 유형:MinMax, Polylnomial Degree:2
alpha 0.1일 때 5 폴드 세트의 평균 rmse:5.298
alpha 1일 때 5 폴드 세트의 평균 rmse:4.323
alpha 10일 때 5 폴드 세트의 평균 rmse:5.185
alpha 100일 때 5 폴드 세트의 평균 rmse:6.538

## 변환 유형:Log, Polylnomial Degree:None
alpha 0.1일 때 5 폴드 세트의 평균 rmse:4.770
alpha 1일 때 5 폴드 세트의 평균 rmse:4.676
alpha 10일 때 5 폴드 세트의 평균 rmse:4.836
alpha 100일 때 5 폴드 세트의 평균 rmse:6.241

• 표준정규분포와 최소값/최대값 정규화로 피처 데이터 세트를 변경해도 성능상의 개선이 별로 없음
• 표준정규분포로 일차 변환 후 2차 다항식 변환을 했을 때 alpha=100에서 4.634로 성능이 개선
• 최소값/최대값 정규화로 일차 변환 후 2차 다항식 변환을 했을 때 alpha=1에서 4.323으로 성능이 개선
• 하지만 다항식 변환은 피처의 개수가 많을 경우 적용하기 힘들고 과적합의 이슈가 발생할 수 있음
• 로그변환으로 피처 데이터 세트를 변경했을 때 alpha가 0.1, 1, 10인 경우에 모두 좋은 성능 향상이 있음

→ 일반적으로 선형 회귀를 적용하려는 데이터 세트에 데이터 값의 분포가 왜곡(skew)되어 있을 경우에 로그변환을 적용하는 것이 좋은 결과를 기대할 수 있고, 다항 특성 변환 방법은 예측에 대한 분산이 높고(alpha값에 따른 다항 특성 변환을 적용한 결과들을 보면 예측에 대한 분산이 높음), 과적합의 우려가 있어 최근에 많이 쓰이지 않음

다항회귀에서 degree1의 경우 지나치게 예측 곡선을 단순화해 과소적합 모델이 만들어졌고, degree15의 경우 지나치게 모든 학습 데이터에 적합한 회귀식을 만들기 위해서 다항식이 복잡해지고 회귀계수가 매우 크게 설정되면서 테스트 데이터 세트에 대해서 형편없는 예측 성능을 보이는 과적합 모델이 만들어졌다.

→ 회귀모델은 적절히 데이터에 적합하면서도 회귀계수가 기하급수적으로 커지는 것을 제어해야 함

이전까지 선형 모델의 비용함수는 RSS를 최소화하는, 즉 실제값과 예측값의 차이를 최소화 하는 것만 고려해 학습 데이터에 지나치게 맞춰지고 회귀계수가 쉽게 커져 과적합이 발생하는 경향이 있음

→ 위의 문제를 해결하기 위해 비용함수는 RSS 최소화 방법과 과적합을 방지하기 위해 회귀계수 값이 커지지 않도록 하는 방법이 서로 균형을 이루어야 함(학습 데이터의 잔차 오류와 회귀계수의 값이 함께 최소화하는 것을 목표로 함)

alpha는 학습 데이터 적합 정도와 회귀계수 값의 크기를 제어하는 파라미터로, 적절하게 설정해야한다.

○ alpha가 0(또는 매우 작은 값)이라면 Min(RSS(W) + 0)이 되면서 기존과 동일하게 RSS를 최소화하는 것만 고려하는 것과 다를게 없고

○ alpha가 무한대(또는 매우 큰 값)이라면 RSS(W)에 비해 alpha * llWll22 값이 너무 커지게 되므로 ​W를 0(또는 매우 작은 값)으로 만들어야 비용함수 목표를 달성할 수 있음

즉, alpha를 작게 하면 회귀계수 W의 값이 커져도 어느 정도 상쇄가 가능하므로 학습 데이터 적합을 더 개선할 수 있고, alpha를 크게 하면 회귀계수 W의 값을 작게 해 과적합을 개선할 수 있음

이처럼 비용함수에 alpha 값으로 페널티를 부여해 회귀계수 값의 크기를 감소시켜 과적합을 개선하는 방식을 규제(Regularization)라고 함

릿지 회귀(L2 Norm)

W의 제곱에 대해 페널티를 부여하는 방식. 회귀계수의 크기를 감소시키는 규제 모델

비용함수는

이고, 이 식을 최소화하는 W를 찾는 것이 목표이다.

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

from sklearn.datasets import load_boston
%matplotlib inline

boston = load_boston()

bostonDF = pd.DataFrame(boston.data, columns=boston.feature_names)

bostonDF['PRICE'] = boston.target

y_target = bostonDF['PRICE']
X_features = bostonDF.drop(['PRICE'], axis=1, inplace=False)

from sklearn.linear_model import Ridge
from sklearn.model_selection import cross_val_score

# alpha=10으로 설정해 릿지 회귀 수행
ridge = Ridge(alpha=10)
mse_scores = -cross_val_score(ridge, X_features, y_target, scoring='neg_mean_squared_error', cv=5)
rmse_scores = np.sqrt(mse_scores)
avg_rmse = np.mean(rmse_scores)

print('5fold의 개별 mse scores:', np.round(mse_scores, 3))
print('5fold의 개별 rmse scores:', np.round(rmse_scores, 3))
print('5fold의 평균 rmse:{0:.3f}'.format(avg_rmse))

alpha 값에 따른 rmse와 회귀계수를 살펴보자

alphas = [0, 0.1, 1, 10, 100]

for alpha in alphas:
    ridge = Ridge(alpha=alpha)
    
    # cross_val_score를 이용해 5 fold의 평균 rmse 계산
    mse_scores = -cross_val_score(ridge, X_features, y_target, scoring='neg_mean_squared_error', cv=5)
    avg_rmse = np.mean(np.sqrt(mse_scores))
    print('alpha {0} 일 때 5 folds의 평균 rmse : {1:.3f}'.format(alpha, avg_rmse))

# alpha 값에 따른 회귀계수 시각화
fig, axs = plt.subplots(figsize=(18,6), nrows=1, ncols=5)
# alpha 값에 따른 회귀계수를 데이터로 저장하기 위한 DataFrame 생성
coeff_df = pd.DataFrame()

# alphas 리스트 값을 차례로 입력해 회귀계수 시각화 및 데이터 저장. pos는 axis의 위치 지정
for pos, alpha in enumerate(alphas):
    ridge = Ridge(alpha=alpha)
    ridge.fit(X_features, y_target)
    # alpha에 따른 피처별로 회귀계수를 Series로 변환하고 이를 DataFrame의 컬럼으로 추가
    coeff = pd.Series(ridge.coef_, index=X_features.columns)
    colname = 'alpha:'+str(alpha)
    coeff_df[colname] = coeff
    # 막대그래프로 alpha 값에 따른 회귀계수 시각화. 내림차순
    coeff = coeff.sort_values(ascending=False)
    axs[pos].set_title(colname)
    axs[pos].set_xlim(-3,6)
    sns.barplot(x=coeff.values, y=coeff.index, ax=axs[pos])
    
plt.show()

coeff_df.sort_values(by='alpha:0', ascending=False)

릿지 회귀는 alpha 값이 커질수록 회귀계수 값을 작게 만든다

라쏘 회귀(L1 Norm)

W의 절대값에 대해 페널티를 부여하는 방식. 불필요한 회귀계수를 급격하게 감소시켜 0으로 만드는 규제 모델

→ 이러한 측면에서 적절한 피처만 회귀에 포함시키는 피처 선택의 특성을 가지고 있음

비용함수는

이고, 이 식을 최소화하는 W를 찾는 것이 목표이다.

alpha 값에 따른 rmse와 회귀계수를 살펴보자. (alpha 값을 변화시키면서 결과를 출력하는 별도의 함수를 만들것)

from sklearn.linear_model import Lasso, ElasticNet

# alpha 값에 따른 회귀모델의 폴드 평균 rmse를 출력, 회귀계수 값들을 DataFrame으로 반환
def get_linear_reg_eval(model_name, params=None, X_features_n=None, y_target_n=None,
                       verbose=True, return_coeff=True):
    coeff_df = pd.DataFrame() # 빈 데이터프레임 생성
    if verbose : print('#######', model_name, '#######')
    for param in params:
        if model_name == 'Ridge':
            model = Ridge(alpha=param)
        elif model_name == 'Lasso':
            model = Lasso(alpha=param)
        elif model_name == 'ElasticNet':
            model = ElasticNet(alpha=param, l1_ratio=0.7) # l1_ratio는 0.7로 고정시킴
        
        mse_scores = -cross_val_score(model, X_features_n, y_target_n, scoring='neg_mean_squared_error', cv=5)
        avg_rmse = np.mean(np.sqrt(mse_scores))
        print('alpha {0}일 때 5 폴드 세트의 평균 rmse:{1:.3f}'.format(param, avg_rmse))
        
        # cross_val_score는 evaluation metric만 반환하므로 모델을 다시 학습하여 회귀계수 추출
        model.fit(X_features_n, y_target_n)
        if return_coeff:
            # alpha에 따른 피처별 회귀계수를 Series로 변환하고 이를 DataFrame의 컬럼으로 추가
            coeff = pd.Series(model.coef_, index=X_features_n.columns)
            colname = 'alpha:'+str(param)
            coeff_df[colname] = coeff
            
    return coeff_df

# 라쏘에 사용될 alpha 파라미터의 값을 정의하고 get_linear_reg_eval() 함수 호출
lasso_alphas = [0.07, 0.1, 0.5, 1, 3]
coeff_lasso_df = get_linear_reg_eval('Lasso', params=lasso_alphas, X_features_n=X_features, y_target_n=y_target)

# 반환된 coeff_lasso_df를 첫 번째 컬럼순으로 내림차순 정렬해 회귀계수 DataFrame 출력
coeff_lasso_df.sort_values(by='alpha:'+str(lasso_alphas[0]), ascending=False) # by='alpha:0.07'

NOX 피처는 alpha가 0.07일 때부터 회귀계수가 0이다.

라쏘 회귀는 alpha의 크기가 증가함에 따라 일부 피처의 회귀계수는 아예 0으로 바뀌고 있다. 회귀계수가 0인 피처는 회귀식에서 제외되면서 피처 선택의 효과를 얻을 수 있다.

엘라스틱넷 회귀(L2 Norm+L1 Norm)

L2 규제와 L1 규제를 결합한 회귀.

○ 엘라스틱넷은 라쏘 회귀가 서로 상관관계가 높은 피처들의 경우에 이들 중에서 중요 피처만을 셀렉션하고 다른 피처들은 모두 회귀계수를 0으로 만드는 성향이 강하다. 특히 이러한 성향으로 인해 alpha값에 따라 회귀계수의 값이 급격히 변동할 수도 있는데, 엘라스틱넷 회귀는 이를 완화하기 위해 L2 규제를 라쏘 회귀에 추가한 것

○ 주요 생성 파라미터는 alpha, l1_ratio

비용함수는

이고, 이 식을 최소화하는 W를 찾는 것이 목표이다.

alpha 값에 따른 rmse와 회귀계수를 살펴보자.(코드에 l1_ratio를 0.7로 고정시켰는데, 이는 단순히 alpha값의 변화만 살피기 위해 미리 고정했음)

# 엘라스틱넷에 사용될 alpha 파라미터의 값들을 정의하고 get_linear_reg_eval() 함수 호출
# l1_ratio는 0.7로 고정
elastic_alphas = [0.07, 0.1, 0.5, 1, 3]
coeff_elastic_df = get_linear_reg_eval('ElasticNet', params=elastic_alphas, X_features_n=X_features, y_target_n=y_target)

# 반환된 coeff_elastic_df를 첫 번째 컬럼순으로 내림차순 정렬해 회귀계수 DataFrame 출력
coeff_elastic_df.sort_values(by='alpha:'+str(elastic_alphas[0]), ascending=False) # by='alpha:0.07'

alpha값에 따른 피처들의 회귀계수들 값이 라쏘보다는 상대적으로 0이 되는 값이 적음을 알 수 있다.

Reference)

https://dsbook.tistory.com/206

지금까지 공부한 회귀는 y = w0 + w1*x1 + w2*x2 + ... + wn*xn과 같이 독립변수(feature)와 종속변수(target)의 관계가 일차 방정식 형태로 표현된 회귀였다. 하지만 세상의 모든 관계를 직선으로만 표현할 수 없다.

즉, 다항 회귀는 y = w0 + w1*x1 + w2*x2 + w3*x1*x2 + w4*x1^2 + w5*x2^2과 같이 회귀식이 독립변수의 단항식이 아닌 2차, 3차 방정식과 같은 다항식으로 표현되는 것을 지칭한다.

데이터 세트에 대해서 피처 X에 대해 타겟 y값의 관계를 단순 선형회귀 직선형으로 표현한 것보다 다항회귀 곡선형으로 표현한 것이 더 예측 성능이 높을 수 있음

한 가지 주의할 것은 다항회귀는 선형회귀이다. 회귀에서 선형회귀/비선형회귀를 나누는 기준은 회귀계수가 선형/비선형인지에 따른 것이지 독립변수의 선형/비선형 여부와는 무관하다.

사이킷런은 다항회귀를 바로 API로 제공하지 않는다. 

→ PolynomialFeatures 클래스로 원본 단항 피처들을 다항 피처들로 변환한 데이터 세트에 LinearRegression 객체를 적용하여 다항회귀 구현(아래의 과정을 각각 수행하는 것보다, pipeline 클래스를 이용해 두 가지 과정을 결합하여 한 번에 다항회귀를 구현하는 것이 일반적이고 코드가 더 명료함)

단항 피처[x1, x2]를 degree=2, 즉 2차 다항 피처로 변환하면

(x1+x2)^2 의 식 전개에 대응되는 [1, x1, x2, x1x2, x1^2, x2^2]의 다항 피처들로 변환

                                                     ↓

1차 단항 피처들의 값이 [x1, x2] = [0, 1] 일 경우

2차 다항 피처들의 값은 [1, x1=0, x2=1, x1x2=0, x1^2=0, x2^2=1]

형태인 [1, 0, 1, 0, 0, 1] 로 변환

마찬가지로, 단항 피처[x1, x2]를 degree=3, 즉 3차 다항 피처로 변환하면

(x1+x2)^3 의 식 전개에 대응되는 [1, x1, x2, x1x2, x1^2, x2^2, x1^3, x1^2x2, x1x2^2, x2^3]의 다항 피처들로 변환

참고로, PolynomialFeatures 클래스는 원본 피처 데이터 세트를 기반으로 degree(차수)에 따른 다항식을 적용하여 새로운 피처들을 생성하는 클래스로 피처 엔지니어링의 기법중의 하나임

PolynomialFeatures를 이용해 다항식 변환을 연습해보자

from sklearn.preprocessing import PolynomialFeatures
import numpy as np

# 단항식 생성, [[0,1],[2,3]]의 2X2 행렬 생성
X = np.arange(4).reshape(2,2)
print('일차 단항식 계수 feature:\n', X)

# degree=2인 2차 다항식으로 변환
poly = PolynomialFeatures(degree=2)
poly.fit(X)
poly_ftr = poly.transform(X)
print('변환된 2차 다항식 계수 feature:\n', poly_ftr)

이번에는 3차 다항회귀 함수를 임의로 설정하고 이의 회귀계수를 예측해보자.

3차 다항식 결정값을 구하는 함수 polynomial_func(X) 생성. y = 1+ 2x_1 + 3x_1^2 + 4x_2^3

def polynomial_func(X):
    y = 1 + 2*X[:,0] + 3*X[:,0]**2 + 4*X[:,1]**3
    return y

X = np.arange(4).reshape(2,2)

print('일차 단항식 계수 feature:\n', X)
y = polynomial_func(X)
print('삼차 다항식 결정값:\n', y)

from sklearn.linear_model import LinearRegression

# 3차 다항식 변환
poly_ftr = PolynomialFeatures(degree=3).fit_transform(X)
print('3차 다항식 계수 feature:\n', poly_ftr)

# LinearRegression에 3차 다항식 계수 feature와 3차 다항식 결정값으로 학습 후 회귀계수 확인
model = LinearRegression()
model.fit(poly_ftr, y)
print('Polynomial 회귀계수\n', np.round(model.coef_, 2))

일차 단항식 계수 피처는 2개였지만, 3차 다항식 변환 후에는 다항식 계수 피처가 10개로 늘어난다.이 피처 데이터 세트에 LinearRegression을 통해 3차 다항회귀 형태의 다항회귀를 적용하면 회귀계수가 10개로 늘어난다. 

pipeline을 이용해 한 번에 다항회귀를 구현해보자.

from sklearn.preprocessing import PolynomialFeatures
from sklearn.linear_model import LinearRegression
from sklearn.pipeline import Pipeline
import numpy as np

def polynomial_func(X):
    y = 1 + 2*X[:,0] + 3*X[:,0]**2 + 4*X[:,1]**3
    return y

# Pipeline 객체로 streamline하게 PolynomialFeature 변환과 LinearRegression 연결
model = Pipeline([('poly', PolynomialFeatures(degree=3)),
                 ('linear', LinearRegression())])
X = np.arange(4).reshape(2,2)
y = polynomial_func(X)

model = model.fit(X, y)
print('Polynomial 회귀계수\n', np.round(model.named_steps['linear'].coef_, 2))

다항회귀를 이용해 보스턴 주택가격을 예측해보자

import numpy as np
import pandas as pd

from sklearn.datasets import load_boston
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import PolynomialFeatures
from sklearn.linear_model import LinearRegression
from sklearn.pipeline import Pipeline
from sklearn.metrics import mean_squared_error, r2_score

# boston 데이터셋 로드
boston = load_boston()

# boston 데이타셋 DataFrame 변환 
bostonDF = pd.DataFrame(boston.data, columns=boston.feature_names)

# boston dataset의 target array는 주택 가격임. 이를 PRICE 컬럼으로 DataFrame에 추가함. 
bostonDF['PRICE'] = boston.target
print('보스턴 데이터셋 shape:', bostonDF.shape)

y_target = bostonDF['PRICE']
X_features = bostonDF.drop(['PRICE'], axis=1, inplace=False)

X_train, X_test, y_train, y_test = train_test_split(X_features, y_target, test_size=0.3, random_state=156)

# Pipeline 이용: PolynomialFeatures 변환, LinearRegression 적용을 순차적으로 결합
p_model = Pipeline([('poly', PolynomialFeatures(degree=3, include_bias=False)),
                   ('linear', LinearRegression())])

p_model.fit(X_train, y_train)
y_preds = p_model.predict(X_test)
mse = mean_squared_error(y_test, y_preds)
rmse = np.sqrt(mse)


print('MSE:{0:.3f}, RMSE:{1:.3f}'.format(mse, rmse))
print('Variance score:{0:.3f}'.format(r2_score(y_test, y_preds)))

다항회귀를 이용한 underfitting, overfitting 이해

피처 X에 대해 타겟 y값의 관계를 단순 선형회귀 직선형으로 표현한 것보다 다항회귀 곡선형으로 표현한 것이 더 예측 성능이 높을 수 있다. 하지만 다항회귀의 차수(degree)를 높일수록 학습 데이터에만 너무 맞춘 학습이 이뤄져서 정작 테스트 데이터 환경에서는 오히려 예측 정확도가 떨어진다. 즉, 차수가 높아질수록 과적합 문제가 크게 발생한다.

# 데이터 생성
import numpy as np
import matplotlib.pyplot as plt
from sklearn.preprocessing import PolynomialFeatures
from sklearn.linear_model import LinearRegression
from sklearn.pipeline import Pipeline
from sklearn.model_selection import cross_val_score
%matplotlib inline

# X는 0이상 1미만의 랜덤값을 30개 생성후 순서대로 샘플링
np.random.seed(0)
n_samples = 30
X = np.sort(np.random.rand(n_samples))

# X값에 대해 Cosine 변환값을 반환하는 함수
def true_fun(X):
    return np.cos(1.5 * np.pi * X)

# y는 X의 Cosine 값에서 약간의 노이즈를 더한 값
y = true_fun(X) + np.random.randn(n_samples) * 0.1

plt.scatter(X, y)

plt.figure(figsize=(14, 5))
degrees = [1, 4, 15]

# 다항회귀 차수를 1, 4, 15로 각각 변화시키면서 비교
for i in range(len(degrees)):
    ax = plt.subplot(1, len(degrees), i+1)
    plt.setp(ax, xticks=(), yticks=())
    
    # 차수별로 Polynomial 변환
    pipeline = Pipeline([('poly', PolynomialFeatures(degree=degrees[i], include_bias=False)),
                        ('linear', LinearRegression())])
    pipeline.fit(X.reshape(-1,1), y)
    
    # 교차검증으로 다항회귀 평가
    scores = -cross_val_score(pipeline, X.reshape(-1,1), y, scoring='neg_mean_squared_error', cv=10)
    # 회귀계수 추출
    print('\ndegree {0} 회귀계수:{1}'.format(degrees[i], np.round(pipeline.named_steps['linear'].coef_, 2)))
    print('degree {0} mse:{1}'.format(degrees[i], np.round(np.mean(scores), 2))) # 교차검증별 mse를 평균냄
    
    # 0부터 1까지 테스트 데이터 세트를 100개로 나눠 예측 수행
    # 테스트 데이터 세트에 회귀 예측을 수행하고 예측 곡선과 실제 곡선을 그려서 비교
    X_test = np.linspace(0,1,100)
    # 예측값 곡선
    plt.plot(X_test, pipeline.predict(X_test[:, np.newaxis]), label='Model')
    # 실제값 곡선
    plt.plot(X_test, true_fun(X_test), '--', label='True function')
    # 학습 데이터
    plt.scatter(X, y, edgecolor='b', s=20, label='Samples')
    
    plt.xlabel('x'); plt.ylabel('y'); plt.xlim((0,1)); plt.ylim((-2,2)); plt.legend(loc='best')
    plt.title('degree {0}\nMSE = {1:.2e}(+/- {2:.2e})'.format(degrees[i], scores.mean(), scores.std()))
    
plt.show()

실선은 예측곡선, 점선은 실제 데이터 세트(테스트 데이터) X(X_test), y(true_func(X_test))의 코사인 곡선이다. 학습데이터는 0부터 1까지 30개의 랜덤의 X값과 그에 따른 코사인 y값에 노이즈를 추가해 구성했으며 MSE 평가는 학습 데이터를 10fold로 나누어 측정해서 평균한 것이다.

• degree1 예측곡선은 단순한 직선으로 단순 선형회귀와 똑같다. 실제 데이터 세트인 코사인 데이터 세트를 직선으로 예측하기에는 너무 단순해 보인다. 예측 곡선이 학습 데이터의 패턴을 제대로 반영하지 못하고 있는 과소적합 모델이다. MSE 값은 약 0.41이다.
• degree4 예측곡선은 실제 데이터 세트와 유사하다. 노이즈까지 예측하지는 못했지만, 학습 데이터 세트를 비교적 잘 반영해 코사인 곡선 기반으로 테스트 데이터를 잘 예측한 곡선을 가진 모델이다. MSE 값은 약 0.04로 가장 뛰어난 예측 성능을 나타낸다.
• degree15 예측곡선은 학습 데이터의 노이즈까지 지나치게 반영한 결과, 예측곡선이 학습 데이터 세트만 정확히 예측하고, 테스트 데이터의 실제 곡선과는 완전히 다른 형태이다. 결과적으로 학습 데이터에 너무 충실하게 맞춘 과적합이 심한 모델이고 MSE 값은 182815433이 될 정도로 말도 안되는 오류값이 발생했다.  
• degree 15 회귀계수는 degree1, 4와 비교할 수 없을 정도로 매우 큰 값이다. degree 15라는 복잡한 다항식을 만족하기 위해 계산된 회귀계수는 결국 현실과 너무 동떨어진 예측 결과를 보여준다.

→결국 좋은 예측모델은 degree1과 같이 학습 데이터의 패턴을 지나치게 단순화한 과소적합 모델도 아니고, degree15와 같이 모든 학습 데이터의 패턴을 하나하나 감안한 지나치게 복잡한 과적합 모델도 아닌, 학습 데이터의 패턴을 잘 반영하면서 복잡하지 않은 균형 잡힌 모델이다.

편향-분산 트레이드오프(Bias-Variance Trade off)

degree1의 모델: 매우 단순화된 모델로 데이터를 충분히 표현하지 못함 → 높은 편향을 가짐(낮은 분산) → 지나치게 특정 방향으로 치우쳤다는 의미 → 과소적합(underfitting)

degree15의 모델: 학습 데이터 하나하나의 특성을 반영하면서 매우 복잡한 모델로 데이터를 너무 과하게 표현함 → 높은 분산을 가짐(낮은 편향) → 지나치게 높은 변동성을 가졌다는 의미 → 과적합(overfitting)

편향과 분산은 한쪽이 높으면 한쪽이 낮아지는 트레이드오프 관계에 있다. 따라서 둘을 적절하게 조절하여 전체 오류가 가장 낮아지는 '골디락스' 지점을 찾는 것이 매우 중요하다. 즉, 편향-분산 트레이드오프는 머신러닝이 극복해야 할 가장 중요한 이슈 중 하나로 머신러닝 모델을 만들때 과소적합, 과적합 모델이 아닌 적절한 모델을 만들어야 한다.

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from scipy import stats
from sklearn.datasets import load_boston
import warnings
warnings.filterwarnings('ignore')
%matplotlib inline

# boston 데이터 세트 로드
boston = load_boston()

# boston 데이터 세트 DataFrame 변환
bostonDF = pd.DataFrame(boston.data, columns=boston.feature_names)

# boston 데이터 세트의 target array는 주택 가격. 이를 PRICE 컬럼으로 DataFrame에 추가
bostonDF['PRICE'] = boston.target
bostonDF.head()

변수 설명

• CRIM: 지역별 범죄 발생률
• ZN: 25,000평방 피트를 초과하는 거주 지역의 비율
• INDUS: 비상업 지역 넓이 비율
• CHAS: 찰스강에 대한 더미변수(강의 경계에 위치한 경우1, 아니면 0)
• NOX:일산화질소 농도
• RM: 거주할 수 있는 방 개수
• AGE: 1940년 이전에 건축된 소유 주택의 비율
• DIS: 5개 주요 고용센터까지의 가중 거리
• RAD: 고속도로 접근 용이도
• TAX: 10,000달러당 재산세율
• PTRATIO: 지역의 교사와 학생 수 비율
• B: 지역의 흑인 거주 비율
• LSTAT: 하위 계층의 비율

bostonDF.info()

bostonDF.isnull().sum()

결측값은 없으며 피처들이 모두 float형이다.

다음으로 각 피처와 타겟값 사이의 상관관계를 살펴보자.(참고로 seaborn의 regplot() 함수는 x, y 축 값의 산점도와 함께 선형 회귀 직선을 그려줌)

# 2X4 subplots 이용. axs는 4*2개의 ax를 가짐
fig, axs = plt.subplots(figsize=(16,8), ncols=4, nrows=2)

lm_features = ['RM', 'ZN', 'INDUS','NOX', 'AGE', 'PTRATIO', 'LSTAT', 'RAD']

for i, feature in enumerate(lm_features):
    row = int(i/4) # 나누기
    col = i%4 # 나머지
    sns.regplot(x=feature, y='PRICE', data=bostonDF, ax=axs[row][col])

타겟값(주택 가격)과 가장 상관관계가 있는 피처는 RM, LSTAT이다.

RM은 양의 상관관계를 보이며, 방의 크기가 클수록 주택 가격이 커짐

LSTAT는 음의 상관관계를 보이며, 하위 계층의 비율이 높아질수록 주택 가격이 낮아짐

이제 train, test 데이터 분리하고 학습/예측/평가를 수행하자.

from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error, r2_score

y_target = bostonDF['PRICE']
X_features = bostonDF.drop(['PRICE'], axis=1, inplace=False)

X_train, X_test, y_train, y_test = train_test_split(X_features, y_target, test_size=0.3, random_state=156)

# LinearRegression으로 학습/예측/평가 수행
lr = LinearRegression()
lr.fit(X_train, y_train)
y_preds = lr.predict(X_test)
mse = mean_squared_error(y_test, y_preds)
rmse = np.sqrt(mse)

print('MSE:{0:.3f}, RMSE:{1:.3f}'.format(mse, rmse))
print('Variance score:{0:.3f}'.format(r2_score(y_test, y_preds)))

선형회귀로 학습한 주택가격 모델의 평가 결과를 보면 만족스러운 결과는 아니다.

일단, 선형회귀로 학습한 주태가격 모델의 절편과 회귀계수를 보자.

print('절편:', lr.intercept_)
print('회귀계수:', np.round(lr.coef_, 1))

# 각 피처와 매핑하여 정렬
coeff = pd.Series(data=np.round(lr.coef_, 1), index=X_features.columns)
coeff.sort_values(ascending=False)

NOX 피처의 회귀계수가 음으로 너무 커 보인다. 이는 나중에 규제를 공부한 뒤 최적화를 수행하자.

이번에는 교차검증을 통해 MSE와 RMSE를 측정해보자.

from sklearn.model_selection import cross_val_score

y_target = bostonDF['PRICE']
X_features = bostonDF.drop(['PRICE'], axis=1, inplace=False)
lr = LinearRegression()

mse_scores = -cross_val_score(lr, X_features, y_target, scoring='neg_mean_squared_error', cv=5)
rmse_scores = np.sqrt(mse_scores)
avg_rmse = np.mean(rmse_scores)

print('5folds의 개별 mse scores:', np.round(mse_scores, 2))
print('5folds의 개별 rmse scores:', np.round(rmse_scores, 2))
print('5folds의 평균 rmse:{0:.3f}'.format(avg_rmse))