1. EXPORT

• 테이블의 데이터를 다른 형태 데이터로 추출하는 작업

1.1 문법

• COPY 테이블명(칼럼1, 칼럼2,..) TO '경로' DELIMITER ',' CSV HEADER
  • COPY : 추출할 테이블과 컬럼들을 지정
  • TO : 추출할 경로를 지정(폴더가 미리 존재해야함)
  • DELIMITER : CSV 파일의 구분자 지정
  • CSV HEADER : HEADER 가 있으면 헤더 추가됨

1.2 예제

• CATEGORY 테이블을 DB_CATEGORY.csv 파일로 출력

COPY CATEGORY(CATEGORY_ID, NAME, LAST_UPDATE)
TO 'D:\DB_CATEGORY.csv'
DELIMITER ','
CSV HEADER
;

2. IMPORT

• 다른 데이터를 테이블에 넣는 작업

2.1 문법

• COPY 테이블명(칼럼1, 칼럼2,..) FROM '경로' DELIMITER ',' CSV HEADER
  • COPY : 적재할 테이블과 컬럼들을 지정
  • FROM : 적재할 파일 경로를 지정(폴더가 미리 존재해야함)
  • DELIMITER : CSV 파일의 구분자 지정
  • CSV HEADER : HEADER 가 있으면 첫행을 헤더로 인식

2.2 예제

• 데이터 적재를 할 테이블 생성

CREATE TABLE CATEGORY_IMPORT
(
CATEGORY_ID SERIAL NOT NULL
, "NAME" VARCHAR(25) NOT NULL
, LAST_UPDATE TIMESTAMP NOT NULL DEFAULT NOW()
, CONSTRAINT CATEGORY_IMPORT_PKEY PRIMARY KEY (CATEGORY_ID)
);

• 데이터 적재

COPY CATEGORY_IMPORT(CATEGORY_ID, "NAME", LAST_UPDATE)
FROM 'D:\DB_CATEGORY.csv'
DELIMITER ','
CSV HEADER
;
SELECT * FROM CATEGORY_IMPORT;

참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. INSERT문

• 테이블 안에 데이터를 삽입하는 명령어

1.1 문법

• INSERT INTO 테이블명 VALUES(v1,v2,...)
• INSERT INTO 테이블명(칼럼명1, 칼럼명2,..) VALUES(v1, v2,...)

1.2 예제

• 실습용 테이블 생성

CREATE TABLE LINK (
ID SERIAL PRIMARY KEY
, URL VARCHAR (255) NOT NULL
, NAME VARCHAR (255) NOT NULL
, DESCRIPTION VARCHAR (255)
, REL VARCHAR (50)
);

SELECT * FROM LINK;

• 1개의 데이터 행 추가

INSERT INTO LINK (URL, NAME)
VALUES ('http://naver.com','Naver');
COMMIT;

SELECT * FROM LINK;

• 작은따옴표를 데이터로 입력

INSERT INTO LINK(URL, NAME)
VALUES ('''http://naver.com''', '''Naver''');
COMMIT;

SELECT * FROM LINK;

• 여러 개의 데이터 행 추가

INSERT INTO LINK(URL, NAME)
VALUES ('http://www.google.com','Google')
     , ('http://www.yahoo.com' ,'Yahoo')
     , ('http://www.bing.com'  ,'Bing')
;
COMMIT;

SELECT * FROM LINK;

• 다른 테이블을 이용해 스키마만 있는 빈 껍데기 테이블 생성

CREATE TABLE LINK_TMP AS
SELECT * FROM LINK WHERE 0=1;
COMMIT

SELECT * FROM LINK_TMP

• 다른 테이블로부터 SELECT 하여 데이터 삽입

INSERT INTO LINK_TMP
SELECT * FROM LINK;
COMMIT;

SELECT * FROM LINK_TMP

2. UPDATE문

• 테이블 안에 데이터를 편집할 때 사용하는 명령어

2.1 문법

• UPDATE 테이블명 SET 컬럼명 = 값 WHERE 조건

2.2 예제

• name 이 'Naver'인 데이터의 description을 name 컬럼값으로 수정

UPDATE LINK SET DESCRIPTION = NAME
WHERE NAME = 'Naver'
COMMIT;

SELECT * FROM LINK

3. UPDATE JOIN 문

• 다른 테이블의 값을 참조하여 한 테이블의 값을 편집할 때 사용하는 명령어

3.1 문법

