[SQL] SQL

Ik·2022년 7월 25일
sql
0

Data

목록 보기
27/34

SQL

SQL

  • 웹, 모바일을 통해 생성된 여러가지 데이터 관계형 데이터베이스에 저장
  • 관계형 데이터베이스에 저장되어 있는 데이터를 기반으로 각자 직무에 필요한 분석보고서 생성
  • 이 때 사용 되는 언어 SQL

SQL의 이점

  • 엑셀처럼 이해하기 쉽다
  • 중요하다
    • SQL을 하다 python, R로 넘어가는 경우에도
      • python - pandas, R - dplyr을 이용해 RDB를 바로 사용 가능
      • 데이터 타입도 동일
      • 기본 개념 다지기 좋다
  • 정말 많이 쓰임

SQL 명령어 분류

  • DDL - Data Definition Language
  • DML - Data Manipulation Language
    • 분석가 들이 가장 많이 사용하는 명령어
    • 하지만 다른 명령어들도 알아야됨
      • DBA와의 원활한 소통 위해
  • DCL - Data Control Language
  • TCL - Transaction Control Language

DDL 

  • table 생성
CREATE TABLE (테이블 이름)(
(열 이름) (열의 data type) (제약 조건),		--제약 조건 필수 X
(열 이름) (열의 data type)
);


//코드 예
CREATE TABLE 회원테이블 (
회원번호 INT PRIMARY KEY,
이름 VARCHAR(20),		-- (20) = 최대 20byte까지 저장 가능
가입일자 DATE NOT NULL,
수신동의 BIT
);
  • table 조회
SELECT * 						--*, 모든 열을 조회하고자 할 때 사용
FROM (조회하고자 하는 테이블 이름);		

SELECT  *  
FROM  회원테이블;
  • table열 추가
ALTER TABLE (해당 테이블 이름) ADD (열 이름) (data type);

--코드 예
ALTER TABLE 회원테이블 ADD 성별 VARCHAR(2);
  • table 열 data type 변경
ALTER TABLE (테이블 명) MODIFY (열 이름) (data type); 

--코드 예
ALTER TABLE 회원테이블 MODIFY 성별 VARCHAR(20);
  • table 열 이름 변경
ALTER TABLE (테이블 이름) CHANGE (해당 열 이름) (바꾸고자 하는 열 이름) (data type);	
										//data type도 바꿀 수 있음

//코드 예
ALTER TABLE 회원테이블 CHANGE 성별 성 VARCHAR(2);
  • table명 변경
ALTER TABLE (테이블 이름) RENAME (바꾸고자 하는 테이블 이름);

--코드 예
ALTER TABLE 회원테이블 RENAME 회원정보;

DML

  • data 삽입
    • 제약 조건을 어긴 경우 data 삽입 자체가 되지 않음
INSERT INTO (목표 테이블) VALUES (열 순서에 맞는 DATA들);

--코드 예
INSERT INTO 회원테이블 VALUES (1001, '홍길동', '2020-01-02', 1);	--문자형, 날짜형은 쉽표로 묶음
  • 특정 열 data 조회
SELECT  (열 이름),
	(열 이름)
FROM  (테이블 이름);
  
--코드 예  
SELECT  회원번호,
    	이름
FROM  회원테이블;
  • 특정 열 이름 변경해 조회
SELECT  (열 이름),
	(열 이름) AS (바꾸고자하는 열 이름)
FROM  회원테이블;
  
  
SELECT  회원번호,
    	이름 AS 성명
FROM  회원테이블;
  • 모든 데이터 수정
    • 데이터 수정 위해 데이터 수정 및 삭제에 대한 제한설정 풀어줘야된다
      • Workbench 상단에 edit -> preferences -> SQL editor, 맨 마지막 safe updates 해제-->restart
UPDATE (테이블 명)
   SET (수정하고자 하는 열 이름) = (조건);

--코드 예
UPDATE 회원테이블
   SET 수신동의 = 0;
  • 특정 데이터 수정
UPDATE (테이블 이름)
   SET (열 이름A) = (열 조건A)			//B열에 열 조건B의 A열 data를 열 조건A로 교체
 WHERE (열 이름B) = (열 조건B);			//열 이름A에 열 조건A로 바꿈
 
 --코드 예
UPDATE 회원테이블
   SET 수신동의 = 1						
 WHERE 이름 = '홍길동';
 				//이름 열에 홍길동의 수신동의 열 DATA를 1로
  • 특정 데이터 삭제
DELETE 
  FROM (테이블 이름)
 WHERE (특정 열) = (열 data);		//테이블의 특정 열 data의 row 삭제
 
--코드 예
DELETE 
  FROM 회원테이블
 WHERE 이름 = '홍길동';		//회원테이블의 이름이 홍길동인 data 삭제
  • 모든 데이터 삭제
DELETE 
  FROM (테이블 이름);
  
--코드 예  
DELETE 
  FROM 회원테이블;

DCL

  • 사용자 아이디 및 비밀번호 추가
CREATE USER ('사용자 ID')@LOCALHOST IDENTIFIED BY ('비밀번호');		--@LOCALHOST : local에서 접속 가능

--코드 예
CREATE USER 'TEST'@LOCALHOST IDENTIFIED BY 'TEST';
  • 사용자 비밀번호 변경
SET PASSWORD FOR (생성된 ID)@LOCALHOST = ('변경할 비밀번호');

--코드 예
SET PASSWORD FOR 'TEST'@LOCALHOST = '1234';
  • 만들어진 사용자로 접속 확인
    • Workbench에서 홈버튼 클릭
      • MySQL Connections에서 + 버튼 클릭
        • Connection Name, Username에 사용자 ID, 설정한 비밀번호 입력
          • 완료 시 MySQL Connections에 사용자 ID 생성됨
  • 특정 권한 부여
GRANT SELECT, DELETE ON (DB).(테이블 이름) TO ('권한 부여할 ID')@LOCALHOST;


--코드 예
GRANT SELECT, DELETE ON PRACTICE.회원테이블 TO 'TEST'@LOCALHOST;

권한 부여 확인은 해당 사용자 ID에 들어가 DB 확인하면 알 수 있음

  • 특정 권한 제거
REVOKE DELETE ON (DB).(테이블 이름) FROM ('사용자 ID')@LOCALHOST;

--코드 예
REVOKE DELETE ON PRACTICE.회원테이블 FROM 'TEST'@LOCALHOST;
  • 모든 권한 부여
GRANT ALL ON (DB).(테이블 이름) TO ('사용자 ID')@LOCALHOST;

--코드 예
GRANT ALL ON Practice.회원테이블 TO 'TEST'@LOCALHOST;
  • 모든 권한 제거
