지지플랏의 DataScience

예상독자

• 로컬 머신와 빅쿼리를 연결해보고 싶은 독자
• 데이터 적재 자동화를 동해 태블로에 시각화하고 싶은 독자

1. 배경

데이터분석을 넘어 데이터 파이프라인을 만들어 자동화를 하는 경우가 생긴다. 제일 좋은 것은 데이터 엔지니어 혹은 개발자에게 요청하는 것이겠지만 prototype 혹은 나만의 product를 만드는 관점에서 진행해보고자 한다. 

GCP 혹은 AWS와 같은 서버(인스턴스)를 생성하여 서버를 사용하는 법이 있긴 하지만 초보자들에게는 서버관리의 어려움도 있고 삽질을 조금 해야해서 최대한 간단한 방법인 google SDK 를 이용해서 big query에 데이터를 저장하는 방법을 알아보겠다. 본 글에서 구현하고자하는 기능은 다음과 같다.

1. Google Cloud Storage 로그인
2. Google Cloud SDK를 이용한 로컬과 big query 연결
3. big query에 해당 데이터를 저장
4. big query에서 태블로온라인으로 데이터 가져오기

2. 본문

1. Google Cloud Storage 로그인

GCP 에 처음 접속하면 무료 계정을 생성하라 하며,  개인 정보와 결제정보를 요구하는데, 첫 가입에는 300$ 약 40만원의 크레딧을 지급해줍니다.  순순히 가입해봅니다. 우리는 용량을 그렇게 많이 쓰지 않을 것이니 걱정은 덜으셔도 됩니다. 

만약 이미 GCP에 가입해있다면 이번 단계를 넘기셔도 되며, 첫 로그인임에도 입력하는 창이 안 뜨면 뜰때 입력하시면 됩니다.(저의 경우 이미 GCP에 가입되있어서 어떤 단계에서 진행되는지 모르겠네요)

https://cloud.google.com/?hl=ko

만약 본인이 빅쿼리를 지나치게 많이 쓴거 같다 싶으면 위와 같은 GCP 콘솔 홈화면에서  크레딧 사용량을 보면 됩니다. 

2. Google Cloud SDK를 이용한 로컬과 bigquery 연결

Google Cloud SDK란?

Google Cloud SDK란 Google Cloud 제품 및 서비스와 상호작용하기 위한 도구 및 라이브러리입니다. 쉽게말해 내컴퓨터와 bigquery를 연결하는 방법이며 따로 프로그램을 깔아주어야 합니다. 단 개발용 설정이므로 서비스용으로 사용하기에는 적절하지 않습니다. 서비스용으로 진행하려한다면 Json 형식의 서비스 계정 키를 로컬에 발급받아야합니다. 다음 구글 공식문서를 참고해주세요. 

https://cloud.google.com/docs/authentication/application-default-credentials?hl=ko

돌아와서 SDK 설치 진행방법으로 계속하겠습니다. 

설치주소: https://cloud.google.com/sdk/docs/install?hl=ko

설치가 완료되면 어떤 프로젝트를 고를지 뜹니다. GCP 홈페이지에 들어가면 기본 프로젝트 명을 확인할 수 있습니다. 혹은 다음 명령어로 프로젝트 리스트를 확인할 수 있습니다. 

gcloud projects list

반면 홈페이지로 볼때는 이름은 MyFirstProject인데 프로젝트 리스트를 보면 eru 로 시작하는 ID  로 확인되네요

위 cmd 창에서 3번을 입력하면 gcloud 와 상호작용할 수 있는 명령줄이 나옵니다. 

고인물 들은 cmd로 하시는 것에 익숙하겠지만 저는 그렇지 않으므로, 창을 닫도록 하겠습니다. 

만약 프로젝트를 바꾸고 싶다면 다음 명령어를 치시면 됩니다.

gcloud config set project <프로젝트id>

2. 윈도우에 환경변수 설정

SDK는 설치했지만 환경변수에 등록되지 않은 경우  Python 스크립트에서 빅쿼리연결이 안 됩니다. 이를 위해 먼저 SDK연결이 된 경로를 확인해 봅니다. 

윈도우의 경우 "C:\Users\snowg\AppData\Local\Google\Cloud SDK" 이 경로에 있습니다. 내PC 혹은 탐색기를 이용하면 접근할 수 있는데 만약 폴더가 안보인다면 보기옵션 - 숨긴항목 체크를 하면 숨겨진 폴더가 나옵니다.

이번에는 해당 경로를 윈도우가 인식하도록 환경변수를 설정해주겠습니다. 

제어판 - 시스템 - 고급 시스템 설정 - 환경 변수 탭을 선택하면 다음과 같은 화면이 나오며, 시스템 변수에서 Path 변수를 확인할 수 있습니다. Path 변수 클릭 - 편집 - 새로만들기 하여 다음 과 같은 경로를 추가해줍니다.

경로명: C:\Users\snowg\AppData\Local\Google\google-cloud-sdk\Cloud SDK\bin

모두 확인을 누르고 나가줍니다.

환경 변수 설정이 잘 되었는지 보기 위해서 cmd 창을 열어 1번 마무리에 띄워서 cloud에 접속했던 것과 동일하게 하려면 다음 윈도우키 - 명령프롬프트를 실행한 뒤 다음 코드를 치면 됩니다. 그럼 구글 이메일로 인증화면이 오면서 gcloud에 접속할 수 있게 됩니다. 

gcloud auth login

* 주의 * 

이 부분에서 엇갈리는 분기가 있습니다. 바로 서비스계정이냐 개발용 계정이냐의 차이입니다. 

만약 서비스 계정이라면 보안의 이유로 인해 위의 SDK설치로 진행하는 것은 권장되지 않으며 google application credential 획득을 big query가 제공해주는 json 형식의 서비스 계정 키를 사용하는 것이 적절합니다. 하지만 우리는 간단하게 연결할 것 이기 때문에 SDK로 진행할 것입니다. 

왜 이 얘기를 하게 되었냐면 SDK로 설치할 경우 따로 서비스 계정 키를 사용하지 않으므로 다음 명령어를 통해서 이메일을 이용하여 계정의 인증정보를 로컬 시스템에 저장할 것입니다. 

gcloud auth application-default login

3. Python을 이용하여 빅쿼리에 데이터 넣기

- GCP 콘솔로 돌아와 좌측 줄 세개 - Big Query - BigQuery Studio를 선택해줍니다.  

erud로 시작하는 경로에는 아무것도 현재 없는 걸 볼 수 있습니다. 

이제 vscode를 이용해서 데이터를 불러오고 bigquery에 데이터를 적재해보겠습니다. 저는 간단하게 yfiance데이터를 가져오겠습니다. 

• 그리고 처음이라면 googl.cloud 모듈을 설치해줍니다. 파이썬이 google cloud에 접근할 수 있도록 해주는 모듈입니다.
• 또한,  pandas의 데이터프레임 객체를 google bigquery(gbq)에 올리는 라이브러리는 pandas 2.2.0. 버전부터 없다고 하니 따로 pandas-gbq 모듈을 설치주어야 합니다. 

!pip install google.cloud
!pip install pandas-gbq

그리고 다음 코드를 이용하여 데이터셋을 생성하고, 데이터를 넣어줍니다.   여기서 설정해주어야하는 것은 데이터셋의 이름(my_dataset), 테이블명(my_table)과 빅쿼리에 올릴 데이터프레임 객체(stock_data)입니다. 

from google.cloud import bigquery
from google.cloud.exceptions import NotFound

# BigQuery 클라이언트 생성
client = bigquery.Client()
project_id = client.project #현재 gcloud 명령어를 통해 접속한 프로젝트

### 여기 설정 ###
dataset_name = 'my_dataset' # 데이터셋 명명
### 여기 설정 끝 ###

# 데이터 세트 ID 설정. 이 ID는 '[YOUR_PROJECT_ID].[DATASET_ID]' 형식이어야 합니다.
dataset_id = "{}.{}".format(project_id, dataset_name)

# 데이터 세트 참조 생성
dataset_ref = bigquery.DatasetReference.from_string(dataset_id)

try:
    # 데이터 세트가 존재하는지 확인
    client.get_dataset(dataset_ref)
    print("Dataset already exists.")
except NotFound:
    # 데이터 세트가 존재하지 않으면, 데이터 세트 생성
    dataset = bigquery.Dataset(dataset_id) # 데이터 세트 설정
    dataset.location = "asia-northeast3" # 데이터 세트의 지역을 설정(서울로 설정)
    dataset = client.create_dataset(dataset)  # API request # 데이터 세트 생성
    print("Created dataset {}.{}".format(project_id, dataset.dataset_id))


### 여기 설정 ###
table_name = 'my_table' # 데이터 테이블 명
### 여기 설정 끝 ###

table_id = "{}.{}".format(dataset_name,table_name)

# 구글빅쿼리(gbq)에 올릴 데이터프레임 변수를 기입
stock_data.to_gbq(destination_table=table_id, project_id=project_id, if_exists='replace')

그럼 다음과 같이 업로드 되었다는 출력이 나옵니다. 

또한, bigquery studio에서 데이터셋과 테이블이 생성된 것을 볼 수 있습니다. 

이로서 로컬에서 가공하거나 데이터 예측을 수행한 데이터를 빅쿼리에 올리는 과정까지 마쳤습니다. 

*혹시* 

위 과정에서 DefaultCredentialError 가 뜬다면 제대로된 인증정보를 찾지 못하였다는 것입니다.  새롭게 설치하는 환경에서 이런 문제가 생기던데, 이경우 다음 코드를 통해 gcloud 모듈을 다시 설치하면 해결되는 경우가 많았습니다. 

!pip install google.cloud

아마 SDK를 설치하면서 중간에 google.cloud 모듈이 꼬이는 것 같은데 정확한 원인은 알 수 없네요  😥

4. bigquery에서 태블로온라인으로 데이터 가져오기

새로운 통합문서를 연 뒤 데이터에 연결 - 커넥터 - 구글 빅쿼리(JDBC)를 선택해 줍니다. 

그럼 다음과 같이 청구 프로젝트ID를 입력하라고 하는데,  bigquery studio에 있는 프로젝트ID를 선택해주면 됩니다. 