• UPDATE 테이블1 A SET A.컬럼 = 값 FROM 테이블2 B WHERE A.컬럼 = B.컬럼

3.2 예제

• 실습용 테이블 생성

-- PRODUCT_SEGMENT 테이블 생성 및 삽입
CREATE TABLE PRODUCT_SEGMENT
(
ID SERIAL PRIMARY KEY
, SEGMENT VARCHAR NOT NULL
, DISCOUNT NUMERIC (4, 2)
);

INSERT INTO PRODUCT_SEGMENT (SEGMENT, DISCOUNT)
VALUES
('Grand Luxury', 0.05)
, ('Luxury', 0.06)
, ('Mass', 0.1);
COMMIT;

-- PRODUCT 테이블 생성 및 삽입
CREATE TABLE PRODUCT(
ID SERIAL PRIMARY KEY
, NAME VARCHAR NOT NULL
, PRICE NUMERIC(10, 2)
, NET_PRICE NUMERIC(10, 2)
, SEGMENT_ID INT NOT NULL
, FOREIGN KEY(SEGMENT_ID)
REFERENCES PRODUCT_SEGMENT(ID)
);
NSERT INTO PRODUCT (NAME, PRICE, SEGMENT_ID)
VALUES
('K5', 804.89, 1)
, ('K7', 228.55, 3)
, ('K9', 366.45, 2)
, ('SONATA', 145.33, 3)
, ('SPARK', 551.77, 2)
, ('AVANTE', 261.58, 3)
, ('LOZTE', 519.62, 2)
, ('SANTAFE', 843.31, 1)
, ('TUSON', 254.18, 3)
, ('TRAX', 427.78, 2)
, ('ORANDO', 936.29, 1)
, ('RAY', 910.34, 1)
, ('MORNING', 208.33, 3)
, ('VERNA', 985.45, 1)
, ('K8', 841.26, 1)
, ('TICO', 896.38, 1)
, ('MATIZ', 575.74, 2)
, ('SPORTAGE', 530.64, 2)
, ('ACCENT', 892.43, 1)
, ('TOSCA', 161.71, 3);
COMMIT;

SELECT * FROM PRODUCT_SEGMENT;

SELECT * FROM PRODUCT;

• PRODUCT_SEGMENT의 값을 참조하여 NET_PRICE 계산

UPDATE PRODUCT A
SET NET_PRICE = A.PRICE - (A.PRICE * B.DISCOUNT)
FROM PRODUCT_SEGMENT B
WHERE A.SEGMENT_ID = B.ID;
COMMIT;
SELECT * FROM PRODUCT;

4. DELETE 문

• 테이블의 특정 데이터를 삭제할 때 사용

4.1 문법

• DELETE FROM 테이블 WHERE 조건

4.2 예제

• LINK 테이블

SELECT * FROM LINK;

• LINK_TMP 테이블

SELECT * FROM LINK_TMP;

• LINK 테이블에서 ID가 5인 행 삭제

DELETE FROM LINK WHERE ID = 5;
COMMIT;
SELECT * FROM LINK;

• 다른 테이블 값을 참조하여 삭제

DELETE FROM LINK_TMP A USING LINK B
WHERE A.ID = B.ID
COMMIT;
SELECT * FROM LINK_TMP;

5. UPSERT 문

• INSERT 시도 시에 조건에 따라 UPDATE를 할 수 있는 구문

5.1 문법

• INSERT INTO 테이블명(컬럼) VALUES(값) ON CONFLICT(타켓컬럼) 액션
• 액션 종류
  • DO NOTHING : 충돌 시 아무 것도 하지 말라
  • DO UPDATE SET ... : 충돌 시 업데이트 하라

5.2 예시

• 실습 테이블 생성(NAME 컬럼에 UNIQUE 제약 조건 설정)

CREATE TABLE CUSTOMERS
(
CUSTOMER_ID SERIAL PRIMARY KEY
, NAME VARCHAR UNIQUE
, EMAIL VARCHAR NOT NULL
, ACTIVE BOOL NOT NULL DEFAULT TRUE
);

INSERT INTO CUSTOMERS (NAME, EMAIL)
VALUES
('IBM', 'contact@ibm.com'),
('Microsoft', 'contact@microsoft.com'),
('Intel', 'contact@intel.com');
COMMIT;

SELECT * FROM CUSTOMERS;

• 이미 있는 NAME 값을 추가 시 아무것도 하지 않음

