티스토리 뷰

Others

모두의 SQL 정리중

j0n9m1n1 2019. 9. 10. 17:56

모두의 SQL

Aug 28, 2019

1장

Aug 22, 2019

SQL: 관계형 DB 조작 하기 위함

DML: Manipulation

SELECT: 검색
INSERT: 삽입
UPDATE: 수정
DELETE: 삭제

DDL: Defenition

CREATE
ALTER
DROP
RENAME
TRUNCATE

DCL: Control

GRANT: 권한부여
REVOKE: 권한회수

TCL: Transaction

COMMIT
ROLLBACK
SAVEPOINT

DB 구성요소

table(행,열 구조 저장 단위)
view(테이블에서 데이터를 선택해 만든 가상테이블 )
index(주소)로 구성
entity, relation들의 집합

2장 실습환경

oracle계정생성
11g windows 64bit 설치
sql developer 설치
기본 계정 잠금 해제, 계정 PW변경

3장 SELECT

table 모든 행 조회

SELECT *
FROM employees;

특정 컬럼 조회

SELECT first_name, last_name
FROM employees;

조회결과 정렬 방법

SELECT employee_id, first_name, last_name
FROM employees
ORDER BY employee_id ASC;
--ORDER BY employee_id DESC;

중복 행 제외 출력

SELECT DISTINCT job_id
FROM employees;

열 이름 별명으로 출력

SELECT employee_id AS 사원번호,
             first_name AS 이름,
             last_name AS 성
FROM employees;

AS 생략 가능

데이터 값 붙이기(연결연산자)

SELECT employee_id AS ID,
             first_name || ' ' || last_name AS NAME
FROM employees;

SQL에서 문자, 문자열('')로 구분 함

산술연산자

SELECT employee_id AS ID,
                salary,
                 salary + 500,
                 salary - 500, 
                salary * 5, 
                salary / 5,
                (salary * 0.3) / 2
FROM employees;

Aug 23, 2019

WHERE

FROM 바로 뒤에 온다.
비교, 논리, SQL 연산자, 열 이름, 표현식, 숫자, 문자 가능

논리 순서

SELECT 열 이름   --3
FROM 테이블 이름 --1
WHERE 조건;     --2

id가 100인 직원 정보

SELECT *
FROM employees
WHERE employee_id = 100;

id가 100 보다 큰 직원 정보

SELECT *
FROM employees
WHERE employee_id >= 100;

first_name이 David인 정보

SELECT *
FROM employees
WHERE first_name = 'David';

BETWEEN a AND b(salary 10000~20000)

SELECT *
FROM employees
WHERE salary BETWEEN 10000 and 20000;

COLUMN IN list(10000, 17000, 24000)중에 해당하는 것

조회해야 할 데이터가 여러개 일 경우

SELECT * FROM employees
WHERE salary IN (10000, 17000, 24000);

COLUMN NOT IN list(100, 101, 102)

employee_id가 100, 101, 102가 아닌 row들

SELECT * FROM employees
WHERE employee_id NOT IN (100, 101, 102);

salary = (10000, 17000, 24000); #불가능 IN 사용 해야 함

LIKE

~~와 같다, 명확하지 않을 때, wild card(%, _)와 함께 사용
문자나 숫자 포함
%는 모든 문자('%AD', 'AD%', '%AD%')
%는 ~로 생각 하면 이해가 쉬움
_는 한 글자

SELECT * FROM employees
WHERE job_id LIKE 'AD___'
--ADEDE
--ADPCV

SELECT * FROM employees
WHERE first_name LIKE 'Le%';
--Lex
--Legion

SELECT * FROM employees
WHERE first_name LIKE '%ame%';
--James
--pamet

SELECT * FROM employees
WHERE first_name LIKE '%in';
--doin
--join

IS NULL

NULL: 다른 언어들과 같은 기준임(0, 공백 아닌것처럼)

SELECT * FROM employees
WHERE manager_id IS NULL;
--manager_id가 NULL인 것

논리연산자(똑같음)

or는 성능영향 크기 때문에 주의
<> : 같지않다(ISO 표준임)

SELECT * FROM employees
WHERE employee_id <> 105;
--eployee_id가 105가 아닌 것들

IS NOT NULL

SELECT * 
FROM employees
WHERE manager_id IS NOT NULL;
--manger_id가 NULL이 아닌 것

함수

Oracle Data Type(most)

이 외에 더 있다 BLOB, BFILE ...

단일 행 함수는 100개의 행의 한개의 행 씩 처리되는것

단일 행 함수 종류

단일행함수

문자타입

LOWER, UPPER, INITCAP

SELECT last_name NORMAL,
    LOWER(last_name) LOWER,
    UPPER(last_name) UPPER,
    email MAIL,
    INITCAP(email) INITMAIL 
FROM employees;
--last_name 기본
--last_name 전부소문자
--last_name 전부대문자
--메일의 이니셜만 대문자

SUBSTR

SELECT job_id,
    SUBSTR(job_id, 1, 2)
FROM employees;
--job_id의 첫번째부터 두번째 문자만 출력

