SQL 행 관련 순서함수(오프셋 함수) 완벽 정리 🚀

안녕하세요! 오늘은 SQL 윈도우 함수 중에서도 행의 순서를 기반으로 데이터를 처리하는 오프셋 함수(OFFSET Functions)에 대해 알아보겠습니다.

오프셋 함수는 특정 행을 기준으로 앞뒤에 위치한 행의 값을 쉽게 참조할 수 있도록 도와줍니다. 주로 데이터를 시계열적으로 분석하거나 비교할 때 매우 유용합니다.

🔍 오프셋 함수의 종류와 특징

SQL에서 가장 자주 사용하는 오프셋 함수는 크게 세 가지입니다:

1️⃣ LAG()

LAG() 함수는 현재 행에서 지정한 수만큼 앞에 있는 행의 값을 반환합니다.

사용법

LAG(컬럼명, offset, 기본값) OVER (ORDER BY 컬럼명)

• offset(생략 가능): 몇 번째 앞의 행을 참조할지 지정 (기본값 1)
• 기본값(생략 가능): 참조할 행이 없을 경우 반환할 값 (기본값 NULL)

예시

SELECT emp_name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary
FROM employee;

• 각 직원의 이전 직원의 급여를 보여줍니다. 첫 행의 경우 이전 행이 없으므로 0을 반환합니다.

2️⃣ LEAD()

LEAD() 함수는 현재 행에서 지정한 수만큼 뒤에 있는 행의 값을 반환합니다.

사용법

LEAD(컬럼명, offset, 기본값) OVER (ORDER BY 컬럼명)

• offset(생략 가능): 몇 번째 뒤의 행을 참조할지 지정 (기본값 1)
• 기본값(생략 가능): 참조할 행이 없을 경우 반환할 값 (기본값 NULL)

예시

SELECT emp_name, salary,
       LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS next_salary
FROM employee;

• 각 직원의 다음 직원의 급여를 표시합니다. 마지막 행은 다음 행이 없으므로 0을 반환합니다.

3️⃣ FIRST_VALUE(), LAST_VALUE()

• FIRST_VALUE(): 지정된 윈도우 범위 내의 첫 번째 행 값을 반환합니다.
• LAST_VALUE(): 지정된 윈도우 범위 내의 마지막 행 값을 반환합니다.

사용법