이후 프로젝트와 내 테이블을 드롭다운 메뉴로 선택하게 되면 데이터를 정상적으로 불러올 수 있습니다. 혹시모르니 "라이브"가 아닌 "추출"로 우측 상단 선택하시고 데이터를 업데이트하시면 확인 가능합니다. 

마무리로 혹시 이 짧은  데이터에서도  크레딧이 청구가 되었는지 확인해 보겠습니다. 

사실 데이터가 너무 작아서 크레딧 사용이 안되긴 하였다. 여기에서 우측에 있는 My First Project 프로젝트 명을 선택하면 사용량을 확인 할 수 있다. 

아무래도 크레딧이 계정당 정해져 있고, 혹여나 데이터가 큰 경우 크레딧 남발이 될 수 있으니, 이 사용량을 잘 확인하면서 빅쿼리를 사용하면 되겠다. 같은 맥락으로 태블로에서도 라이브가 아닌 추출로 설정하여 단발로 사용하길 권하는 것이다. 

3. 마무리

한창 프로젝트 하겠다고 AWS Micro service를 몇개 설정하고 했는데, 제일 힘들었던게 막 열어버린 나의 서버들을 한땀한땀 중지시키고 요금 발생할 곳을 검토하는게 어려웠다. 알고보니 서버가 덜 닫혀서 과금된 기억이 있다. 그래서 더 이런 클라우드 서비스를 할 때 최대한 관리가 가능하도록 글을 썼다.  물론 서버를 만들고 사용하는 것은 좋은 경험이지만,  돈벌러 공부하는데 돈쓰면 슬프니까(그게 수업료일지도...?) 

반면에 참 예전보다는 클라우드 서비스 쓰기 좋은 세상이 온 것 같다. 

4. 글또 9기 글 모음

• 당신은 절대 글을 잘 쓸 수 없다.
• 기호주의와 연결주의로 알아보는 AI의 발전
• 알고리즘은 데이터 분석가에게 필요할까?
• Analytics Engineer 직무 뜯어보기
• 데이터 분석을 위한 준비하고 배울 것들
• 시계열 Prophet 모형과 하이퍼파라미터 뜯어보기 with 주가데이터
• Google bigquery에 데이터를 적재하고 태블로로 데이터 가져오기

예상 독자

• 시계열 데이터 분석을 입문해보자 하는 사람
• Python의 기본을 알고 있고 머신러닝을 접해본 사람

1. 들어가며

시계열 데이터는 말그대로 시간의 정보를 가지고 있는 데이터를 말합니다.  시간마다 측정된 관측치 값(일일 매출량, 재고량, 판매 수익)등을 예측하기 위한 시도는 과거로부터도 관심이 많았습니다. 미리 정보를 알 수가 있다는 것은 그만큼 경영전략을 미리 세울 수 있는 장점이 있기 때문입니다.  시계열의 메인 관점은 다음과 같습니다. 

과거가 미래에 어떤 영향을 주는가?

2. 머신러닝 모델과 시계열 모델의 차이

사실 시계열은 머신러닝의 부분 집합이지만 모델링 과정이 살짝 이질적이기 때문에 따로 공부하는 경우가 많습니다. 본 글에서는 머신러닝과 시계열 모델을 구별해서 명명하겠습니다.

대표적으로 머신러닝을 배운 학생들의 경우 간과하는 것이 머신러닝 데이터의 예제는 대부분 독립변수에 시간이 없는 반면  독립변수가 하나하나 특징의 정보를 가지고 있습니다.타이타닉 데이터의 예로 들면 독립변수가 성별, 나이, 좌석등급 인 것처럼 말입니다. 그런 이유 때문인지 시간이 독립변수인 경우 이질적인 느낌이 드는 경우가 많습니다. 대부분 시계열 데이터는 독립변수는 시간, 종속변수는 관측값 일 뿐입니다. 

머신러닝 모델과 시계열 모델의 차이를 아래에 기술해보겠습니다.

3. 시계열 데이터 모델의 종류

1. ARIMA: 가장 많이 사용하는 모델로 데이터의 시계열적 특성(자기상관성*)을 모델링합니다. 비계절적 데이터에 자주 사용됩니다. 확장형으로 계절적 변동성을 포함한 시계열 모델링을 하기 위한 S-ARIMA(seasonal ARIMA)라는 확장형 모델도 있습니다. 

*자기상관성: 서로 다른 두 시점에서의 관측치  사이에 나타나는 상관성. 시계열 데이터는 시간 의존성에 의해서 인접한 변수간의  자기 상관성이 나타날 수 있음.

2. LSTM(Long Shrot Term Memory): 딥러닝 중 순환신경망의 한 형태로 시계열 데이터의 장기 의존성을 효과적으로 학습할 수 있는 장점이 있습니다. LLM 전에 한때 유행했던 알고리즘이지만 요즘은 잘 언급이 안됩니다(ㅠㅠ)

3. Prophet: 계절성, 휴일, 주말(특별 이벤트)을 고려한 시계열 예측을 가능하게 합니다. 사용하기 쉽고 다양한 시계열 데이터에 적용할 수 있는 장점이 있습니다.

4.  XGBoost, LightGBM 등 앙상블 기반 모델: 기술적 지표,  이동평균 등 시계열 데이터의 특성을 이용해 예측할 때 유용할 수 있습니다. 

5. 지수평활법: 계산이 간단하고 구현이 용이하여, 시계열에서 단기 예측을 위해 널리사용됩니다. 실시간 데이터 업데이트에 효율적입니다. 태블로에서 시계열 예측에서 구현되어 있습니다.

4. Prophet 모형

Prophet 은 2017년 메타(구 페이스북)에서 공개한 시계열 예측 라이브러리입니다. 메타가 최근에도 LLM 관련하여 LLama와 같은 sLM 모델도 공개하는등 구글과 더불어서 데이터 사이언스에 기여하는 바가 많은 것 같습니다. 저도 재직 당시에 회사에서 제품 판매량 예측 보고서를 위해서 Prophet 모델을 적용했던 사례가 기억이 나네요. 시계열은 파다보면 끝도 없이 깊게 들어갈 수 밖에 없기 때문에 간단하게 Prophet 모델을 적용하는 시간을 가져보고도록 하겠습니다. 

https://facebook.github.io/prophet/

4.1 .이론

Prophet 모델의 주요 구성요소는 Trend, Seasonality, Holiday 입니다. 이 세가지를 결합하면 아래의 공식으로 나타낼 수 있습니다.

• g(t):  Trend를 구성하는 요소로 주기적이지 않은 변화인 트렌드를 타나냅니다.
• s(t): Seasonality 를 구성하는 요소로 weekly, yearly 등 주기적으로 나타내는 패턴을 말합니다.
• h(t): Holiday 를 구성하는 요소로 휴일과 같은 불규칙한 이벤트를 나타냅니다.(꼭 휴일일 필요는 없고 event라는 점에 주목하세요 예를들어 주가는 휴일에 장이 쉬기 때문에 사용할 수 없는 변수라고 생각할 수 있는 반면, 월/금과 같은날 주문량의 몰리기 마련입니다. 이걸 event로 정의할 수 있습니다.)
• E(i): 오차항입니다.

4.2. 실습

애플의 21년 ~ 23년 주가데이터를 기준으로 1년 주가를 예측하는 실습을 진행해보겠습니다. 

1. 모듈 불러오기

import pandas as pd
import numpy as np
from prophet import Prophet
# import pandas_datareader.data as web
import yfinance as yf
import matplotlib.pyplot as plt
plt.style.use('fivethirtyeight')

2. 주가 데이터 가져오기

start = '2021-01-01'
end = '2023-12-31'
ticker = 'AAPL'

df = yf.download(ticker, start=start, end=end)
df['ds'] = pd.to_datetime(df.index, format = '%Y-%m-%d')
df['y'] = df['Close']
df = df[['ds','y']]
display(df)

3. 주가 데이터 시각화

ax = df['y'].plot(title = ticker, figsize =(12,4))
ax.set_ylabel('Closs price (Won)')
plt.show()

4. 모델 적합

model_prophet = Prophet(changepoint_prior_scale = 0.15, daily_seasonality = True)
model_prophet.fit(df)

5. 향후 1년간의 time stamp 생성

fcast_time = 365 # 365일 예측
df_forecast = model_prophet.make_future_dataframe(periods = fcast_time, freq = 'D')
df_forecast.tail(10)

6. 예측 결과 생성

df_forecast = model_prophet.predict(df_forecast)
df_forecast[['ds','yhat','yhat_lower','yhat_upper']].tail()

7. 예측값 시각화

model_prophet.plot(df_forecast, xlabel = 'Date',ylabel ='price($)');

4.3. 하이퍼 파라미터 튜닝

Prophet 모델에서 조정 할 수 있는 파라미터는 Trend, Seasonality, Holiday 3가지로 나눌 수 있습니다.  위의 예측값 시각화 한 값은 이 3가지를 모두 반영한 그래프입니다. 세부 componenet 을 확인하기 위해서 다음 코드를 작성합니다.

model_prophet.plot_components(df_forecast);

1) Trend

Prophet 모델에서 기본적으로 Trend와 관련된 파라미터는 changepoint라는 단어가 붙어있습니다. 명시적으로 작성하지 않아도 기본 값이 정해져 있으나 파라미터 튜닝을 위해서 임의의 값을 넣어 줄 수 있습니다. 

y축 스케일이 작아서 그렇지 우상향하는 그래프의 트렌드를 확인할 수 있습니다. 처음 모델을 수립할때 changpoint_prior_scale을 0.15 설정하였는데요. 이 스케일을 더 크게 설정할 수록  트렌드를 더 디테일하게 학습하겠지만, 너무 디테일하게 학습하면 과적합이 문제가 생길 것으로 예상할 수 있습니다.

changpoint_prior_scale을 0.3으로 설정하면, y축이 늘어나는데도 불구하고 트렌드 그래프가 좀 더 각진 형태를 확인할 수 있습니다.

Trend 관련 파라미터

위 파라미터에 대한 자세한 설명은 다음 깃허브에서 찾아볼 수 있습니다.

https://github.com/facebook/prophet/blob/main/python/prophet/forecaster.py

2) Seasonality