INSERT INTO CUSTOMERS (NAME, EMAIL)
VALUES
(
'Microsoft', 'hotline@microsoft.com'
)
ON CONFLICT (NAME) DO NOTHING;
COMMIT;

• 이미 있는 NAME 값을 추가 시 email 컬럼에 내용 추가
  • EXCLUDED.EMAIL 은 위에서 INSERT 시도한 EMAIL을 가리킴

INSERT INTO CUSTOMERS (NAME, EMAIL)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (NAME) DO UPDATE
SET EMAIL = EXCLUDED.EMAIL || ';' || CUSTOMERS.EMAIL;

참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. 아파치 하둡이란?

• 빅데이터를 수집, 저장, 처리, 분석 할 수 있는 소프트웨어 프레임워크
• 주요 특징
  • Ditributed : 수십만대의 컴퓨터에 자료 분산 저장 및 처리
  • Scalable : 용량이 증대되는 대로 컴퓨터 추가
  • Fault-tolerant : 하나 이상의 컴퓨터가 고장나도 시스템이 정상 동작
  • Open source : 공개 소프트웨어

2. hadoop을 왜 쓰는가?

• 데이터 크기가 커져 가면서, 저장과 처리를 어떻게 할지에 대해 고민을 하게됨

2.1 전통적인 고성능 서버 방식의 한계

• 더 많은 데이터를 처리하기 위해서는 더 빠른 Scale-up을 수행
  • scale-up : processor와 memory를 확장하여 해결
• 하지만, 지속적으로 향상시키는데에는 한계가 존재함

2.2 분산 처리 시스템 방식 도입-1(MPI)

• 하나의 작업을 처리하기 위해 여러 대의 컴퓨터를 사용
• 더 많은 데이터를 처리하기 위해서는 더 많은 컴퓨터를 확장하여 해결
• 분산 처리를 위해 MPI(Message Passing Interface) 방법을 사용
  • 컴퓨터 사이에 메시지를 전달하여 분산 처리를 수행함
  • 하지만 복잡하여 사용하기 어려움

2.3 분산 처리 시스템 방식 도입-2(GFS+MR , Haddop)

• 구글에서 저성능 서버 여러 대에 데이터를 분선 저장/처리 하는 시스템 개발함
  • GFS(GoogleFile System) 라고 하는 파일 저장 관리 시스템을 개발
  • MR(MapReduce)라고 하는 분산 데이터 처리 방법을 개발
• 하지만 Google에서 해당 기술을 Open하지 않음
• Apache 에서 GFS와 MR을 참고하여 Hadoop이라고 하는 기술을 개발하여 open함

3. hadoop ecosystem

• 데이터 수집
  • Sqoop : 정형데이터(관계형 데이터베이스등)에 있는 데이터를 가져 오는 프로그램
  • Flume : 비정형데이터(로그파일 등)에 있는 데이터를 가져오는 프로그램
• 데이터 저장
  • HDFS(Haddop Distributed File System) : 데이터를 분산하여 저장하는 파일시스템
  • HBase : 컬럼기반 NoSQL 데이터베이스
• 데이터 처리/분석
  • YARN/Map Reduce : 분산 데이터 처리
  • Pig : 스크리브 언어 기반 분석 도구
  • Hive : SQL기반 분석 도구
  • Mahout : 기계학습 알고리즘 기반 데이터 처리
• 데이터 관리
  • Oozie : 빅데이터 처리 과정 관리
  • HCatalog : 빅데이터 메타 정보 관리
  • Zoo keeper : 빅데이터 서버 시스템 관리
• Hadoop ecosystem 관리
  • Hue : 하둡의 프로그램들을 쉽게 실행시키는 동작하는 프로그램

참고

• 숭실대_박영택교수님_Distributed Computing for Big Data
• hadoop eco system_이상철교수님

1. 서브 쿼리란?

• SQL 문 내에서 Main 쿼리가 아닌 하위에 존재하는 쿼리
• 서브 쿼리를 활용해 다양한 결과 도출 가능

2. 서브 쿼리 종류

• 인라인 뷰(Inline View)

  • From 절에 위치하여 하나의 Table 용도로 사용되는 서브 쿼리

• 스칼라 서브쿼리(Scalar Sub Query)

  • 칼럼절에 위치하여 하나의 컬럼 용도로 사용되는 서브 쿼리

• 중첩 서브쿼리(일반 서브쿼리)

  • where 절에 위치하여 하나의 변수 용도로 사용되는 서브쿼리

