조인과 윈도우 함수 이해

Source: Dev.to

Joins

조인은 두 개 이상의 테이블에서 관련된 열을 기준으로 행을 결합할 수 있게 해줍니다.

INNER JOIN

두 테이블 모두에서 일치하는 값이 있는 행만 반환합니다.

-- Syntax
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- Example
SELECT
    c.name,
    o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

LEFT JOIN

왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환합니다. 오른쪽 테이블에 일치하지 않는 행은 NULL을 포함합니다.

-- Syntax
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- Example
SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

RIGHT JOIN

오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 반환합니다. 왼쪽 테이블에 일치하지 않는 행은 NULL을 포함합니다.

-- Syntax
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- Example
SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

FULL JOIN

왼쪽 또는 오른쪽 테이블 중 하나에 일치하는 행이 있으면 모든 행을 반환합니다. 반대쪽 테이블에 일치하지 않는 행은 NULL을 포함합니다.

-- Syntax
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

-- Example
SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id;

CROSS JOIN

두 테이블의 카르테시안 곱을 생성합니다; 첫 번째 테이블의 모든 행이 두 번째 테이블의 모든 행과 짝을 이룹니다.

-- Syntax
SELECT columns
FROM table1
CROSS JOIN table2;

-- Example
SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM customers c
CROSS JOIN orders o;

Window Functions

윈도우 함수는 현재 행과 어떤 식으로든 관련된 테이블 행 집합에 대해 계산을 수행합니다. 집계 함수와 달리 행을 축소하지 않으며, 원래 행 수가 유지됩니다.

General Syntax

window_function([arguments]) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC | DESC], ...]
    [frame_clause]
)

Categories

Aggregate Window Functions

정의된 윈도우에 대해 집계 계산을 수행합니다.

-- Example: total quantity per customer
SELECT
    o.customer_id,
    o.order_date,
    c.first_name,
    c.last_name,
    o.quantity,
    SUM(o.quantity) OVER (PARTITION BY o.customer_id) AS customer_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

주요 함수: SUM(), AVG(), COUNT(), MIN(), MAX().

Ranking Window Functions

정렬 기준에 따라 행에 순위나 위치를 부여합니다.

-- ROW_NUMBER()
SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

-- RANK()
SELECT
    employee_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- DENSE_RANK()
SELECT
    employee_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- NTILE()
SELECT
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_group
FROM employees;

Value (Navigation) Window Functions

같은 윈도우 내 다른 행의 값을 접근합니다.

-- LAG(): value from the previous row
SELECT
    order_id,
    quantity,
    LAG(quantity) OVER (ORDER BY order_id) AS previous_quantity
FROM orders;

-- LEAD(): value from the next row
SELECT
    order_id,
    quantity,
    LEAD(quantity) OVER (ORDER BY order_id) AS next_quantity
FROM orders;

이 함수들은 결과 집합을 축소하지 않고 행을 동료와 비교해야 하는 분석 쿼리에서 특히 유용합니다.