REVOKE ALL ON (DB).(테이블 이름) FROM ('사용자 ID')@LOCALHOST;

--코드 예
REVOKE ALL ON Practice.회원테이블 FROM 'TEST'@LOCALHOST;
  • 사용자 삭제
DROP USER ('사용자 ID')@LOCALHOST;

--코드 예
DROP USER 'TEST'@LOCALHOST;
  • 사용자 확인
SELECT  *
  FROM  USER;

TCL

  • 트랜잭션 시작 명령어
BEGIN;
  • 취소
    • BEGIN 이후부터 ROLLBACK 전까지의 작업 무효화
ROLLBACK;
  • 실행
    • BEGIN 이후부터 ROLLBACK 전까지의 작업 실행
COMMIT;
  • 임시 저장
    • 트랜잭션 중 작업이 끝날 때 SAVEPOINT 이용해 임시 저장점 저장
    • ROLLBACK TO 이용해 SAVEPOINT 이동
----작업----
------------
SAVEPOINT (변수);

ROLLBACK TO (변수);

--코드 예
----작업----
------------
SAVEPOINT S2;

ROLLBACK TO S2;

SQL - 문법

  • SELECT
    • column이 위치
    • 사용자가 원하는 data를 찾는 경우에도 결국 새로운 column이 생성되는 것이므로 
  • Table 생성
CREATE TABLE table_name(			
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY( one or more columns)
)
  • Table 삭제
DROP TABLE table_name
  • Data 추가
INSERT INTO TABLE_NAME(column1,...columnN)
VALUES(value1,...valueN)
  • Data 수정
UPDATE table_name
SET column1 = value1,...columnN = valueN
WHERE [condition]
  • 특정 Table에서 Data 추출
SELECT column1, column2,...columnN
FROM table_name
  • 원하는 데이터만 골라보고 싶을 때 - WHERE, 조건절
    • excel로 치면 filter
SELECT			--원하는 컬럼
FROM			--조회하는 테이블
WHERE			--필터링하고 싶은 조건
  • 단일 행 함수
    • 숫자형 
      • SELECT ABS(숫자);		--절대값 반환
      • SELECT ROUND(숫자, N)		--N 기준으로 반올림 값 반환, (N-1)자리에서 반올림
      • SELECT SQRT(숫자);		--제곱근 값 반환
    • 문자형
      • SELECT LOWER('(문자)');		--소문자로 변환
SELECT UPPER('(문자)');		--대문자로 변환
      • SELECT LEFT('(문자)', N);		--문자 왼쪽부터 N번째 반환	 	
SELECT RIGHT('(문자)', N); 		--문자 오른쪽부터 N번째 반환
					--index(순서)는 1부터
      • SELECT LENGTH('문자');		--문자 길이 반환
    • 날짜형
      • SELECT YEAR('2022-12-31');		--연도만 반환
SELECT MONTH('2022-12-31');		--월만 반환
SELECT DAY('2022-12-31');		--일만 반환
      • SELECT DATE_ADD(날짜, INTERVAL)		--INTERVAL만틈 더한 값 반환

--코드 예
SELECT DATE_ADD('2022-12-31', INTERVAL -1 MONTH);
--출력
2022-11-30
      • SELECT DATEDIFF(날짜A, 날짜B)		--날짜A - 날짜B 일수 반환
    • 형변환
      • SELECT DATE_FORMAT(날짜, 형식) : 날짜형식으로 변환

--코드 예
SELECT DATE_FORMAT('2022-12-31', '%m-%d-%y');
SELECT DATE_FORMAT('2022-12-31', '%M-%D-%Y');
      • SELECT CAST(형식A, 형식B)		--형식A를 형식B로 변환

--코드 예
SELECT CAST('2022-12-31 12:00:00' AS DATE);
    • 일반 함수
      • SELECT IFNULL(A, B) : A가 NULL이면 B를 반환, 아니면 A 반환

--코드 예
SELECT IFNULL(NULL, 0);
SELECT IFNULL('NULL이 아님', 0);
      • --여러 조건별로 반환값 지정
CASE WHEN [조건1] THEN [반환1]
     WHEN [조건2] THEN [반환2]
     ELSE [나머지] END
     
--코드 예
SELECT  *
		,CASE WHEN GENDER = 'MAN' THEN '남성'
              ELSE '여성' END
  FROM  CUSTOMER;
--CUSTOMER table에서 GENDER 열이 MAN이면 남성, 그 외는 여성으로 반환값 지정
  • 복수 행 함수
    • --집계 함수
SELECT COUNT(특정 열) AS A  --열 앞에 DISTINCT 이용하면 중복 제거
SELECT SUM(특정 열) AS B
SELECT AVG(특정 열) AS C
SELECT MAX(특정 열) AS D
SELECT MIN(특정 열) AS E
--특정 열 연산, AS 이용해 이름 정의
    • --그룹 함수
WITH ROLLUP		--GROUP BY 다음에 사용, 열들을 오른쪽에서 왼쪽순으로 그룹
  • 윈도우 함수
    • --순위 함수
ROW_NUMBER: 동일한 값이라도 고유한 순위 반환 (1,2,3,4,5...) 
RANK: 동일한 값이면 동일한 순위 반환 (1,2,3,3,5...) 
DENSE_RANK: 동일한 값이면 동일한 순위 반환(+ 하나의 등수로 취급) (1,2,3,3,4...)