FIRST_VALUE(컬럼명) OVER (ORDER BY 컬럼명 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

예시

SELECT emp_name, salary,
       FIRST_VALUE(salary) OVER (ORDER BY hire_date
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_salary,
       LAST_VALUE(salary) OVER (ORDER BY hire_date
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_salary
FROM employee;

• 입사일 기준으로 처음 입사한 직원의 급여와 현재까지 마지막 직원의 급여를 각각 반환합니다.

⚠️ 주의사항

• 오프셋 함수를 사용할 때는 반드시 ORDER BY 절을 명시해야 합니다.
• FIRST_VALUE(), LAST_VALUE() 함수는 프레임절(Frame Clause)을 함께 사용할 때 정확한 결과를 얻을 수 있습니다.

🎯 요약 정리

SQL 윈도우 함수의 프레임절(Frame Clause) 완벽 이해하기 📈

안녕하세요, 오늘은 SQL의 강력한 기능 중 하나인 윈도우 함수(Window Functions)에서 자주 사용되는 프레임절(Frame Clause)에 대해 자세히 알아보겠습니다.

📌 프레임절(Frame Clause)이란?

프레임절은 윈도우 함수를 사용할 때 분석할 데이터의 범위를 지정하는 역할을 합니다. 윈도우 함수는 OVER 절을 이용해 특정 범위를 정의하는데, 이때 프레임절로 범위를 구체적으로 설정합니다.

기본 형태는 다음과 같습니다

윈도우함수() OVER (
    PARTITION BY 컬럼명
    ORDER BY 컬럼명
    [ROWS 또는 RANGE 프레임시작 AND 프레임종료]
);

🔍 ROWS와 RANGE의 차이점

1️⃣ ROWS

ROWS는 행의 물리적 위치를 기준으로 프레임을 정의합니다.

• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 현재 행을 기준으로 이전 행과 다음 행을 포함
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 첫 행부터 현재 행까지 포함

예시:

SELECT emp_name, salary,
       AVG(salary) OVER (
           ORDER BY salary
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS avg_salary
FROM employee;

• 각 직원의 급여를 기준으로 앞뒤 한 명씩(총 3명)의 급여 평균을 계산합니다.

2️⃣ RANGE

RANGE는 논리적인 값을 기준으로 프레임을 정의합니다. ORDER BY로 정렬된 값에 따라 프레임 범위가 결정됩니다.

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: ORDER BY의 값이 시작부터 현재 행까지 같은 모든 행을 포함합니다.

예시:

SELECT salary,
       SUM(bonus) OVER (
           ORDER BY salary
           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_bonus
FROM employee;

• 급여를 기준으로 누적된 보너스를 합산하여 나타냅니다. 급여가 동일한 모든 직원은 같은 프레임에 포함됩니다.

📝 프레임절 사용 시 주의사항

• ROWS를 사용할 때는 행 위치가 명확히 구분되지만, RANGE를 사용할 때는 동일 값이 여러 개일 경우 같은 프레임으로 묶입니다.
• 성능적인 측면에서 ROWS가 RANGE보다 일반적으로 더 빠르게 처리됩니다.

🚩 프레임절 자주 쓰는 표현

표현식 의미

💡 외우기 쉬운 꿀팁!

• ROWS는 “Row by Row(행 단위로)“를 생각하면 물리적 행 위치로 기억하기 쉬워요!
• RANGE는 “Logical RANGE(논리적 범위)“로 논리적 값을 기준으로 기억하세요.
• “PRECEDING(이전), CURRENT(현재), FOLLOWING(이후)” 단어 뜻 그대로 외우면 이해하기 편합니다.

🎯 요약 및 결론

• 프레임절은 윈도우 함수에서 분석 범위를 명확히 정의합니다.
• 물리적 행 위치를 명확히 다룰 때는 ROWS를, 논리적 값의 범위를 다룰 때는 RANGE를 사용합니다.
• 적절한 프레임 설정을 통해 원하는 데이터를 정확히 분석할 수 있습니다.

SQL NULL 관련 함수 완벽 정리 🌟

오늘은 SQL에서 자주 사용되면서도 초보자들이 헷갈려 하는 NULL 처리 함수들에 대해 알아보겠습니다.

SQL에서 NULL이란 ‘값이 없다’ 또는 ‘알 수 없는 값’을 의미합니다. 따라서 일반적인 값과는 다르게 처리해야 하는데요, 이때 유용하게 사용할 수 있는 함수들이 있습니다. 대표적인 NULL 처리 함수들을 하나씩 살펴볼까요?

🔍 1. NVL(컬럼명, 대체값)

NVL 함수는 Oracle에서 제공하는 함수로, 특정 컬럼이 NULL일 경우 지정한 다른 값으로 대체해줍니다.

사용법

SELECT NVL(COMM, 0)
FROM EMP;

의미: EMP 테이블의 COMM 컬럼 값이 NULL이라면 0으로 출력합니다.

🔍 2. IFNULL(컬럼명, 대체값)

MySQL/MariaDB에서 NVL 함수와 유사한 역할을 합니다.

사용법

SELECT IFNULL(COMM, 0)
FROM EMP;

의미: COMM 컬럼 값이 NULL이라면 0으로 표시합니다.

🔍 3. ISNULL(컬럼명, 대체값)

SQL Server에서 사용하는 NULL 대체 함수입니다.

사용법

SELECT ISNULL(COMM, 0)
FROM EMP;

의미: COMM 컬럼 값이 NULL일 때 0으로 변경하여 출력합니다.

🔍 4. COALESCE(컬럼1, 컬럼2, …, 컬럼n)

COALESCE 함수는 표준 SQL 함수로 여러 개의 컬럼 또는 표현식을 입력받아, NULL이 아닌 첫 번째 값을 반환합니다.

사용법

SELECT COALESCE(COMM, BONUS, 0)
FROM EMP;

의미: COMM 값이 NULL이라면 BONUS를 확인하고, BONUS도 NULL이라면 마지막 값 0을 출력합니다.

• COALESCE는 여러 개의 컬럼을 사용할 수 있어 NVL이나 IFNULL보다 확장성이 높습니다.

🔍 5. NULLIF(표현식1, 표현식2)

두 표현식을 비교하여 값이 같다면 NULL을 반환하고, 다르면 표현식1의 값을 반환합니다.

사용법

SELECT NULLIF(SAL, 0)
FROM EMP;

의미: SAL이 0일 경우 NULL로 처리하여 출력하고, 0이 아니면 SAL의 원래 값을 출력합니다.

🌟 각 함수별 DB 지원 정리

🎯 언제, 무엇을 쓰면 좋을까?

• Oracle 환경이라면 NVL을, MySQL이라면 IFNULL을 선택하세요.
• SQL Server에서는 ISNULL이 최적입니다.
• 여러 컬럼 중 첫 번째로 나타나는 NULL이 아닌 값을 원할 땐 COALESCE를 쓰세요!
• 특정 값이 나오면 강제로 NULL로 변환하고 싶다면 NULLIF가 딱입니다.

데이터베이스 설계 과정에서 테이블의 행(row)을 고유하게 식별하는 방법으로는 크게 본질식별자와 인조식별자가 있습니다. 각각의 식별자는 특징과 용도에 따라 적절히 활용됩니다. 오늘은 이 두 가지 식별자에 대해 알아보겠습니다.

1️⃣ 본질식별자

본질식별자는 테이블에 저장되는 데이터의 본질적인 특성을 기반으로 하여 정의되는 식별자입니다. 쉽게 말해, 업무적으로 자연스럽게 만들어지는 식별자입니다.

📌 특징

• 업무 프로세스에서 자연스럽게 생성됩니다.
• 테이블의 데이터 자체가 본질식별자를 포함하고 있습니다.
• 데이터의 의미를 직접적으로 나타내는 경우가 많습니다.

📖 예시

• 학번: 학생이 학교에 입학할 때 부여되는 번호로, 각 학생을 명확히 구별할 수 있습니다. 학번 자체만으로도 학생이라는 데이터의 본질적 특성을 반영하고 있습니다.
• 고객번호: 고객을 고유하게 구분하기 위해 업무적으로 생성되는 번호입니다.

2️⃣ 인조식별자

인조식별자는 데이터베이스 내에서 데이터를 쉽게 관리하기 위해 인위적으로 만들어진 식별자입니다. 업무 프로세스와 직접적으로 관련되지 않으며, 주로 데이터의 본질식별자가 복잡할 때 사용됩니다.

📌 특징

• 데이터베이스 시스템에서 자동으로 생성됩니다.
• 본질적인 데이터 특성과는 무관하게 운영됩니다.
• 일반적으로 순차적인 숫자나 UUID 등의 형태로 만들어집니다.

📖 예시

• 등록ID: 수강 기록 데이터를 관리할 때 시스템이 자동으로 1, 2, 3...과 같은 순차적인 번호를 부여합니다.
• 주문번호: 주문 엔티티가 고객번호+주문날짜+순번과 같이 복잡한 구성을 가지고 있을 때, 이를 간략화하고 쉽게 관리하기 위해 인조식별자로 단순화된 번호를 사용합니다.

인조식별자만으로는 데이터의 구체적인 내용을 알기 어렵습니다. 예를 들어, 등록ID만으로 특정 학생이 언제 어떤 과목을 수강했는지를 알 수 없으며, 반드시 학번, 학기, 과목코드 등의 다른 정보를 함께 사용해야 정확한 의미를 알 수 있습니다.

🔍 요약 정리

문제 설명

다음 SQL의 결과는 무엇일까요?

SELECT COUNT(*)
FROM T1
WHERE NOT EXISTS (
  SELECT 'X'
  FROM T2
  WHERE T1.COL1 = T2.COL1
);

T1 테이블

COL1

T2 테이블

COL1

① 2  ② 0  ③ 1  ④ 오류 발생

쿼리 분석 및 핵심 포인트

이 문제는 단순한 NOT EXISTS 서브쿼리 문제가 아니에요. NULL과의 비교 연산이 핵심 포인트입니다.

1.  NOT EXISTS란?

• NOT EXISTS (SELECT ... FROM T2 WHERE 조건)은 T2의 조건절을 만족하는 값이 존재하지 않을 때 참이 됩니다.
• 즉, T2에서 T1.COL1 = T2.COL1이 절대 참이 되지 않는 경우에만 해당 T1 행이 결과에 포함돼요.

2.  비교 연산자 = 와 NULL

여기서 주의할 점! SQL에서 NULL = 어떤 값은 무조건 UNKNOWN이 되고, 이는 WHERE 절에서 거짓(FALSE)으로 처리돼요.

예시:

• 10 = 10 → TRUE
• 10 = NULL → UNKNOWN → WHERE에서 걸러짐
• NULL = NULL → UNKNOWN

실제 동작 흐름 분석

T1의 각 행에 대해 서브쿼리를 돌려보자!

T1의 첫 번째 행: COL1 = 10

서브쿼리:

SELECT 'X' FROM T2 WHERE T1.COL1 = T2.COL1;
-- 즉, WHERE 10 = T2.COL1;

• T2에 10이 있음 → 조건 만족하는 행 있음 → EXISTS는 TRUE → NOT EXISTS는 FALSE
• → T1의 이 행은 제외됨

T1의 두 번째 행: COL1 = 20

서브쿼리:

SELECT 'X' FROM T2 WHERE 20 = T2.COL1;

• T2에는 20이 없음
• 20 = NULL → 비교 불가(UNKNOWN)→ 이 행은 결과에 포함됨
• → 조건을 만족하는 행이 하나도 없음 → EXISTS는 FALSE → NOT EXISTS는 TRUE

정답

→ 조건을 만족하는 행은 딱 T1의 두 번째 행 1개

③ 1

마무리 요약 정리

NULL이 있는 컬럼과 비교하는 서브쿼리에서는, 절대 = 조건이 항상 원하는 결과를 주지 않음! EXISTS/NOT EXISTS에서 더더욱 주의!

💡 문제

다음 SQL문 중에서 다른 결과를 반환하는 것은 무엇일까요?

-- 1번
SELECT * FROM TBL WHERE V1 = 'A' AND V2 IN ('T1', 'T2', 'T3');

-- 2번
SELECT * FROM TBL WHERE V1 = 'A' AND V2 = 'T1' OR V2 = 'T2' OR V2 = 'T3';

-- 3번
SELECT * FROM TBL WHERE (V1, V2) IN (('A', 'T1'), ('A', 'T2'), ('A', 'T3'));

-- 4번
SELECT * FROM TBL WHERE V1 = 'A' AND (V2 = 'T1' OR V2 = 'T2' OR V2 = 'T3');

📌 핵심 개념 요약

1. SQL 논리 연산자 우선순위

• NOT > AND > OR
• 괄호가 없으면 AND가 먼저 계산됨!

2. 튜플 비교 (row value constructor)

• (컬럼1, 컬럼2) IN ((값1, 값2), ...) 형식
• 컬럼 쌍이 정확히 매칭될 때만 TRUE

📊 각 쿼리 분석

✅ 1번

SELECT * FROM TBL WHERE V1 = 'A' AND V2 IN ('T1', 'T2', 'T3');

• V1 = 'A'이면서, V2가 T1, T2, T3 중 하나일 때만 조회
• ✅ 조건이 명확함
• 정상적으로 필터링된 결과

❌ 2번 (정답)

SELECT * FROM TBL WHERE V1 = 'A' AND V2 = 'T1' OR V2 = 'T2' OR V2 = 'T3';

• 괄호 없음! 그래서 아래처럼 해석됨👇

(V1 = 'A' AND V2 = 'T1') OR V2 = 'T2' OR V2 = 'T3'

👉 이렇게 되면?

• V1='A'이고 V2='T1' → ✅
• 하지만 V2='T2' 또는 V2='T3'인 행은 V1이 무엇이든 조회됨! → ❌
• 즉 결과가 달라짐

✅ 3번

SELECT * FROM TBL WHERE (V1, V2) IN (('A', 'T1'), ('A', 'T2'), ('A', 'T3'));

• (V1, V2) 쌍이 (‘A’, ‘T1’), (‘A’, ‘T2’), (‘A’, ‘T3’) 중 하나일 때만 조회
• 1번 쿼리와 논리적으로 완전 동일한 결과
• 튜플 비교 방식의 간결한 표현

✅ 4번

SELECT * FROM TBL WHERE V1 = 'A' AND (V2 = 'T1' OR V2 = 'T2' OR V2 = 'T3');

• 괄호로 OR 조건을 묶었기 때문에

V1 = 'A' AND V2 IN ('T1', 'T2', 'T3')

와 동일한 의미입니다.

SQL에서는 AND가 OR보다 우선순위가 높기 때문에, 괄호가 없으면 해석이 달라질 수 있습니다.
위의 2번 쿼리는 V1='A' 조건이 일부 OR 조건에만 적용되므로 불필요한 레코드까지 조회됩니다.
정확한 조건식 표현을 위해 괄호로 논리 구조를 명확히 하는 것이 중요합니다!

🧠 꿀팁 암기

🎯 논리 연산자 우선순위: NOT > AND > OR
🎯 괄호 없이 쓰면 예기치 않은 결과 가능성 있음
🎯 튜플 비교는 깔끔하고 안전한 대안!

SQLD 문제

다음 중애서 도메인(Domain)에 대한 특징으로 옳지 않은 것은?

1) 릴레이션의 속성에 대한 데이터 타입과 크기이다.

2) 속성에 대하여 NOT NULL 제약사항을 설정하여 NULL 값을 허용하지 않는다.

3) 속성에 값을 입력할 때 CHECK 기능을 사용해서 입력값을 검사한다.

4) 하나의 릴레이션과 관계된 다른 릴레이션의 FK(Foreign Key) 제약조건이다.

도메인(Domain) 이란 릴레이션(=테이블)의 속성(컬럼)이 가질 수 있는 값들의 집합(타입, 범위, 형식)을 말합니다.

예를 들어,

emp_age NUMBER(3)

➡️ 여기에서 emp_age는 속성, NUMBER(3)은 도메인(정수형 숫자 3자리까지) 이라고 보면됩니다.

✍️ 보기 분석

✔ 1) 릴레이션의 속성에 대한 데이터 타입과 크기이다.

