the-ze

이번 포스팅에서 정의한 테이블 및 데이터:

CREATE DATABASE mydatabase;
CREATE USER 'myundb'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myundb'@'localhost';
FLUSH PRIVILEGES;
USE mydatabase;
CREATE TABLE user(
  id INT NOT NULL AUTO_INCREMENT,
  email VARCHAR(500) NOT NULL,
  name VARCHAR(500) NOT NULL,
  nickname VARCHAR(500) NOT NULL,
  pw VARCHAR(500) NOT NULL,
  newdate DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT user_pk PRIMARY KEY (id)
);

예시 데이터 집어넣기:

MySQL 데이터베이스에 저장된 데이터를 확인하는 방법은 여러 가지가 있다.

첫 번째 방법은, 명령 프롬프트 또는 터미널에서 MySQL 클라이언트를 시작하고

mysql -u your_username -p

사용할 데이터베이스를 선택한다.

USE your_database_name;

그리고 필요한 데이터를 조회한다. 예를 들어, users 테이블의 모든 데이터를 조회하려면

SELECT * FROM users;

이렇게 할 수 있다.

결과 화면:

두 번째 방법은 MySQL Workbench에서 확인하는 방법이다.

SELECT  * FROM mydatabase.users; 를 쿼리로 실행해도 되고, UI로도 가능하다.

스키마에서 조회하고자 하는 테이블을 우클릭하고 Select Rows - Limit 1000을 누르면 상위 1000개의 데이터 목록을 확인할 수 있다. 만약 테이블을 정의해도 Schema에 데이터베이스가 안뜨면, Schemas 부분에 우클릭을 한 뒤, Load Spatial Data를 눌러 최근 리스트들을 갱신해야 한다.

결과 화면:

MySQL 데이터 베이스 관리 도구인 phpMyAdmin을 사용하여 브라우저를 통해 데이터베이스에 접속하고, 테이블을 조회하거나 SQL 쿼리를 실행할 수도 있다.

https://www.phpmyadmin.net/

CREATE TABLE user(
  id INT NOT NULL AUTO_INCREMENT,
  email VARCHAR(500) NOT NULL,
  name VARCHAR(500) NOT NULL,
  nickname VARCHAR(500) NOT NULL,
  pw VARCHAR(500) NOT NULL,
  newdate DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT user_pk PRIMARY KEY (id)
);

테이블에서 사용할 열의 이름을 id로 하고 열의 데이터 유형을 INT로 지정했다. 여기서 정의한 id는 사용자가 사용할 id가 아닌 식별을 위한 번호일 뿐이다. AUTO_INCREMENT는 자동으로 증가하는 값을 생성함을 나타낸다. 새로운 행이 삽입될 때마다 자동으로 증가한다.

DATATIME은 날짜 및 시간 데이터 유형을 지정한다. DEFAULT CURRENT_TIMESTAMP는 새로운 행이 삽입될 때 현재 날짜 및 시간이 자동으로 저장되는 것이다.

CONSTARAINT user_pk는 제약 조건의 이름을 지정한 것이다. PRIMARY KEY로 보통 각 행을 고유하게 식별하는데 위 코드에서는 id 열을 주 키로 설정했다.

CREATE USER 'myundb'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON myundatabase.* TO 'myundb'@'localhost';
FLUSH PRIVILEGES;

myundb로 user의 name을 정의하고 localhost로 로컬 머신에서만 접근을 허용한다라고 정의 하였다. IDENTIFIED BY 로 사용자의 암호를 설정하고 지정된 사용자에게 모든 권한 (ALL PRIVILEGES)을 부여했다.

ON myundatabase 테이블에 대한 권한과 권한을 받을 호스트(myundb@localhost)를 지정하고 FLUSH PRIVILEGES로 사용자 및 권한 관련 변경 사항을 즉시 적용하도록 하였따. 이 명령은 새로운 사용자나 권한을 추가했을 때, MySQL이 즉시 이를 적용하도록 한다.

MySQL 서버를 킬 때, 환경 변수 설정을 해야한다. 이를 위해서는 MySQL 서버의 경로를 알아야하는데, 경로는 보통

로컬 디스크 (C:) \ Program Files \ MySQL \ MySQL Server x.x \ bin

에 있다. 경로를 찾았으면, 고급 시스템 설정을 열고 환경 변수를 클릭한다.

