[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
33. 조인과 집계 데이터 - 17. LAG, LEAD 함수
34. 조인과 집계 데이터 - 18. 실습 문제 - 1
35. 조인과 집계 데이터 - 19. 실습 문제 - 2
[강의내용]
33. 조인과 집계 데이터 - 17. LAG, LEAD 함수
해당 집합안에서 특정 컬럼의 이전 행의 값(LAG) 혹은 다음 행의 값(LEAD)을 구한다.
[실습1] LAG함수
LAG()는 특정 컬럼의 이전 행(한칸 위의 행)의 값을 찾는다
SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE,
LAG (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE) AS PREV_PRICE,
A.PRICE - LAG (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
AS CUR_PREV_DIFF
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
prev_price 컬럼을 살펴보면
PARTITION별(GROUP_NAME별)로 price 컬럼에서 한칸씩 아래로 밀린 것을 볼 수 있다.
Sony VAIO 제품의 경우 이전 행이 없기 때문에 LAG(,1)을 걸어도 NULL값이 나온다.
Microsoft Lumia와 Kindle Fire 제품들도 마찬가지이다.
cur_prev_diff 컬럼은 price - prev_price의 결과이다.
숫자와 NULL의 연산은 그대로 NULL을 리턴하는 것을 확인할 수 있다.
[실습2] LEAD함수
LEAD()는 특정 컬럼의 다음 행(한칸 아래의 행)의 값을 찾는다
SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE,
LEAD (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE) AS NEXT_PRICE,
A.PRICE - LEAD (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
AS CUR_NEXT_DIFF
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
next_price 컬럼은 price 컬럼이 위로 한칸씩 밀린 것을 알 수 있다.
Laptop 그룹에서 HP Elite 제품의 price 다음 행이 없기 때문에 next_price 값이 NULL이 나왔다.
iPhone, iPad도 마찬가지이다.
cur_next_diff는 price - next_price의 결과이다.
LAG()과 LEAD는 일일매출의 차이를 비교하는 경우 등에 활용할 수 있을것 같다.
실무에서 자주 쓰이는 함수라고 한다.
34. 조인과 집계 데이터 - 18. 실습 문제 - 1
[문제]
RENTAL 테이블을 이용하여 연, 연월, 연월일, 전체 각각의 기준으로 RENTAL_ID 기준 렌탈이 일어난 횟수를 출력하라.
(전체 데이터 기준으로 모든 행을 출력)
rental 테이블의 rental_date 컬럼을 살펴보면
데이터 타입은 date이며, 그중에서도 timestamp이기 때문에 시각까지 존재하는 것을 알 수 있다.
이 값을 연, 월, 일로 바꿔주어야 한다.
TO_CHAR()를 쓰면 된다.
[풀이]
SELECT TO_CHAR(rental_date, 'YYYY') AS year,
TO_CHAR(rental_date, 'YYYYMM') AS year_month,
TO_CHAR(rental_date, 'YYYYMMDD') AS year_month_date,
COUNT(rental_id)
FROM rental r
GROUP BY ROLLUP (year, year_month, year_month_date);
TO_CHAR(date 컬럼, 'YYYY')를 통해 문자열로 바꿔주었다.
'YYYY/MM'으로 지정해주면 2005/05 처럼 출력할 수도 있다.
또한, 'YYYY년'으로 지정해주면 출력값은 2005년 이 된다.
ROLLUP()절에는 알리아스를 사용할 수 있다.
35. 조인과 집계 데이터 - 19. 실습 문제 - 2
[문제]
RENTAL과 CUSTOMER 테이블을 이용하여 현재까지 가장 많이 RENTAL을 한 고객의
고객ID, 렌탈순위, 누적렌탈횟수, 이름을 출력하라.
[내 풀이]
SELECT c.customer_id, rank() over(ORDER BY count) ranking,
t1.count, c.first_name, c.last_name
FROM customer c, (
SELECT customer_id, count(rental_id)
FROM rental r
GROUP BY customer_id
ORDER BY count DESC
LIMIT 1) AS t1
WHERE c.customer_id = t1.customer_id;
rank() over()와 count()를 동시에 SELECT하지 못할것 같아서 서브쿼리 안에 count를 집어 넣었다.
그리고 리소스 낭비를 줄이려는? 의도로 서브쿼리 절에서 LIMIT 1을 걸어주었다.
하지만 서브쿼리없이 작성도 가능하다.
[모범 답안]
SELECT a.customer_id,
ROW_NUMBER() OVER (ORDER BY count(a.rental_id) desc) AS rental_rank,
count(*) rental_count,
max(b.first_name) AS first_name,
max(b.last_name) AS last_name
FROM rental a, customer b
WHERE a.customer_id = b.customer_id
GROUP BY a.customer_id
ORDER BY rental_rank
LIMIT 1;
결과는 동일하다.
row_number() over()를 쓰면, GROUP BY에 적용되지 않는 효과가 생긴다.
그래서 다음에 오는 count()를 안심하고 쓸 수 있다.
하지만 집계함수 뒤에 일반 컬럼이 오면 에러가 발생한다.
따라서 이름과 성 컬럼에 집계함수인 max()를 걸어주었다.
customer_id 하나당 이름과 성은 하나만 존재하는 고유한 값이다 (사람의 이름과 성이 여러개일 수 없는 것처럼)
따라서 max를 걸어주어도 데이터가 1개이기에 그대로 출력된다.