Seasonality는 문자 그대로 4계절을 연상하기 쉬운데 그보다는 주기적으로 돌아오는 변화라고 이해하는게 더 맞습니다. 따라서 파라미터도 연, 주, 일 계절성을 설정할 수 있습니다. 일례로 주가의 경우 배당시기가 오면 주식을 매수하는 경향이 있습니다. 이를 Seasonality에 적용 할 수 있습니다. 

year_seasonality를 2배로 올렸더니 연간 계절성을 더 많이 학습하는 것 처럼 보입니다. 이 역시 과적합을 조심해야겠네요. 

Seasonality 관련 파라미터

seasonality mode 의 경우 계절성을 합연산인가(additive) 아니면 곱연산(multiplicative)하게 가져갈 것이냐의 차이 입니다. 이부분은  Multiplicative Seasonality 에 자세히 나와있는데 사실 2장의 그래프면 쉽게 이해할 수 있습니다.  대표적인 비행기 승객 수에 대한 데이터와 시계열 예측입니다. 

비행승객 수는 계절성을 띄는 경향이 있지만 그 계절성이 점차 큰 폭으로 증가하는 경향이 있습니다. 전자 additive의 경우는 1950년에는 계절성을 과대평가하는 반면 1960년대에서는 과소평가하는 경향이 있습니다. 비행기 승객은 점차 꾸준히 발전해왔기 때문입니다. 이 때문에 multiplicative 한 계절성이 더 잘 맞다고 말할 수 있습니다. 

3) Holiday

Holiday도 역시 공휴일이라는 event를 추가할 수 있습니다. 간단하게 모델객체에 추가할 수 있습니다. 

model_prophet = Prophet(changepoint_prior_scale = 0.15, daily_seasonality = True)
model_prophet.add_country_holidays(country_name='US')
model_prophet.fit(df)

반면 명시적으로 데이터프레임 등으로 이벤트 날짜를 넣고 싶은 경우 다음 파라미터를 참고하면 됩니다.

Holiday 관련 파라미터

hoidlays 에는 데이터프레임 객체가 들어가는데 첫 열은 holidays 라는 문자열, 두번째 열은 날짜가 들어갑니다. 또한 공휴일 전후로 이벤트가 있는 경우(주식과 같은 경우) lower_window 과 upper_window 컬럼을 옵션으로 추가하여 공휴일 전후 날짜를 인식하게 만들수 있습니다. holiday 데이터 처리하는데 꽤 시간이 걸릴 것 같네요

5. 출처

[1] https://facebook.github.io/prophet/

[2] 시계열 예측 패키지 Prophet 소개, Hyperconnet 

[3] Prophet을 이용한 주가 예측

글또 9기 글 모음

• 당신은 절대 글을 잘 쓸 수 없다.
• 기호주의와 연결주의로 알아보는 AI의 발전
• 알고리즘은 데이터 분석가에게 필요할까?
• Analytics Engineer 직무 뜯어보기
• 데이터 분석을 위한 준비하고 배울 것들
• 시계열 Prophet 모형과 하이퍼파라미터 뜯어보기 with 주가데이터

오늘은 준비하고 배울것들을 주제로 데이터분석가가 뻗어나갈 수 있는 Scope에 대해서 작성해보고자 한다.

배경설명

2018년에 데이터분야를 입문하고 의료데이터 분석을 거쳐 이제 5년차를 접어들고 있는 분석가이다. 요즘은 부트캠프에서 학생들에게 튜터링을 진행하고 있는데 나는 당연하다고 생각했던 지식들이 입문자들에게는 어렵고 어떻게 배울지 모르는 부분이 많은 것 같다.  이부분은 고찰해보고 추후 특강 자료로 활용할 생각이다.

예상독자는 데이터분석을 입문하려는 취업준비생이다.

본문

1. 기본 통계지식

통계라는 단어는 무겁고 그 깊이가 끝이 없는 학문이라 어디까지 배워야한다는 다들 다양한 관점이 있을 수 있으나, 적어도 분석을 수행하기 위한 최소한의 기준은 있는 듯하다.

나는 다음 학습을 권하는 편이다.

관련 학습자료
- 기초 통계: 통계의 힘
- 데이터 리터러시: ex 데이터문해력책
- ADspP


2. SQL

SQL은 아마 데이터 분석가라면 거의 필수로 요구되는 언어이다. 관계형 데이터베이스는 이미 기업에 깊게 파고들어서 이를 이용하기 위한 SQL은 필수가 되었다.

SQL은 프로그래밍언어지만 우리가 생각하는 프로그래밍과는 괴리가 있으며 적어도 문법이 어렵진않다(어색할 뿐이다). 그보다 더 큰일 인 것은 SQL은 실제 업무를 할때 부족한 점을 느낄뿐 책로 공부하는 것은 그 중요함이 와닿지 않을때가 많다는 점이다.

SQLD가 관련 자격증인데 취준생들에게 요구되는 자격증이다. oracle 기반의 standard를 배운다는 점이 중요하다. 반면 1과목의 데이터모델링은 현업에서 일하다보면  모호한 개념이 정리되기 때문에 3년차 이상 현업자에게도 재미로 한번쯤 보길 권하는 편이다.

관련 업무
비즈니스 분석: 비즈니스 이해도
데이터마트 생성: Database 지식

관련 학습 자료
자격증: SQLD

3. 파이썬

파이썬은 분석가에게 뭐랄까 계륵일때가 있다. 개발, 분석 다양한 범주를 아우르기에 있으면 좋다! 라는 느낌이 있다.

반면 최신은 분석가의 역할이 LLM에 대체될 것이라고 믿는 분위기라  데이터 엔지니어링을 포함한 개발 스콥을 야금야금 접수하고 있는 것이 트렌드이다.  이런 관점에서 파이썬은 익혀놓으면 쓰기 좋다.

초기 머신러닝이 활발할때 파이썬 무조건! 이라는 분위기가 있었는데  요즘은 LLM 이 워낙 활발해서 기업내에서 Langchain을 이용한 활용에 관심이 많다.


관련 업무
시각화: steamlit(대시보드), folium(지도시각화)
생성형API: langchain

관련학습자료
점프투파이썬
오픈소스 docs

Action Item

• 다차원 척도법으로 업무 내용을 구분해보기. X축 문과적-이과적(유사 기술적 난이도) 등
• 최근 학습자료 업데이트하기

7. 글또 9기 글 모음

• 당신은 절대 글을 잘 쓸 수 없다.
• 기호주의와 연결주의로 알아보는 AI의 발전
• 알고리즘은 데이터 분석가에게 필요할까?
• Analytics Engineer 직무 뜯어보기
• 데이터 분석을 위한 준비하고 배울 것들

글의 목적

데이터 분석은 self serving부터 시작한다는 가치관을 가지고 일을 하다 보니 엔지니어링 프레임워크 관심을 두게 되었다. 이런 흐름을 반영하듯 Analytic Engineer 직무가 최근에 국내 기업에도 도입되고 있는 것 같아서 구직활동 겸 공부해본다. 

대상독자

1. Analytic Engineer에 관심 있는 현직자, 취준생
2. 데이터 분야에 흐름이 궁금한 개발/데이터 직군

1. Analytics Engineer(AE) 출현배경

데이터의 저장비용하락과 처리기술의 발전으로 빅데이터 직군이 태동하면서 빅데이터 관련 직무들이 많이 파생되어 가는 것은 잘 알려진 사실이다. 이 부분은 성윤님의 유튜브에서 잘 설명된 장표가 있어서 대체한다.

데이터 산업이 발전함에 따라 기존의 세부직군도 회사에 따라 재정의 되며 나눠지게 되었고 그 대표적인 예가 기존에 데이터분석가가 비즈니스 결정을 돕는 비즈니스분석가와 서비스의 발전을 지향하는 프로덕트 분석가로 나누어진 것 이다.

이런 상황에서 데이터 엔지니어와 데이터분석가의 gap을 채워줄 중간자의 역할로 Analytics Engineer가 새로운 직군으로 나타난 것은 비슷한 결의 현상으로 예상할 수 있다. 데이터를 self-serving 하는 역량이 꽤나 중요해졌고 기존 데이터 분석가에게는 실험과 인사이트 발굴에 집중해야 하는 데이터분석가에게는 프로그래밍 역량도 높다고 기대하기 어려우니 그 현실을 반영하는 것 같다. 

2. Analytics Engineer 가 해야할 일

이런 출현 배경 속에서 AE가 할일은 당연히 엔지니어링과 데이터 분석 그 어딘가 일 것이다. AE 자체가 직군을 세분화하면서 만들어진 상황이다 보니 데이터 그룹의 성숙도에 영향을 받을 수 있다는 점을 참고하면 좋을 것 같다. 따라서 역할을 깊게 이해하려면 데이터 파이프라인의 맥락 속에서 이해해보려 한다.

① 데이터 처리 및 분석 플랫폼

• 역할: 대규모 데이터 세트의 처리 및 분석을 수행하며, 실시간 및 배치 데이터 처리를 위한 플랫폼을 구축한다. 데이터 처리의 성능과 효율성 향상에 초점
• 기술 스택 예시
  1. Hadoop: 대규모 데이터 세트를 처리하고 저장하기 위한 오픈소스 프레임워크로 분산 파일 시스템(HDFS)를 사용하여 데이터를 저장하고 대용량 데이터 처리를 수행
  2. Apache Spark: 메모리 기반 분산 컴퓨팅 시스팀. 실시간 처리

② 데이터 모델링 및 웨어하우징

• 역할: 데이터의 구조화 및 조직화, 데이터 웨어하스 내 테이블과 마트 설계, 데이터 적재 조회 분석을 위한 효휼적인 모델링
• 기술 스택 예시

1. SQL 데이터베이스: MySQL과 같은 데이터 웨어하우스에서 테이블 설계 및 관리
2. 데이터 웨어하우징 솔루션: Amazon RedShift, Snowflake 등 대규모 데이터를 효율적으로 저장하고 분석

③ 데이터 파이프라인 및 오케스트레이션

• 역할: 데이터의 ETL 과정을 관리하고, 다양한 데이터 소스와 시스템의 데이터 흐름 조율, 파이프라인 자동화 및 최적화
• 기술 스택 예시: 데이터 파이프라인 워크플로우 관리 플랫폼 Apache Airflow

3. DE, AE, DA 별 차이 정리

