너도 알아야 한다, SQL과 데이터베이스

profile
FE Developer - 죠지
November 16, 2023
GPT 요약
Thumbnail

The database and Nam June Paik's painting style reinterpreted by Midjourney.

너도 알아야 한다, SQL과 데이터베이스

"2018년 OPEN AI가 GPT-1을 발표했을 때, 이 정도의 시장 영향력과 파급력을 예상했을까?"

개인적인 의문으로 시작했습니다.

물론 117,000,000(대략 1억 2천만)개의 파라미터 수가 보여주는 볼륨감은 GPT-3(1750억 개) 또는 GPT-4(미공개, 4000~5000억 개로 추측)의 수준에 비교할 바는 아니지만, 12개의 NLP Task 중 9개에서 SOTA(State of the art, 최고 수준)를 달성할 정도로 비약적인 발전을 보여줬습니다.

1000억개의 파라미터가 너무 흔해지고 거대함을 넘어서 초거대로 들어온 요즘, 2018년도로 돌아간다면 어떤 기분일지 상상이 되지 않습니다.

어쩌면 당연한 말이지만 이러한 발전은 데이터를 다루는 기술과 매우 밀접한 관련이 있습니다. 데이터의 양과 질 또한 비약적으로 좋아진 것도 한 몫했지만 결국 본질적으로 매우 큰 데이터를 다루는 기술 즉, 빅데이터 기술의 발전이 있었기 때문입니다.

시작하며

이번 글에서는 제목에서 알다시피 개발자라면 알아야하는 SQL과 데이터베이스를 다루도록 하겠습니다. 이미 능통하신 분들도 계시고, 학부에서 심층적으로 공부하신 분들도 계시겠지만, 아무리 강조해도 지나치지 않는 것이 SQL과 데이터베이스 지식이라는 지론이 많이 반영되었습니다.

중간부터 시작

기본적인 데이터베이스 지식 그리고 DDL, DML, DCL, TCL 등의 SQL 기본을 습득한 상태를 전제로 시작합니다.

Tip
ORACLE 방식과 ISO 방식 중 ISO 방식을 기준으로 진행합니다.

반복되는 SELECT는 이제 그만

혹시 SQL을 사용해본 것은 SELECT, UPDATE 정도이고 가끔 사용하는 GROUP BY나 HAVING도 문법을 검색 하시고 계시지는 않나요? 만약 이런 상황에 계시다면 매우 적합합니다.

ROWNUMBER, ROWID

둘을 구분하는 것은 어렵지 않습니다. ROWNUMBER는 명백히 각 행에 순차적인 번호(1부터 시작)의 할당이고, ROWID는 AUTO_INCREMENT 같은 식별자는 아니지만 데이터베이스 내에서 어디에 위치하는지를 나타내는 고유 식별자입니다.

# window function으로 사용
SELECT ROWNUMBER() OVER (ORDER BY column_name) AS row_num, *
FROM table_name;
SELECT ROWID, *
FROM table_name;

INLINE VIEW, SUBQUERY, SCALAR SUBQUERY

이 세가지는 모두 서브쿼리를 사용하는 방법입니다. 서브쿼리를 사용하는 위치에 따라서 이름이 달라지는데, INLINE VIEW는 서브쿼리를 FROM 절에 사용하는 것이고, SUBQUERY는 WHERE 절에 사용하는 것입니다. 물론 SCALAR SUBQUERY는 남은 위치인 SELECT 절에 사용하는 것입니다.

구분사용 위치설명
INLINE VIEWFROM서브쿼리를 FROM 절에 사용하는 것으로, 서브쿼리의 결과를 테이블처럼 사용할 수 있습니다.
SUBQUERYWHERE서브쿼리를 WHERE 절에 사용하는 것으로, 서브쿼리의 결과를 조건식으로 사용할 수 있습니다.
SCALAR SUBQRYSELECT서브쿼리를 SELECT 절에 사용하는 것으로, 서브쿼리의 결과를 값으로 사용할 수 있습니다.

JOIN

JOIN은 매우 중요합니다. JOIN은 데이터베이스에서 가장 많이 사용되는 기능 중 하나입니다. JOIN은 크게 INNER JOIN, OUTER JOIN으로 나뉩니다. INNER JOIN은 교집합을 의미하고, OUTER JOIN은 합집합을 의미합니다. OUTER JOIN은 LEFT, RIGHT, FULL OUTER JOIN으로 나뉘는데, LEFT OUTER JOIN은 왼쪽 테이블을 기준으로 OUTER JOIN을 수행하고, RIGHT OUTER JOIN은 오른쪽 테이블을 기준으로 OUTER JOIN을 수행합니다. FULL OUTER JOIN은 LEFT, RIGHT OUTER JOIN을 합친 것으로, 두 테이블의 합집합을 의미합니다.

  • INNER JOIN
SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
  • LEFT, RIGHT, FULL OUTER JOIN
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

WINDOW FUNCTION

여기서부터는 좀 낯설 수 있습니다. 왜냐하면 WINDOW FUNCTION은 ANSI SQL:2003 표준에 추가된 기능이기도 하고, 여기까지는 배우지 않았을 수도 있기 때문입니다. 조금 어려울 수 있지만, 매우 유용한 기능이기 때문에 꼭 알아야 합니다. 우리가 흔히 코딩테스트에서 사용하는 RANK, DENSE_RANK, ROW_NUMBER 등이 WINDOW FUNCTION의 일부입니다. 또한 심화로 들어가서 이동평균이나 복잡한 통계량을 구할 때도 사용할 수 있습니다.

SHAPE OF WINDOW

WINDOW_FUNCTION은 크게 3가지로 나뉩니다. PARTITION BY, ORDER BY, WINDOWING CLAUSE 입니다.

  • PARTITION BY는 GROUP BY와 비슷한 기능을 합니다. GROUP BY는 테이블 전체를 기준으로 그룹을 나누지만, PARTITION BY는 테이블 전체가 아닌 PARTITION BY 절에 지정한 컬럼을 기준으로 그룹을 나눕니다.

  • ORDER BY는 정렬을 의미합니다. ORDER BY 절에 지정한 컬럼을 기준으로 정렬을 수행합니다.

  • WINDOWING CLAUSE는 PARTITION BY와 ORDER BY를 통해 나눈 그룹에서 어떤 행을 기준으로 WINDOW FUNCTION을 수행할지를 결정합니다. WINDOWING CLAUSE는 ROWS와 RANGE가 있습니다. ROWS는 행을 기준으로 WINDOW FUNCTION을 수행하고, RANGE는 값의 범위를 기준으로 WINDOW FUNCTION을 수행합니다.

구분설명
PARTITION BYGROUP BY와 비슷한 기능으로, 테이블 전체가 아닌 PARTITION BY 절에 지정한 컬럼을 기준으로 그룹을 나눕니다.
ORDER BY정렬을 의미합니다.
WINDOWINGPARTITION BY와 ORDER BY를 통해 나눈 그룹에서 어떤 행을 기준으로 WINDOW FUNCTION을 수행할지를 결정합니다.

WINDOWING의 구성을 좀 더 정리하면 아래와 같습니다.

WINDOWING설명
ROWS행을 기준으로 WINDOW FUNCTION을 수행하고, ROWS BETWEEN 절을 통해 행의 범위를 지정합니다.
RANGE값의 범위를 기준으로 WINDOW FUNCTION을 수행하고, RANGE BETWEEN 절을 통해 범위를 지정합니다.
UNBOUNDED PRECEDING윈도우의 시작 위치가 첫 번째 행을 의미합니다.
UNBOUNDED FOLLOWING윈도우의 마지막 위치가 마지막 행을 의미합니다.
N PRECEDINGN 이전 행을 의미합니다.
N FOLLOWINGN 이후 행을 의미합니다.
CURRENT ROW현재 행을 의미합니다.

TUTORIAL

이제 실제로 WINDOW FUNCTION을 사용해보겠습니다. 물론 가장 어려운 부분 중에 하나인 ROWS와 RANGE의 차이를 이해하는 것이 중요합니다.

SELECT
  DEPARTMENT_ID,
  EMPLOYEE_ID,
  SALARY,
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPARTMENT_AVG, -- PARTITION BY
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS DEPARTMENT_AVG_ORDERED, -- ORDER BY
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DEPARTMENT_AVG_ORDERED_ROWS,
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS DEPARTMENT_AVG_ORDERED_ROWS_2_PRECEDING,
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS DEPARTMENT_AVG_ORDERED_ROWS_2_PRECEDING_2_FOLLOWING,
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DEPARTMENT_AVG_ORDERED_RANGE,
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS DEPARTMENT_AVG_ORDERED_RANGE_1000_PRECEDING,
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS DEPARTMENT_AVG_ORDERED_RANGE_1000_PRECEDING_1000_FOLLOWING
FROM
  EMPLOYEES
ORDER BY
  DEPARTMENT_ID,
  SALARY;

세 번째 WINDOWING 절은 누적 평균을 구하는 것입니다. PARTITION BY DEPARTMENT_ID에 따라 DEPARTMENT_ID를 기준으로 그룹을 나누고, ORDER BY SALARY에 의해 그룹 내에서 SALARY를 오름차순 정렬합니다. 그리고 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 그룹의 첫 번째 행부터 현재 행까지를 기준으로 누적 평균을 구합니다.

