SQL 4. SQL 활용_총정리

<SQL 활용 총정리>

연산자는 비교, 논리, 특수, 산술, 집합 연산

UNION : 2개 이상 테이블의 중복된 행들을 제거 하여 집합

UNION ALL : 2개 이상 테이블의 중복된 행들을 제거 없이 집합

 

<함수>

단일 : 숫사형, 문자형, 날짜형, 형 변환, 일반

복수 : 집계형, 그룹형

윈도우 함수 : 순위, 집계(누적)

 

단일 행 함수 : 모든 행에 대하여 각각 함수 적용되어 반환

복수 행 함수 : 여러 행들이 하나의 결과값으로 반환

 

윈도우 함수 : 행과 행간의 관계를 정의하여 결과 값을 변환

 

> 윈도우 함수 기본 문법(ORDER BY)

함수를 맨 앞에 정의 > OVER(ODERY BY 열 ASC OR DESC)

 

> 윈도우 함수 기본 문법(ORDER BY) + PARTITION BY(그룹화)

OVER(PARTITION BY 열 ODER BY 열 ASC OR DESC)

 

VIEW

하나 이상의 테이블들을 활용하여 사용자가 정의한 가상 테이블

JOIN 사용을 최소화 > 편의성

가상 테이블 > 중복되는 열이 저장될 수 없다.

 

PRECEDURE

IN : 매개 변수를 프로시저로 전달

OUT : 프로시저 결과값 반환

INOUT : 매개 변수를 프로시저로 전달, 프로시저 결과값 반환

 

데이터 마트

분석에 필요한 데이터를 가공한 분석용 데이터

요약 변수 : 수집된 데이터를 분석에 맞게 종합한 변수(기간별 구매 금액, 횟수, 수량 등)

파생 변수 : 사용자가 특정 조건 또는 함수로 의미를 부여한 변수(연령대, 선호 카테고리 등)

데이터 정합성 : 데이터가 서로 모순 없이 일관되게 일치함을 나타낼 때 사용

 

<데이터 정합성>

1. 데이터 마트의 회원 수는 중복이 없는가?

2. 데이터 마트의 요약 및 파생변수의 오류는 없는가?

3. 데이터 마트의 구매자 비중(%)의 오류는 없는가?

 

 

USE PRACTICE;

/******************************SQL 활용 단원 정리*******************************/
/***************연산자 및 함수***************/

/* 1. CUSTOMER 테이블을 활용하여, 가입일자가 2019년이며 생일이 4~6월생인 회원수를 조회하시오.*/
SELECT * FROM CUSTOMER;
SELECT COUNT(MEM_NO)
FROM CUSTOMER
WHERE YEAR(JOIN_DATE) = 2019 AND MONTH(BIRTHDAY) between 4 AND 6;

/* 2. SALES 및 PRODUCT 테이블을 활용하여, 1회 주문시 평균 구매금액를 구하시오.(비회원 9999999 제외)*/
SELECT * FROM PRODUCT;
/* 1회 주문시 구매금액 */
SELECT  AVG(A.SALES_QTY * B.PRICE) AS 평균_구매금액,
        SUM(A.SALES_QTY * B.PRICE) / COUNT(A.ORDER_NO) AS 1회_주문시_구매금액
FROM  SALES AS A
LEFT JOIN PRODUCT AS B
ON  A.PRODUCT_CODE = B.PRODUCT_CODE
WHERE  A.MEM_NO <> '9999999';



/* 3. SALES 테이블을 활용하여, 구매수량이 높은 상위 10명을 조회하시오.(비회원 9999999 제외)*/
SELECT * FROM SALES;

SELECT MEM_NO,
SUM(SALES_QTY) AS 구매수량,
        ROW_NUMBER() OVER (ORDER BY SUM(SALES_QTY)DESC) AS 고유한_순위_반환,
        RANK()   OVER(ORDER BY SUM(SALES_QTY)DESC) AS 동일한_순위_변환_띄어넘기,
        DENSE_RANK() OVER(ORDER BY SUM(SALES_QTY)DESC) AS 동일한_순위_변환_순서대로
FROM SALES
WHERE MEM_NO <> '9999999'
GROUP BY MEM_NO;

/* 회원별 구매수량 순위 + 상위 10위 이하 필터링 */  
SELECT * 
FROM(
SELECT MEM_NO,
SUM(SALES_QTY) AS 구매수량,
ROW_NUMBER() OVER (ORDER BY SUM(SALES_QTY)DESC) AS 순위
FROM SALES
WHERE MEM_NO <> '9999999'
GROUP BY MEM_NO
    ) AS A
WHERE 순위 <= 10;


/***************View 및 Procedure***************/
/* 1. View를 활용하여, Sales 테이블 기준으로 CUSTOMER 및 PRODUCT 테이블을 LEFT JOIN 결합한 가상 테이블을 생성하시오.*/
/* 열은 SALES 테이블의 모든 열 + CUSTOMER 테이블의 GENDER + PRODUCT 테이블의 BRAND*/
CREATE VIEW SALES_GENDER_BRAND AS
SELECT A.*, B.GENDER, C.BRAND
FROM SALES AS A
LEFT JOIN CUSTOMER AS B
ON A.MEM_NO = B.MEM_NO
LEFT JOIN PRODUCT AS C
ON A.PRODUCT_CODE = C.PRODUCT_CODE;

/* 확인 */
SELECT * FROM SALES_GENDER_BRAND;

/* 2. Procedure를 활용하여, CUSTOMER의 몇월부터 몇월까지의 생일인 회원을 조회하는 작업을 저장하시오.*/
DELIMITER //
CREATE PROCEDURE CST_BIRTH_MONTH_IN( IN INPUT_A INT, INPUT_B INT )
BEGIN
SELECT *
    FROM CUSTOMER
    WHERE MONTH(BIRTHDAY) BETWEEN INPUT_A AND INPUT_b;
END// 
DELIMITER ;


/* 확인 */
CALL CST_BIRTH_MONTH_IN(4,6);

/***************데이터 마트***************/
/* 1. SALES 및 PRODUCT 테이블을 활용하여, SALES 테이블 기준으로 PRODUCT 테이블을 LEFT JOIN 결합한 테이블을 생성하시오.*/
/* 열은 SALES 테이블의 모든 열 + PRODUCT 테이블의 CATEGORY, TYPE + SALES_QTY * PRICE 구매금액 */
CREATE TABLE SALES_MART
SELECT A.*, B.CATEGORY, B.TYPE, A.SALES_QTY*B.PRICE AS 구매금액
FROM SALES AS A
LEFT JOIN PRODUCT AS B
ON A.PRODUCT_CODE = B.PRODUCT_CODE;
    
/* 확인 */
SELECT * FROM SALES_MART;

/* 2. (1)에서 생성한 데이터 마트를 활용하여, CATEGORY 및 TYPE별 구매금액 합계를 구하시오*/
SELECT CATEGORY, TYPE, SUM(구매금액) as 구매금액_합계
from sales_mart
group by category, type;