4. 그래서 공부 뭐해야 함?

결론은 어떻게 입문할까 고민하고 네이버,토스 등 공고를 확인 해보았을 떄 한가지 문장으로 요약될 수 있었다.

DBT, Spark, Airflow,  를 기반으로 한 데이터 파이프라인 개발 지식과 경험

위 성과를 달성하기 위해서 참고해볼만한 컨텐츠를 커뮤너티와 지인들에게 수집해봤는데 아무래도 데이터엔지니어링 관련 과목들 중에 AE의 Job Description에 적용해볼 만한 것을 고르는 게 고민스러웠다. 정리해보자면 다음과 같다.

• 공통: 프로그래머스 데브코스 데이터 엔지니어링
• 공통(책): 빅데이터를 지탱하는 기술
• DBT: The Complete dbt (Data Build Tool) Bootcamp: Zero to Hero
• airflow(책): Apache Airflow 기반의 데이터 파이프라인
• airflow: Marc Lamberti Udemy 강의

5. 마무리

생각보다 오랜만에 기술적인 내용을 파려니까 모호한 단어들과 상상력을 동원해서 이해하기가 힘들었다. 아직도 모호하지만 한편으로는 새로운 세계로 진입하게 되는 것 같다 흥미롭기도 엉덩이가 들썩들썩 ~ 이제 공부하자 !

*이 글을 쓰는데 알짜시간으로는  4시간, calender day로 2일 걸렸습니다.

6. 출처

• 카일스쿨 - 요즘 데이터 분석가의 현실, 데이터 분석 직군의 세분화 트렌드
• Selecting the right tech stack for a data pipeline solution
• PAP - 데이터 잡부, 근데 이제 Analytic Engineering을 곁들인
• What is analytics engineering?
• What Will “Analyst 2.0” Look Like?

7. 글또 9기 글 모음

• 당신은 절대 글을 잘 쓸 수 없다.
• 기호주의와 연결주의로 알아보는 AI의 발전
• 알고리즘은 데이터 분석가에게 필요할까?
• Analytics Engineer 직무 뜯어보기

1. 글의 목적

종종 데이터 부트캠프에 강사로 참여하다 보면 알고리즘 강의를 해달라는 요청을 받는다. 알고리즘이란 무엇인지 그리고 분석가가 배우면 좋은 점은 어떤 이득이 있는지 작성해본다.

2. 예상독자

• 데이터 직무에 진입하려는 입문자
• 알고리즘을 간단하게 알아보고자 하는 사람

3. 본문

•  도대체 알고리즘이 뭐고 왜 쓰이는가? 

알고리즘은 쉽게 말하면 개발자의 수능, 적성문제이다. 프로그래밍이라는 실력의 척도를 측정하기 위한 하나의 도구로 컴퓨터 과학에 등장하는 알고리즘이 발전되어왔다. 알고리즘을 잘한다고 프로그래밍 실력을 보장하진 않지만 어느정도 최소치를 만족하기에 입사시험으로 쓰고 있는 것이다. 

• 그럼 분석가가 알고리즘을 왜 배워야 하는가?

1. 단적인 예!: 누군가에게 아쉬운 소리(특히 개발자에게) 하지 않아도 된다.

마치 특강을 위해 만든 자료가 있어서 대체!

2. 분석가는 프로그래밍을 업무에 활용할 일이 많다.

데이터 엔지니어는 개발자 트랙이니 말할 것도 없고 데이터 사이언티스트는 Python 프로그래밍으로 모델링을 하기 때문에 역시 필수적이다. 또한,  데이터 분석가는 업무에 이바지할 부분이 많다. 데이터 분석의 좁은 분야는 RDB(관계형 데이터베이스)기반에서 데이터를 뽑아내는 쿼리 작업부터 시작하고 그 부분에서  비즈니스적인 내용을 붙일지, A/B test를 포함한 통계를 붙일지 등등 뻗어나갈 분야는 많다. 어느 방향으로 주 분야를 뻗어 나가던 확실한 것은 Python과 같은 프로그래밍을 다룰 줄 아는 것은 업무 범위를 확장하기에 매우 좋다는 것은 사실이다. 

• 그럼 얼마나 배워야할까?

개인적인 기준으로는  프로그래머스와 같은 코딩 테스트의 기본 문제들만 풀어도 된다고 생각한다. 하지만 Lv3,4 혹은 카카오 입사시험 테스트를 본격적으로 푸는 것은 조금 길에서 벗어난 방법이라고 생각한다.

반면, 기본적인 알고리즘을 익히고 파이썬 라이브러리가 지원하는 함수와 메소드를 잘 이해하는 게 좋다.

오히려 기본적인 알고리즘을 배우고 이를 통해 API로 데이터 수집하기, Airflow로 자동화 하기와 같은 데이터 엔지니어링 부분을 확장하거나 분석모델링(간단한 선형회귀, 군집화)등을 활용할 정도만 되어도 된다. 데이터 분석의 본질은 문제를 인식하고 근거기반으로 판단하는 방법이 목적이며, 데이터 가공, 추출, 코딩 등은 이를 원활하게 하는 방법임을 잊으면 안 되겠다.

• 대표적인 데분에 활용할만한 내용 살펴보기 

알고리즘에서 나오는 문제 중 하나의 개념을 알아야한다면, 당연히 배열이다. 정형데이터 분석에서 사용하는 자료는 배열이 대부분이며, Numpy의 array 자료형, Pandas의 Series 형 자료형도 모두 배열의 추상화 개념을 상속한 것이 때문에, 나는 가장 이 부분을 강조한다. 사실 가장 직관적이기도 하고..!

또한, 배열자료형 하면 또 Pandas DataFrame 자료형을 빼놓을 수 가 없는데, 파이썬에서 대용량 데이터를 사용하다보면 메모리의 압박으로 문제가 생기는 경우가 있다. 이 경우 데이터의 메모리 효율을 고민하여 성능과 속도를 높히게 되는데, 이 부분에 대한 설명은 이수진님의 블로그글이 잘 정리되어 있어서 자주 참고하는 편이다 

https://lsjsj92.tistory.com/604

매년 이맘때쯤 부트캠프 & 데이터 사이언스 교육이 이뤄저서 그런지 딱 1년전에 L모기업 직원을 대상으로 교육했던 기억이 난다. 그때 공부했던 나의 기록링크를 남기며 이번에도 뉴비&초심자를 위한 자료를 한번 만들어 보면 좋겠다. 네? 데분 멘토링왔는데 알고리즘 2달 알려주라고요?

4. 글또 9기 글 모음

• 당신은 절대 글을 잘 쓸 수 없다.
• 기호주의와 연결주의로 알아보는 AI의 발전
• 알고리즘은 데이터 분석가에게 필요할까?

MySQL은 데이터베이스의 한 종류로, 가장 범용적인 데이터베이스입니다. 일반적으로 회사에 데이터베이스에 구축되어 있으면 따로 로컬컴퓨터에 설치하지 않고 SQL WorkBench, DBeaver등 어플리케이션 툴을 이용하여 접속하면 되기 때문에 설치할 일이 없습니다. 반면 개인 학습 혹은 서버를 띄운 상태에서 데이터베이스를 설치하고자 한다면 이 가이드를 통해 로컬에 MySQL 데이터베이스를 설치할 수 있습니다.

Last Updated

• 2024-06-26: Custom 설치방법 추가
• 2024-11-26: Initializing Database 오류 방법추가
• 2025-05-19: Trouble shooting 추가

1. MySQL 설치

MySQL을 설치하는 것은 설치파일을 실행 후 "다음", "다음"의연속입니다. 중요한 점은 중간에 mysql 접속을 위한 비밀번호 설정과 mysql 포트(지하철로 따지면 출구번호)가 3306으로 사전 지정이 잘 되어있는지 확인하면 됩니다. 이를 통해 MySQL을 로컬 컴퓨터에 설치할 수 있고 추후 Database에 접속할 수 있는 Command Line 혹은 DBeaver와 같은 Application을 통해서 테이블을 생성하고 SQL쿼리를 실행할 수 있게 됩니다. 

https://dev.mysql.com/downloads/mysql/

여기서 본인 운영체제에 따라 Windows 혹은 Macos 를 선택해줍니다.

2023년 12월기준 ver 8이 최신이네요. Download를 눌러서 설치파일을 받고 실행 하겠습니다. 두 파일의 차이는 설치 시 인터넷이 필요한가 아닌가의 유무여서 별다른 차이는 없습니다. 

• mysql-installer-web-community-8.0.35.0.msi: 인터넷 연결을 통해 다운로드 받는 설치 
• mysql-installer-community-8.0.35.0.msi: 인터넷 연결 없이 설치하는 full package

로그인 이나 회원가입을 하지 않고 No thanks, just start my download를 누르겠습니다.

실습이나 과제를 위해서는 Server only를 사용하는 걸 권장

• ※ 이부분은 기존 23년 1월 배포버전만 해도 Develop only 항목이 있었는데 최근되어서 삭제 되었다.
• Server only 방법) Server only로 진행하여도 크게 문제는 없다.오히려 Full 이 설치하는 것이 많아서 많이 무거워진다. 본인이 사용하려는 Application에 따라 선택하면 된다. 
  • 방법1: Server Only + DBeaver 와 같은 Application 따로 사용
  • 방법2: Full 설치하여 WorkBench Application이용  
• Custom 방법) Product를 구체적으로 다음을 추가한다
  • [MySQl Servers] - [MySQL Server] - [MySQL Server 8.0] - [MySQL Server 8.0.21 - X64]
  • [Applications] – [MySQL Workbench] – [MySQL Workbench 8.0] – [MySQL Workbench 8.0.21 – X64]
  • [Documentation] – [Samples and Examples] – [Samples and Examples 8.0] – [Samples and Examples 8.0.21 – X86]

MySQL를 설치를 위한 Microsoft Visual이 필요하여, Execute 버튼을 눌러줍니다. 

설정한 비밀번호 넣고 check

설치 완료 후 위 start ~~ 는 모두 해제 해주도록 합시다.

2. MySQL 실행하기

MySQL을 설치가 완료되었으니 잘 실행되는지 확인해 봅시다. 사전에 설정한 비밀번호를 입력 후 하기 코드를 이용해서 데이터베이스의 종류, 데이터베이스 선택, 테이블의 종류를 확인해 볼 수 있습니다.