--코드 예
SELECT  ORDER_DATE
		,ROW_NUMBER() OVER (ORDER BY ORDER_DATE ASC) AS 고유한_순위_반환
        ,RANK() 	  OVER (ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환
        ,DENSE_RANK() OVER (ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환_하나의등수
  FROM  SALES;
--순서 기준이 되는 열 : ORDER_DATE, 맨 왼쪽에 위치하며 ORDER_DATE열의 DATA들을 기준으로 차순 결정
    • --순위 함수+ PARTITION BY: 그룹별 순위

--코드 예
SELECT  MEM_NO
		,ORDER_DATE
		,ROW_NUMBER() OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 고유한_순위_반환
        ,RANK() 	  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환
        ,DENSE_RANK() OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환_하나의등수
  FROM  SALES;
--MEM_NO 열을 기준으로 그룹화, MEM_NO, ORDER_DATE 순으로
    • /*집계 함수-누적  (복수 행 함수 + 윈도우 함수)
날짜가 증가함에 따라 집계 함수가 누적되어 반환
코드 예*/
SELECT  ORDER_DATE
		,SALES_QTY
        ,'-' AS 구분
        ,COUNT(ORDER_NO) OVER (ORDER BY ORDER_DATE ASC) AS 누적_구매횟수
		,SUM(SALES_QTY)  OVER (ORDER BY ORDER_DATE ASC) AS 누적_구매수량
        ,AVG(SALES_QTY)  OVER (ORDER BY ORDER_DATE ASC) AS 누적_평균구매수량
        ,MAX(SALES_QTY)  OVER (ORDER BY ORDER_DATE ASC) AS 누적_가장높은구매수량
		,MIN(SALES_QTY)  OVER (ORDER BY ORDER_DATE ASC) AS 누적_가장낮은구매수량    
  FROM  SALES;
  
  
  
/* 집계 함수(누적)+ PARTITION BY: 그룹별 집계 함수(누적) 
그룹별로 누적된 집계 함수 값이 반환 
코드 예*/
SELECT  MEM_NO
		,ORDER_DATE
		,SALES_QTY
        ,'-' AS 구분
        ,COUNT(ORDER_NO) OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_구매횟수        
		,SUM(SALES_QTY)  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_구매수량
        ,AVG(SALES_QTY)  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_평균구매수량
        ,MAX(SALES_QTY)  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_가장높은구매수량
		,MIN(SALES_QTY)  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_가장낮은구매수량       
  FROM  SALES;

기본 문법

  • 대소문자 구분 X
    • 하지만 명령어는 대문자
    • 명령어가 아닌 경우는 소문자로 사용해주는게 가독성 좋음
  • 공백 구분 X
    • 명령문만 구분해 줄바꿈 해주면 마찬가지로 가독성 좋음
  • 새로운 컬럼을 만들 때
    • 띄어쓰기는 _로 
    • table 이름과는 겹치지 않게
    • SQL 명령어(Reserved Words)와 겹치지 않게
      • Reserved Words
        • 이미 SQL 명령어에서 사용되고 있는 단어들
        • SQL 예약어라 보면 된다
      • 명령어와 동일한 이름으로 만든다면 syntax error 발생
    • 이름 축약하지 않는 것이 좋다
      • full name으로 작성하는 것이 가독성면에서 좋음
  • 참고
    • SQL convention
    • SQL style guide

SQL 명령어

  • 명령어의 경우 오른쪽 줄 기준으로 좌우 간격 맞춰줌

  • 모든 명령이 끝난 경우에만 세미콜론(;) 사용

  • table의 모든 열 조회

SELECT  *
  FROM  CUSTOMER;
  • 특정 조건 추가
SELECT  *
  FROM  (table)
 WHERE  (특정 열) = (열의 data);

--코드 예
SELECT  *
  FROM  CUSTOMER
 WHERE  GENDER = 'MAN';			--GENDER열이 MAN인 경우만 조회
  • GROUP BY 추가
SELECT  (그룹화 할 열)		--지역 별로 데이터 조회되어야 하기 때문에 SELECT절에도 작성
	,COUNT(특정 열) AS (만들어지는 column 이름)	--행들의 개수를 구하는 집계함수
  FROM  (table)
 WHERE  (특정 열) = (열의 data);
 GROUP
    BY  (그룹화 할 열);
    

--코드 예
SELECT  ADDR
	,COUNT(MEM_NO) AS 회원수
  FROM  CUSTOMER
 WHERE  GENDER = 'MAN'		--성별이 남자인
 GROUP
    BY  ADDR;		--주소지(열 중에 하나)로 그룹화
  • HAVING 추가
SELECT  (그룹화 할 열)		
	,COUNT(특정 열) AS (만들어지는 column 이름)	
  FROM  (table)
 WHERE  (특정 열) = (열의 data);
 GROUP
    BY  (그룹화 할 열);
HAVING  (특정 조건);


--코드 예
SELECT  ADDR
	,COUNT(MEM_NO) AS 회원수
  FROM  CUSTOMER
 WHERE  GENDER = 'MAN'			
 GROUP
    BY  ADDR				--ADDR열로 그룹 지은 후
HAVING  COUNT(MEM_NO) < 100;		--회원 수 100명 미만의 결과만 조회
  • ORDER BY 추가
    • 맨 하단에 작성되어야 함
SELECT  (그룹화 할 열)		
	,COUNT(특정 열) AS (만들어지는 column 이름)	
  FROM  (table)
 WHERE  (특정 열) = (열의 data);
 GROUP
    BY  (그룹화 할 열);
HAVING  (특정 조건);
 ORDER
	BY  (특정 조건) DESC;		--DESC : 내림차순
    					--ASC : 오름차순

--코드 예
SELECT  ADDR
	,COUNT(MEM_NO) AS 회원수
  FROM  CUSTOMER
 WHERE  GENDER = 'MAN'
 GROUP
    BY  ADDR
HAVING  COUNT(MEM_NO) < 100		--100이하 중에
 ORDER
	BY  COUNT(MEM_NO) DESC;		--집계 회원수 높은 순
  • GROUP BY +  집계함수
SELECT  (특정 열A)
	,(특정 열B)
	,(특정 조건) AS (만들 열)		--특정 조건으로 새로운 열을 만듬
  FROM  (테이블 명)
 WHERE  (특정 열) IN ('열 data', '열 data')	--()안에 있는 DATA만 filter, IN을 이용한 DATA를 LIST라 표현
 GROUP
    BY  (특정 열A)
	,(특정 열B);
        
        
--코드 예
SELECT  ADDR
	,GENDER
	,COUNT(MEM_NO) AS 회원수
  FROM  CUSTOMER
 WHERE  ADDR IN ('SEOUL', 'INCHEON')
 GROUP
    BY  ADDR
	,GENDER;				//ADDR, GENDER열로 그룹화

실습

강의자료 파일 참고

숫자, 문자 조건 이용해 DATA 구분

  • 100달러 미만 숙소 찾아보기 - 숫자
SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM `rising-precinct-339211.eclass.air`
WHERE  price < 100
  • Manhattan에 있는 숙소 찾기 - 문자
SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM `rising-precinct-339211.eclass.air`
WHERE  neighbourhood_group = 'Manhattan'		--String type 유의, 부호 개발 언어와 동일(!=,>=등)
							--큰, 작은 따옴표 구분 X
                            				--Data값의 경우는 대,소문자 구분
                            				--SQL자체는 구분하지 않지만 DATA는 구분해야됨
  • Brooklyn Writer's Nook 숙소 찾기
    • data자체에 '포함되어 있음(1)
    • 조건 2개 이상
      • AND(2) 
      • int type 범위(3)
      • String type
        • 만약 문자 부등식으로 비교하면 알파벳 순으로 비교하여 나중에 있는 값 추출

(1) 데이터 내에 작은따옴표(') 포함되어 있는 경우

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM `rising-precinct-339211.eclass.air`
WHERE "Brooklyn Writer's Nook"				--만약 data자체에 '써야 한다면 "로 포함

(2) 조건 2개 이상인 경우 AND 이용

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM `rising-precinct-339211.eclass.air`
WHERE neighbourhood_group = 'Manhattan'
AND price < 100

(3)INT 사이 범위를 정의하는 경우 AND 이용

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM `rising-precinct-339211.eclass.air`
WHERE neighbourhood_group = 'Manhattan'
AND price BETWEEN  50 AND 150				--(price >=50) && (price <= 150)

INT, 문자 함께 비교

SELECT id, name,neighbourhood_group,neighbourhood,room_type,price
FROM `rising-precinct-339211.eclass.air`
WHERE neighbourhood_group = 'Manhattan'
AND price <= 150
AND (neighbourhood = 'Manhattan' OR neighbourhood = 'SoHo') --AND neighbourhood IN ('Midtown', 'SoHo') 동일

String type 비교연산 가능

SELECT id, name,neighbourhood_group,neighbourhood,room_type,price
FROM `rising-precinct-339211.eclass.air`
WHERE neighbourhood_group = 'Manhattan'
AND price <= 150
AND neighbourhood != 'Manhattan' 
AND neighbourhood != 'SoHo'		--5,6줄 코드 AND neighbourhood NOT IN('Midtown', 'SoHo') 동일
  • 비어있는 데이터(NULL)과 0은 다름
    • column이 비어있는 경우를 찾을 때 
SELECT id, name,neighbourhood_group,neighbourhood,room_type,price, number_of_reviews, last_review
FROM `rising-precinct-339211.eclass.air`
WHERE last_review IS NULL		--A


비어있지 않은 경우
A 코드 - WHERE last_review IS NOT NULL 대체
  • 특정 String type 포함 유무 판단
    • LIKE % 이용
SELECT id, name,neighbourhood_group,neighbourhood,room_type,price, number_of_reviews, last_review
FROM `rising-precinct-339211.eclass.air`
WHERE name LIKE '%terrace%'


'%String type%' - 위치에 상관없이 String type 들어간 경우
'%String type' - String type으로 끝나는 경우
	ex) WHERE email LIKE '%naver.com' 
    	이용해 특정 도메인 사용자만 체크 가능
'String type%' - String type으로 시작하는 경우



포함하지 않은 경우
3번 - WHERE name NOT LIKE '%terrace%'
  • 대, 소문자 변경
    • UPPER, LOWER
    • AS
      • 새로운 column 이름 생성 or 기존의 column 이름 변경
      • 생략 가능
SELECT name, UPPER(name) AS upper_name, LOWER(name) AS lower_name
FROM `rising-precinct-339211.eclass.air`


--대, 소문자로 바꿔 새로운 DATA(=table column)만듬
--생략 가능
  • 대,소문자 변경해 특정 String type 확인
SELECT id, name,neighbourhood_group,neighbourhood,
room_type,price, number_of_reviews, last_review
FROM `rising-precinct-339211.eclass.air`
WHERE LOWER(name) LIKE '%terrace%'	--name이라는 column 소문자로 바꿔 String type 포함 유무 확인
					--대문자의 경우 WHERE LOWER(name) LIKE '%TERRACE%'
  • WHERE 사용할 때
WHERE price*3 < 200		--한번 가공한 값이 들어가도 상관없다

Aggregation

  • 함수
  • 데이터를 하나의 값으로 요약
  • 엑셀로 치면 피벗 테이블
  • count
    • 갯수 측정, 새로운 data 만들어냄
    • 중복 제거 - COUNT(DISTINCT column)
--특정 column 갯수 파악
SELECT COUNT(id) AS id_count
FROM `rising-precinct-339211.eclass.air`
--갯수라는 새로운 data 발생, 새로운 column 만들어짐

=========================================================================================
--table row 수 구하기
SELECT COUNT(*) AS id_count
FROM `rising-precinct-339211.eclass.air`
--1번 * : 전체 table이 가지고 있는 low 수를 구해줌
--비어 있는 데이터(=null)인 경우는 count 되지 않음
  • 최대, 최소
    • MIN, MAX
SELECT MIN(컬럼명) AS minimum_컬럼명, MAX(컬럼명) AS maximum_컬럼명

ex)
SELECT MIN(price) AS minimum_price, MAX(price) AS maximum_price
  • 평균
    • AVG 
    • null 제외
SELECT  AVG(컬럼명) AS average_컬럼명

ex)
SELECT  AVG(price) AS average_price
  • 전체 리뷰 수
    • SUM