그러면 사용자 변수와 시스템 변수가 나올텐데, 만약 사용자 변수의 PATH에 해당 경로를 추가하면 현재 사용자만 해당 경로를 사용할 수 있기 때문에, 모든 사용자가 MySQL 서버를 실행할 수 있께 하고 싶으면, 시스템 변수에 PATH를 추가 시켜야한다.

그리고 변경 사항을 적용한 후에는 시스템을 다시 시작하거나 로그아웃하여 변경 사항이 적용되도록 하는 것을 꼭 잊지말자.

DB 인덱스는 데이터베이스에서 데이터 검색 속도를 빠르게 하기 위해 사용되는 데이터 구조다. 특정 열(컬럼)의 값을 기준으로 정렬해 둔 목록이라고 볼 수 있다. 이 인덱스를 사용하면 데이터베이스는 테이블 전체를 처음부터 끝까지 찾아보지 않고도 빠르게 원하는 데이터를 찾을 수 있다.

예를 들어, 책 뒤에 있는 색인(index)을 생각해보면 이해가 쉽다. 책의 색인을 사용하면 원하는 정보를 책 전체를 훑어보지 않고도 빠르게 찾아낼 수 있다. DB 인덱스도 비슷하게 작동한다. 특정 데이터를 찾을 때, 인덱스가 있으면 데이터베이스는 인덱스를 먼저 확인해 어디에서 데이터를 찾아야 할지 빠르게 알 수 있다.

그러나 인덱스는 데이터 검색 속도를 향상시키는 대신, 추가적인 저장 공간을 사용하고, 데이터를 삽입하거나 수정할 때 추가 작업이 필요해 성능에 영향을 줄 수 있기 때문에 신중하게 사용해야 한다.

DB 인덱스는 SQL 기반의 관계형 데이터베이스(RDBMS)와 NoSQL 데이터베이스 모두에서 사용될 수 있다. 각 데이터베이스 유형에 따라 인덱스의 구현, 관리 방법, 최적화 전략 등이 다를 수 있다.

SQL 데이터베이스에서 인덱스는 테이블의 하나 이상의 컬럼에 적용되어 데이터 검색 속도를 향상시킨다. 대표적으로 B-트리(B-Tree) 인덱스가 널리 사용된다. 관계형 데이터베이스에서 인덱스는 SELECT 쿼리의 WHERE 절, JOIN 연산 등에서 데이터 검색 시간을 줄이기 위해 주로 사용된다.

NoSQL 데이터베이스는 키-값 저장소, 문서 지향 데이터베이스, 컬럼 패밀리 데이터베이스, 그래프 데이터베이스 등 다양한 형태가 있다. NoSQL 데이터베이스도 데이터 검색 성능을 향상시키기 위해 인덱스를 사용한다. 예를 들어, 문서 지향 데이터베이스인 MongoDB에서는 문서의 특정 필드에 인덱스를 생성하여 쿼리 성능을 개선할 수 있다.

SQL 문에서 발생한 오류 Error Code: 1064는 SQL 구문에 문법 오류가 있음을 나타낸다. 특히 DB 외적인 프로그래밍을 많이 하다가 스키마(데이터베이스)를 정의할 때 작은따옴표(') 대신에 백틱(`)을 사용해야 하는 것을 주의하지 않아 자주 발생한다.

예를 들어, CREATE SCHEMA 'mydatabase' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;를 쳤다고 하자 'mydatabase'는 작은 따옴표다.

그럼 0 4 21:31:56 CREATE SCHEMA 'mydatabase' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''mydatabase' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci' at line 1 0.000 sec 에러가 뜰 것 이다.

그러면 작은 따옴표를 백틱으로 바꿔주면 된다. 그리고 일부 버전의 mySQL에선 SCHEMA 대신 DATABASE 키워드를 더 선호할 수도 있으니

CREATE DATABASE `mydatabase` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

로 바꾸는게 좋다. 이 구문은 mydatabase라는 이름의 데이터베이스를 생성하며, 기본 문자 세트로 utf8mb4를, 정렬 순서로 utf8mb4_general_ci를 설정한다. utf8mb4는 이모지를 포함한 다양한 유니코드 문자를 지원하기 때문에, 다국어 데이터를 처리하는 애플리케이션에 적합하다.

++

MySQL에서 Error Code: 1007은 데이터베이스가 이미 존재할 때 발생하는 오류다. mydatabase가 이미 존재하기 때문에 같은 이름으로 데이터베이스를 다시 만들 수 없다. 데이터베이스가 이미 존재하는지 확인하거나, 새로운 이름으로 데이터베이스를 생성해야 한다. 이미 존재하는 데이터베이스에 테이블을 추가하려면, 해당 데이터베이스를 선택하고 필요한 SQL 명령을 실행하면 된다.

ex)

