Project 기획 및 관리 (2) MySQL, Excel 시각화

이번 시간도 SQL 이어서 공부함

---

하위쿼리는 쉽다. 위에서 부터 만드는게 아니라 작은거 만들고 큰걸로 간다. 하위쿼리 먼저 만들고 그다음 확장하는 식이다.

SELECT * FROM (
SELECT 이름, 점수 TOT
FROM 학생
ORDER BY 점수 DESC)
LIMIT 2

문제는 속도와 비용이다. 한 개의 커리를 조금 만 더 바꾸면 속도가 바뀐다.

전자북 ‘MySQL과 주식 데이터로 재밌게’

데이터 시대와 SQL

• 데이터는 기업 의사 결정의 근거
• 기업의 가치는 기업이 보유한 데이터도 고려해야 한다.
• 우리의 행동 하나하나는 모두 데이터로 어딘가에 저장되고 있다.
• SQL은 데이터와 가장 근접한 언어다.
• SQL은 정형적 구조의 데이터를 가장 효율적으로 다룰 수 있다.
• SQL 몇 줄로 보고서를 완성하고, 남이 찾지 못한 기회를 찾고, 위험도 감지할 수 있다.
• SQL을 다룰 줄 아는 기획자가 가치가 높다.

 

데이터는? 아래와 같이 지하철 승하차 정보 역시 데이터로 저장된다.

아침 일찍 출근하는 직장인을 타겟으로 김밥 장사를 한다면 어디가 좋을까?
저녁 시간 자녀들 간식 장사를 한다면 어디가 좋을까?
데이터를 잘 모으고 제대로 분석할 수 있어야 한다.
감과 촉이 아닌 데이터를 토대로 의사 결정을 해야 한다.

데이터베이스? 데이터를 효율적으로 사용할 수 있게 모아 놓은 데이터 집합이다.
데이터가 모여 데이터베이스가 된다.
쿠팡의 데이터베이스는 회원, 상품, 주문, 배송 데이터의 집합이다.
아래 그림은 배민의 데이터베이스다. 주문을 하면 배민 데이터베이스에 저장된다.

DBMS (Database Management Systems) 데이터베이스 관리 시스템? 데이터베이스를 효율적으로 사용하거나 관리할 수 있는 소프트웨어
관계형 모델로 데이터를 관리하는 DBMS는 RDBMS(Relational Database Management Systems, 관계형 데이터베이스 관리 시스템)라고 한다.
MySQL, ORACLE 등이 바로 RDBMS다.
아래 그림은 데이터와 데이터 베이스, DBMS를 나타낸다.

테이블? 데이터를 담는 그릇
데이터베이스에 테이블이라는 그릇을 생성하면, 그릇 안에 데이터를 차곡차곡 쌓을 수 있다.
하나의 테이블에는 한 종류의 데이터만 담는 것이 기본 규칙이다.
한 종류의 데이터란 어떤 한 개체(대상)를 나타내는 속성 집합을 뜻한다.
예) ‘회원’테이블은 ‘회원번호, 이름, 생년월일, 전화번호’와 같은 속성들로 구성할 수 있다.

하나의 시스템, 또는 하나의 서비스를 구축하려면 많은 테이블이 필요하다.
아래 그림은 시스템(서비스)별로 갖고 있을 법한 테이블의 예이다.

주식 데이터베이스의 테이블 중에 종목이란 테이블을 확대해서 들여다보면 아래 그림과 같다.

SQL은 데이터를 지배하는 가장 강력한 언어이다.

데이터베이스에 SQL 명령을 전달하는 방법은 두가지이다.
첫번째로 개발된 시스템의 프로그래밍 코드에 SQL을 만들어 놓고 데이터베이스에 명령을 전달하는 방법
두번째 방법은 아래 그림에 2번으로 표시된 것과 같이 SQL 툴을 사용하는 방법이다.

SQL 툴은 데이터베이스에 SQL 명령어를 전달할 때 사용하는 소프트웨어다.

ORDER BY ASC (오름차순) 와 DESC (내림차순)

필요한 데이터만 골라내고 싶을 때는 WHERE
SELECT SQL에서 FROM절과 ORDER BY절 사이에는 WHERE절을 사용할 수 있다. WHERE절은 FROM절의 테이블에서 필요한 데이터만 골라내기 위한 조건을 주는 역할을 한다.
WHERE절은 FROM절의 테이블에서 필요한 데이터만 골라내기 위한 조건을 주는 역할을 한다. STOCK에서 ‘삼성전자’만 조회하고 싶다면, WHERE절에 관련 조건을 주면 된다.
테이블에서 원하는 데이터만 출력하려면 WHERE절에 ‘<컬럼명><조건 연산식><조건값>’의 형식으로 조건을 주어야 한다. Ex) SEC_NM = '문구류’
WHERE절에 ‘<컬럼명><조건 연산식><조건 값>’의 형식으로 조건을 주어야 한다. 기본적인 조건 연산식에는 = 같다, =! 같지않다, > 크다, > = 크거나 같다, < 작다, < = 작거나 같다.
WHERE절에는 여러 조건을 동시에 사용할 수 있다. 동시에 여러 조건을 주기 위해서는 AND나 OR를 사용해 조건들을 연결한다.