-- ㄱ) 인라인 뷰
SELECT *
FROM PRODUCT A,
    ( SELECT AVG(PRICE) AS AVG_PRICE FROM PRODUCT ) B
WHERE A.PRICE > B.AVG_PRICE;

-- ㄴ) 스칼라 서브 쿼리
SELECT *, ( SELECT AVG(PRICE) FROM PRODUCT) AS AVG_PRICE
FROM PRODUCT A
WHERE A.PRICE > A.AVG_PRICE

-- ㄷ) 중첩 서브 쿼리
SELECT *
FROM PRODUCT
WHERE PRICE > (SELECT AVG(RENTAL_RATE) FROM PRODUCT)

3. 서브 쿼리 연산자

• 서브 쿼리 결과가 1건 이상일 때 사용하는 연산자
• 종류

3.1 ANY 연산자

• 서브 쿼리 결과 중 하나라도 만족하면 조건 성립
• 예제 1)
  • 영화 분류별 최대 상영시간 집합 추출
  • 상영시간이 상기 집합 중 하나라도 크거나 같은 영화 출력

SELECT TITLE, LENGTH FROM FILM
WHERE LENGTH >= ANY
    ( SELECT DISTINCT MAX(LENGTH)
      FROM FILM A INNER JOIN FILM_CATEGORY B
      ON A.FILM_ID = B.FILM_ID
      GROUP BY B.CATEGORY_ID
     )

-- 서브 쿼리 결과 집합은
-- 178, 181, 183, 184, 185 이다.
-- LENGTH가 상기 집합 중 하나의 값 만이라도 크거나 같으면 참
-- 180의 경우 178보다 크므로 참
-- 170의 경우 아무 데이터도 크지 않으므로 거짓

• 예제 2)
  • 영화 분류별 최대 상영시간 집합 추출
  • 상영시간이 상기 집합 중 하나라도 같은 영화 출력
  • = ANY의 경우 IN과 같다.

SELECT TITLE, LENGTH FROM FILM
WHERE LENGTH = ANY
    ( SELECT DISTINCT MAX(LENGTH)
      FROM FILM A INNER JOIN FILM_CATEGORY B
      ON A.FILM_ID = B.FILM_ID
      GROUP BY B.CATEGORY_ID
     )

-- 서브 쿼리 결과 집합은
-- 178, 181, 183, 184, 185 이다.
-- LENGTH가 상기 집합 중 하나의 값 만이라도 같으면 참
-- 178의 경우 178과 동일하므로 참
-- 179의 경우 같은 데이터가 없으므로 거짓

3.2 ALL 연산자

• 서브 쿼리 결과의 모든 값이 만족해야 조건 성립
• 예제 1)
  • 영화 분류별 최대 상영시간 집합 추출
  • 상영시간이 상기 집합의 모든 데이터 보다 크거나 같은 영화 출력

SELECT TITLE, LENGTH FROM FILM
WHERE LENGTH >= ALL
    ( SELECT DISTINCT MAX(LENGTH)
      FROM FILM A INNER JOIN FILM_CATEGORY B
      ON A.FILM_ID = B.FILM_ID
      GROUP BY B.CATEGORY_ID
     )

-- 서브 쿼리 결과 집합은
-- 178, 181, 183, 184, 185 이다.
-- LENGTH가 상기 집합의 모든 데이터보다 크거나 같으면 참
-- 185의 경우 모든 데이터 보다 크거나 같으므로 참
-- 180의 경우 178보다 크지만 181보다 작으므로 거짓

3.3 EXISTS 연산자

• 서브 쿼리 결과에 특정 집합이 존재하는지 확인
• 동작 중 조건에 만족하는 데이터 발견 시 동작을 멈추므로 속도 빠름
• 예제 1)
  • 고객중에서 11불을 초과한 지불내역이 있는 고객 추출

SELECT FIRST_NAME, LAST_NAME
FROM CUSTOMER C
WHERE EXISTS (
    SELECT 1
    FROM PAYMENT P
    WHERE P.CUSTOMER_ID = C.CUSTOMER_ID
    AND P.AMOUNT > 11
    )
ORDER BY FIRST_NAME, LAST_NAME;

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. 집합 연산자 종류

• 예제 데이터

-- SALE1 Table
CREATE TABLE SALE1
(
NAME VARCHAR(50)
, AMOUNT NUMERIC(15,2)
);
INSERT INTO SALE1
VALUES
('Mike', 150)
, ('Jon', 132)
, ('Mary', 100)
;