SELECT  SUM(컬럼명) AS sum_컬럼명

ex)
SELECT  SUM(number_of_reviews) AS sum_number
  • 그룹별로 나눠 데이터를 보고 싶은 경우
    • GROUP BY 이 후에 위치한 colunm과 SELECT 이 후에 위치한 column 동일해야됨
SELECT 			--원하는 컬럼
FROM 			--조회하는 테이블
WHERE 			--필터링하고 싶은 조건
GROUP BY		--묶어서 보고 싶은 컬럼
ORDER BY		--정렬해서 보고 싶은 컬럼
LIMIT 			--상위 n개 결과만 조회


ex) neighbourhood_group별 min, max, avg
SELECT neighbourhood_group,				
MIN(price) AS minimum_price,			
Max(price) AS maximum_price,
AVG(price) AS average_price
FROM `rising-precinct-339211.eclass.air`
GROUP BY neighbourhood_group

17번 코드
--column 순차적으로 그룹화 가능
--ex) GROUP BY neighbourhood_group, neighbourhood
  • 정렬 - ORDER BY 
    • 기본 값은 오름차순 
      • 알파벳도 마찬가지
    • 정렬을 단계별로 시킬 수 있음
SELECT neighbourhood_group,neighbourhood,
COUNT(id) AS id_count,
AVG(price) AS average_price
FROM `rising-precinct-339211.eclass.air`
GROUP BY neighbourhood_group, neighbourhood
ORDER BY COUNT(id)

6번
오름차순이 기본
내림차순 - ORDER BY COUNT(id) DESC
		붙여서 사용 X


여러 요소들로 정렬
ORDER BY neighbourhood_group, COUNT(id)
  • column 순서 이용한 정렬
SELECT A, B , C, D
FROM DATA ---
GROUP BY 1, 2
ORDER BY 1, 3

--A~D 들어온 순서대로 1~4

SQL - 조건절

  • WHERE
    • 개별 행에 적용
    • 원본에 적용, GROUP BY 이전 작동
  • HAVING
    • 그룹화된 결과에 적용
    • GROUP BY 이후 작동
SELECT neighbourhood_group,neighbourhood,
COUNT(id) AS id_count,
AVG(price) AS average_price
FROM `rising-precinct-339211.eclass.air`
GROUP BY neighbourhood_group, neighbourhood
HAVING AVG(price) < 150
ORDER BY COUNT(id)