REPLACE

SELECT job_id,
REPLACE(job_id, 'ST', 'JM')
FROM employees;
--job_id에 포함된 ST -> JM으로 변경하여 job_id출력

LPAD, RPAD

SELECT job_id,
--RPAD(job_id, 10, '*')
LPAD(job_id, 10, '*')
FROM employees;
--job_id에 기준 왼쪽, 오른쪽부터 문자를 제외한 빈칸 10개를 *로 채움

LTRIM, RTRIM

SELECT job_id,
LTRIM(job_id, 'ST'),
RTRIM (job_id, 'CLERK')
FROM employees;
--왼쪽부터 ST문자열 삭제
--오른쪽부터 CLERK 문자열 삭제

TRIM

SELECT 'start' || TRIM('     -space-      ') || 'end'
FROM dual;
--공백삭제

숫자타입

ROUND

1 2 3 . 4 5 6 7
-3 -2 -1 0 1 2 ...

SELECT salary,
                salary / 30 일급,
                ROUND(salary / 30, 0) i0,
                ROUND(salary / 30, 1) i1,
                ROUND(salary / 30, -1) M1
FROM employees;
--정수로 반올림 한다
--소수점 첫번째 자리까지 반올림
--정수 첫번째자리 반올림

TRUNC

SELECT salary,
                salary / 30 일급,
                TRUNC(salary / 30, 0) i0,
                TRUNC(salary / 30, 1) i1,
                TRUNC(salary / 30, -1) M1
FROM employees;
--반올림이 아닌 해당 자리수 뒤로 버림

날짜타입

날짜에 시간 더할때는 Number/24해서 더해야 함
- 한글판 오라클은 YY/MM/DD가 기본

Aug 28, 2019

MONTHS_BETWEEN

SELECT SYSDATE, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date)
FROM employees
WHERE employee_id = 100;
--employee테이블에 employee_id가 100인 hiredate와 현재 날짜간의 차이 구하기

MONTHS_BETWEEN(큰 날짜, 작은 날짜)

ADD_MONTHS

SELECT hire_date,
            ADD_MONTHS(hire_date, 3),
            ADD_MONTHS(hire_date, -3)
FROM employees
WHERE employee_id BETWEEN 100 AND 106;
--employee_id 100~106의 hire_date, month + 3된 hire_date, month - 3된 hire_date출력

LAST_DAY

SELECT hire_date,
       LAST_DAY(hire_date)
FROM employees
WHERE employee_id BETWEEN 100 AND 106;
--employee_id 100~106사이 hire_date의 월의 마지막 날짜

ROUND, TRUNC

SELECT hire_date,
            ROUND(hire_date, 'MONTH'),
            ROUND(hire_date, 'YEAR'),
            TRUNC(hire_date, 'MONTH'),
            TRUNC(hire_date, 'YEAR')
FROM employees
WHERE employee_id BETWEEN 100 AND 106;
--ROUND MONTH는 일이 절반 이상일때 올려줌
--ROUND YEAR는 월이 절반 이상일때 올려줌
--위와 같지만 TRUNC는 숫자 버림

형 변환

오라클의 경우 자동으로 캐스팅 되는 경우 있음

SELECT 1 + '2'
FROM DUAL;
-- 3

수동 캐스팅

TO_CHAR() : VARCHAR2로 변환
TO_NUMBER() : 문자를 숫자로 변환
TO_DATE() : 날짜 형식의 문자열을 날짜로 변환

날짜 변환 예

SELECT TO_CHAR(SYSDATE, 'YY'),
            TO_CHAR(SYSDATE, 'YYYY'),
            TO_CHAR(SYSDATE, 'MM'),
            TO_CHAR(SYSDATE, 'MON'),
            TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
            TO_CHAR(TO_DATE('20171008'), 'YYYYMMDD')
FROM DUAL;
--TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
--TO_CHAR(TO_DATE('20130412'), 'YY/MM/DD') 슬래쉬도 됨

날짜와 시간 조합

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS PM'),
                TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS PM')
FROM DUAL;

숫자지정형식

SELECT TO_CHAR(salary, '9999999'),
                TO_CHAR(salary, '09999999'),
                TO_CHAR(salary, '$9999999'),
                TO_CHAR(salary, 'L9999999')
FROM employees
WHERE employee_id BETWEEN 100 AND 110;

TO_NUMBER()

SELECT TO_NUMBER('123') + 123
FROM DUAL;
--문자열 NUMBER로 캐스팅 246

TO_DATE()

SELECT TO_DATE('20170302', 'YYYY/MM/DD')
FROM DUAL;

일반 함수

NVL(column, value)

SELECT employee_id, salary * NVL(commission_pct, 1)
FROM employees
ORDER BY commission_pct ASC;
--NVL(NULL 확인 할 COLUMN, NULL일시 바꿀 값)

DECODE()

SELECT first_name,
                last_name,
                department_id,
                salary,
                DECODE(department_id, 60, salary * 1.1, salary),
                DECODE(department_id, 60, '10%인상', '미인상')