SELECT * FROM SALE1;

-- SALE2 Table
CREATE TABLE SALE2
(
NAME VARCHAR(50)
, AMOUNT NUMERIC(15,2)
);
INSERT INTO SALE2
VALUES
('Mike', 120)
, ('Jon', 142)
, ('Mary', 100)
;

SELECT * FROM SALE2;

2. UNION

• 두 집합을 합치며, 중복 데이터는 제거 된다.
• 중복 데이터인 Mary는 중복 제거 후 1건만 출력됨

SELECT * FROM SALE1
UNION
SELECT * FROM SALE2

3.UNION ALL

• 두 집합을 합치며, 중복 데이터도 모두 출력된다.
• 중복 데이터인 Mary도 2건 모두 출력

SELECT * FROM SALE1
UNION ALL
SELECT * FROM SALE2

4.INTERSECT

• 두 집합 모두 가지는 데이터만 출력한다.
• 중복 데이터인 Mary만 출력

SELECT * FROM SALE1
INTERSECT
SELECT * FROM SALE2

5.EXCEPT

• 앞 집합에서 뒷 집합을 제외한 모든 데이터를 출력
• JON, MIKE, MARY에서 중복된 MARY만 제외 후 출력

SELECT * FROM SALE1
EXCEPT
SELECT * FROM SALE2

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. 분석 함수(Analytic Function)

• 분석 함수란?

  • 테이블의 행을 특정 그룹별로 그루핑하여 편리하게 함수를 적용하는 것

• Sample 데이터 생성 SQL

-- 1. PRODUCT_GROUP 테이블 생성
CREATE TABLE PRODUCT_GROUP (
GROUP_ID SERIAL PRIMARY KEY,
GROUP_NAME VARCHAR (255) NOT NULL
);

-- 1.1 PRODUCT_GROUP 데이터 삽입
INSERT INTO PRODUCT_GROUP (GROUP_NAME)
VALUES
('Smartphone')
, ('Laptop')
, ('Tablet');

SELECT * FROM PRODUCT_GROUP

-- 2. PRODUCT 테이블 생성
CREATE TABLE PRODUCT (
PRODUCT_ID SERIAL PRIMARY KEY
, PRODUCT_NAME VARCHAR (255) NOT NULL
, PRICE DECIMAL (11, 2)
, GROUP_ID INT NOT NULL
, FOREIGN KEY (GROUP_ID)
REFERENCES PRODUCT_GROUP (GROUP_ID)
);

-- 2.1 PRODUCT 데이터 삽입
INSERT INTO PRODUCT (PRODUCT_NAME,
GROUP_ID,PRICE)
VALUES
('Microsoft Lumia', 1, 200)
, ('HTC One', 1, 400)
, ('Nexus', 1, 500)
, ('iPhone', 1, 900)
, ('HP Elite', 2, 1200)
, ('Lenovo Thinkpad', 2, 700)
, ('Sony VAIO', 2, 700)
, ('Dell Vostro', 2, 800)
, ('iPad', 3, 700)
, ('Kindle Fire', 3, 150)
, ('Samsung Galaxy Tab', 3, 200);

SELECT * FROM PRODUCT

2. OVER절

• OVER절?
  • GROUP BY의 한계를 개선하기 위해 나온 함수
  • 집계 함수의 결과를 테이블에 바로 보여줌

SELECT *
      , COUNT(*) OVER()
FROM PRODUCT

3. OVER ... PARTITION BY 절

• PARTITION BY 절?
  • GROUP BY와 비슷한 기능으로 OVER절, 분석함수와 같이 사용
  • 특정 칼럼으로 그룹핑 하여 앞의 집계 함수를 그룹별로 적용

SELECT
    A.PRODUCT_NAME
    , A.PRICE
    , B.GROUP_NAME
    , AVG (A.PRICE) OVER (PARTITION BY B.GROUP_NAME)
FROM
PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

4. ROW _NUMBER, RANK, DENSE_RANK 함수

• 그룹에서 특정 컬럼의 순위를 구하는 함수

  함수명	내용
  ROW_NUMBER	- 같은 순위면 중복 상관 없이 순서대로 순위 구함 - EX) 10,20,20,30 일 때 -> 순위 : 1,2,3,4
  RANK	- 같은 순위면 다음 순위로 건너뜀 - EX) 10,20,20,30 일 때 -> 순위 : 1,2,2,4
  DENSE_RANK	- 같은 순위면 다음 순위로 건너뛰지 않음 -EX) 10,20,20,30 일 때 -> 순위 : 1,1,2,3