SQL - 최종 순서

SELECT 			--원하는 컬럼
FROM 			--조회하는 테이블
WHERE 			--GROUP BY 이전 필터링하고 싶은 조건
GROUP BY		--묶어서 보고 싶은 컬럼
HAVING			--GROUP BY 이후 필터링하고 싶은 조건
ORDER BY		--정렬해서 보고 싶은 컬럼
LIMIT 			--상위 n개 결과만 조회

Table

  • 여러개 존재
    • DB 효율적으로 관리 위해
  • primary key로 연결해 이용
  • JOIN(Merge)
    • DB안의 여러 테이블을 합치거나 연결하는 기능
    • 키 값을 기준으로 가로로 테이블을 붙이는 형태
    • 새로운 column 생성
      • 일반적으로 JOIN만 쓴 경우는 INNER JOIN 뜻함
  • 열마다 반드시 1가지의 데이터 타입으로 정의
    • ex) 열의 데이터 타입이 날짜열로 된 경우 숫자형과 문자형 기록 불가(숫자형, 문자형과는 다른 것)
  • SQL에서 table 사용하기 위해서는 USE 명령 필수

관계형 테이블 간의 관계

  • 1:N 관계
    • 회원 테이블의 회원번호는 중복 없이, 주문 테이블의 회원번호는 중복 가능하게 저장되어 있는 것
    • 한명의 회원이 여러번 주문 가능하다 의미
SELECT							--각 테이블에서 필요한 컬럼들
FROM (data-set).(1table) AS (1table-첫글자)		--첫번째 테이블
JOIN (data-set).(2table) AS (2table-첫글자)		--첫번째 테이블과 합치려는 두번째 테이블
ON (1table-첫글자).(column) = (2table-첫글자).(column)	--column의 경우 공통


ex)
SELECT o.*, p.category, p.name			--* : 전체 column
FROM data.orders AS o
JOIN data.products AS p
ON o.product_id = p.product_id			
--product_id를 기준으로 orders table에 products table의 category, name column 두 가지 연결
--기존의 orders table 뒤에 이어서 붙음

--table 3개 이상인 경우
ex)
SELECT o.*, p.category, p.name, u.segment
FROM data.orders AS o
JOIN data.products AS p
ON o.product_id = p.product_id
JOIN data.users AS u
ON o.customer_id = u.customer_id

JOIN

-   INNER
    -   합칠 때 양쪽 항목에 중복되는 데이터만 가지고 오는 join
    -   두 table의 교집합만 return
-   OUTER
    -   FULL
        -   양쪽 항목의 합집합에 해당하는 데이터를 가져오는 JOIN
        -   두 table의 합집합 return
            -   null이 존재
    -   LEFT
        -   한 쪽 테이블의 데이터를 유지한 채 다른 쪽 데이터를 가져오는 JOIN
            -   최대 ROW(가로) 갯수는 기준이 되는 한 쪽 테이블
        -   null이 존재
        -   기준 테이블 + join하는 테이블 정보(기준 테이블과의 교집합만)
    -   RIGHT  
        -   LEFT JOIN과 방향 반대
        -   실무에서는 LEFT를 많이 씀
        -   null이 존재
--INNER 두 가지 방법
SELECT
FROM table1
JOIN table2
SELECT
FROM table1
INNER JOIN table2
========================================================================================================
--FULL 두 가지 방법
SELECT
FROM table1
FULL JOIN table2
SELECT
FROM table1
FULL OUTER JOIN table2
==========================================================================================================
--LEFT 두 가지 방법
SELECT
FROM table1
LEFT JOIN table2
SELECT
FROM table1
LEFT OUTER JOIN table2
EX)
SELECT u.*,o.order_id,o.order_date
FROM data.users AS u
LEFT JOIN data.orders AS o
ON u.customer_id = o.customer_id
==========================================================================================================
--RIGHT 두 가지 방법
SELECT
FROM table1
RIGHT JOIN table2
SELECT
FROM table1
RIGHT OUTER JOIN table2
  • JOIN(=INNER JOIN)
SELECT  *
  FROM  (테이블 이름) AS A		--테이블 이름을 A로 변경
 INNER
  JOIN  (테이블 이름) AS B		--테이블 이름을 B로 변경
    ON  A.(열 이름) = B.(열 이름);		--결합 조건, 각 테이블들의 열을 가리킴
    
--코드 예    
SELECT  *
  FROM  CUSTOMER AS A
 INNER
  JOIN  SALES AS B
    ON  A.MEM_NO = B.MEM_NO;		--CUSTOMER TABLE, SALES TABLE 회원 번호 기준으로 결합
  • LEFT JOIN
    • 두 테이블의 공통 값이 맻이되는 데이터만 결합, 왼쪽 테이블의 매칭되지 않는 데이터는 NULL
SELECT  *
  FROM  (테이블 이름) AS A
  LEFT
  JOIN  (테이블 이름) AS B
    ON  A.(열 이름) = B.(열 이름);
    
table 3개 이상, table 1~3
SELECT  *
  FROM  (테이블 이름1) AS A
  LEFT
  JOIN  (테이블 이름2) AS B
    ON  A.(열 이름1) = B.(열 이름1)
  LEFT
  JOIN  (테이블 이름3) AS B
    ON  A.(열 이름2) = B.(열 이름2);
  • RIGHT JOIN
    • LEFT JOIN과 반대
SELECT  *
  FROM  (테이블 이름1) AS A
 RIGHT
  JOIN  (테이블 이름2) AS B
    ON  A.(열 이름) = B.(열 이름);
 WHERE  A.(열 이름2) IS NULL;		--IS NULL : 비교 연산자, NULL인 값만 filtering
 					--A테이블의 열 이름2이 NULL인 부분만 출력
                    			--테이블 이름2와 매칭되지 않는 부분은 NULL로 출력
  • 임시테이블 생성
    • DB상에 존재하지 않으며 서버 연결 종료시 자동으로 삭제
CREATE TEMPORARY TABLE (임시테이블 명)
SELECT  A.*
	,B.(열 이름)
  FROM  (테이블 이름) AS A		
 INNER
  JOIN  (테이블 이름) AS B		
    ON  A.(열 이름) = B.(열 이름);	
    

--코드 예
CREATE TEMPORARY TABLE CUSTOMER_SALES_INNER_JOIN
SELECT  A.*
	,B.ORDER_NO
  FROM  CUSTOMER AS A
 INNER
  JOIN  SALES AS B
    ON  A.MEM_NO = B.MEM_NO;