# 설치된 데이터 베이스 조회
show databases;

# 데이터베이스 중 mysql 선택
use mysql;

# 선택한 mysql 데이터베이스의 테이블 조회하기
show tables;

3. Application을 통해 mysql 접속하기 - DBeaver

Command Line을 통해 쿼리를 날릴 수 있지만 인터페이스가 불편하기 때문에 DBeaver 라는 어플리케이션을 이용하여 연결해보겠습니다. DBeaver가 설치 되어있지 않다면 다음 글을 참조해주세요

https://velog.io/@yongseok0419/dbeaver-%EC%84%A4%EC%B9%98-%EB%B0%8F-%EC%84%A4%EC%A0%95

SQL WorkBench 등의 다른 Tool을 이용하여 연결할 수 도 있습니다. 

새 데이터베이스 연결 클릭

연결할 수 있는 다양한 Database를 지원합니다. 우리는 MySQL을 설치했기 때문에 해당하는 것을 클릭합니다.

MySQL 설치시 설정한 Password를 넣어줍니다. Username은 기본적으로 root로 설정되어있어서 변경하지 않아도 됩니다.

3306 포트의 localhost가 연결된 것을 확인 할 수 있습니다. 

4. 나의 데이터베이스 생성하기 

localhost의 좌측 꺽쇄를 누르니 sys 라는 Database밖에 조회되지 않았습니다. 기본적으로 필수적이지 않은 Database는 숨겨진 채로 DBeaver에서 조회할 수 없네요. 또한, sys 이름으로 유추하건데 시스템 정보에 관한 데이터베이스므로, 테이블을 생성하는 등 작업에 적합하지 않아 보여 새로운 데이터베이스를 생성해보겠습니다. 

우클릭 - Create New Database

Database 이름을 편의상 Ecommerce라고 설정하겠습니다.

테이블 조회를 하기 위해서 임의로 3개의 파일을 데이터베이스에 넣어 보도록 하겠습니다. 

Ecommerce 데이터베이스 - Table 에서 우클릭 - 데이터 가져오기

CSV 가져오기 클릭

파일 드래그 & 열기

총 3개의 테이블이 정상적으로 로드된 것을 알 수 있습니다.

5. 데이터 조회하기

SQL편집기를 이용하여 데이터를 조회해 보겠습니다.

SQL 편집기 탭 - 새 SQL 편집기

select * from List_of_Orders;

이렇게 MySQL 설치와 DBeaver 연결까지 모든 과정이 끝났습니다. 필요에 따라 추가적인 테이블을 불러오거나  혹은 외부 Server의 접속 정보를 받아 연결해볼 수 도 있겠습니다. 

6. 에러 해결하기

6.1. 설치 시 Initializing Database 에서 오류

Initializing Database에서 오류가 난다면 윈도우 사용자명이 한글명이여서그렇습니다. 윈도우 이름을 영어로 바꾸는 방법도 있지만, 다음 포스팅을 참고하는게 빠릅니다.

https://codinghalbae.tistory.com/9

6.2. Public Key Retrieval is not allowed(MySQL 8버전부터)

•  MySQL 8부터 일어나는 문제
  • 데이터베이스 우클릭 - Edit Connection - Driver Properties 의 False -> True로 변경하기

6.3. 데이터가 커서 로딩이 안되는 경우

• 기본적으로 DBeaver는 1024메가의 데이터를 램에 올려서 받을 수 있습니다.이를 힙사이즈라고합니다.
• 이 설정은 DBeaver.ini의 파일을 수정하여 해결 할 수 있지만, 컴퓨터의 가용 램에 따라서 적절히 산정해야합니다.
  • 맥 경로: cd /Applications/DBeaver.app/Contents/Eclipse/dbeaver.ini
  • 윈도우 경로: C:\Users\사용자이름\AppData\Local\DBeaver/ dbeaver.ini
• dbeaver.ini 파일에서 -Xmx1024m  부분의 숫자가 최대 용량 MB이므로 2048 혹은 ****(제한 없음)으로 변경해주면 됩니다.
• https://rastalion.dev/dbeaver%EC%97%90%EC%84%9C-java-heap-space-%EB%B6%80%EC%A1%B1%EC%9D%B4%EB%9D%BC%EA%B3%A0-%EB%82%98%EC%98%AC%EB%95%8C/

1. 글의 목적

• AI에  대하여 대중들이 쉽게 이해할만한 글을 작성해보자
• AI의 발전과정을 알아보고 기호주의와 연결주의에 대해서 알아보자 

2. 본문

• AI가 세상을 완전히 바꾸지 못하는 이유

머신러닝 딥러닝을 포함한 AI가 세상을 바꿀 것 같이 광고하고 있다. 하지만 실제로 산업에서 적용에 실패하고 AI는 허상이라고 느낄 수도 있을 것 같다. 여기엔 2가지 문제가 있다.

1. 데이터 분석 등 의사결정에 영향을 끼치는 AI는 그 성과를 정량화하기 어려움
2. 성과가 있더라도 그 노하우를 굳이 외부에 홍보할 필요가 없기 때문
3. 성과 낼 수 있는 딥러닝은 현재 이미지와 자연어 처리에 국한되어 있어, 작고 좁은 분야에서 활약을 하기 때문

• 세상의 모든 문제를 AI로 해결하지 못하는 이유 

세상의 정보에는 2가지가 있다고 한다. 바로 암묵지와 형식지이다.  암묵지란 언어란 형식을 갖추어 표현될 수 없는 경험과 학습으로 머리에 쌓인 지식이며 흔히 노하우(know-how)라는 것이다. 반면, 형식지는 명시적으로 표현한 지식이다.  예를 들면 요리법은 레시피(형식지)에 써있지만,  그걸 그대로 재현한다고 똑같은 음식점 맛이 나타나는 것은 아니다.(암묵지)

지식의 두 갈래에 관해서 이야기하는 이유는, AI는 명시적인 형식지의 정보만 인식하기 때문이다. 다시 말해, 컴퓨터가 학습하지 못하는 정보는 AI에 학습시킬 수 없으며 당연히 예측하기도 어렵다. 이런 깨달음이 있기 전에 과거 50년대에는 기호주의와 연결주의 2가지 학파가 있었다는 것이 흥미롭다.

• 기호주의와 연결주의의 등장

1956년 다트머스 회의에서 처음으로 인공지능을 언급한 마빈 민스키는 인간의 지식을 기호화하고 그 기호화의 관계를 일일이 컴퓨터에 입력하면 비슷한 입력을 얻었을 때 출력도 비슷하게 낼 것이라는 주장을 하였고 이것이 바로 기호주의 인공지능의 시작이다. 조건과 액션으로 이루어진 규칙기반(Rule based) 인공지능은 80년대까지 약 30년 정도를 지배하였고, 전문가시스템(GPS)을 이용하여 성과를 이뤘지만, 전문가의 지식의 데이터의 수가 부족했고 암묵지의 한계로  후술할 연결주의에 자리를 내주게 되었다.

반면 1957년 프랭크 로젠블랫은 뇌의 신경을 모방한 퍼셉트론을 발표했는데, 이는 연결주의 AI의 기반이 된다. 신경망 연구는 80년대에 들어와 메인 스트림으로 들어온 뒤 2010년이 되어서야 딥러닝으로 주목 받는다. 특히, 1986년 연결주의를 설명한 <병렬 분산처리> 논문을 발표한 러멜하트는 인간의 사고가 사람들이 컴퓨터가 똑똑하게 만드는 이유를 주장하며 연결주의 태동을 이끈다. 이후 2012년은 이미지 인식 기술 경진대회(ILSVRC)에서 100만 개의 이미지의 정확도를 딥러닝 알고리즘 알렉스넷(AlexNet)이 기존 26%에서 16%까지 획기적으로 낮추며 딥러닝 호황기가 시작되었다. 

• AI 발전의 겨울과 여름

현재에도 매스컴에서 AI만 붙이면 뭔가 세상을 바꿔버릴 것 같이 홍보하고 있다. 이는 1950년대 처음 인공지능을 제안한 시기에도 마찬가지였다. 기호주의가 성행하나 70년대까지 기업문제를 해결하지 못하자 첫 번째 AI 겨울(1974년 - 80년)이 일어나고,  전문가 시스템의 한계로 두 번째 AI 겨울(1987년 - 97년)이 일어났다. 그런 겨울 와중에도 인공신경망의 연결주의로 극복하며 역전파 알고리즘 발전 때문에 지금의 호황기를 누리고 있는 것이다. 

• 인공시대에 대처하는 우리의 자세 

늘 데이터 분야에 있으면서 인공지능을 필두로 발전한 데이터 직무분야가 허상으로 꺼지지 않을까 라는 생각과 의구심을 듣고 1년간 사람들에게 자문을 구했던 것 같다. 이렇게 역사를 짚어보니, AI에는 난관이 있었고 늘 이를 극복하여 다시 붐을 일으킨 것 같아 그런 고민은 접어도 될 것 같다.

우리가 할 일은 좁은 AI를 활용하며 어떻게 사업 문제를 "잘" 해결할 것 방법을 찾는 것인 것은 변함이 없다. 인공지능 전문가가 아니라 코드 활용만 가능해도 딥러닝 아키텍처를 적용할 수 있는 만큼, 일반인들에게 접근성 좋은 인터페이스를 가진 인공지능이 Chatgpt를 넘어 더 좋게 나올 시대가 기대된다.

도서관 선반에 있는 책을 아무거나 집어서 가져온 것인데 작가분이 너무 글을 잘 쓰고 이해가 잘되어서 완독예정
*이 글을 쓰는데 1시간 20분이 소요되었습니다.(독서는 대략 5시간)

4. 출처

• https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=304404993

• https://ssollacc.tistory.com/25

5. 글또 9기 글 모음

• 당신은 절대 글을 잘 쓸 수 없다.
• 기호주의와 연결주의 

부제: 글또 처음 시작하려는 뉴비에게 고함!

1. 예상 독자

• 정기적인 글을 쓰려고 하는 블로거
• 글또가 처음인 뉴비 🙋‍♂️

2. 글의 목적