• ROW_NUMBER

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , ROW_NUMBER () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• RANK

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , RANK () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• DENSE_RANK

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , DENSE_RANK () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

5. FIRST _VALUE , LAST_VALUE 함수

• 그룹안에서 특정컬럼의 첫 번째 값 또는 마지막 값을 구하는 함수

• FIRST_VALUE(컬럼명)

  • 그룹안에서 컬럼명의 첫 번째 값을 구함

SELECT
  A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
  , FIRST_VALUE (A.PRICE) over (
      PARTITION BY B.GROUP_NAME ORDER BY A.PRICE
      ) AS LOWEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• LAST_VALUE(컬럼명)

  • 그룹안에서 컬럼명의 마지막 값을 구함

  • 범위 지정 옵션 : 옵션 범위 기준 마지막값을 구함

    옵션	내용
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	첫 번째 로우부터 마지막 로우
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	첫 번째 로우부터 현재 로우

SELECT
  A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
  , LAST_VALUE (A.PRICE) OVER(
    PARTITION BY B.GROUP_NAME ORDER BY A.PRICE
    RANGE BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING
  ) AS HIGHEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

6. LAG, LEAD 함수

• 그룹안에서 특정 컬럼의 이전 행 또는 다음 행 값을 구함

• LAG

SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
,             LAG (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS PREV_PRICE
, A.PRICE - LAG (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS CUR_PREV_DIFF
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• LEAD

SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
,             LEAD (A.PRICE, 1) OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS NEXT_PRICE
, A.PRICE - LEAD (A.PRICE, 1) OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE )AS CUR_NEXT_DIFF
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. Group by절

• Group by 란?
  • 특정 컬럼값을 기준으로 그룹화함
  • 각 그룹에 대한 합계, 평균, 개수 등의 함수 적용 가능
  • 예) 이름으로 그룹핑 및 각 그룹의 평균 계산 후 평균으로 내림차순 정렬

SELECT NAME, AVG(SCORE) AS AVG_SCORE
FROM SCORELIST GROUP BY NAME
ORDER BY AVG_SCORE DESC

2. Having절

• Having 절이란?
  • Group by의 결과를 특정조건으로 필터링하는 기능
  • 예시) 평균이 94점 이상인 이름(그룹)만 출력

SELECT NAME, AVG(SCORE) AS AVG_SCORE
FROM SCORELIST GROUP BY NAME
having AVG(SCORE)  >= 94

3. GROUPING SETS절

• Grouping sets절이란?

  • 여러 칼럼에 대한 여러 개의 집계를 한번에 출력할 때 사용

• Grouping sets의 필요성

  • 만약 과목별 평균과 이름별 평균, 전체 평균 정보를 한번에 보고 싶다면...
  • 아래와 같이 하나씩 Group by 한 결과를 Union All 해서 출력 가능

-- 1. 전체 평균 계산
SELECT NULL,   NULL, AVG(SCORE) FROM SCORELIST
-- 2. 과목, 이름별 평균 계산
UNION ALL
SELECT SUBJECT, NAME, AVG(SCORE) FROM SCORELIST GROUP BY SUBJECT, NAME
-- 3. 과목별 평균 계산
UNION ALL
SELECT SUBJECT, NULL, AVG(SCORE) FROM SCORELIST GROUP BY SUBJECT
-- 4. 이름별 평균 계산
UNION ALL
SELECT NULL,    NAME, AVG(SCORE) FROM SCORELIST GROUP BY NAME

• 상기 예제를 Grouping sets 절로 구현
  • 수행 속도가 더 빠르고 간단함

SELECT SUBJECT, NAME, AVG(SCORE) FROM SCORELIST
GROUP BY
GROUPING SETS(
    (SUBJECT, NAME),
    SUBJECT,
    NAME,
    ()
)

4. ROLL UP 절

• ROLL UP 이란?
  • 지정된 GROUPING 컬럼의 소계를 생성함
• ROLL UP의 원리
  • 인자로 들어온 컬럼을 오른쪽부터 하나씩 빼면서 Group 생성
• 예제 1, 2, 3 비교(아래 그림 참고)
  • SUBJECT, NAME 으로 그룹핑 하여 평균 출력하는 부분은 동일함
    

    