ON과 WHERE 

  • 연산 순서 차이 존재
    • 결과 차이 발생 
    • ON
      • table의 JOIN하는 기준 정의
      • table 합쳐지기 전
    • WHERE
      • table 합쳐지고 나서 실행

UNION

  • 테이블을 아래로, 세로로 붙이는 형태
  • 새로운 row 생성
SELECT
  FROM table1				--완전한 table1 정의, column
 UNION ALL				--중복제거 목적이라면 UNION DISTINCT 사용
SELECT
  FROM table2				--완전한 table2 정의


--일부 DBMS는 UNION 사용
--table정의에 있어 table1과 table2 column 이름 달라도 쿼리 가능, 하지만 갯수와 type 같아야됨
--서로 다른 data여도 갯수와 type같으면 붙을 수 있기에 유의해야됨


ex)
SELECT *					--* : 전체 column
  FROM `rising-precinct-339211.data.spotify_1990`		
UNION ALL or UNION DISTINCT
SELECT *
  FROM `rising-precinct-339211.data.spotify_2000`


--3개인 경우
--완전한 table1 정의
--UNION ALL
--완전한 table2 정의
--UNION ALL
--완전한 table3 정의

--갯수 늘어날 때 계속해서 정의하면 됨

서브쿼리

  • 위치에 따라 SELECT, FROM, WHERE절로 나뉨
  • SELECT절
    • 테이블의 열을 추가할 때 사용
    • JOIN 사용 없이 다른 테이블의 정보 가져올 수 있다
      • JOIN보다 처리속도 느림
--코드 예
SELECT  *
	,(SELECT GENDER FROM CUSTOMER WHERE A.MEM_NO = MEM_NO) AS GENDER
  FROM  SALES AS A;
  
--CUSTOMER table이 GENDER 조회 가능, WHERE을 이용해 SALES table의 회원번호와 CUSTOMER table의 회원번호
--해당 열을 GENDER로 변경

FROM절

-   FROM(), 괄호 안에는 테이블이 와야되며 GROUP BY로 생성된 테이블을 사용할 수 있다
SELECT  *
  FROM  (
	SELECT  MEM_NO
		,COUNT(ORDER_NO) AS 주문횟수--(1)
          FROM  SALES
	 GROUP
            BY  MEM_NO
		)AS A--(2);
        
--주문횟수 = 테이블, AS 이용해 (1,2) 정의해줘야 된다

WHERE절

-   List로 사용
SELECT  COUNT(ORDER_NO) AS 주문횟수
  FROM  SALES
 WHERE  MEM_NO IN (SELECT  MEM_NO FROM CUSTOMER WHERE YEAR(JOIN_DATE) = 2019);
--CUSTEMOR TABLE의 가입일자가 2019인 회원의 주문횟수 구하는 명령어

--명령어 IN 뒤에는 LIST형태가 와야 된다
--YEAR - 날짜형 함수 : 해당 열을 연도로 반환



--JOIN의 경우
SELECT  COUNT(A.ORDER_NO) AS 주문횟수
  FROM  SALES AS A
 INNER
  JOIN  CUSTOMER AS B
    ON  A.MEM_NO = B.MEM_NO
 WHERE  YEAR(B.JOIN_DATE) = 2019;
 
--SALES, CUSTOMER table을 회원번호 기준으로 INNER JOIN 후 WHERE 통해 JOIN DATA 2019년 인 경우만 
--ORDER_NO(열의 갯수) 구함
  • GROUP BY
    • FROM table을 기반으로 GROUP BY에서 정의한 열들만 묶어 테이블화 시켜줌

날짜 형식

  •  DATE
    • 2017-06-11
  • TIME
    • 21:58:31
  • DATETIME
    • 2017-06-11 21:58:31
  • TIMESTAMP
    • 2017-06-11 21:58:31 UTC
    • DATETIME + 시간대(타임존)포함
    • UTC - 국제 표준 시간 기준

날짜 관련 데이터 전처리

형식 교체  - 데이터 전처리 과정

  • 강의자료 첨부파일 참고
  • 'YYYY-MM-DD' 형식
DATE(컬럼명)
  • 그 외의 형식
    • 컴퓨터가 어떤 것이 연도, 월, 일인지 구분 불가
    • 형식
연도2017%Y
17%y
월06%m
JUNE - full name%B
JUN - 축약형%b
일11%d
PARSE_DATE(날짜형태, 컬럼명)

ex)
SELECT 
PARSE_DATE('%Y%m%d','20170611'),
PARSE_DATE('%Y/%m/%d','2017/06/11'),
PARSE_DATE('%y.%m.%d','17.06.11'),
PARSE_DATE('%y-%b/%d','17-JUN/11'),
PARSE_DATE('%Y%B%d','2017JUNE11'),
  • 원하는 정보 추출
EXTRACT(원하는정보 FROM 컬럼명)

원하는 정보
	YEAR
	MONTH
	DAY
	DAYOFWEEK		--일요일부터 토요일까지 1~7
	WEEK			--0~53, 주의 시작 기본값은 일요일
	WEEK(MONDAY)		--월요일을 주의 시작으로
	QUARTER
    
    
ex)
SELECT rental_date, 
        EXTRACT(YEAR FROM rental_date) AS rental_year		--rental_date에서 YEAR만 추출
  FROM `rising-precinct-339211.data.bike_sharing`

--중복 없이 보고싶은 경우
SELECT DISTINCT rental_date, 
        EXTRACT(YEAR FROM rental_date) AS rental_year		
  FROM `rising-precinct-339211.data.bike_sharing`
  • 날짜에 일정 기간 덧셈, 뺄셈
DATE_ADD(컬럼명, INTERVAL 기간)		--더할 때

DATE_SUB(컬럼명, INTERVAL 기간)		--뺄 때

--기간
    YEAR
    MONTH
    DAY
    WEEK
    QUARTER
    
ex)
SELECT
	DISTINCT
    rental_Date,
    DATE_ADD(rental_date, INTERVAL 5 DAY) AS rental_Date_5day,
    DATE_ADD(rental_date, INTERVAL 2 YEAR) AS rental_Date_2year,
    DATE_ADD(rental_date, INTERVAL 24 MONTH) AS rental_Date_24month,
    DATE_SUB(rental_date, INTERVAL 1 WEEK) AS rental_Date_1week_ago,
    DATE_SUB(rental_date, INTERVAL 3 QUARTER) AS rental_Date_3quarter_ago,
FROM data.bike_sharing
ORDER BY rental_date
  • 두 날짜 사이의 차이 구하기
DATE_DIFF(컬럼명1, 컬럼명2, 단위)

--컬럼명 1이 컬럼명 2보다 최근 일자여야 한다

단위
YEAR
    MONTH
    DAY
    WEEK
    WEEK(MONDAY)
    QUARTER
    
    
