SQL with 절, row number , lag/lead 함수 사용

with 절은 이름이 부여된 서브쿼리

view 공통점

임시 테이블을 만든다.

view 차이점

view는 한 번 만들면 drop할 때까지 없어지지 않는다.

with은 쿼리문 안에서만 존재한다.

 

장점

재사용 가능

반복사용 시 성능향상

 

 

with ex1 as (
 select 'everyone' as str1
 from answer
)
select * from ex1;

 

str1 칼럼에 everyone이라는 내용으로 answer 테이블 행 갯수만큼 채워진다.

 

 

with 재사용

with ex1 as 
(
select 'everyone1' a from answer
union all
select 'everyone2' from answer
),
ex2 as
(
select 'everyone3' a from answer
union all
select a from ex1
)

select * from ex2;

union이나 union all은 세로 나열 데이터를 합칠 때 사용 

 

---

 

 

row_number( ) over ( order by )

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"   
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

 

row_number( ) over ( partition by order by )

SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD,  
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) 
  AS Row  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0  
ORDER BY TerritoryName;

 

 

partition by 와 order by 3개 예시

select 
big1, mid1, sm1
,row_number() over(partition by big1, mid1, sm1 order by big1, mid1, sm1) as rn
from table1;

	big1	mid1	sm1	rn
1	1	2	1	1
2	1	2	1	2
3	1	2	1	3
4	1	2	2	1
5	1	2	2	2
6	2	1	1	1
7	2	1	1	2
8	2	1	2	1
9	2	1	2	2

 

 

 

 

ROW_NUMBER(Transact-SQL) - SQL Server | Microsoft Docs

ROW_NUMBER(Transact-SQL) - SQL Server

---

lag 함수와 lead 함수

lag는 이전 값

lead는 다음 값

 

 

SELECT 
	a.product_nm
	,b.group_nm
	,a.price
	,LAG (a.price, 1) OVER
		(PARTITION BY b.group_nm
		ORDER BY a.price)
		AS prev_price
FROM product a, product_group b
where a.group_id = b.group_id;

row_number, lag, lead 함수 적용 방식 같다.

over를 걸어 partition by 써서 나누고 order by 로 정렬한 다음 함수 적용.

 

	product_nm	group_nm	price	prev_price
1	gram	laptop	1000000	null
2	thinkpad	laptop	1400000	1000000
3	macbook	laptop	2000000	1400000
4	galaxy	phone	1200000	null
5	iphone	phone	1400000	1200000