DEPARTMENT_IDEMPLOYEE_IDSALARYDEPARTMENT_AVG_ORDERED_ROWS
1020044004400
1020160005200
1020265005633.333333333
2011444004400
2011544004400

네 번째 WINDOWING 절은 3구간 이동 평균을 구하는 것입니다.

DEPARTMENT_IDEMPLOYEE_IDSALARYDEPARTMENT_AVG_ORDERED_ROWS_2_PRECEDING
1020044004400
1020160005200
1020265005633.333333333
201144400(6000 + 6500 + 4400) / 2 = 5450
201154400(6500 + 4400 + 4400) / 2 = 5100

마찬가지로 다섯 번째 WINDOWING 절은 5구간 이동 평균을 구하는 것입니다. 2 FOLLOWING은 현재 행부터 2행 뒤까지를 의미합니다.

DEPARTMENT_IDEMPLOYEE_IDSALARYDEPARTMENT_AVG_ORDERED_ROWS_2_PRECEDING_2_FOLLOWING
102004400(4400 + 6000 + 6500) / 3 = 5633.333333333
102016000(4400 + 6000 + 6500 + 4400) / 4 = 5325
102026500(4400 + 6000 + 6500 + 4400 + 4400) / 5 = 5360
201144400(6000 + 6500 + 4400 + 4400) / 4 = 5325
201154400(6500 + 4400 + 4400) / 3 = 5100

여섯 번째 WINDOWING 절은 RANGE를 사용해서 논리적 범위를 지정합니다. 앞서 DEPARTMENT_ID로 그룹을 나누면, 내부에서는 논리적 그룹이 형성됩니다. 이제 논리적 그룹 내에서 ORDER BY로 정렬된 순서대로 평균을 구합니다.

DEPARTMENT_IDEMPLOYEE_IDSALARYDEPARTMENT_AVG_ORDERED_RANGE
1020044004400
1020160005200
1020265005633.333333333
2011444004400
2011544004400

일곱 번째 WINDOWING 절은 1 PRECEDING을 사용해서 논리적 범위를 지정합니다. 직전의 그룹과 현재 그룹만을 기준으로 평균을 구합니다.

DEPARTMENT_IDEMPLOYEE_IDSALARYDEPARTMENT_AVG_ORDERED_RANGE_1_PRECEDING
1020044004400
1020160005200
1020265005633.333333333
2011444005200
2011544005633.333333333

마지막은 1 PRECEDING과 1 FOLLOWING을 사용해서 논리적 범위를 지정합니다. 직전의 그룹과 현재 그룹, 그리고 다음 그룹까지를 기준으로 평균을 구합니다.

DEPARTMENT_IDEMPLOYEE_IDSALARYDEPARTMENT_AVG_ORDERED_RANGE_1_PRECEDING_1_FOLLOWING
1020044005200
1020160005633.333333333
1020265005200
2011444005633.333333333
2011544004400

PARTITION

파티션은 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장하는 것을 의미합니다. 데이터 베이스를 설계할 때 개념적, 논리적, 물리적으로 분리하게 되는데 물리적으로 분리하는 것이 파티션입니다. 즉 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리해서 저장하는 것입니다. 물리적으로 분리하면 데이터의 입력, 수정, 삭제, 조회 성능이 향상됩니다. 이는 물리적으로 분리된 테이블을 병렬로 처리할 수 있기 때문입니다.

LIST PARTITION

LIST PARTITION은 특정 컬럼의 값을 기준으로 파티션을 나눕니다. 예를 들어서 DEPARTMENT_ID 컬럼을 기준으로 파티션을 나누면 DEPARTMENT_ID가 10인 행들은 하나의 파티션에 저장하고, DEPARTMENT_ID가 20인 행들은 다른 파티션에 저장하는 것입니다.

RANGE PARTITION

RANGE PARTITION은 특정 컬럼의 값의 범위를 기준으로 파티션을 나눕니다. 예를 들어서 SALARY 컬럼을 기준으로 파티션을 나누면 SALARY가 1000 이하인 행들은 하나의 파티션에 저장하고, SALARY가 1000 초과인 행들은 다른 파티션에 저장하는 것입니다.

HASH PARTITION

HASH PARTITION은 특정 컬럼의 값의 해시 값을 기준으로 파티션을 나눕니다. 예를 들어서 EMPLOYEE_ID 컬럼을 기준으로 파티션을 나누면 EMPLOYEE_ID가 1, 2, 3, 4, 5, 6, 7, 8, 9인 행들은 하나의 파티션에 저장하고, EMPLOYEE_ID가 10, 11, 12, 13, 14, 15, 16, 17, 18인 행들은 다른 파티션에 저장하는 것입니다.