ex)
SELECT
	DISTINCT
    rental_date,
    DATE_DIFF(DATE('2020-02-02'), rental_date, YEAR) AS diff_year,
    DATE_DIFF(DATE('2020-02-02'), rental_date, MONTH) AS diff_month,
    DATE_DIFF(DATE('2020-02-02'), rental_date, DAY) AS diff_day,
    DATE_DIFF(DATE('2020-02-02'), rental_date, WEEK) AS diff_week,
    DATE_DIFF(DATE('2020-02-02'), rental_date, WEEK(MONDAY)) AS diff_week_monday,
    DATE_DIFF(DATE('2020-02-02'), rental_date, QUARTER) AS diff_quarter
FROM data.bike_sharing
ORDER BY rental_date

조건에 따라 값 설정

  • CASE WHEN
    • 엑셀에서는 if
CASE WHEN 조건1 THEN 결과1			--조건1로 나온 내용 AS column에 결과 1저장
     WHEN 조건2 THEN 결과2
     ...
     WHEN 조건N THEN 결과N ELSE 기본값 END AS column	--ELSE 기본값
							--앞에 조건들에 모두 해당하지 않을 때 정해 줄 기본값
							--생략 가능
								--생략한 경우 null로 들어감
  
ex)
SELECT humidity,
       CASE WHEN humidity>=0 AND humidity<30 THEN '건조'
            WHEN humidity>=30 AND humidity<60 THEN '적정'
            WHEN humidity>=60 AND humidity<1000 THEN '습함' ELSE 'error' END AS humidity_group
FROM `rising-precinct-339211.data.bike_sharing`


ex)
--숫자 함께 기입해 정렬
SELECT DISTINCT humidity,		--DISTINCT : 중복 제거
       CASE WHEN humidity>=0 AND humidity<30 THEN '0_건조'
            WHEN humidity>=30 AND humidity<60 THEN '1_적정'
            WHEN humidity>=60 AND humidity<1000 THEN '2_습함' ELSE '3_error' END AS humidity_group
FROM `rising-precinct-339211.data.bike_sharing`
ORDER BY humidity_group			--ORDER BY : 정렬

한번 다듬어진 데이터를 다시 사용해야 할 때

  • 두 가지 경우
    • 서브쿼리
      • JOIN과 달리 굳이 새로운 컬럼이 필요하지 않을 때 서브쿼리 사용
        • 알고리즘과 유사
        • 서브쿼리 결과가 값이나 컬럼 한개인 경우 사용
      • 종류
        • WHERE에 쓰는 경우
        • FROM에 쓰는 경우
    • WITH
      • 서브쿼리가 길어지고 복잡해지는 경우
      • 한 쿼리내에서 같은 서브쿼리가 여러번 반복되는 경우
  • 서브쿼리 - WHERE
ex) 가장 최근 주문일 데이터 골라보기
SELECT *
FROM data.orders
WHERE order_date = '2017-12-30'


--위의 코드를 사용했을 때 문제점
DB에 있는 가장 최근 일자를 데이터를 뽑을 때마다 확인
DB 최근 일자에 영향 받아 날짜 수정필요


--개선
1. 가장 최근 주문일 구하고
SELECT MAX(order_date) AS max_order_date		--MAX : 가장 최근 주문일 확인 가능
FROM data.orders


2. 가장 최근 주문일에 해당하는 데이터 필터링하기
SELECT *	
FROM data.orders
WHERE order_date = (SELECT MAX(order_date) AS max_order_date FROM data.orders)	--1에서 사용한 쿼리 2에 추가
ex) 구매경험이 있는 고객 리스트만 구할 경우
1.주문 테이블에 있는 고객 리스트 구하기
SELECT customer_id
FROM data.orders

2.주문 테이블에 있는 고객 필터링하기
SELECT *
FROM data.users
WHERE customer_id IN(SELECT customer_id FROM data.orders)
		    --IN (목록) 구조, 목록에 목록 대신 쿼리 대입
                    --IN : list에서 하나라도 속한 경우 다 가져옴
  • 서브쿼리 - FROM
ex) 캘리포니아 일평균 판매량

1.주(stste)별로 일별 판매량

--stste column 붙임
SELECT o.*, u.state
FROM data.orders o
LEFT JOIN data.users u
ON o.customer_id = u.customer_id

--각 주들의 일 별 주문의 총 합 구함
--FROM 뒤에는 SELECT하고 싶은 DATA의 위치 적어주는데 ()이용해 전에 만든 결과 넣는 것도 가능  
FROM(		
SELECT u.state, o.order_date, SUM(quantity) AS daily_quantity
FROM data.orders o
LEFT JOIN data.users u
ON o.customer_id = u.customer_id
GROUP BY u.state, o.order_date
ORDER BY u.state, o.order_date			--state 알파벳, date 날짜 순으로 정렬
)(서브 쿼리 이름)					--생략 가능
								)AS sub 
                    						)sub 
                    						)가능
                                                                                      


2.주별 일별 판매량의 평균
--각 주의 일평균 판매량
--앞에 만든 쿼리 이용
SELECT state, AVG(daily_quantity) AS state_daily_quantity
FROM(		
SELECT u.state, o.order_date, SUM(quantity) AS daily_quantity
FROM data.orders o
LEFT JOIN data.users u
ON o.customer_id = u.customer_id
GROUP BY u.state, o.order_date
)
Group by state


--서브쿼리를 이용한다면 가독성 좋게 사용하는 것도 중요
  • with
    • 임시 테이블
    • 서브쿼리에서 만들었던 결과물을 이름을 붙여 임시 테이블로 생성
--임시테이블을 이용해 주 별로 평균을 낼 수 있음
--WITH 이후는 생성하고자 하는 이름, WITH 안에는 완전한 쿼리가 들어가야된다
WITH daily_quantity_table AS(	
	SELECT u.state, o.order_date, SUM(quantity) AS daily_quantity
	FROM data.orders o LEFT JOIN data.users u
	ON o.customer_id = u.customer_id
	GROUP BY u.state, o.order_date
	ORDER BY u.state, o.order_date			
)

--임시테이블을 이용해 주 별로 평균을 낼 수 있음
SELETE state, AVG(daily_quantity) AS state_daily_quantity
FROM daily_quantity_table
GROUP BY state

-------여기까지가 주 별로 평균 



--임시 테이블은 DB에 있는 테이블이 아니지만 쿼리에서는 사용 가능
SELECT *
FROM daily_quantity_table


--임시테이블 여러 개 만드는 경우
WITH table1 AS(
	SELECT
	FROM
),
table2 AS(
	SELECT
	FROM
)							--마지막 임시 테이블의 경우 , 없어야됨