정답! ✅

도메인은 컬럼의 데이터 타입과 크기를 포함하는 개념입니다.

• 예) VARCHAR2(20) → 문자형 20자 = 도메인 OK

✔ 2) 속성에 대하여 NOT NULL 제약사항을 설정하여 NULL 값을 허용하지 않는다.

정답! ✅

도메인은 NOT NULL 같은 제약조건도 포함할 수 있습니다.

• 제약조건도 도메인의 일부로 간주함.
• 도메인을 정의할 때, “이 컬럼은 반드시 값이 있어야 한다”는 것도 포함 가능.

✔ 3) 속성에 값을 입력할 때 CHECK 기능을 사용해서 입력값을 검사한다.

정답! ✅

도메인 정의 시 CHECK 제약조건으로 값의 범위나 조건을 설정할 수 있습니다.

age NUMBER(3) CHECK (age BETWEEN 0 AND 120)

➡️ 나이는 0~120 사이 값만 허용 → 도메인의 일부임.

❌ 4) 하나의 릴레이션과 관계된 다른 릴레이션의 FK(Foreign Key) 제약조건이다.

오답! ❌❌❌

요게 바로 “도메인의 개념이 아닌 것”, 즉 정답입니다.

🔻왜냐면:

• FK(외래키)는 두 릴레이션 간의 관계를 정의하는 것이고
• 도메인은 단일 속성의 데이터 형식이나 제약조건에 대한 것입니다.
• 즉, 도메인은 “속성”의 내부적 제약조건, FK는 “릴레이션 간” 외부적 제약조건입니다.