AND를 사용해 조건들을 연결하면, 주어진 조건을 모두 만족하는 데이터만 조회된다.
SELECT STK_NM ,STK_CD ,SEC_NM ,EX_CD
FROM STOCK
WHERE SEC_NM = '문구류' AND EX_CD = 'KD'
ORDER BY STK_CD;

이번에는 네 개의 조건을 조합해보자. 아래는 STK_CD(종목코드)가 '100000' 이상이면서 '101000' 이하고, SEC_NM이 '기계와장비'이면서 EX_CD가 'KP'(코스피)인 데이터만 조회한다.
SELECT STK_NM ,STK_CD ,SEC_NM ,EX_CD
FROM STOCK
WHERE STK_CD >= '100000'
AND STK_CD <= '101000'
AND SEC_NM = '기계와장비'
AND EX_CD = 'KP'
ORDER BY STK_CD;

OR 역시 WHERE절에서 여러 조건을 연결하기 위해 사용한다. AND는 연결된 조건을 모두 만족해야만 결과에 나올 수 있는 반면에 OR는 연결된 조건 중에 단 하나라도 만족하면 결과에 출력된다.

아래는 OR를 사용해 SEC_NM(섹터명)이 '문구류'이거나 STK_NM(종목명)이 '대교'인 데이터를 조회하는 SQL이다.
SELECT STK_NM ,STK_CD ,SEC_NM ,EX_CD
FROM STOCK
WHERE SEC_NM = '문구류' OR STK_NM = '대교'
ORDER BY STK_NM;

SQL의 WHERE절에서도 마찬가지다. AND와 OR를 동시에 사용할 때는 원하는 조건에 맞게 괄호를 적절히 사용해야 한다. EX_CD(거래소코드)가 'KD'(코스닥)이면서, SEC_NM(섹터명)이 '담배'이거나 '주류제조업'인 데이터를 찾으려고 한다. 아래와 같이 조건을 사용해야 한다. * EX_CD = KD AND (SEC_NM = 담배 OR SEC_NM = 주류제조업)

위 조건을 SQL의 WHERE절로 구현하면 아래와 같다.
SELECT STK_CD ,STK_NM ,SEC_NM, EX_CD
FROM STOCK
WHERE EX_CD = 'KD'
AND (SEC_NM = '담배'
OR SEC_NM = '주류제조업')
ORDER BY STK_CD ASC;

OR 연결이 사용되면 괄호를 반드시 사용하는 습관을 갖기 바란다.

특수조건 LIKE IN BETWEEN
LIKE는 일부 값만 같은 데이터를 검색하기 위해 사용한다.
IN은 여러개의 조건 값을 한번에 처리하기 위해 사용한다.
BETWEEN은 이상(>,=) 조건과 이하(>,=)조건을 한번에 처리하는 범위 조건 연산자다.
응답속도를 확인해서 비교분석을 하는 것이 덕목이다. WHERE라는 조건식이 조건명이 많다.

특수조건 LIKE
'_'를 이용한 검색
('아모' 다음에 한 글자는 아무 글자, 뒤에는 '퍼시픽'으로 끝나는 데이터)
SELECT STK_CD ,STK_NM ,SEC_NM
FROM STOCK
WHERE STK_NM LIKE '아모_퍼시픽';

'%'를 앞쪽에 놓는 검색('전자'로 끝나는 모든 데이터를 검색)
SELECT STK_CD ,STK_NM ,SEC_NM
FROM STOCK
WHERE STK_NM LIKE '%전자';

'%'를 앞뒤로 사용(SEC_NM이 '증권'이면서 STK_NM에 '투자'가 포함된 데이터 검색)
SELECT STK_NM ,STK_CD ,EX_CD ,SEC_NM
FROM STOCK
WHERE SEC_NM = '증권'
AND STK_NM LIKE '%투자%'
ORDER BY STK_NM;

NOT LIKE 검색(SEC_NM이 '증권'이면서 STK_NM이 '투자'가 포함되지 않은 데이터 검색)
SELECT STK_NM ,STK_CD ,EX_CD ,SEC_NM
FROM STOCK
WHERE SEC_NM = '증권'
AND STK_NM NOT LIKE '%투자%'
ORDER BY STK_NM;

특수조건 IN
IN은 OR라고 생각하면 된다. 괄호 안에 여러 개의 조건 값을 콤마로 구분해 입력하면 된다.
SEC_NM이 '담배'이거나 '주류제조업' 또는 '문구류'인 데이터를 조회하고 있다.
SELECT STK_CD ,STK_NM ,SEC_NM
FROM STOCK
WHERE SEC_NM IN ('담배','주류제조업','문구류')
ORDER BY STK_NM ASC;

SELECT STK_NM ,STK_CD ,EX_CD ,SEC_NM
FROM STOCK
WHERE STK_NM LIKE '삼성%'
AND SEC_NM NOT IN ('보험','금융','증권')
ORDER BY STK_NM;