• 예제 1) GROUP BY ROLLUP( SUBJECT, NAME )
  • SUBJECT, NAME에서 오른쪽인 NAME이 제외된 SUBJECT 그룹으로 평균 출력
  • 그 다음으로 SUBJECT가 제외되어 아무것도 없고 GROUP BY절에도 아무것도 없으므로 전체 평균 출력
• 예제 2) GROUP BY SUBJECT, ROLLUP( NAME )
  • ROLLUP에 NAME뿐이어서 NAME이 제외되면 아무것도 없으므로 GROUP BY에 있는 SUBJECT 그룹으로 평균 출력
• 예제 3) GROUP BY ROLLUP((SUBJECT, NAME))
  • ROLLUP에 SUBJECT, NAME이 괄호안에 하나의 그룹이고, 제외 되면 아무 것도 없으므로 전체 평균 출력

5. CUBE 절

• CUBE란?

  • 지정된 GROUPING 컬럼의 다차원 소계를 생성함

• ROLL UP과의 차이

  • ROLL UP은 오른쪽부터 순서대로 하나씩 뺌

  • CUBE는 방향 상관없이 하나씩 빼며 모든 가능한 조합의 Group 생성

  • CUBE와 ROLLUP의 그룹 조합 차이

    CUBE(C1, C2)	ROLLUP(C1,C2)
    C1, C2	C1, C2
    C1	C1
    C2	X
    ()	()

• 예제 1, 2, 3 비교(아래 그림 참고)

  • SUBJECT, NAME 으로 그룹핑 하여 평균 출력하는 부분은 동일함
    

    

• 예제 1) GROUP BY CUBE( SUBJECT, NAME )

  • SUBJECT, NAME에서 NAME이 제외된 SUBJECT 그룹으로 평균 출력
  • SUBJECT, NAME에서 SUBJECT가 제외된 NAME 그룹으로 평균 출력
  • 그 다음으로 모두 제외되어 아무것도 없고 GROUP BY절에도 아무것도 없으므로 전체 평균 출력

• 예제 2) GROUP BY SUBJECT, CUBE( NAME )

  • CUBE에 NAME뿐이어서 NAME이 제외되면 아무것도 없으므로 GROUP BY에 있는 SUBJECT 그룹으로 평균 출력

• 예제 3) GROUP BY CUBE((SUBJECT, NAME))

  • CUBE에 SUBJECT, NAME이 괄호안에 하나의 그룹이고, 제외 되면 아무 것도 없으므로 전체 평균 출력

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. Join의 종류

• Join이란?
  • 두 테이블에 공통으로 있는 칼럼을 이용해 연결 시키는 것

2. Join의 문법

3. Join 예시

• INNER JOIN

SELECT * FROM BASKET_A BA
INNER JOIN BASKET_B BB
ON BA.FRUIT = BB.FRUIT

• LEFT OUTER JOIN

SELECT * FROM BASKET_A BA
LEFT OUTER JOIN BASKET_B BB
ON BA.FRUIT = BB.FRUIT

• RIGHT OUTER JOIN

SELECT * FROM BASKET_A BA
RIGHT OUTER JOIN BASKET_B BB
ON BA.FRUIT = BB.FRUIT

• FULL OUTER JOIN

SELECT * FROM BASKET_A BA
FULL OUTER JOIN BASKET_B BB
ON BA.FRUIT = BB.FRUIT

• SELF JOIN

SELECT A.EMPLOYEE_ID AS ID, A.MANAGER_ID AS M_ID
    ,B.EMPLOYEE_ID AS ID,  B.MANAGER_ID AS M_ID
FROM EMPLOYEE A
INNER JOIN EMPLOYEE B
ON A.EMPLOYEE_ID = B.MANAGER_ID

• CROSS JOIN

SELECT * FROM CROSS_T1 A
CROSS JOIN CROSS_T2 B

• NATURAL JOIN

SELECT CATEGORY_ID AS C_ID, PRODUCT_ID AS P_ID
      , CATEGORY_NAME AS C_NAME ,PRODUCT_NAME AS P_NAME
FROM PRODUCTS NATURAL JOIN CATEGORIES

# 참고

​

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. 데이터 조회

• 데이터 조회
  • SELECT 컬럼명 FROM 테이블명

SELECT * FROM CUSTOMER;          -- 모든 컬럼 조회
SELECT FIRST_NAME FROM CUSTOMER; -- 특정 컬럼 조회

