23.12.16 ~ 17

[오늘 한 일]

1. SQL 코드카타 문제 풀기 (10문제)

2. SQLD 자격증 대비반 강의 13주차 & 14주차

 

[내용정리[

1. SQL 코드카타

-- ROUND(반올림) TRUNC(내림) CEIL(올림)
SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'
GROUP BY CAR_TYPE

-- 서브 쿼리 활용
SELECT PRICE_GROUP,
        COUNT(*) AS PRODUCTS
FROM (
    SELECT TRUNC(PRICE/10000)*10000 AS PRICE_GROUP
    FROM PRODUCT)
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

-- DISTINCT 중복없이
SELECT DISTINCT(A.CAR_ID) AS CAR_ID
FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    ON A.CAR_ID = B.CAR_ID
WHERE A.CAR_TYPE = '세단'
    AND TO_CHAR(B.START_DATE, 'MM') = '10'
ORDER BY A.CAR_ID DESC

-- WINDOW함수 사용
SELECT  FOOD_TYPE, REST_ID, REST_NAME,FAVORITES
FROM    (SELECT ROW_NUMBER() OVER(PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) MAX_NUM,
        A.*
        FROM  REST_INFO   A)
WHERE   MAX_NUM = 1
ORDER BY FOOD_TYPE DESC
-- 또 다른 방법
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN(SELECT FOOD_TYPE, MAX(FAVORITES) 
                               FROM REST_INFO
                               GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC;

-- 잘 안 풀린 문제..
SELECT A.PRODUCT_ID, A.PRODUCT_NAME, A.PRICE*B.AMOUNT AS TOTAL_SALES
FROM FOOD_PRODUCT A JOIN (SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
                          FROM FOOD_ORDER
                          WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
                          GROUP BY PRODUCT_ID) B 
     ON A.PRODUCT_ID = B.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID ASC

2. SQLD 자격증 대비반 

<13주차>

[그룹함수]
크게 AGGREGATE(집계), GROUP(그룹), 윈도우(WINDOW)함수 3가지 함수를 정의하고 있다.
0. AGGREGATE 함수 : COUNT, SUM, AVG, MAX 등
1. GROUP FUNCTION (like 엑셀 부분합)

GROUP BY ROLLUP/CUBE ( CAL1 , CAL2)

1) ROLLUP 함수
- 컬럼으로 그룹을 만든 후 각 칼럼의 중간 합계를 만들기 위해 사용하는 함수.

- 괄호를 어디에 두느냐에 따라 다른 결과가 나온다.

- ROLLUP 함수에서 소계나 합계의 경우 NULL로 표시되는데, 어떤 그룹화된 값인지를 표현하고 싶은 경우 CASE 문을 사용하면 된다.
2) CUBE
- 결합 가능한 모든 값에 대해서 다차원 집계를 구하는 함수

- ROLLUP과는 다르게 평등한 관계이므로 칼럼의 순서가 바뀌어도 정렬되는 순서는 바뀌지만 데이터의 결과는 동일
3). GROUPING
- ROLLUP이나 CUBE에 의해서 그룹화 된 칼럼의 소계와 합계로 집계되어 출력된 행을 구분할 때 사용

- 소계는 값 0 합계는 값 1 출력

4) GROUPING SET
GROUP BY 문장을 여러 번 반복하지 않아도 다양한 소계 집합을 만들 수 있다.

SELECT
    DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
    DECODE(GROUPING(JOB), 1, 'All Jobs', JOB)            AS JOB,
FROM ----

[윈도우 함수]
- 기존 관계형 데이터 베이스는 칼럼 간의 연산, 비교, 연결이나 집합에 대한 집계는 비교적 쉽게 할 수 있다. 하지만 '행과 행간의 관계'를 정의하거나, 행 간 비교를 하거나 하나의 SQL 문으로 처리하기는 매우 힘든 일이다.이렇게 복잡한 SQL 문을 작성해야 하던 것을 부분적으로나마 쉽게 정의하기 위해 만들어진 함수

SELECT WINDOW_FUNCTION(ARGUMENTS) 
OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명;

1) RANK FUNCTION : 그룹 내 순위를 내는 함수
RANK / DENSE_RANK / ROW_NUMBER(동일한 순위라도 고유한 순위)
2) 일반 집계 함수
SUM / MIN / AVG / MAX
3) 집계함수
FIRST_VALUE / LAST_VALUE / LAG / LEAD
4) 그룹 내 비율 함수
RATIO_TO_REPORT / PERCENT_RANK / CUME_DIST / NTITLE

[절차형 SQL]
- DDL, DML, DCL 등은 비절차적인 언어.

- 일반적인 개발 언어처럼 절차 지향적으로 프로그래밍이 가능하도록 DBMS 별로 절차형 SQL을 제공한다. 연속적인 실행이나 조건에 따른 분기 등을 처리하는 저장 모듈

- PROCEDURE

특정한 로직을 처리하기만 하고 결괏값을 반환하지않는 서브 프로그램. 즉, 테이블에서 데이터를 추출해 입맛에 맞게 조작하고 그 결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할 때 주로 프로시저를 사용