SELECT
FROM table1
JOIN talbe2
ON table1.id = table2.id

View 및 Procedure

  • View function
    • 생성
      • CREATE VIEW (가상테이블 명) AS (명령어)

--코드 예, SELECT~ON까지는 사용하려고 만든 가상 테이블
CREATE VIEW SALES_PRODUCT AS
SELECT  A.*
        ,A.SALES_QTY * B.PRICE AS 결제금액
  FROM  SALES AS A
  LEFT
  JOIN  PRODUCT AS B
    ON  A.PRODUCT_CODE = B.PRODUCT_CODE;
    • 수정
      • /*생성에 CREAT -> ALTER로 교체
코드 예*/
ALTER VIEW SALES_PRODUCT AS
SELECT  A.*
        ,A.SALES_QTY * B.PRICE * 1.1 AS 결제금액_수수료포함
  FROM  SALES AS A
  LEFT
  JOIN  PRODUCT AS B
    ON  A.PRODUCT_CODE = B.PRODUCT_CODE;
    • 삭제
      • DROP VIEW (view table 이름);
  • Procedure
    • IN 매개변수
      • 생성 
        • /* DELIMITER: 여러 명령어들을 하나로 묶어줄때 사용 */
DELIMITER //
CREATE PROCEDURE (PROCEDURE명)(IN 매개변수명 매개변수 DATA TYPE...)
BEGIN
	(매개변수를 사용할 명령어)
END//				//DELIMITER 시작과 끝 의미
DELIMITER;			

--코드 예
DELIMITER //
CREATE PROCEDURE CST_GEN_ADDR_IN( IN INPUT_A VARCHAR(20), INPUT_B VARCHAR(20) )
BEGIN
	SELECT  *
	  FROM  CUSTOMER
	 WHERE  GENDER = INPUT_A
	   AND  ADDR = INPUT_B;
END //
DELIMITER ;
      • 실행
        • CALL (PROCEDURE명)('IN매개변수 값', 'IN매개변수 값');

--코드 예
CALL CST_GEN_ADDR_IN('MAN', 'SEOUL');
      • 삭제
        • DROP PROCEDURE (삭제할 PROCEDURE명);
    • OUT 매개변수
      • 생성 
        • --코드 예
DELIMITER //
CREATE PROCEDURE CST_GEN_ADDR_IN_CNT_MEM_OUT( IN INPUT_A VARCHAR(20), INPUT_B VARCHAR(20), OUT CNT_MEM INT )
BEGIN
	SELECT  COUNT(MEM_NO)
	  INTO  CNT_MEM		--OUT 매개변수의 결과값 반환하는 부분
	  FROM  CUSTOMER
	 WHERE  GENDER = INPUT_A
	   AND  ADDR = INPUT_B;
END //
DELIMITER ;
--조건(WHERE,AND)이 PROCEDURE에 전달되면 결과 값 COUNT는 OUT매개변수를 통해 반환
      • 실행
        • CALL (PROCEDURE명)('IN매개변수 값', 'IN매개변수 값','OUT매개변수 값');
SELECT  OUT매개변수 값;

--코드 예
CALL CST_GEN_ADDR_IN_CNT_MEM_OUT('WOMEN', 'INCHEON', @CNT_MEM);
SELECT  @CNT_MEM;
    • IN/OUT 매개변수
      • 생성
        • --SET 부분에서 IN 매개변수(COUNT)인 동시에 +10을 해 새로운 값(SET COUNT)을 반환하는 OUT 매개변수 존재
DELIMITER //
CREATE PROCEDURE IN_OUT_PARAMETER( INOUT COUNT INT)
BEGIN
	SET COUNT = COUNT + 10;
END //
DELIMITER ;
      • 실행
        • SET @counter = 1;
CALL IN_OUT_PARAMETER(@counter);
SELECT  @counter;			--여기서 11출력

데이터 마트

  • 임시 테이블 생성
    • --만들 테이블 앞에 적어줌
CREATE TEMPORARY TABLE (임시 테이블 명) AS

--코드 예
CREATE TEMPORARY TABLE CUSTOMER_PUR_INFO AS
SELECT  A.MEM_NO, A.GENDER, A.BIRTHDAY, A.ADDR, A.JOIN_DATE
		,SUM(B.SALES_QTY * C.PRICE) AS 구매금액
        ,COUNT(B.ORDER_NO) 			AS 구매횟수
        ,SUM(B.SALES_QTY)			AS 구매수량
  FROM  CUSTOMER AS A
  LEFT
  JOIN  SALES AS B
    ON  A.MEM_NO = B.MEM_NO
  LEFT
  JOIN  PRODUCT AS C
    ON  B.PRODUCT_CODE = C.PRODUCT_CODE
 GROUP
    BY  A.MEM_NO, A.GENDER, A.BIRTHDAY, A.ADDR, A.JOIN_DATE;

    • 원하는 데이터 얻기 위해 함수 수정

      • --코드 예
CREATE TEMPORARY TABLE CUSTOMER_AGEBAND AS
SELECT  A.*
		,CASE WHEN 나이 < 10 THEN '10대 미만'
			  WHEN 나이 < 20 THEN '10대'
              WHEN 나이 < 30 THEN '20대'
              WHEN 나이 < 40 THEN '30대'
              WHEN 나이 < 50 THEN '40대'
              ELSE '50대 이상' END AS 연령대         
  FROM  (
		SELECT  *
				,2021-YEAR(BIRTHDAY) + 1 AS 나이
		  FROM  CUSTOMER
		)AS A;
        
        
/*CASE WHEN의 경우 순차적으로 연산, 때문에
CASE WHEN 나이 < 50 THEN '40대'
			  WHEN 나이 < 10 THEN '10대 미만'
              WHEN 나이 < 20 THEN '10대'
              WHEN 나이 < 30 THEN '20대'
              WHEN 나이 < 40 THEN '30대'
              ELSE '50대 이상' END AS 연령대
식으로 작성한다면 원하는 분류 얻지 못한다 */
  • 정합성 체크 
    • 실습의 경우는
      • 회원 수의 중복 파악
      • 요약 및 파생변수의 오류 파악
        • 특정 열들의 data가 변질된 것이 없는지 확인
      • 구매자 비중 오류
        • 구매자와 미구매자 비율이 같은지

정리 

  •  실습 파일 확인
    • SQL 연산자 및 함수
    • View, Procedure

오류

  • 에러 메시지
    • 에러 상황을 극복하는데 도움을 줌
  • stackoverflow 
    • 전세계 개발자들 커뮤니티
  • 공식 문서 참고
  • data type 매칭 오류
    • cast 문법
      • type변경으로 가능
profile
Ik
이전 포스트

[Data] BigData?

0개의 댓글