• 데이터 정렬
  • ORDER BY 칼럼명 ASC => 정렬하기
    • ASC : 오름차순 정렬(Default)
    • DESC : 내림차순 정렬

SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER
ORDER BY FIRST_NAME ASC,
    LAST_NAME DESC

• 중복 제외값 조회
  • SELECT DISTINCT 칼럼명 FROM 테이블명
    • 테이블 내에 존재하는 칼럼명을 중복 제거 후 1개씩만 출력
  • SELECT DISTINCT ON(칼럼명1) 칼럼명1, 칼럼명2 FROM 테이블명 ORDER BY 칼럼명1, 칼럼명2 DESC
    • 칼럼명1 기준으로 중복 제고 후 칼럼명2는 DESC 정렬된 맨 위 1개의 값만 보여줌

2. 데이터 필터링

• 조건으로 필터링
  • SELECT 칼럼명 FROM 테이블명 WHERE 조건
    • WHERE절 이하 조건에 따라 테이블 조회

SELECT FIRST_NNAME FROM CUSTOMER
WHERE FIRST_NAME='Jamie'

• 출력 개수로 필터링
  • LIMIT
    • SELECT * FROM 테이블명 LIMIT N
      • N개행만 출력
    • SELECT * FROM 테이블명 LIMIT N OFFSET M
      • M+1번째 행부터 N개 출력
  • FETCH
    • SELECT * FROM 테이블명 FETCH FIRST N ROW ONLY
      • N개행만 출력
      • N을 입력하지 않으면 1개 행만 출력
    • SELECT * FROM 테이블명 OFFSET M ROWS FETCH FIRST N ROW ONLY
      • M+1번째 행부터 N개 출력

-- LIMIT
SELECT * FROM CUSTOMER LIMIT 5          -- 5개 행만 출력
SELECT * FROM CUSTOMER LIMIT 4 OFFSET 3 -- 3+1번째 행부터 4개행 출력

-- FETCH
SELECT * FROM CUSTOMER FETCH FIRST 5 ROW ONLY               -- 5개 행만 출력
SELECT * FROM CUSTOMER OFFSET 3 ROWS FETCH FIRST 4 ROW ONLY -- 3+1번째 행부터 4개행 출력

• IN 연산자
  • SELECT * FROM 테이블명 WHERE 칼럼명 IN (V1, V2)
    • 칼럼명의 값이 V1, V2인 것만 조회
  • SELECT * FROM 테이블명 WHERE CUSTOMER_ID NOT IN (V1, V2)
    • 칼럼명의 값이 V1, V2가 아닌 것만 조회

SELECT * FROM CUSTOMER WHERE CUSTOMER_ID IN (1,2) -- ID가 1,2인 대상 추출
SELECT * FROM CUSTOMER WHERE CUSTOMER_ID NOT IN (1,2) -- ID가 1,2가 아닌 대상 추출

• BETWEEN 연산자
  • SELECT * FROM 테이블명 WHERE 칼럼명 BETWEEN V1 AND V2
    • 칼럼명의 값이 V1 이상이면서 V2 이하인 것만 조회
  • SELECT * FROM 테이블명 WHERE 칼럼명 NOT BETWEEN V1 AND V2
    • 칼럼명의 값이 V1 미만이면서 V2 초과인 것만 조회
• LIKE 연산자
  • SELECT * FROM 테이블명 WHERE 칼럼명 LIKE 특정패턴
    • 칼럼명이 특정 패턴을 갖는 것만 조회
      • 특정패턴이 % : 길이와 상관없이 아무 문자가 있는 패턴
      • 특정패턴이 _ : 한 개의 문자가 아무 문자가 있는 패턴

-- 이름이 KIM으로 시작하는 고객 추출
SELECT * FROM CUSTOMER
WHERE NAME LIKE 'KIM%';

-- 이름이 3자리이고 가운에 문자가 'A' 인 고객 추출
SELECT * FROM CUSTOMER
WHERE NAME LIKE '_A_';

• IS NULL 연산자
  • SELECT * FROM 테이블명 WHERE 칼럼명 IS NULL
    • 칼럼명의 값이 NULL 인 것만 조회
  • SELECT * FROM 테이블명 WHERE 칼럼명 IS NOT NULL
    • 칼럼명의 값이 NULL이 아닌 것만 조회

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

blog.naver.com/PostView.nhn?blogId=ilsan_ilsan&logNo=221483634939&parentCategoryNo=&categoryNo=63&viewDate=&isShowPopularPosts=true&from=search