- USER DEFINED FUNCTION

사용자가 임의로 만들어서 사용하는 함수

- TRIGGER

특정한 테이블에 데이터의 삽입(insert), 갱신(update), 삭제(delete) 등의 특정 이벤트 발생 시 관련 작업이 자동 수행되는 절차형 SQL

프로시저와 함수는 그 실행이 외부적인 실행 명령에 의해, 트리거의 실행은 트리거링 사건(Triggering Event)에 의해 내부적으로 이뤄진다.

사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행되기 때문에 보통 트리거는 데이터 변경, 무결성 유지, 로그 메시지 출력 등을 목적으로 작성됩니다. 트리거에 오류 존재 시, 처리 데이터에도 바로 영향 미치므로 주의가 필요
 

<14주차>

[옵티마이저]
사용자가 질의한 SQL문의 결과를 얻을 수 있는 다양한 실행 방법 중에서 최적의 실행 방법을 결정하는 역할을 한다. 다시 말해, 어떤 방식으로 SQL 쿼리를 처리하고 데이터를 검색할지 결정하는데 도움을 주는 도구

항목	규칙기반 옵티마이저	비용기반 옵티마이저
개념	사전에 정의된 규칙 기반	최소비용 계산 실행계획 수립
기준	실행우선 순위(Ranking)	액세스 비용(Cost)
인덱스	인덱스 존재 시 가장 우선시 사용	Cost에 의한 결정
단점	예측 통계정보 요소 무시	최소 성능 보장 계획의 예측 제어 어려움
장점	판단이 매우 규칙적 실행 예상 가능	통계 정보를 통한 현실 요소 적용
성능	사용자 SQL작성 숙련도	서옵티마이저 예측 성능

 

[실행계획]
1. 실행계획
- 실행계획이란 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업 절차.
- 실행계획을 구성하는 요소에는 조인 순서(Join Order), 조인 기법(Join Method), 액세스 기법(Access Method), 최적화 정보(Optimization Information), 연산(Operation)등이 있다.
2. SQL 처리 흐름도
SQL 처리 흐름도는 SQL 내부적인 처리 절차 즉, 실행계획을 시각적으로 표현한 도표를 말합니다. SQL문의 처리를 위해 어떤 테이블을 먼저 읽었는지, 테이블을 읽기 위해 인덱스 스캔을 수행했는지 혹은 전체 테이블을 스캔했는지와 조인 기법 등을 표현할 수 있다.

[인덱스]
 100만 개의 데이터가 저장된 테이블에서 특정한 한 개의 데이터를 가져온다고 할 때, 모든 데이터를 하나하나 찾아가면서 체크해야 하는 비효율이 생긴다. 이러한 문제를 해결하기 위해 등장한 것이 인덱스다. 검색 성능의 최적화이며 테이블을 기반으로 선택적으로 생성할 수 있는 구조다.

[조인 수행 원리]
테이블 또는 조인 결과를 이용하여 조인을 수행할 때 조인 단계별로 각각 다른 조인 기법을 사용할 수 있다.
1. NL Join(Nested Loops Join)
- 중첩된 반복문과 유사한 방식으로 조인을 수행하는 기법으로, 선행 테이블의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행한다.
- NL Join은 선행 테이블의 조건을 만족하는 행의 수가 많을수록 후행 테이블의 조인 작업이 반복 수행되므로 결과의 행 수가 적은 테이블을 조인의 선행 테이블로 선택하는 것이 전체 작업량을 줄일 수 있다.
- NL Join은 조인이 성공하면 바로 결과를 사용자에게 보여 줄 수 있기 때문에 소량의 테이블에 대한 조회 결과를 빨리 화면에 보여줘야 하는 온라인 프로그램에 사용하기 적당한 조인 기법
- NL Join은 조인 칼럼에 적당한 인덱스가 존재해서 Natural 조인을 효율적으로 사용할 때 유용한 조인 기법

2. Sort Merge Join
- 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행
- NL Join은 주로 랜덤 액세스 방식으로 데이터를 읽는 반면 Sort Merge Join은 주로 스캔 방식으로 데이터를 읽는다.  Sort Merge Join은 인덱스가 없기 때문에 NL Join이 비효율적일 때 많이 사용한다.
- 정렬할 데이터가 많아서 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역을 사용하기 때문에 성능이 떨어질 수 있다.
- 동등 조인뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다는 장점

3. Hash Join
- hashing 기법(해시 함수를 이용하여 데이터를 결합하는 방법)을 이용하여 조인을 수행.
- 조인을 수행할 테이블의 조인 칼럼을 기준으로 해시 함수를 수행하여 동일한 해시 값을 갖는 것들 사이에서 실제 값이 같은지 비교하면서 조인을 수행(동등 조인에서만 사용)
- Hash Join은 NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 정렬 작업의 부담을 해결하기 위한 대안으로 등장. 한 쪽 테이블의 크기가 작고 해시 키 속성에 중복 값이 적을 때 효과적이며 조인 칼럼에 적당한 인덱스가 없어서 자연조인이 비효율적일 때 사용하기 좋다.
- Hash Join을 할 때는 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다.