DataBaser.Net: 이전Row접근하기

1 개요
2 테스트 데이터 만들기
3 SQL1: self join을 이용한 방법
4 SQL2: cross join과 group by를 이용한 방법
5 이런 비슷한 것도..
6 결론

1 개요 #

SQL을 작성하다 보면 이전 Row 또는 바로 다음 Row를 접근 할 필요가 있다. 예를들어, 기간 업무를 수행하는 어플리케이션에서 로그인, 로그아웃에 대한 기록을 남긴다고 가정해 보자. 테이블은 다음과 같은 구조일 것이다.

empid      log_dt                  log_type
---------- ----------------------- --------
yasicom    2007-05-30 22:11:33.570 1
mkex       2007-05-30 22:11:34.560 1
yasicom    2007-05-30 22:11:37.577 0
endless    2007-05-30 22:11:41.570 1
soma       2007-05-30 22:11:43.563 1
mkex       2007-05-30 22:11:48.560 0
yasicom    2007-05-30 22:11:59.557 1
soma       2007-05-30 22:12:03.563 0
endless    2007-05-30 22:12:04.553 0
endless    2007-05-30 22:23:17.363 1

만약 empid별 시스템의 사용 시간을 알고 싶다면 어떻게 SQL을 작성 할 것인가? 이런 경우 필자가 아는 몇 가지 솔루션을 소개하도록 하겠다. (참고: log_type = 1 이면 로그인, 0 이면 로그아웃)

[edit]

2 테스트 데이터 만들기 #

if object_id('tempdb..#temp') is not null
        drop table #temp
 
create table #temp(
        empid varchar(10)
,       log_dt datetime
,       log_type bit
)

insert #temp values('yasicom', getdate(), 1)
waitfor delay '00:00:01'

insert #temp values('mkex', getdate(), 1)
waitfor delay '00:00:03'

insert #temp values('yasicom', getdate(), 0)
waitfor delay '00:00:04'

insert #temp values('endless', getdate(), 1)
waitfor delay '00:00:02'

insert #temp values('soma', getdate(), 1)
waitfor delay '00:00:05'

insert #temp values('mkex', getdate(), 0)
waitfor delay '00:00:11'

insert #temp values('yasicom', getdate(), 1)
waitfor delay '00:00:04'

insert #temp values('soma', getdate(), 0)
waitfor delay '00:00:01'

insert #temp values('endless', getdate(), 0)
waitfor delay '00:00:05'

insert #temp values('endless', getdate(), 1)
go

[edit]

3 SQL1: self join을 이용한 방법 #

--1. 먼저 임시 테이블에 넣어서 일련번호를 생성한다. 정렬 순서가 맞아야 한다. 
--   2005 버전의 경우는 row_number()함수를 이용한다. 임시테이블에 넣기 귀찮으므로
--   row_number()를이용하겠다. 

set statistics io on
with tmp(rownum, empid, log_dt, log_type)
as
(
	select
		row_number() over(order by empid, log_dt, log_type desc) rownum
	,	empid
	,	log_dt
	,	log_type
	from #temp
)
select 
	a.empid
,	sum(datediff(ss, a.log_dt, b.log_dt)) usedtime
from tmp a inner join tmp b
on a.rownum = b.rownum - 1 -- 여기가 핵심이다.
and a.empid = b.empid
where cast(a.log_type as varchar) + cast(b.log_type as varchar) = '10'
group by a.empid
go

[edit]

4 SQL2: cross join과 group by를 이용한 방법 #

with tmp(rownum, empid, log_dt, log_type)
as
(
	select
		row_number() over(order by empid, log_dt, log_type desc) rownum
	,	empid
	,	log_dt
	,	log_type
	from #temp
)
select
	empid
,	sum(datediff(ss, login_dt, logout_dt)) usedtime
from (
	select 
		a.empid
	,	min(case when b.id = 1 and log_type = 1 then log_dt end) login_dt
	,	min(case when b.id = 2 and log_type = 0 then log_dt end) logout_dt
	from tmp a cross join (select 1 id union all select 2) b
	group by 
		a.empid
	,	case when b.id = 1 then rownum else rownum - 1 end --여기가 핵심이다.
) t
group by empid
go