"문서로 남기지 않는 사람은 사기꾼" 이라는 팀장님의 격언이 뇌리에 박힌 이후, 글또를 시작하게 되었다. 글또를 시작할 때는 패기넘치기게 2주에 1번 글 쓰는 거? 쉽지 라고 생각했지만, 생각보다 일정하게 글을 기고기는 쉽지 않았다. 앞으로 (1)매번 새 기수에 진행할 나에게 그리고 (2) 새롭게 글을 쓰고자 하는 독자들을 위해 글 잘 쓰는 노하우를 남겨본다. 

3. 본문

1. 글쓰기 쉬운 주제를 정하자
   
   개인적으로 제일+제일 중요하다고 생각하는 것이다. 공부를 따로 해서 글을 남기면 투자시간이 확 올라간다. 그보단 현실이나 업무에서 마주치는 문제와 경험들을 정리하는 연상선이 더 쉽게 작성이 된다. 새롭게 공부한다고 책 읽고 인강듣고.. 하다 보면 꾸준한 글쓰기 습관을 형성이 매우 어렵더라. 그래서 평소에 업무하는 부분에서 idea를 얻거나 간단한 개요를 상상하면서 글감을 모으면 훨씬 글쓰기가 쉽다. 
   주제와 별개로 글쓰기 쉬운 Editor를 선택하는 것도 매우 도움이 된다. 작성하기 편해야 부담 없이 쓸 마음이 드는데 에디터가 편하지 않는다면 자칫 현타올 수 가 있다. github 블로그 파서, vscode으로 작성한 다음에 커밋하고, 앗 그림 안 올렸네 다시 커밋하고.. 맞춤법 틀렸네 다시 커밋하고.. 
   
   
   
   
2. 글 쓰는 시간을 정하자

   

   
   
   글또를 시작하면서 예상은 평일 현생을 열심히 살고 밤에 글을 쓰는 갓생을 상상했다면, 그건 감히 불가능한 생각이다.
   2주간 글쓰는 시간이 주면, 1주간은 현생에 치여서 집 -> 회사를 반복하다가 첫 번째 주말이 당도할 것이다.첫 번째 주말에 가장 먼저 드는 생각은 "다음 주말있으니까 놀아야지 ㄹㅇㅋㅋ"  그렇게 약 13일동안 걱정, 고민을 마음 한 쪽에 가지고 지낸다. 어차피 고통받고 마감직전에 작성할 것이면 처음부터 마감일을 비워 놓고 마음껏 자기 할 일을 하는 게 낫다.(물론 그전에 하면 좋겠지만..!!)
   
   
   
   
3. 형식이 내용을 지배한다
   
   여러분이 대량의 문서 검토를 상황이라면(ex 이력서 검토) 형식 없는 문서는 볼 마음 조차 안 들 것이다. 이렇듯 형식이 내용보다 우선순위가 있다. 또한, 모든 글에는 목적이 있고 예상되는 독자가 있다. 글의 목적과 독자는 글에 내용을 전개하는데 대들보 같은 역할을 해서 글이 중구난방이 되는 것을 방지해준다. 본인은 글을 구조화하는 것을 매우 못하는 사람이여서 작성 글 마다 템플릿을 차용해서 작성하니 일괄된 작성글이 나와서 좋았다. 추가로 회정 블로그를 보면 구조화를 참 잘하셔서 유심히 👀 배우고 있다.
   
   

   

   
    
4. 글쓰는 무드를 조성하자
   
   꾸준히 성실하게 글을 쓰는 사람들이 있지만 때로는 글을 쓰기 싫을 때가 있을 수 있다. 그럴떈 글 쓰는 "멋진 모습"을 구현해본다. 예를 들어, 스타벅스에서 기술 글 쓰는 멋진 사람을 상상하며 행동한다던가, 아니면 아이패드-키보드를 이용해서 좀 더 트렌디한 연출을 해본다. 생각보다 내 행동이 멋지고 뿌듯한 건 중요하다. 그게 나의 삶을 좀 더 생산적이게 만든다면 Why Not? 다들 갓생 살려고 열심히 노력하는 것 아니겠음?
   
   
5. 마무리는 시작만큼 중요하다 - 퇴고
   
   입사 지원 전 날 밤새 작성한 자기소개서를 뿌듯하다고 생각하고 제출하고 다음에 일어나보면 그렇게 촌스러운 글이 있나 싶다. 글또 글 역시 "캬 명글이야 이러다 나 큐레이션 가는거 아님?" 이라고 생각하고 자고 일어나보면 부끄러운 글이 많다. 정작 큐레이션에 올라온 글은 퀄리티가 다르다(그냥 보법이 다름..!). 일정 시간을 두고 다시 읽어보고 수정하는 것은 퀄리티 향상에 아주 큰 도움이 된다. 특히...맞춤법!(않이 맞춤범 웨 안 쓺?)검사기를 한 번 꼭 돌려보도록 하자!

나름 스스로 정리해보니 글에 작성에 대한 생각도 정리된 것 같아서 뿌듯하다. 이제 절대 시간내서 글써!
*위 글은 작성에 1시간 10분이 소요됨

4. 글또 모음

• 9기
  • 당신은 절대 글을 잘 쓸 수 없다.
• 8기
  • 사이드 프로젝트(장보기 대시보드 개발)
    • 사이드프로젝트 중간 회고 & 서버 제공 서비스 비교하기(S3,Blob,Firebase)
  • 데이터 엔지니어링 공부
    • 분석알고리즘(머신러닝,딥러닝) 간단 정리해보기 1편
    • 서버리스 환경 구상하기 AWS Lambda, RDS, ec2
    • 추천시스템 이론 정리하기
    • 추천시스템 실습 - 아이템기반 협업필터링
  • 기타 분석 idea 정리
    • 23년 나의 삶의 지도 - 나는 왜 분석가의 길을 선택 했을까?
    • 나의 올해 컨셉은 분발자로 정했다.
    • 데이터분석가의 끝은 어디일까?
    • 네? 데분 멘토링왔는데 알고리즘 2달 알려주라고요?
    • 글또 8기와 개인 상반기를 정리하며

1. 취지

저도 처음에 데이터분석 분야에 입문했을때 여러사람의 많은 도움을 받았습니다. 그 배움에 보답하고자 제가 만든 요약자료를 무료 배포합니다. 열심히 공부하셔서 SQLD 자격층 취득에 도움이 되시길 바랍니다 :) 

저작권 문제로 수정되어, 요약자료가 약간 개연성이 떨어질 수 있습니다만, 자격증 취득에는 충분히 도움이 되실거라 믿습니다.  추가로 하기  SQLD문제를 같이 실습하시면 더 효과가 좋습니다.

https://snowgot.tistory.com/entry/SQLD-%EC%97%B0%EC%8A%B5%EB%AC%B8%EC%A0%9C-Oracle-Live-SQL

2. 출처

• 쑤 선생님의 velog: 데이터베이스(DB)의 스키마: (Schema)!https://velog.io/@soohun9909/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4DB%EC%9D%98-%EC%8A%A4%ED%82%A4%EB%A7%88Schema
• 망나니개발자 - [Database] 정규화(Normalization) 쉽게 이해하기: https://mangkyu.tistory.com/110
• 기서무나구물오라클 - SQL 함수 리스트: http://statwith.com/oracle-sql-function-list/
• 꿈을 향하여 질주하기 - 테이블조인의 종류: https://sparkdia.tistory.com/17

SQLD 연습하기 위한 기본 문제와 답
 Oracle 환경을 이용하여 자유롭게 SQL쿼리를 이용하여 연습하세요.

Oracle Live SQL: https://livesql.oracle.com/apex/f?p=590:1000

다음 자료와 함께 보면 풀기 더 쉽습니다.

https://snowgot.tistory.com/entry/SQLD-%EC%9A%94%EC%95%BD%EC%9E%90%EB%A3%8C

1. SELECT /WHERE 문제

문제1) ORDER_TOTAL이 500 이상인 주문의 ORDER_ID와 ORDER_TOTAL 값을 구하세요.

SELECT ORDER_ID, ORDER_TOTAL
FROM OE.orders
WHERE ORDER_TOTAL >= 500;

문제2) ORDER_STATUS가 “Entered” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요. (힌트 Schema를 참조)

SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS = 1;

문제3) ORDER_STATUS가 “Cancled” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요.

SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS in (2,3);

문제4) ORDER_STATUS가 “Shipped” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요.

SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS not  in (0,1,2,3);

문제5)SALES_REP_ID가 153인 모든 주문의 ORDER_ID, ORDER_DATE, 및 ORDER_TOTAL 값을 구하세요.

SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL
FROM oe.orders
WHERE SALES_REP_ID = 5;

문제6) ORDER_MODE가 'online'인 주문 중, ORDER_TOTAL이 2000 이하인 주문의 ORDER_ID와 CUSTOMER_ID 값을 구하세요.

SELECT ORDER_ID, CUSTOMER_ID
FROM oe.orders
WHERE ORDER_MODE = 'online' AND ORDER_TOTAL <= 2000;

2. GROUP / HAVING 문제

문제1) OE.ORDERS 테이블에서 ORDER_STATUS 별로 주문 건수를 구하세요.

SELECT ORDER_STATUS, COUNT(*) as NUMBER_OF_ORDERS
FROM OE.ORDERS
GROUP BY ORDER_STATUS;

문제2) ORDER_TOTAL의 합계가 10,000 이상인 CUSTOMER_ID를 구하세요.

SELECT CUSTOMER_ID, SUM(ORDER_TOTAL) as TOTAL_AMOUNT
FROM OEorders
GROUP BY CUSTOMER_ID
HAVING SUM(ORDER_TOTAL) >= 10000;

문제3): 각 SALES_REP_ID별로 처리한 주문의 평균 ORDER_TOTAL 값을 구하세요.

SELECT SALES_REP_ID, AVG(ORDER_TOTAL) as AVERAGE_ORDER_TOTAL
FROM OE.orders
GROUP BY SALES_REP_ID;

문제4) 3번의 결과에서 평균 ORDER_TOTAL이 500 이상인 SALES_REP_ID를 구하세요.

SELECT SALES_REP_ID, AVG(ORDER_TOTAL) as AVERAGE_ORDER_TOTAL
FROM OE.orders
GROUP BY SALES_REP_ID
HAVING AVG(ORDER_TOTAL) >= 500;