SQL에서 그룹함수는 GROUP BY와 함께 쓰지만,
윈도우 함수(Window Function) 는 행(row)을 유지하면서,
그 위에 순위, 누적합, 평균, 비율을 매겨주는 강력한 분석 도구에요!

🧪 실습용 테이블 (Oracle 기준)

CREATE TABLE employees (
  emp_id     NUMBER PRIMARY KEY,
  emp_name   VARCHAR2(50),
  dept_id    NUMBER,
  salary     NUMBER
);

INSERT INTO employees VALUES (1, '홍길동', 10, 3000);
INSERT INTO employees VALUES (2, '김영희', 10, 2800);
INSERT INTO employees VALUES (3, '이철수', 10, 3000);
INSERT INTO employees VALUES (4, '최지우', 20, 3500);
INSERT INTO employees VALUES (5, '박민수', 20, 2700);
INSERT INTO employees VALUES (6, '서지훈', 30, 4000);

 
✔️ 설정 포인트

• 3개 부서 (dept_id)
• 동일 급여자 있음
• 부서별 순위/합계/비율 실습 가능

1️⃣  순위 함수 (Ranking Functions)

SELECT emp_name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num
FROM employees;

📌 PARTITION BY는 부서별로 그룹화
📌 ORDER BY는 급여 기준 정렬

