sql syntax

[seoyeong200]

window function + over clause

window function

• ROW_NUMBER(): 파티션 내의 각 행에 대한 고유 번호를 반환합니다.
• RANK(): 파티션 내의 행에 순위를 매기며, 동일한 값을 가진 행에 동일한 순위를 부여합니다.
• DENSE_RANK(): RANK()와 비슷하지만, 동일한 순위를 가진 행이 있을 때 순위에 공백이 생기지 않습니다.
  • leet#178
• NTILE(n): 파티션을 n개의 그룹으로 나누고, 각 행에 해당 그룹 번호를 반환합니다.
• LAG(): 현재 행의 이전 행 값을 반환합니다.
• LEAD(): 현재 행의 다음 행 값을 반환합니다.
• SUM(): 윈도우 내에서 합계를 계산합니다.
• AVG(): 윈도우 내에서 평균을 계산합니다.
• MAX(): 윈도우 내에서 최대 값을 계산합니다.
• MIN(): 윈도우 내에서 최소 값을 계산합니다.

LEAD & LAG

LEAD, LAG : 행 기준 이전행/다음행 값 참조하기 LEAD(): 현재 행에서 지정한 오프셋만큼 '뒤'에 있는 행의 값을 반환

LEAD(column_name, offset, default_value) 
      OVER (PARTITION BY partition_column ORDER BY order_column)

LAG(): 현재 행에서 지정한 오프셋만큼 '앞'에 있는 행의 값을 반환

LAG(column_name, offset, default_value) 
        OVER (PARTITION BY partition_column ORDER BY order_column)

- offset: 뒤로 몇 번째 행의 값을 참조할지 지정 (기본값은 1)
- default_value: 참조할 값이 없는 경우 사용할 기본값 (기본값은 NULL)
- PARTITION BY: 윈도우를 나누는 기준

over clause

윈도우 함수는 OVER 구문과 함께 사용됩니다. OVER 구문은 윈도우를 정의하며, 다음과 같은 요소를 포함할 수 있습니다:

• PARTITION BY: 데이터를 파티션으로 나눕니다.
• ORDER BY: 각 파티션 내의 행을 정렬합니다.
• ROWS / RANGE: 윈도우 프레임을 정의합니다.

examples

각 employee의 sale_amount에 대해 고유 번호 매기기

SELECT 
    employee, 
    sale_amount, 
    ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale_amount) AS row_num
FROM sales;

각 employee의 누적 sale_amount 계산하기

SELECT 
    employee, 
    sale_amount, 
    SUM(sale_amount) OVER (PARTITION BY employee ORDER BY id) AS cumulative_sales
FROM sales;

[seoyeong200]

user defined function

CREATE FUNCTION function_name (parameter_list) # parameter_list에 이름, 데이터 타입 지정해야 한다.
RETURNS return_type
BEGIN
    -- 함수 본문
    DECLARE variable_name datatype;

    -- SQL 문
    -- 변수 선언, 조건문, 반복문 등을 포함할 수 있습니다.

    RETURN return_value;
END;

example

# sum two numbers
CREATE FUNCTION add_numbers (a INT, b INT)
RETURNS INT
BEGIN
    DECLARE sum INT;
    SET sum = a + b;
    RETURN sum;
END;

# check given num is positive or negative or 0
CREATE FUNCTION check_number (num INT)
RETURNS VARCHAR(10)
BEGIN
    DECLARE result VARCHAR(10);

    IF num > 0 THEN
        SET result = 'Positive';
    ELSEIF num < 0 THEN
        SET result = 'Negative';
    ELSE
        SET result = 'Zero';
    END IF;

    RETURN result;
END;

[seoyeong200]

어라? (뿌듯

[seoyeong200]

union vs union all

UNION 중복 제거: UNION은 기본적으로 결과에서 중복된 행들을 제거합니다. 동일한 값이 여러 번 나타나면 한 번만 출력됩니다.

UNION ALL 중복 허용: UNION ALL은 중복된 행들을 제거하지 않고, 모든 결과를 그대로 반환합니다. 즉, 중복된 행들도 결과 집합에 포함됩니다.