문제5) ORDER_MODE별로 주문된 ORDER_TOTAL의 합계를 구하세요.

SELECT ORDER_MODE, SUM(ORDER_TOTAL) as TOTAL_AMOUNT
FROM OE.orders
GROUP BY ORDER_MODE;

문제6) CUSTOMER_ID별로 주문한 횟수가 5회 이상인 고객들의 ID를 구하세요.

SELECT CUSTOMER_ID, COUNT(*)
FROM OE.orders
GROUP BY CUSTOMER_ID
HAVING COUNT(*) >= 5;

문제7) 각 SALES_REP_ID별로 처리한 주문 중 ORDER_TOTAL이 100 이상인 주문의 건수를 구하세요.

SELECT SALES_REP_ID, COUNT(*) as NUMBER_OF_ORDERS
FROM OE.orders
WHERE ORDER_TOTAL >= 100
GROUP BY SALES_REP_ID;

3. built-in 함수 문제 1

문제 1) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)이 50000 이상인 주문들에 대해 주문 모드(ORDER_MODE)를 기반으로 'direct'인 경우 '직접 주문', 그렇지 않은 경우 '일반 주문'으로 출력하세요.

SELECT ORDER_ID, DECODE(ORDER_MODE, 'direct', '직접 주문', '일반 주문') AS ORDER_TYPE
FROM OE.ORDERS
WHERE ORDER_TOTAL >= 50000;

문제 2) OE.ORDERS 테이블에서 주문 상태(ORDER_STATUS)를 기반으로 상태 값이 0인 경우 '진행 중', 1인 경우 '완료', 2,3인 경우 '취소'로 출력하세요. 나머지는 ‘기타’로 출력하세요.

SELECT ORDER_ID, ORDER_STATUS, CASE ORDER_STATUS
                 WHEN 0 THEN '진행 중'
                 WHEN 1 THEN '완료'
                 WHEN 2 THEN '취소'
		         WHEN 3 THEN '취소'
                 ELSE '기타' END  AS STATUS_DESC
FROM OE.ORDERS;

문제 3) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)의 절대값을 출력하세요.

SELECT ORDER_ID, ABS(ORDER_TOTAL) AS ABS_TOTAL
FROM OE.ORDERS;

문제 4) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)이 양수인지 음수인지를 판별하여 결과를 출력하세요.

SELECT ORDER_ID, SIGN(ORDER_TOTAL) AS SIGN_STATUS
FROM OE.ORDERS;

문제 5) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)을 100으로 나눈 나머지를 출력하세요.

SELECT ORDER_ID, ORDER_TOTAL, MOD(ORDER_TOTAL, 100) AS MOD_RESULT
FROM OE.ORDERS;

문제 6) OE.ORDERS 테이블에서 주문 날짜(ORDER_DATE)의 년도만을 출력하세요.

SELECT ORDER_ID, ORDER_DATE, TO_CHAR(ORDER_DATE, 'YYYY') AS ORDER_YEAR
FROM OE.ORDERS;

문제7) OE.ORDERS 테이블에서 ORDERS_STATUS 별로 그룹지은 후 ORDER_TOTAL 의 값을 평균을 내세요. 해당 테이블을 with 절을 이용하여 temp1 에 저장한 후 전체 temp1 테이블을 출력하세요.

WITH TEMP1 AS
(SELECT ORDER_STATUS, ROUND(AVG(ORDER_TOTAL))
    FROM OE.ORDERS GROUP BY ORDER_STATUS)
SELECT * FROM TEMP1

문제8) 당신은 ORDE_STATUS 별로 전체 매출액을 알고 싶다. 다만, 현재 취소건 등 고객들의 클레임이 많은 것을 확인하였다. 얼마나 취소로 인한 손해가 많은지 알고 싶다. 해당 쿼리를 하단에 작성하라.

-- order_status 2,3 취소건 -> 취소건만 order_total
-- order_status 0 ~ 10 까지있는데, 카테고리화 / 퍼센트(분율) 로 보여주면 더 구체적으로 판단할수있다. 

-- 0. 쓸 컬럼을 order_total, order_status
-- 1. case문을 이용해서 order_status 카테고리화 할예정.
-- 2. order_status 별로 그룹을 지어 sum(order_total)

with temp1 as(
select order_total,
    case
    	when order_status = 0 then '주문미완료'
    	when order_status = 1 then '주문완료'
    	when order_status in (2,3) then '주문취소'
    	else '배송중'
    end as os_cat
from oe.orders)
select os_cat, sum(order_total), round(sum(order_total)/3668054.7*100)
from temp1
group by os_cat

4. DML/DDL 문제

문제 1) 새로운 테이블 NEW_EMPLOYEES를 만들어 주세요. 이 테이블에는 EMP_ID, EMP_NAME, EMP_SALARY 세 개의 컬럼이 있어야 합니다.

• EMP_ID는 숫자 타입이며, 기본 키로 설정해주세요.
• EMP_NAME은 최대 50자의 문자열 타입으로 설정해주세요.
• EMP_SALARY는 숫자 타입으로 설정해주세요.

CREATE TABLE NEW_EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(50),
    EMP_SALARY NUMBER
);

문제 2) NEW_EMPLOYEES 테이블에 다음 정보를 입력해주세요.

• EMP_ID: 101, EMP_NAME: 'John Doe', EMP_SALARY: 5000

INSERT INTO NEW_EMPLOYEES (EMP_ID, EMP_NAME, EMP_SALARY) VALUES (101, 'John Doe', 5000);

문제 3) NEW_EMPLOYEES 테이블에서 EMP_ID가 101인 직원의 EMP_SALARY를 5500으로 수정해주세요.

UPDATE NEW_EMPLOYEES SET EMP_SALARY = 5500 WHERE EMP_ID = 101;

문제 4) NEW_EMPLOYEES 테이블에서 EMP_ID가 101인 직원의 정보를 삭제해주세요.

DELETE FROM NEW_EMPLOYEES WHERE EMP_ID = 101

문제 5) NEW_EMPLOYEES 테이블에 EMP_EMAIL 컬럼을 추가해주세요. 이 컬럼은 최대 100자의 문자열 타입입니다.

ALTER TABLE NEW_EMPLOYEES ADD (EMP_EMAIL VARCHAR2(100));

문제6) NEW_EMPLOYEES 테이블의 모든 데이터를 삭제해주세요.

TRUNCATE TABLE NEW_EMPLOYEES;

문제 7) NEW_EMPLOYEES 테이블을 삭제해주세요.

DROP TABLE NEW_EMPLOYEES;

문제8) 최종 문제

주문 테이블을 생성하고, 데이터를 삽입한 뒤 데이터를 생성하고 주문별로 개수를 확인하려한다. 다음 1번~4번 절차대로 쿼리를 수행하여 결과를 다음과 같은 예시처럼 나타내어라. 내부 값은 다를 수 있음

문제8-1) 아래 제공된 테이블의 컬럼 이름과 제약 조건을 기반으로 주문 정보를 저장할 orders 테이블을 생성하세요.

1. order_id: 주문 번호, 숫자, 기본 키
2. customer_name: 고객 이름, 문자열(50자 제한)
3. product_name: 제품 이름, 문자열(50자 제한)
4. quantity: 주문 수량, 숫자
5. order_date: 주문 날짜, 날짜형

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(50),
    product_name VARCHAR2(50),
    quantity NUMBER,
    order_date DATE
);

문제8-2) 주어진 데이터를 orders 테이블에 삽입하세요.

1, '김영희', '노트북', 1, '2023-10-01'
2, '박철수', '키보드', 2, '2023-10-02'
3, '이민수', '마우스', 1, '2023-10-03'
4, '최수진', '모니터', 2, '2023-10-05'
5, '박지훈', '노트북', 1, '2023-10-06'
6, '김소연', '키보드', 3, '2023-10-07'
7, '이태희', '마우스', 5, '2023-10-08'
8, '박민아', '노트북', 2, '2023-10-09'
9, '최영준', '모니터', 1, '2023-10-10'
10, '정희선', '노트북', 1, '2023-10-11'

INSERT ALL
  INTO orders VALUES (1, '김영희', '노트북', 1, TO_DATE('2023-10-01', 'YYYY-MM-DD'))
  INTO orders VALUES (2, '박철수', '키보드', 2, TO_DATE('2023-10-02', 'YYYY-MM-DD'))
  INTO orders VALUES (3, '이영수', '마우스', 1, TO_DATE('2023-10-03', 'YYYY-MM-DD'))
  INTO orders VALUES (4, '최민영', '모니터', 2, TO_DATE('2023-10-04', 'YYYY-MM-DD'))
  INTO orders VALUES (5, '황영준', '노트북', 1, TO_DATE('2023-10-05', 'YYYY-MM-DD'))
  INTO orders VALUES (6, '김영미', '키보드', 1, TO_DATE('2023-10-06', 'YYYY-MM-DD'))
  INTO orders VALUES (7, '박진수', '마우스', 3, TO_DATE('2023-10-07', 'YYYY-MM-DD'))ㄷㄷ
  INTO orders VALUES (8, '이민철', '모니터', 1, TO_DATE('2023-10-08', 'YYYY-MM-DD'))
  INTO orders VALUES (9, '최영주', '노트북', 1, TO_DATE('2023-10-09', 'YYYY-MM-DD'))
  INTO orders VALUES (10, '황지수', '키보드', 2, TO_DATE('2023-10-10', 'YYYY-MM-DD'))
SELECT * FROM DUAL;

문제8-3) 모든 주문 정보를 조회하되, 수량이 3 이상인 경우 '대량 주문'이라는 문자열을, 그렇지 않은 경우 '일반 주문'이라는 문자열을 새로운 컬럼 order_type에 표시하여 출력하세요.

SELECT order_id, customer_name, product_name, quantity, order_date,
       CASE WHEN quantity >= 3 THEN '대량 주문'
            ELSE '일반 주문'
       END AS order_type
FROM orders;

문제8-4): 제품별로 총 주문 수량을 계산하고, 주문 수량이 많은 순으로 정렬하여 출력하세요.