[edit]

5 이런 비슷한 것도.. #

--WITH Game
--AS
--(
--SELECT 'A' CharName, 1 No1, 1 Val01, 0 Val02, 1 Val03 UNION ALL
--SELECT 'B' CharName, 2 No1, 0 Val01, 0 Val02, 0 Val03 UNION ALL
--SELECT 'C' CharName, 3 No1, 0 Val01, 2 Val02, 1 Val03 UNION ALL
--SELECT 'D' CharName, 4 No1, 1 Val01, 1 Val02, 0 Val03 UNION ALL
--SELECT 'E' CharName, 5 No1, 2 Val01, 2 Val02, 1 Val03 UNION ALL
--SELECT 'F' CharName, 6 No1, 1 Val01, 0 Val02, 2 Val03 UNION ALL
--SELECT 'G' CharName, 7 No1, 0 Val01, 2 Val02, 0 Val03 UNION ALL
--SELECT 'H' CharName, 8 No1, 1 Val01, 0 Val02, 2 Val03 UNION ALL
--SELECT 'I' CharName, 9 No1, 0 Val01, 2 Val02, 2 Val03 UNION ALL
--SELECT 'J' CharName, 10 No1, 1 Val01, 0 Val02, 1 Val03 UNION ALL
--SELECT 'K' CharName, 11 No1, 0 Val01, 2 Val02, 2 Val03 UNION ALL
--SELECT 'L' CharName, 12 No1, 1 Val01, 2 Val02, 0 Val03 UNION ALL
--SELECT 'M' CharName, 13 No1, 0 Val01, 1 Val02, 1 Val03 UNION ALL
--SELECT 'N' CharName, 14 No1, 2 Val01, 0 Val02, 0 Val03 UNION ALL
--SELECT 'O' CharName, 15 No1, 1 Val01, 2 Val02, 1 Val03 UNION ALL
--SELECT 'P' CharName, 16 No1, 2 Val01, 1 Val02, 2 Val03
--)
--SELECT * INTO #Temp 
--FROM Game

SELECT
	GroupNo
,	MIN(CASE WHEN No1%2=1 THEN No1 END) NoA
,	MIN(CASE WHEN No1%2=1 THEN CharName END) A
,	MIN(CASE WHEN No1%2=1 THEN Val01 END) V1
,	MIN(CASE WHEN No1%2=1 THEN Val02 END) V2
,	MIN(CASE WHEN No1%2=1 THEN Val03 END) V3
,	MIN(CASE WHEN No1%2=0 THEN No1 END) NoB
,	MIN(CASE WHEN No1%2=0 THEN CharName END) B
,	MIN(CASE WHEN No1%2=0 THEN Val01 END) V4
,	MIN(CASE WHEN No1%2=0 THEN Val02 END) V5
,	MIN(CASE WHEN No1%2=0 THEN Val03 END) V6
FROM (
	SELECT 
		No1
	,	CharName
	,	Val01
	,	Val02
	,	Val03
	,	(Row_NUmBER() OVER(ORDER BY No1)+1)/2 GroupNo
	FROM #Temp
) T
GROUP BY
	GroupNo

[edit]

6 결론 #

두 쿼리의 비용은 비슷하다. 현재로써는 그래도 2번째 쿼리가 비용은 더 적게 든다. 비용이 비슷하고, 성능이 비슷하다면 어떤 것을 쓰겠는가? 시스템의 리소스에 여유가 있다면 첫 번째 쿼리를 권하고 싶다. 왜냐하면 좀 더 이해하기 쉽고 명확하기 때문이다. 그러므로 유지보수 측면에서 보면 유리하다. 만약 그래도 테이블을 2번 읽는 것이 꿉꿉하다면 2번째 방법을 사용하기 바란다.

Contents

1 개요 #

2 테스트 데이터 만들기 #

3 SQL1: self join을 이용한 방법 #

4 SQL2: cross join과 group by를 이용한 방법 #

5 이런 비슷한 것도.. #

6 결론 #