2️⃣  집계 함수 (Aggregate Functions over Window)

SELECT emp_name, dept_id, salary,
       SUM(salary) OVER (PARTITION BY dept_id) AS dept_total,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max,
       MIN(salary) OVER (PARTITION BY dept_id) AS dept_min
FROM employees;

 
🧠 설명:
각 사원에 대해 부서별 총합, 평균, 최댓값, 최솟값을 행 단위로 함께 출력
✔️ GROUP BY 없이도 집계 가능!
✔️ 행을 유지하면서 통계도 동시에 계산 가능함

3️⃣  행 순서 함수 (Row Navigation)

SELECT emp_name, dept_id, salary,
       LAG(salary, 1) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS prev_salary,
       LEAD(salary, 1) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS next_salary
FROM employees;

 
🧠 설명

• LAG() : 현재 행 기준으로 이전 행의 값
• LEAD() : 현재 행 기준으로 다음 행의 값

 
📌 사용 예

• 전월 대비 매출 비교
• 이전/다음 행과의 차이 분석

4️⃣  비율 함수 (Ratio Functions)

SELECT emp_name, dept_id, salary,
       RATIO_TO_REPORT(salary) OVER (PARTITION BY dept_id) AS dept_salary_ratio
FROM employees;

 
🧠 설명:
각 사원의 급여가 자신의 부서 내에서 차지하는 비율을 계산함
→ 부서 내에서 내가 몇 %나 차지하고 있는지 분석할 때 유용!
 
📌 예) 부서별 급여 합계가 100이라면, 내 급여는 그 중 몇 퍼센트?