DROP DATABASE mydatabase;

CREATE DATABASE `mydatabase` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

참고한 데이터 : https://www.blockchain.com/ko/charts/market-price?timespan=60days

위의 데이터를 가져온다.

.index: DataFrame 또는 Series에서 인덱스를 참조한다. 데이터 구조의 행에 접근하는 데 사용된다.
.set_index(keys): DataFrame에서 특정 열을 인덱스로 설정하다. keys에 지정된 열이 새로운 인덱스로 사용된다.

head(n=5): DataFrame 또는 Series의 처음 n행을 반환한다. n을 지정하지 않으면 기본적으로 처음 5행을 보여준다.

.describe() 함수는 pandas DataFrame 또는 Series의 기술 통계량을 요약하여 보여준다. 기본적으로 수치형 데이터에 대한 요약 통계량을 반환하며, 이에는 평균(mean), 표준편차(std), 최솟값(min), 최댓값(max), 사분위수(25%, 50%, 75%) 등이 포함된다.

문자열이나 카테고리형 데이터가 포함된 경우, .describe(include=['object', 'category'])와 같이 include 매개변수를 사용해 요약 정보를 얻을 수 있다. 이 경우, 유니크한 값의 개수(count), 가장 흔한 값(top), 그 값이 나타나는 빈도(freq) 등의 정보를 포함한다.

plot() 함수는 데이터 시각화 라이브러리인 Matplotlib에서 데이터를 그래프로 그리기 위해 사용된다. plt.show()는 그려진 그래프를 화면에 표시하다. 예를 들어, X축과 Y축 데이터 간의 관계를 선 그래프로 나타내고자 할 때 plot() 함수를 사용하고, 최종적으로 그래프를 보기 위해 plt.show()를 호출한다.

ARIMA 모델은 시계열 데이터를 분석하고 예측하기 위한 통계적 방법이다. ARIMA는 AutoRegressive Integrated Moving Average의 약자로, 세 가지 주요 구성 요소로 이루어져 있다.

1. 자기 회귀(AR: AutoRegressive): 모델이 이전 관측값의 의존성을 설명한다. 파라미터 p는 과거 관측값이 현재 값을 예측하는 데 사용되는 시차의 수를 나타낸다.
2. 차분(I: Integrated): 비정상성을 제거하기 위해 원 시계열 데이터에 차분을 적용하는 과정을 말한다. 파라미터 d는 데이터가 정상성을 달성하기 위해 필요한 차분의 횟수를 나타낸다.
3. 이동 평균(MA: Moving Average): 모델이 이전 예측 오차의 의존성을 설명한다. 파라미터 q는 과거 예측 오차가 현재 값을 예측하는 데 사용되는 시차의 수를 나타낸다.

Prophet 모델은 Facebook에서 개발한 시계열 예측을 위한 도구다. 주로 일일 활동 패턴, 주간 및 연간 계절성 등이 명확한 시계열 데이터에 대한 예측에 적합하다. Prophet은 특히 휴일 효과나 이벤트가 예측에 중요한 역할을 하는 경우에 유용하며, 누락 데이터나 이상치가 있는 데이터셋에서도 견고한 예측 성능을 보인다.

 

prophet모델은 연간, 월간, 주간, 일간 등의 주기적 변화를 모델링한다. Fourier series를 사용해 복잡한 계절성 패턴을 유연하게 캡처할 수 있다. 또한, 공휴일이나 특별 이벤트 등 예측 대상 시계열 데이터에 영향을 미치는 날짜를 모델링한다. 사용자가 직접 휴일과 이벤트 날짜를 지정할 수 있다.

 

데이터 불러오기: start_date와 end_date를 설정하여 원하는 기간의 비트코인 가격 데이터를 가져온다. fdr.DataReader() 함수를 사용하여 데이터를 가져온다. 가져온 데이터 중에서 'Close' 컬럼의 값만 선택하여 변수 close_data에 저장한다.

​

데이터 전처리: MinMaxScaler()를 사용하여 데이터를 0과 1 사이의 범위로 스케일링한다. 스케일링된 데이터는 변수 scaled_data에 저장된다.

​

이동 평균선 계산: 이동 평균을 계산하기 위해 np.convolve() 함수를 사용한다. moving_avg에 이동 평균선 데이터가 저장된다.

​

데이터셋 생성 함수: create_dataset() 함수는 입력 데이터와 레이블 데이터를 생성하는 함수이다. 입력 데이터는 이전 일 수(lookback)에 해당하는 데이터로 구성된다.