FROM employees;
--DECODE(SEX, 'M', '남성', '여성')
--(열, 조건, True, False)
--(열, 조건, True, 조건, True, 조건, False) 가능

CASE END

SELECT employee_id, first_name, last_name, salary, 
    CASE
        WHEN salary >= 9000 THEN 'high grade'
        WHEN salary BETWEEN 6000 AND 8999 THEN 'middle grade'
        ELSE 'low grade'
    END AS salary_grade
FROM employees
WHERE job_id = 'IT_PROG';
--salary가 9000보다 크거나 같으면 high grade
--아니고 salary가 6000 ~ 8999 사이면 middle grade
--아니면 low

RANK() OVER(ORDER BY COLUMN ASC)

SELECT employee_id, RANK() OVER(ORDER BY salary DESC)
--SELECT employee_id, DENSE_RANK() OVER(ORDER BY salary DESC)
--SELECT employee_id, ROW_NUMBER() OVER(ORDER BY salary DESC)
FROM employees;
--RANK()는 중복없이 salary를 내림차순으로 정렬
--DENSE_RANK는 COLUMN 값이 중복있음 (2: 20000, 3: 20000)
--ROW_NUMBER는 (2: 20000, 2:20000, 4: 30000)

ORDER BY PARTITION

SELECT A.employee_id,
                A.department_id,
                B.department_name,
                salary,
                RANK() OVER(PARTITION BY A.department_id ORDER BY salary DESC) RANK_급여,
                DENSE_RANK() OVER(PARTITION BY A.department_id ORDER BY salary DESC) DENSE_RANK_급여,
                ROW_NUMBER() OVER(PARTITION BY A.department_id ORDER BY salary DESC) ROW_NUMBER_급여
FROM employees A, departments B
WHERE A.department_id  = B.department_id
ORDER BY B.department_id, A.salary DESC;
--보류 구문마다 group by 해서 컬럼에 값 넣는다.

그룹 함수

COUNT()

SELECT COUNT(salary) salary_row_count
FROM employees;
-- column갯수

SUM(), AVG()

SELECT SUM(salary) AS 총합,
                AVG(salary) AS 평균
FROM employees;
--총합, 평균

AVG는 NULL 제외, 포함 시 AVG(NVL(salary, 0))

MIN(), MAX()

SELECT  MAX(salary) 최대,
                MIN(salary) 최저,
                MAX(first_name) 최대문자,
                MIN(first_name) 최소문자
FROM employees;
-- 최대, 최저

GROUP BY

열을 기준으로 그룹화

SELECT job_id 직무, SUM(salary) 직무별_총급여, AVG(salary) 직무별_평균급여
FROM employees
WHERE employee_id >= 10
GROUP BY job_id
ORDER BY 직무별_총급여 DESC, 직무별_평균급여;
--직무, 총급여, 평균급여를 조회
--employees 테이블에서
--각 job_id별로 eployee_id가 10보다 작거나 같은 사람
--총급여 내림차순

GROUP에 GROUP

SELECT job_id job_id_대그룹,
                manager_id manager_id_중그룹,
                SUM(salary) 그룹핑_총급여,
                AVG(salary) 그룹핑_평균급여
FROM employees
WHERE employee_id >= 10
GROUP BY job_id, manager_id
ORDER BY 그룹핑_총급여 DESC, 그룹핑_평균급여;
-- job_id, manager_id, 총급여, 평균급여
-- employee_id가 10보다 작거나 같은 사람의 job_id, manager_id를 그룹으로 묶음
-- 총급여를 내림차순으로 정렬

HAVING()

집계항목에는 WHERE를 쓸 수 없어 HAVING을 사용 함

SELECT job_id 직무, SUM(salary) 직무별_총급여, AVG(salary) 직무별_평균급여
FROM employees
WHERE employee_id >= 10
GROUP BY job_id
HAVING SUM(salary) > 30000
ORDER BY 직무별_총급여 DESC, 직무별_평균급여;
-- 직무, 총급여, 평균급여
-- employee_id가 10보다 작거나 같은
-- job_id를 그룹에서
-- 봉급의 총 합이 30000이상인것들
-- 총 급여를 내림차순으로 정렬

개체 관계 표기법

컬럼 앞에 '*'은 NOT NULL, 없다면 NULLABLE

JOIN

테이블과 테이블을 연결

동등 조인

양쪽 테이블에서 조인 조건(데이터 값이 정확히 일치)이 일치하는 행만 가져오는 것

SELECT * FROM employees A, departments B
WHERE A.department_id = B.department_id;
--employees 테이블과 departments 테이블이 department_id를 기준으로 합쳐짐

SELECT A.employee_id, A.department_id, B.department_name, C.location_id, C.city
FROM employees A, departments B, locations C
WHERE A.department_id = B.department_id
AND B.location_id = C.location_id;
--department_id와 location_id를 기준으로
--employee_id, department_id, department_name, location_id, city 조회

외부 조인

동등조건 조건(데이터 값이 정확히 일치)에 만족하지 못해 누락되는 행 출력
(+) 기호 사용
데이터 값이 부족한 테이블에 붙힘 table2.column(+)
양쪽에는 불가능

