ROWS & RANGE (feat. Window Function) - MySQL

ROWS & RANGE - 윈도우 함수의 범위 지정 방식

저번에 윈도우 함수에 대해서 공부했다. 그러다 ROWS 와 RANGE에 대해 궁금해져서 오늘은 그걸 공부해보려고 한다.

 

- ROWS vs RANGE 의 정의와 차이

 

- ROWS 와 RANGE의 차이 예시

먼저 RANGE의 예시이다.

 

예시 테이블 :

id	salesperson	amount
1	A	100
2	A	100
3	A	200
4	A	300

 

-- 누적합 계산
SELECT
	id,
    amount,
    SUM(amount) OVER (
    	ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS range_sum
FROM sales

 

출력 결과 :

id	amount	range_sum
1	100	200
2	100	200
3	200	400
4	300	700

 

100이 2개로 중복된다. RANGE는 값이 같으면 같은 범위로 인식해서 둘 다 포함해서 계산한다.

 

 

다음은 ROWS의 예시이다.

예시 테이블 :

id	salesperson	amount
1	A	100
2	A	100
3	A	200
4	A	300

 

-- 누적합 계산
SELECT
	id,
    amount,
    SUM(amount) OVER (
    ORDER BY amount
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS row_sum
FROM sales

출력 결과 :

id	amount	row_sum
1	100	100
2	100	200
3	200	400
4	300	700

 

ROWS 는 정렬된 순서에서 몇 번째 행인지로 범위를 설정하기 때문에 중복값을 같은값으로 생각하지 않는다.

 

 

 

- 왜 사용하는 가?

• ROWS 는 정확한 순서 기준 누적/비교를 위해 사용된다. 예를들어 거래번호, 시간순이 있다.
• RANGE는 값 기준 누적/비교를 위해 사용된다. 예를들어 금액 누적 합계, 같은 값끼리 처리가 있다.

 

 

- 자주 쓰는 범위

 

 

- 실전 예제 문제

 

1. orders 테이블에서 고객(user_id)별로 날짜순으로 정렬했을 때, 자기 자신과 이전 2건의 주문까지의 평균 금액을 구하라.

 

orders 테이블 :

order_id	user_id	order_date	amount
1	A	2024-01-01	100
2	A	2024-01-05	200
3	A	2024-01-10	300
4	A	2024-01-15	400

SELECT
	user_id,
    order_date,
    amount,
    AVG(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS avg_amount
FROM orders

 

출력 결과 :

order_date	amount	avg_amount
2024-01-01	100	100.0
2024-01-05	200	150.0
2024-01-10	300	200.0
2024-01-15	400	300.0

 

 

 

2. orders 테이블에서 고객(user_id)별로 주문금액(amount) 기준으로 정렬했을 때, 자기 금액 기준으로 50원 이내의 금액(자기 금액 - 50 ~ 자기 금액)을 가진 주문들의 평균 금액을 구하라.

 

orders 테이블 :

order_id	user_id	order_date	amount
1	A	2024-01-01	100
2	A	2024-01-05	130
3	A	2024-01-10	140
4	A	2024-01-15	200

 

SELECT
	user_id,
    order_date,
    amount,
    AVG(amount) OVER (
    PARTITION BY user_id,
    ORDER BY amount
    RANGE BETWEEN 50 PRECEDING AND CURRENT ROW
    ) AS avg_amount
FROM orders

 

출력 결과 :

order_date	amount	avg_amount
2024-01-01	100	100.0
2024-01-05	130	115.0
2024-01-10	140	123.3
2024-01-15	200	200.0

---

 

- 실전 문제

 

이제 GPT를 활용해서 실전 문제를 만들고, 채점과 피드백을 요청했다.

 

🔸 문제 1.

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  amount   INT
);

INSERT INTO orders VALUES
(1, 101, 200),
(2, 102, 500),
(3, 103, 150),
(4, 104, 300),
(5, 105, 400);

 

문제 설명
전체 주문의 평균 금액보다 큰 주문만 출력하라

 

사용 테이블: orders
출력 컬럼: order_id, user_id, amount
조건: 전체 주문의 평균 금액보다 큰 주문만

 

SELECT
	order_id,
    user_id,
    amount
FROM ( # 서브쿼리
  SELECT 
  *,
  AVG(amount) OVER ( # 윈도우 함수
      ORDER BY amount
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      ) AS avg_amount
  FROM orders
  ) AS t
WHERE amount > avg_amount

 

 

🔸 문제 2.

CREATE TABLE scores (
  student_id INT,
  subject    VARCHAR(10),
  score      INT
);

INSERT INTO scores VALUES
(1, 'math', 85),
(2, 'math', 90),
(3, 'math', 85),
(4, 'math', 95),
(5, 'math', 80),
(6, 'kor', 95),
(7, 'kor', 80);

문제 설명
수학 점수를 기준으로 등수를 매겨 상위 3등 이내 학생만 출력하라

 

사용 테이블: scores
출력 컬럼: student_id, score, rn
조건: subject = 'math', 등수 3위 이내

 

SELECT
	student_id,
    score,
    RANK() OVER (
      PARTITION BY subject
      ORDER BY score DESC
      ) AS rank
FROM scores
WHERE rank > 3
	AND subject = 'math'

 

 

🔸 문제 3.

CREATE TABLE user_logins (
  login_id   INT,
  user_id    INT,
  login_date DATE
);

INSERT INTO user_logins VALUES
(1, 101, '2024-01-01'),
(2, 101, '2024-01-03'),
(3, 101, '2024-01-05'),
(4, 102, '2024-01-02'),
(5, 102, '2024-01-06');

 

문제 설명
유저별로 가장 최근 로그인 기록만 출력하라

 

사용 테이블: user_logins
출력 컬럼: user_id, login_date
조건: 유저마다 login_date가 가장 최신인 행 1건만

 

SELECT
	user_id,
    login_date
FROM (
  SELECT
  *,
  MAX(login_date) OVER (
      PARTITION BY user_id
      ORDER BY login_date DESC
      ) AS new_login
  FROM user_logins
  ) AS t
WHERE login_date = new_login

 

 

🔸 문제 4.

CREATE TABLE products (
  product_id INT,
  price      INT
);

INSERT INTO products VALUES
(1, 300),
(2, 100),
(3, 400),
(4, 200),
(5, 500),
(6, 350);

 

문제 설명
가격을 기준으로 제품을 **3개 등급(분위)**으로 나누고 등급을 함께 출력하라

 

사용 테이블: products
출력 컬럼: product_id, price, tier
조건: 가격 기준 3분위(tier 값 1‒3)

 

SELECT
	product_id,
    price,
    NTILE(3) OVER (
      ORDER BY price DESC
      ) AS tier
FROM products

 

 

🔸 문제 5.

CREATE TABLE orders (
  order_id   INT,
  user_id    INT,
  order_date DATE,
  amount     INT
);

INSERT INTO orders VALUES
(1, 101, '2024-01-01', 100),
(2, 101, '2024-01-03', 150),
(3, 101, '2024-01-05', 130),
(4, 102, '2024-01-02', 200),
(5, 102, '2024-01-04', 180);

 

문제 설명
각 유저의 주문별로 이전 주문 금액과 다음 주문 금액을 함께 출력하라

 

사용 테이블: orders
출력 컬럼: user_id, order_date, amount, prev_amount, next_amount
조건: prev_amount는 직전 주문 금액, next_amount는 다음 주문 금액

 

SELECT
	user_id,
    order_date,
    amount,
    LAG(amount) OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ) AS prev_amount,
    LEAD(amount) OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ) AS next_amount
FROM orders

 

 

 

2번 문제를 틀렸다. 이유는 다음과 같다.

SQL 쿼리문은 엄연한 순서가 존재한다.

" FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY " 이것이 바로 그 순서이다.

따라서 내가 앞에 작성한 코드는 SELECT 절에서 윈도우함수로 계산한 RANK 값을 WHERE 절에서 사용하지 못해 "UNKNOWN COLUMN" 이라는오류가 발생하게 된다. 그렇기 때문에 서브쿼리를 이용하여 새로운 테이블을 만든 뒤에 WHERE 절에서 RANK 값을 호출해야 한다. 수정한 코드는 아래와 같다.

SELECT
	student_id,
    subject,
    score,
    rn
FROM (
  SELECT
  	*,
  	RANK() OVER (
      PARTITION BY subject
      ORDER BY score DESC
      ) AS rn
  FROM scores
  ) AS t
WHERE rn <= 3 AND subject = 'math'