특수조건 BETWEEN
STOCK 테이블에서 STK_CD(종목코드)가 '200000' 이상이면서 '200500' 이하인 데이터를 조회하고 있다.
SELECT STK_CD ,STK_NM
FROM STOCK
WHERE STK_CD >= '200000' AND STK_CD <= '200500'
ORDER BY STK_CD;

SELECT STK_CD ,STK_NM ,SEC_NM
FROM STOCK
WHERE STK_NM BETWEEN '삼성' AND '삼아'
AND SEC_NM IN ('보험','제약바이오')
ORDER BY STK_NM;

ORDER BY로 특정 컬럼의 오름차순 또는 내림차순 설정
별칭(Alias)

---

종목 테이블 이해하기

시작 전에 테이블 확인한다.

• STK_CD(종목코드): 종목을 식별하는 코드 값
• STK_NM(종목명): 종목의 명칭
• EX_CD(거래소코드): 코스피와 코스닥을 구분하는 코드(KP = 코스피, KD = 코스닥)
• NAT_CD(국가코드): 종목이 상장된 거래소의 국가를 나타내는 코드(KR = 한국)
• SEC_NM(섹터명): 종목이 속하는 섹터(업종, 분류)
• STK_TP_NM(종목유형명): 우선주, ETF, ETN, 스팩주와 같은 종목 유형을 관리

 

두 테이블을 조인하기

SELECT stk_cd from stock where stk_nm='삼성전자';
SELECT * FROM history_dt WHERE stk_cd='005930';

방법은 여러가지가 있다. ‘삼성전자’를 프로시저로 변환하는 방법이 있다. 여기서 프로시저는 매크로이다. 프로세스를 만들어놓고 콜하는 명령을 할 수 있는 기능이다.

개념, 논리설계 단계에서는 테이블이 아닌 개체(Entity)라는 용어를 사용한다. 그러므로 개념, 논리 단계는 개체를 정의하고 설계하는 과정이며 물리설계 단계는 개체를 테이블로 구체화하는 과정이다.

SQL 프로젝트라고 하면 질의어를 작성하고, 튜닝해서 속도차이를 검증한다, 데이터셋을 볼 수 있게 하는 질의어와 일반유저에게 보여지는 화면 구성을 하는 것이 있을 수 있다. GPT와 연결해서 결과물을 해석하는걸 만들 수도 있다.

지금까지 설명한 관계형 데이터 모델에 대해 정리해보면 아래와 같다.

• 관계형 데이터 모델은 테이블 형태의 데이터 저장 구조를 설계한 것
• 관계형 데이터 모델은 개념설계, 논리설계, 물리설계 단계로 이루어진다.
• 테이블을 조회하면 컬럼이 가로로 출력되지만, 설계할 때는 컬럼을 세로로 나열한다.
• PK는 테이블의 데이터를 식별할 수 있는 컬럼이다.

 

관계형데이터베이스 용어

NULL 결측치

결측치는 다양한 경우의 수 때문에 생긴다. 결측치때문에 분석이 안된다. 하나가 에러면 에러가 계속 발생한다. 프로그래밍에 있어서 결측치는 최악의 경우이다.

전체 개수와 결측치 개수를 테이블로 보여지게 만들었다.

 

날짜 관련 함수

데이터 형식를 바꾸는 방법

• STR_TO_DATE: 문자열을 날짜 데이터로 변환한다.
• STR_TO_DATE('문자열','패턴 문자')와 같은 형태로 사용한다.
• 패턴 문자를 사용해 문자열이 날짜의 어느 부분인지를 지정한다.
• %Y는 연도, %m은 월, %d는 일을 나타낸다. (%Y의 Y는 대문자, 나머지 m과 d는 소문자다.)
• STR_TO_DATE('20190102','%Y%m%d'): '연월일'로 구성된 문자열을 날짜 데이터로 변환
• STR_TO_DATE('2019-01-02','%Y-%m-%d'): '연-월-일'로 구성된 문자열을 날짜 데이터로 변환
• STR_TO_DATE('2019/01/02','%Y/%m/%d'): '연/월/일'로 구성된 문자열을 날짜 데이터로 변환
• STR_TO_DATE('01/02/2019','%m/%d/%Y'): '월/일/연'으로 구성된 문자열을 날짜 데이터로 변환

 

GROUP BY
HAVING을 쓰기 싫을 때는 서브쿼리를 만들면 된다.

SELECT * FROM (
SELECT 성별, 지역, COUNT(*) CNT
FROM 학생
GROUP BY 성별, 지역
ORDER BY CNT DESC) A  #서브쿼리
WHERE CNT > = 2

시작과와 거래량만 리스트업한다.
아래 데이터 긁어서 엑셀에 붙여넣기한다. 그리고 산점도나 CORREL 상관계수를 계산한다.
코드를 시각화소프트웨어에 넣으면 시각화가 바로 된다.

SQL에서 추출한 데이터를 엑셀로 붙여넣어서 시각화했다.

MIN, MAX 계산하기

 

선생님이 추천하는 데이터 분석 사이트

한국관광 데이터랩

한국관광 데이터랩