COMPOSITE PARTITION

COMPOSITE PARTITION은 파티션을 여러 개의 컬럼을 기준으로 나눕니다. 예를 들어서 DEPARTMENT_ID와 SALARY 컬럼을 기준으로 파티션을 나누면 DEPARTMENT_ID가 10이고 SALARY가 1000 이하인 행들은 하나의 파티션에 저장하고, DEPARTMENT_ID가 10이고 SALARY가 1000 초과인 행들은 다른 파티션에 저장하는 것입니다.

루프도 빠짐없이

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (
        WITH master_table AS (
            SELECT
                CT.id,
                CASE
                    WHEN MASTER1.master_code IS NOT NULL THEN 0
					          WHEN MASTER2.master_code IS NOT NULL THEN 1
                    WHEN MASTER3.master_code IS NOT NULL THEN 2
                    WHEN MASTER4.master_code IS NOT NULL THEN 3
                    ELSE 0
                END AS result_category
            FROM dentium.complex_table AS CT
                LEFT OUTER JOIN dentium.price_master_1 AS MASTER1 ON CT.code = MASTER1.master_code
				        LEFT OUTER JOIN dentium.price_master_2 AS MASTER2 ON CT.code = MASTER2.master_code
                LEFT OUTER JOIN dentium.price_master_3 AS MASTER3 ON CT.code = MASTER3.master_code
                LEFT OUTER JOIN dentium.price_master_4 AS MASTER4 ON CT.code = MASTER4.master_code
        )
        SELECT id, result_category FROM master_table
    )
    LOOP
        UPDATE dentium.target_table
        SET category = r.result_category
        WHERE id = r.id;
    END LOOP;
END $$;

SQL에도 루프가 가능한 것 알고 계셨나요? 절차형 SQL에서는 일반 언어와 마찬가지로 루프를 사용할 수 있습니다. 물론 위의 SQL은 프로시저로 작성되었습니다(PostgreSQL).

RECORD 타입은 테이블의 한 행을 의미합니다. WITH를 사용해서 서브 쿼리를 만들고, 이를 루프를 돌면서 UPDATE를 수행합니다. 간단하죠? 😆

최종 트리 만들기

SELECT LPAD(' ', 7 - LEVEL) || LPAD('*', LEVEL * 2 - 1, '*')
FROM DUAL
CONNECT BY LEVEL <= 7
UNION ALL
SELECT LPAD(' ', 5) || '***'
FROM DUAL
CONNECT BY LEVEL <= 3;
      *
     ***
    *****
   *******
  *********
 ***********
*************
     ***
     ***
     ***

트리를 만들어보겠습니다. 트리는 재귀적인 구조를 가지고 있기 때문에 재귀 쿼리를 사용해야 합니다. CONNECT BY를 사용하면 재귀 쿼리를 사용할 수 있습니다. 또한 CONNECT BY는 두 개의 키워드를 가지고 있습니다. 하나는 PRIOR, 다른 하나는 LEVEL입니다. PRIOR는 부모 노드를 의미하고, LEVEL은 현재 노드의 레벨을 의미합니다. CONNECT BY를 사용하면 루트 노드부터 시작해서 자식 노드를 순회하면서 쿼리를 실행하게 됩니다.

하지만 위의 쿼리는 PostgreSQL에서는 실행되지 않습니다. PostgreSQL에서는 CONNECT BY를 지원하지 않기 때문입니다. 그렇다면 PostgreSQL에서는 어떻게 트리를 만들 수 있을까요?

WITH RECURSIVE tree AS (
    SELECT 1 AS level, 1 AS value
    UNION ALL
    SELECT level + 1, value + 2
    FROM tree
    WHERE level < 7
)
SELECT LPAD(' ', 7 - level) || LPAD('*', level * 2 - 1, '*')
FROM tree
UNION ALL
SELECT LPAD(' ', 5) || '***'
FROM tree
WHERE level < 4;
      *
     ***
    *****
   *******
  *********
 ***********
*************
     ***
     ***
     ***

PostgreSQL에서는 WITH RECURSIVE를 사용해서 재귀 쿼리를 만들 수 있습니다. 위의 쿼리는 루트 노드부터 시작해서 자식 노드를 순회하면서 쿼리를 실행합니다.

끝으로

SQL은 정말 다양한 기능을 가지고 있습니다. 이 글에서는 SQL의 일부 기능만을 소개했지만, 다음 글에서는 데이터 베이스를 이어서 다루어보겠습니다. 좀 더 어렵고 심층적인 수준 데이터 베이스를 다루어보겠습니다. 그럼 다음 글에서 뵙겠습니다. 😆

profile
안녕하세요 👏
FE Developer 죠지입니다.
githubgithubgithub