SELECT A.employee_id, A.first_name, A.last_name, B.department_id, B.department_name
FROM employees A, departments B
WHERE A.department_id = B.department_id(+)
ORDER BY A.employee_id;

SELECT A.employee_id, A.first_name, A.last_name, B.department_id, B.department_name
FROM employees A, departments B
WHERE A.department_id(+) = B.department_id
ORDER BY A.employee_id;

집합연산자

1:1로 Match돼야 하고, 열 개수와 데이터 타입이 일치해야 한다.
위에서 아래로 진행된다.

Untitled

UNION, UNION ALL, INTERSECT

SELECT department_id
FROM employees
UNION
--UNION ALL
--INTERSECT
SELECT department_id
FROM departments
ORDER BY department_id;
--UNION: employees, departments테이블의 department_id를 중복제외 모두 조회
--UNION ALL: 중복포함 모두 조회
--INTERSECT: 교집합만 조회
--

SELECT department_id
FROM departments
MINUS
SELECT department_id
FROM employees
ORDER BY department_id;
-- employees department_id
-- 첫 번째 SELECT에서 두 번째 SELECT를 제외한 나머지를 검색
-- departments - employees

서브쿼리(nested query)

서브쿼리 연산자

단일 행

--salary = '17000'
SELECT *
FROM employees A

WHERE A.salary = (SELECT salary
                                FROM employees
                                WHERE first_name = 'Neena'
                                );
-- 가장 안쪽 SELECT 실행
-- employees에 first_name이 'Neena'의 salary조회
-- 바깥 WHERE salary = Neena's salary 17000
-- employees 테이블에 salary가 17000인 행 모두 조회

다중 열

SELECT *
FROM employees A
WHERE A.salary IN(
                                SELECT MIN(salary) loewest
                                FROM employees
                                GROUP BY department_id
                                )
ORDER BY A.salary DESC;
-- employees에 department_id를 그룹핑, 그룹별로 가장 낮은 salary 반환
-- employees에 반환된 값들과 일치하는 값들 출력
-- 내림차순 정렬

SELECT *
FROM employees A,(
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
) B
WHERE A.department_id = B.department_id;
-- employees table A,  (departments table에서 dep_name이 'IT'인 dep_id) table B
-- A와 B join

DML

INSERT

varchar 문자열은 'data' 처럼 삽입

INSERT INTO departments(department_id, department_name, manager_id, locatioon_id)
VALUES (271, 'test_dept', 200, 1700);
--departments table에 (dep_id, dep_name, manager_id, location_id) 컬럼에
--271, 'test_dept', 200, 1700 값 insert

INSERT INTO departments
VALUES (271, 'test_dept', 200, 1700);
--departments의 column 순서대로 values의 값이 insert됨

Aug 30, 2019

UPDATE

sample_dept행의 manager_id: 201, location_id: 1800 업데이트
commit 해야 적용
sub query가능

commit 하기 전이라면 ROLLBACK으로 되돌리면 됨

UPDATE departments
SET manager_id = 201,
        location_id = 1800
WHERE department_name = 'Sample_dept';
-- departments table에 dep_name이 'Sample_dept'인 행 manager_id, location_id 컬럼을
-- 201, 1800으로 업데이트

--sub query
UPDATE departments
SET(manager_id, location_id) = (SELECT manager_id, location_id
                                                    FROM departments
                                                    WHERE department_id = 40)
WHERE department_name = 'Sample_dept';
--department_name이 Sample_dept의 manager_id, location_id를 
--departments table에 department_id가 40에 해당하는 manager_id, location_id로 업데이트

DELETE

DELETE FROM departments
WHERE department_name = 'Sample_dept';
-- departments table에 dep_name이 'Sample_dept'인 row 삭제

DELETE FROM departments
WHERE department_id = (SELECT department_id
                       FROM departments
                       WHERE department_name = 'Sample_dept');
-- departments table에 dep_name이 'Sample_dept'인 department_id 반환
-- departments table에서 반환 된 dep_id와 같은 row 삭제

Transaction

동시성 제어 기법

DDL

DDL은 commit없이 됨