​

실제 가격과 예측 가격 비교: df_predictions 데이터프레임에 실제 가격과 예측 가격을 저장한다. 예측 결과를 출력한다.

 

 

데이터 분할: 생성한 데이터셋을 훈련 데이터와 테스트 데이터로 분할한다.

 

테스트 데이터 예측: 테스트 데이터의 마지막 값(마지막 10일의 가격)을 예측 값으로 사용한다.

 

scaler.inverse_transform() 함수를 사용하여 예측 값을 원래 스케일로 변환한다.

 

 

plt.figure() 함수를 사용하여 그래프의 크기를 설정한다.

​

plt.xlabel(), plt.ylabel(), plt.title() 함수를 사용하여 축 레이블과 그래프 제목을 설정한 후 legend와 show함수를 이용하여 그래프와 범례를 표시한다.

​

예측 가격 그래프, 이동 평균선 그래프, 추세 그래프를 차례로 그린다.

 

​

위 그래프는 LSTM은 RNN의 일반적인 문제인 장기 의존성 문제(Long-Term Dependency Problem)를 해결하기 위해 제안된 모델이며 시계열 데이터나 자연어 처리와 같이 시간적인 의존성을 가진 데이터를 처리하는 데에 주로 사용된다.

​

가장 큰 장점으로는 입력의 길이에 따라 동적으로 확장되거나 축소되는 특성을 가지고 있어, 자연어 처리와 같이 문장의 길이가 다양한 작업에 적합하다. 대신 그만큼 단점으로는 계산 비용이 높고, 모델 구조가 복잡하여 사용하기 어렵다는 게 가장 큰 문제라고 할 수 있다.

import FinanceDataReader as fdr
import pandas as pd
import numpy as np
from sklearn.preprocessing import MinMaxScaler
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import LSTM, Dense

#데이터 불러오기
start_date = '2018-01-01'
end_date = '2022-12-31'
bitcoin_data = fdr.DataReader('BTC/KRW', start_date, end_date)

#데이터 추출
close_data = bitcoin_data['Close'].values.reshape(-1, 1)

#데이터 스케일링
scaler = MinMaxScaler()
scaled_data = scaler.fit_transform(close_data)

#데이터 분할
train_size = int(len(scaled_data) * 0.8)
train_data = scaled_data[:train_size]
test_data = scaled_data[train_size:]

#LSTM 입력 데이터 생성
def create_dataset(data, lookback):
X, Y = [], []
for i in range(len(data) - lookback):
    X.append(data[i:(i + lookback), 0])
    Y.append(data[i + lookback, 0])
return np.array(X), np.array(Y)

lookback = 10 # 이전 10일의 데이터를 기반으로 예측
X_train, Y_train = create_dataset(train_data, lookback)
X_test, Y_test = create_dataset(test_data, lookback)

#LSTM 모델 구성
model = Sequential()
model.add(LSTM(32, input_shape=(lookback, 1)))
model.add(Dense(1))

#모델 컴파일 및 학습
model.compile(loss='mean_squared_error', optimizer='adam')
model.fit(X_train, Y_train, epochs=50, batch_size=16)

#테스트 데이터를 사용하여 예측
predictions = model.predict(X_test)
predictions = scaler.inverse_transform(predictions)

#실제 데이터와 예측 데이터 비교
df_predictions = pd.DataFrame(predictions, columns=['Predictions'])
df_predictions['Actual'] = scaler.inverse_transform(Y_test.reshape(-1, 1))
print(df_predictions)

​

결과값 :

​

Predictions Actual

0 50284368.0 50108356.0

1 50338856.0 50834512.0

2 50911044.0 52121544.0

3 52035676.0 50548744.0

4 51556176.0 51299848.0

.. ... ...

351 22036330.0 21274356.0

352 21884058.0 21097826.0

353 21707998.0 20980342.0

354 21548640.0 20950968.0

355 21451494.0 20881450.0

​

[356 rows x 2 columns]

​

시각화

​

import matplotlib.pyplot as plt

#테스트 데이터를 사용하여 예측

predictions = model.predict(X_test)

predictions = scaler.inverse_transform(predictions)

#실제 데이터와 예측 데이터 비교

df_predictions = pd.DataFrame(predictions, columns=['Predictions'])

df_predictions['Actual'] = scaler.inverse_transform(Y_test.reshape(-1, 1))

​

가져온 데이터 github link : https://github.com/financedata-org/FinanceDataReader