with temp1 as (SELECT order_id, customer_name, product_name, quantity, order_date,
       CASE WHEN quantity >= 3 THEN '대량 주문'
            ELSE '일반 주문'
       END AS order_type
FROM orders)
SELECT order_type, SUM(quantity) as total_quantity
FROM temp1
GROUP BY order_type
ORDER BY total_quantity DESC;;

5. 조인문제 1차

문제 1) OE.ORDERS 테이블에서 주문 상태(ORDER_STATUS)가 '1'인 모든 주문을 조회하십시오.

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1;

문제 2) OE.ORDERS와 OE.CUSTOMERS 테이블을 INNER JOIN을 사용하여 결합하고, 이를 기반으로 주문 상태(ORDER_STATUS)가 '0'인 고객의 이름 (CUST_FIRST_NAME, CUST_LAST_NAME)과 주문 총액(ORDER_TOTAL)을 조회하십시오.

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.ORDER_STATUS = 0;

문제 3) OE.CUSTOMERS 테이블을 기준으로 OE.ORDERS 테이블과 LEFT JOIN을 하여, 주문 정보가 없는 고객의 이름 (CUST_FIRST_NAME, CUST_LAST_NAME)을 조회하십시오.

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM OE.CUSTOMERS C
LEFT JOIN OE.ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE O.CUSTOMER_ID IS NULL;

문제 4) OE.ORDERS와 OE.CUSTOMERS 테이블을 INNER JOIN을 사용하여 결합하고, 주문 총액(ORDER_TOTAL)이 10,000 이상이며, 고객의 성별(GENDER)이 'M'인 모든 주문을 조회하십시오.

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.ORDER_TOTAL >= 10000 AND C.GENDER = 'M';

문제 5) FULL OUTER JOIN 이용한 조회 OE.ORDERS와 OE.CUSTOMERS 테이블을 FULL OUTER JOIN을 사용하여 결합하고, 주문 정보나 고객 정보 중 하나라도 없는 모든 레코드를 조회하십시오.

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
FULL OUTER JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.CUSTOMER_ID IS NULL OR C.CUSTOMER_ID IS NULL;

6. 조인문제 2차

문제 1) ORDER_DATE와 관련된 JOIN OE.ORDERS 테이블과 OE.ORDER_ITEMS 테이블을 JOIN하여 ORDER_DATE, PRODUCT_ID 및 QUANTITY 정보를 가져와보세요.

SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID;

SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
natural JOIN OE.ORDER_ITEMS I ;

SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I using(order_id)

문제 2) 특정 조건에 따른 집계 ORDER_STATUS가 1인 주문에 대하여 총 주문 금액(UNIT_PRICE * QUANTITY)을 계산하세요.

SELECT O.ORDER_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_ORDER_AMOUNT
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE O.ORDER_STATUS = 1

문제 3) ORDER_MODE가 'direct'이며 ORDER_STATUS가 0인 주문에 대해 총 주문된 제품 수(QUANTITY)를 계산하세요.

SELECT O.ORDER_ID, SUM(I.QUANTITY) AS TOTAL_QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE O.ORDER_MODE = 'direct' AND O.ORDER_STATUS = 0

문제 4)  각 SALES_REP_ID별로 주문된 제품의 총 가격(UNIT_PRICE * QUANTITY의 합)을 조회하고, 주문 총 가격이 높은 순으로 정렬하세요.

SELECT O.SALES_REP_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_SALES
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
GROUP BY O.SALES_REP_ID
ORDER BY TOTAL_SALES DESC;

문제 5) 2008년에 이루어진 주문과 관련된 모든 PRODUCT_ID와 해당 PRODUCT_ID의 총 판매 금액을 조회하세요.

SELECT O.ORDER_DATE, I.PRODUCT_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_AMOUNT
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE TO_CHAR(O.ORDER_DATE,'YYYY-MM-DD') BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY O.ORDER_DATE,I.PRODUCT_ID;

7. 집합 문제

문제1) 주문 상태(ORDER_STATUS)가 1과 2인 주문을 합쳐서 출력해보세요. (UNION을 이용)

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1 
UNION 
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 2;

문제2) 주문 상태(ORDER_STATUS)가 1인 주문과 ORDER_TOTAL이 50000을 초과하는 주문을 모두 출력하세요. 단, 중복된 주문이 있을 수 있습니다. (UNION ALL을 이용)

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
UNION ALL 
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;

문제3)  주문 상태(ORDER_STATUS)가 1인 주문과 ORDER_TOTAL이 50000을 초과하는 공통된 주문을 찾아 출력하세요. (INTERSECT를 이용)

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1 
INTERSECT 
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;

문제4) 주문 상태(ORDER_STATUS)가 1인 주문 중에서 ORDER_TOTAL이 50000 초과하는 주문을 제외하고 출력하세요. (MINUS를 이용

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1 
MINUS 
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;

8. SubQuery 문제

문제1) 총 주문 금액이 가장 높은 주문의 금액을 조회하시오.

SELECT * 
FROM OE.ORDERS WHERE ORDER_TOTAL = (SELECT MAX(ORDER_TOTAL) FROM OE.ORDERS);

문제2) 2007년에 주문한 모든 고객의 평균 주문 금액을 조회하시오.

SELECT CUSTOMER_ID, AVG(ORDER_TOTAL) AS AVG_TOTAL 
FROM (SELECT CUSTOMER_ID, ORDER_TOTAL FROM OE.ORDERS WHERE TO_CHAR(ORDER_DATE, 'YYYY') = '2007') GROUP BY CUSTOMER_ID;

문제3) SALES_REP_ID가 155인 판매 대표의 평균 주문 금액보다 높은 주문들만 조회하시오.

SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS 
WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM OE.ORDERS WHERE SALES_REP_ID = 155);

문제4) IN 사용: 주문 금액이 10,000 이상 50,000 이하인 CUSTOMER_ID의 모든 주문을 조회하시오.

SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM OE.ORDERS WHERE ORDER_TOTAL BETWEEN 10000 AND 50000);

문제5) ALL 사용: 모든 판매 대표의 평균 주문 금액보다 높은 주문들만 조회하시오.

SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS 
WHERE ORDER_TOTAL > ALL (SELECT AVG(ORDER_TOTAL) FROM OE.ORDERS GROUP BY SALES_REP_ID);

문제6) ANY 사용:주문 상태가 1 또는 3인 주문보다 주문 금액이 높은 주문들을 조회하시오.

SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS 
WHERE ORDER_TOTAL > ANY (SELECT ORDER_TOTAL FROM OE.ORDERS WHERE ORDER_STATUS IN (1,3))

9. GROUPING 문제

문제1) OE.ORDERS 테이블을 이용하여 ORDER_MODE, ORDER_STATUS 그룹짓고 모든 subtotal과 총합을 생성하세요.

SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY CUBE (ORDER_MODE, ORDER_STATUS);

문제2) Grouping sets 으로 OE.ORDERS 테이블을 이용하여 ORDER_MODE, ORDER_STATUS 과 전체 total을 생성하세요.

SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY GROUPING SETS ((ORDER_MODE), (ORDER_STATUS), ());
-- 두 컬럼을 모두 넣으면 모든행 출력

문제3) ORDER_MODE로 그룹화한 후, 각 ORDER_MODE 내에서 SALES_REP_ID로 추가 그룹화하여 주문 총액을 반환합니다. 또한 ORDER_MODE에 대한 subtotal와 Grand total도 반환하세요

SELECT ORDER_MODE, SALES_REP_ID, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY ROLLUP (ORDER_MODE, SALES_REP_ID);

문제4) 문제 3처럼 ORDER_MODE와 ORDER_STATUS의 각 조합에 대한 주문 총액을 반환하며, GROUPING 함수를 사용하여 어떤 열이 집계된 결과인지를 0과 1로 나타내세요.

SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL),
       GROUPING(ORDER_MODE) AS GRP_MODE,
       GROUPING(ORDER_STATUS) AS GRP_STATUS
FROM OE.ORDERS
GROUP BY ROLLUP (ORDER_MODE, ORDER_STATUS);

10. Window function 문제

문제1) 모든 주문에 대한 주문 총액(ORDER_TOTAL)의 합계를 출력하세요.

SELECT ORDER_ID, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER() AS TOTAL_SUM
FROM OE.ORDERS;

문제2) 주문 모드(ORDER_MODE)별 주문 총액(ORDER_TOTAL)의 합계를 출력하세요.

SELECT ORDER_ID, ORDER_MODE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(PARTITION BY ORDER_MODE) AS MODE_TOTAL
FROM OE.ORDERS;

문제3) 주문 날짜(ORDER_DATE)에 따라 주문 총액(ORDER_TOTAL)의 주문 날짜까지의 누적 합계를 반환하세요.

SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(ORDER BY ORDER_DATE) AS CUMULATIVE_TOTAL
FROM OE.ORDERS;

문제4) 주문 모드(ORDER_MODE)별로 그룹화하고, 주문 날짜(ORDER_DATE)에 따라 주문 총액과 해당 주문 모드 및 날짜까지의 누적 합계를 반환하세요.

SELECT ORDER_ID, ORDER_MODE, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(PARTITION BY ORDER_MODE ORDER BY ORDER_DATE) AS CUMULATIVE_MODE_TOTAL
FROM OE.ORDERS;

문제5) 문제: 주문 날짜(ORDER_DATE)에 따라 주문 총액 누적합계와 해당 주문 날짜를 포함한 최근 7일 동안의 누적 합계를 반환하세요.

Hint) BETWEEN INTERVAL ‘7’ DAY PRECEDING AND CURRENT ROW

SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(ORDER BY ORDER_DATE RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS WEEKLY_CUMULATIVE_TOTAL
FROM OE.ORDERS;

문제6) 각 판매 대표(SALES_REP_ID)에 대한 주문 총액(ORDER_TOTAL)과 해당 판매 대표의 평균 주문 총액을 반환하세요.

SELECT ORDER_ID, SALES_REP_ID, ORDER_TOTAL, 
AVG(ORDER_TOTAL) OVER(PARTITION BY SALES_REP_ID) AS AVG_SALES_BY_REP
FROM OE.ORDERS;

도움이 되셨다면 공감을 눌러주시면 큰 도움이 됩니다!!
자격증 취득과 SQL 향상에 도움이 되길 바랍니다 화이팅!
강의문의:https://snowgot.tistory.com/notice/96