CREATE TABLE sample_product(
```
                                                      product_id number,
                                                      product_name varchar2(30),
                                                      manu_date date);
```
--product_id, name, manu_data 컬럼을 갖는 sample_product table 생성

INSERT INTO sample_product VALUES (1, 'television', to_date('140101', 'YYMMDD'));
INSERT INTO sample_product VALUES (2, 'washer', to_date('150101', 'YYMMDD'));
INSERT INTO sample_product VALUES (3, 'caleaner', to_date('160101', 'YYMMDD'));
--sample_product table에 1, 'television', to_data('140101', 'YYMMDD') 값을 insert

ALTER

ALTER TABLE sample_product
    ADD(factory varchar2(10));
-- sample_product table에 varchar2 type의 factory column을 추가

ALTER TABLE sample_product
    MODIFY(factory char(10));
-- sample_product table에 factory column을 varchar2 -> char type으로 변경

ALTER RENAME

ALTER TABLE sample_product RENAME COLUMN factory to factory_name;
-- sample_product table의 factory column명을 factory_name으로 변경

ALTER DELETE

ALTER TABLE sample_product DROP COLUMN factory_name;
-- sample_product에 factory_name column을 삭제

TRUNCATE

TRUNCATE TABLE sample_product;
--테이블 column은 남기고 데이터만 삭제

DROP

DROP TABLE sample_product;
-- sample_product table 삭제

VIEW

사용자별 접근권한
JOIN가능(table + table // table + view)
좋아보임

SELECT A.employee_id, A.hire_date, B.department_name, B.job_title
FROM employees A, emp_details_view B
WHERE A.employee_id = B.employee_id
AND A.employee_id = 100;

성능최적화

RBO, CBO: Rule, Cost Based Optimizer

compare

실전예제

매출분석

SELECT COUNT(*),
          SUM(B.sales),
        AVG(B.sales),
        MAX(B.sales),
        MIN(B.sales)
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no;
-- reservation, order_info JOIN후
-- 총 row 갯수
-- order_info table의 sales 총합, 평균, 최대값, 최소값 반환

특정 상품 조회

SELECT COUNT(*),
                SUM(B.sales),
                SUM(DECODE(B.item_id, 'M0001', 1, 0)),
                SUM(DECODE(B.item_id, 'M0001', B.sales, 0))
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND A.cancel = 'N';
-- reservation, order_info table reserv_no로 JOIN
-- 총 row 갯수
-- 총 상품 매출
-- item_id가 'M0001'이면 1 아니면 0, 'M0001'이 팔린 갯수 총 합
-- item_id가 'M0001'이면 판매금액, 아니면 0, 'M0001'이 팔린 금액의 총 합
-- reservation에 주문취소 여부가 'N'인 것들만

상품별 조회

SELECT C.item_id,   
                C.product_name,
                SUM(B.sales)
FROM reservation A, order_info B, item C
WHERE A.reserv_no = B.reserv_no
AND B.item_id = C.item_id
AND A.cancel = 'N'
GROUP BY C.item_id, C.product_name
ORDER BY SUM(B.sales) DESC;
-- reservation, order_info를 reserv_no로 JOIN
-- order_info, item을 item_id로 JOIN
-- order_info에 cancel이 'N'인것
-- item_id와 product_name을 그룹핑 함
-- 정렬순서는 팔린 금액의 총합 내림차순
-- item_id, product_name, 팔린금액 총합을 반환

상품별 월 매출 조회

SELECT SUBSTR(A.reserv_date, 1, 6),
                SUM(DECODE(B.item_id, 'M0001', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0002', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0003', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0004', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0005', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0006', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0007', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0008', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0009', B.sales, 0)),
                SUM(DECODE(B.item_id, 'M0010', B.sales, 0))
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND A.cancel = 'N'
GROUP BY SUBSTR(A.reserv_date, 1, 6)
ORDER BY SUBSTR(A.reserv_date, 1, 6);
-- reservation, order_info table을 cancel이 'N'인것만 reserv_no로 JOIN
-- substr한 reserv_date를 그룹으로 묶음(월별로)
-- SUBSTR으로 문자열 잘라냄 1~6 '201706' 년월까지
-- 각 메뉴의 판매금액 총액 조회
-- reservation, order_info table에서
-- decode로 item_id 컬럼이 M000N에 해당하면 각 상품별 판매금액을 모두 더함

월 별 매출 조회

SELECT SUBSTR(A.reserv_date, 1, 6),
                SUM(B.sales),
                SUM(DECODE(B.item_id, 'M0001', B.sales, 0))
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND A.cancel = 'N'
GROUP BY SUBSTR(A.reserv_date, 1, 6)
ORDER BY SUBSTR(A.reserv_date, 1, 6);
-- 위와 큰 차이 없음 
-- SUBSTR로 년월까지, 매출, M0001의 매출
-- reservation과 order_info를 reserv_no로 JOIN
-- reservation cancel column이 'N'인 것만
-- 각 월을 그룹으로 묶음
-- date 올림차순 정렬

데이터 가공

SELECT SUBSTR(A.reserv_date, 1, 6) 매출월,
                SUM(B.sales) 총매출,
                SUM(B.sales) - 
                SUM (DECODE(B.item_id, 'M0001', B.sales, 0)) 전용상품외매출,
                SUM(DECODE(B.item_id, 'M0001', B.sales, 0)) 전용상품매출,
                ROUND(SUM(DECODE(B.item_id, 'M0001', B.sales, 0)) / SUM(B.sales)*100, 1)||'%' 전용상품판매율,
                COUNT(A.reserv_no) 총예약건,
                SUM(DECODE(A.cancel, 'N', 1, 0)) 예약완료건,
                SUM(DECODE(A.cancel, 'Y', 1, 0)) 예약취소건,
                ROUND(SUM(DECODE(A.cancel, 'Y', 1, 0)) / COUNT(A.reserv_no) * 100, 1) || '%' 예약취소율
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no(+)
-- AND A.cancel = 'N'
GROUP BY SUBSTR(A.reserv_date, 1, 6)
ORDER BY SUBSTR(A.reserv_date, 1, 6);
-- SUM으로 총 매출
-- SUM으로 총 매출 - DECODE 전용상품 일때만 판매금액을 더함 / 총매출과 해당 메뉴를 빼서 
-- 전용상품외 매출을 구함
-- DECODE 전용상품 일때만 판매금액을 더하여 전용상품 매출
-- DECODE로 전용상품 매출 구함 그 후 총 매출로 나눈 후 * 100 후에 소수점 두번째자리까지 반올림
-- COUNT로 모든 예약을 구함
-- DECODE로 cancel이 N이면 1을 반환하여 SUM으로 예약 완료건을 모두 더함
-- DECODE로 cancel이 Y면 1을 반환하여 SUM으로 예약 취소건을 모두 더함
-- SUM, DECODE로 cancel이 Y인 취소건을 구하고 총 예약 건수로 나눈 후 * 100 후에 소수점
-- 두번째자리까지 반올림
-- reservation과 order_info 테이블의 reserv_no로 JOIN함
-- 각 월을 그룹으로 묶고 date 오름차순으로 정렬

요일별 특별 메뉴 판매총액

SELECT SUBSTR(reserv_date, 1, 6) 날짜,
                A.product_name 상품명,
                SUM(DECODE(A.WEEK, '1', A.sales, 0)) SUNDAY,
                SUM(DECODE(A.WEEK, '2', A.sales, 0)) MONDAY,
                SUM(DECODE(A.WEEK, '3', A.sales, 0)) TUESDAY,
                SUM(DECODE(A.WEEK, '4', A.sales, 0)) WEDNESDAY,
                SUM(DECODE(A.WEEK, '5', A.sales, 0)) THURSDAY,
                SUM(DECODE(A.WEEK, '6', A.sales, 0)) FRIDAY,
                SUM(DECODE(A.WEEK, '7', A.sales, 0)) SATURDAY
FROM(
            SELECT A.reserv_date,
                         C.product_name,
                        TO_CHAR(TO_DATE(A.reserv_date, 'YYYYMMDD'), 'd') WEEK,
                        B.sales
            FROM reservation A, order_info B, item C
            WHERE A.reserv_no = B.reserv_no
            AND B.item_id = C.item_id
            AND B.item_id = 'M0001'

) A
GROUP BY SUBSTR(reserv_date, 1, 6), A.product_name
ORDER BY SUBSTR(reserv_date, 1, 6);
-- SUBSTR로 년월까지만 조회
-- reservation의 product_name 조회
-- DECODE로 주의 첫번째요일(1 ~ 7, 월 ~ 일) 일때의 판매금액을 SUM으로 요일별 판매금액 조회
-- reservation의 reserv_date, item의 product_name, reservation의 reser_date를 
-- 'YYYYMMDD' 형식으로 date조회 후 1~7사이 요일로 반환, 
-- 판매총액 조회
-- reservation의 item_id와 order_info의 item_id가 같고
-- order_info의 item_id가 'M0001'에 해당하는
-- reservation, order_info의 reserv_no로 JOIN함
-- 각 월과 상품이름을 그룹으로 묶음
-- 월을 기준으로 올림차순 정렬

지점별 전용상품 매출순위

SELECT *
FROM(
            SELECT SUBSTR(A.reserv_date, 1, 6) 매출월,
                A.branch 지점,
                SUM(B.sales) 전용상품매출,
                RANK() OVER(PARTITION BY SUBSTR(A.reserv_date, 1, 6)
            ORDER BY SUM(B.sales) DESC) 지점순위
            FROM reservation A, order_info B
            WHERE A.reserv_no = B.reserv_no
            AND A.cancel = 'N'
            AND B.item_id = 'M0001'
            GROUP BY SUBSTR(A.reserv_date, 1, 6), A.branch
            ORDER BY SUBSTR(A.reserv_date, 1, 6)
        ) A

        WHERE A.지점순위 <= 3;
-- 인라인뷰의 모든 컬럼 조회
-- SUBSTR로 reserv_date의 년월만 추출 매출월
-- reservation의 branch 지점
-- SUM(B
-- 같은 순위가 있을 시 같은 등급

지점

SELECT *
    FROM(
        SELECT SUBSTR(A.reserv_date, 1, 6) 매출월,
        A.branch 지점,
        SUM(B.sales) 전용상품매출,
        ROW_NUMBER() OVER(PARTITION BY SUBSTR(A.reserv_date, 1, 6)
            ORDER BY SUM(B.sales) DESC) 지점순위,
            DECODE(A.branch, '강남', 'A', '종로', 'A', '영등포', 'A', 'B') 지점등급
        FROM reservation A, order_info B
        WHERE A.reserv_no = B.reserv_no
        AND A.cancel = 'N'
        AND B.item_id = 'M0001'
        GROUP BY SUBSTR(A.reserv_date, 1, 6), A.branch,
            DECODE(A.branch, '강남', 'A', '종로', 'A', '영등포', 'A', 'B')
        ORDER BY SUBSTR(A.reserv_date,1 ,6)
    ) A
    WHERE A.지점순위 = 1;

SELECT A.매출월 매출월,
                MAX(총매출) 총매출,
                MAX(전용상품외매출) 전용상품외매출,
                MAX(전용상품매출) 전용상품매출,
                MAX(전용상품판매율) 전용상품판매율,
                MAX(총예약건) 총예약건,
                MAX(예약완료건) 예약완료건,
                MAX(예약취소건) 예약취소건,
                MAX(예약취소율) 예약취소율,
                MAX(최대매출지점) 최대매출지점,
                MAX(지점매출액) 지점매출액

FROM(
    SELECT SUBSTR(A.reserv_date, 1, 6) 매출월,
        SUM(B.sales) 총매출,
        SUM(B.sales) 
        - SUM(DECODE(B.item_id, 'M0001', B.sales, 0)) 전용상품외매출,
        SUM(DECODE(B.item_id, 'M0001', B.sales, 0)) 전용상품매출,
        ROUND(SUM(DECODE(B.item_id, 'M0001', B.sales, 0)) / SUM(B.sales) * 100, 1) || '%' 전용상품판매율,
        COUNT(A.reserv_no) 총예약건,
        SUM(DECODE(A.cancel, 'N', 1, 0)) 예약완료건,
        SUM(DECODE(A.cancel, 'Y', 1, 0)) 예약취소건,
        ROUND(SUM(DECODE(A.cancel, 'Y', 1, 0)) / COUNT(A.reserv_no) * 100, 1) || '%' 예약취소율,
        ' ' 최대매출지점,
        0 지점매출액
    FROM reservation A, order_info B
    WHERE A.reserv_no = B.reserv_no(+)
    GROUP BY SUBSTR(A.reserv_date, 1, 6), ' ', 0
UNION
    SELECT A.매출월,
    0   총매출,
    0   전용상품외매출,
    0   전용상품매출,
    ' '   전용상품판매율,
    0   총예약건,
    0   예약완료건,
    0   예약취소건,
    ' ' 예약취소율,
    A.지점 최대매출지점,
    A.전용상품매출 지점매출액
FROM(
    SELECT SUBSTR(A.reserv_date, 1, 6) 매출월,
    A.branch 지점,
    SUM(B.sales)    전용상품매출,
    ROW_NUMBER*( OVER*PARTITION BY SUBSTR(A.reserv_date, 1, 6)
ORDER BY SUM(B.sales) DESC) 지점순위,,
    DECODE(A.branch, '강남', 'A', '종로', 'A', '영등포', 'A', 'B') 지점등급
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND A.cancel = 'N'
AND B.item_id = 'M0001'
GROUP BY SUBSTR(A.reserv_date, 1, 6), A.branch,
    DECODE(A.branch, '강남', 'A', '종로', 'A', '영등포', 'A', 'B')
    ORDER BY SUBSTR(A.reserv_date, 1, 6)
) A
WHERE A.지점순위 = 1

)A
GROUP BY A.매출월
ORDER BY A.매출월;

Sep 02, 2019

인구특징비교

SELECT COUNT(customer_id) 고객수,
                SUM(DECODE(sex_code, 'M', 1, 0)) 남자,
                SUM(DECODE(sex_code, 'F', 1, 0)) 여자,
                ROUND(AVG(MONTHS_BETWEEN(TO_DATE('20171231', 'YYMMDD'), TO_DATE(birth, 'YYMMDD')) / 12), 1) 평균나이,
                ROUND(AVG(MONTHS_BETWEEN(TO_DATE('20171231', 'YYMMDD'), first_reg_date)), 1) 평균거래기간
FROM customer;

개인별매출비교

SELECT A.customer_id 고객아이디,
                A.customer_name 고객이름,
                COUNT(C.order_no) 전체상품주문건수,
                SUM(C.sales) 총매출,
                SUM(DECODE(C.item_id, 'M0001', 1, 0)) 전용상품주문건수,
                SUM(DECODE(C.item_id, 'M0001', C.sales, 0)) 전용상품매출
FROM customer A, reservation B, order_info C
WHERE A.customer_id= B.customer_id
AND B.reserv_no = C.reserv_no
AND B.cancel = 'N'
GROUP BY A.customer_id, A.customer_name
ORDER BY SUM(DECODE(C.item_id, 'M0001', C.sales, 0)) DESC;

거주지와 직업 비율

SELECT B.address_detail 주소, B.zip_code, COUNT(B.address_detail) 카운팅
FROM(
    SELECT DISTINCT A.customer_id, A.zip_code
    FROM customer A, reservation B, order_info C
    WHERE A.customer_id = B.customer_id
    AND B.reserv_no = C.reserv_no
    AND B.cancel = 'N'
    --AND C.item_id = 'M0001'
)A, address B
WHERE A.zip_code = B.zip_code
GROUP BY B.address_detail, B.zip_code
ORDER BY COUNT(B.address_detail) DESC;

SELECT NVL(B.job, '정보없음') 직업, COUNT(NVL(B.job, 1)) 카운팅
FROM(
    SELECT DISTINCT A.customer_id, A.zip_code
    FROM customer A, reservation B, order_info C
    WHERE A.customer_id = B.customer_id
    AND B.reserv_no = C.reserv_no
    AND B.cancel = 'N'
    --AND C.item_id = 'M0001'
)A, customer B
WHERE A.customer_id = B.customer_id
GROUP BY NVL(B.job, '정보없음')
ORDER BY COUNT(NVL(B.job, 1)) DESC;

상위 10명 고객

SELECT *
FROM(
    SELECT A.customer_id,
    A.customer_name,
    SUM(C.sales) 전용상품매출,
    ROW_NUMBER() OVER(PARTITION BY C.item_id ORDER BY SUM(C.sales) DESC) 순위
    FROM customer A, reservation B, order_info C
    WHERE A.customer_id = B.customer_id
    AND B.reserv_no = C.reserv_no
    AND B.cancel = 'N'
    AND C.item_id = 'M0001'
    GROUP BY A.customer_id, C.item_id, A.customer_name
)A
WHERE A.순위 <= 10
ORDER BY A.순위;

SELECT A.주소, COUNT(A.주소) 카운팅
FROM(
    SELECT A.customer_id 고객아이디,
                    A.customer_name 고객이름,
                    NVL(A.job, '정보없음') 직업,
                    D.address_detail 주소,
                    SUM(C.sales) 전용상품매출,
                    RANK() OVER(PARTITION BY C.item_id ORDER BY SUM(C.sales) DESC) 순위
    FROM customer A, reservation B, order_info C, address D
    WHERE A.customer_id = B.customer_id
    AND B.reserv_no = C.reserv_no
    AND A.zip_code = D.zip_code
    AND B.cancel = 'N'
    AND C.item_id = 'M0001'
    GROUP BY A.customer_id, C.item_id, A.customer_name, NVL(A.job, '정보없음'), D.address_detail
) A

WHERE A.순위 <= 10
GROUP BY A.주소
ORDER BY  COUNT(A.주소) DESC;

SELECT A.직업, COUNT(A.직업) 카운팅
FROM(
    SELECT A.customer_id 고객아이디,
    A.customer_name 고객이름,
    NVL(A.job, '정보없음') 직업,
    D.address_detail 주소,
    SUM(C.sales) 전용상품매출,
    RANK() OVER(PARTITION BY C.item_id ORDER BY SUM(C.sales) DESC) 순위

    FROM customer A, reservation B, order_info C, address D
    WHERE A.customer_id = B.customer_id
    AND B.reserv_no = C.reserv_no
    AND A.zip_code = D.zip_code
    AND B.cancel = 'N'
    AND C.item_id = 'M0001'
    GROUP BY A.customer_id, C.item_id, A.customer_name, NVL(A.job, '정보없음'),
    D.address_detail
) A
WHERE A.순위 <= 10
GROUP BY A.직업
ORDER BY COUNT(A.직업) DESC;

SELECT * 
FROM(
    SELECT A.고객아이디,
                    A.고객이름,
                    D.product_name 상품명,
                    SUM(C.sales) 상품매출,
                    RANK() OVER(PARTITION BY A.고객아이디 ORDER BY SUM (C.sales) DESC) 선호도순위
    FROM(
        SELECT A.customer_id 고객아이디, A.customer_name 고객이름, SUM(C.sales) 전용상품매출
        FROM customer A, reservation B, order_info C
        WHERE A.customer_id = B.customer_id
        AND B.reserv_no = C. reserv_no
        AND B.cancel = 'N'
        AND C.item_id = 'M0001'
        GROUP BY A.customer_id, A.customer_name
        HAVING SUM(C.sales) >= 216000
    )A, reservation B, order_info C, item D
    WHERE A.고객아이디 = B.customer_id
    AND B.reserv_no = C.reserv_no
    AND C.item_id = D.item_id
    AND D.item_id <> 'M0001'
    AND B.cancel = 'N'
    GROUP BY A.고객아이디, A.고객이름, D.product_name
) A
WHERE A.선호도순위 = 1;

햇갈리는 것들

RANK() OVER(PARTITION BY )
GROUP BY
HAVING
PK, UK, FK, PF
join

https://www.notion.so/j0n9m1n1/SQL-a39cf81ebec34d22a931f0ed14fcb31a

'Others' 카테고리의 다른 글

MSSQL 암호화 백업, 복구 (0)	2023.11.09
aws ec2 사용기(vscode remote, nginx+fastapi 서버, maria) (0)	2023.10.15
크롬 익스텐션 개발 테스트(마나토끼 히스토리) (0)	2023.10.14
도미노피자 배달 시스템... (0)	2021.09.18
한빛미디어 기초 선형대수학 연습문제 답 솔루션 (0)	2017.10.17

공유하기 링크

페이스북
카카오스토리
트위터

티스토리 방명록

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday