데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

WITH gOrg( 날짜, 아이디, 레벨 )

(

SELECT '2013-01-01', 'AA', 1 UNION ALL

SELECT '2013-01-01', 'AA', 2 UNION ALL

SELECT '2013-01-02', 'AA', 2 UNION ALL

SELECT '2013-01-03', 'AA', 3 UNION ALL

SELECT '2013-01-10', 'AA', 4

)

, gl( 날짜, 아이디, 레벨 )

(

-- (1) 아이디가 2개 이상 있으면 잘 동작하지 않을 게 뻔해서... AA만 골라냄

-- 대충 짜놓고 나서 문제를 발견하고 주어진 예제가 AA 밖에 없었던 핑계를 살짝 탓해보며... 별로 고치고 싶지 않.. T,.T;;

SELECT 날짜, 아이디, 레벨

FROM gOrg

WHERE 아이디 = 'AA'

)

, aaa ( eday, 아이디, 날짜, dayMaxLvl )

(

SELECT *

FROM (

-- (6) 최초일(5)에 범위(4)만큼 각각 더해서 전체 기간에 대한 각각 날짜 구하기

SELECT DATEADD( DAY, num-1, glmd.minDay ) AS eday

FROM

(

-- (5) 최초일 구하기

SELECT MIN( gl.날짜 ) AS minDay

FROM gl

) AS glmd

CROSS JOIN

(

-- (2) 최초일과 끝일 간격 알아내기

SELECT DATEDIFF( DAY, MIN( 날짜 ), MAX( 날짜 ) ) AS rdays

FROM gl

) AS rng

INNER JOIN -- (4-1) (2)로 구한 범위만큼 숫자를 (3)에서 뽑기

(

-- (3) 대략 천여개쯤 되는 숫자 생성. 설마 부족하면 sys.all_columns와 cross join으로 수백만대로 늘리기

SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS num

FROM sys.all_columns

) AS nums ON rng.rdays + 1 >= nums.num -- (4-2) 시작날과 끝날을 모두 포함시키기 위한 + 1

) AS eachDay

LEFT OUTER JOIN -- (8) 주어진 기간 내에 있는 각각의 날짜(6)에 대해 결정할 수 있는 최대 레벨(7)을 구하기

(

-- (7) 날짜별 최대 레벨 구하기

SELECT 아이디, 날짜, MAX(레벨) AS dayMaxLvl

FROM gl

GROUP BY 아이디, 날짜

) AS glMaxLvl ON eachDay.eday = glMaxLvl.날짜

)

-- 위의 CTE까지 됐으면 다음의 SELECT문을 주석을 풀고 어디까지 구했는지 확인 (확인시 주석 아래의 SELECT까지 실행하면 오류나므로 주석을 푼 SELECT만 실행)

-- SELECT * FROM aaa;

-- 확인 했으면 다시 주석 달아 놓기

-- (9) 이빨빠진 즉, 아이디와 레벨이 NULL인 애들은 해당 날짜로부터 가장 최근의 레벨을 심어주기 위해 self-join

-- 이때 조인 조건은 나랑 날짜가 같거나 짝은 모든 애들

-- 안 돌려봤지만 join하지 않고 나랑 같거나 작은 애들 중에서 최대 값을 구해오는 subquery가 성능이 더 좋을 쑤도 있음.

-- 그래봐야 사람이 올릴 수 있는 레벨이 수백개가 최대일테니 뭐 걍 무시해도...

SELECT aa.eday, bb.아이디, MAX( bb.dayMaxLvl ) AS 그날최고레벨

FROM aaa as aa

INNER JOIN aaa as bb on aa.eday >= bb.날짜

GROUP BY aa.eday, bb.아이디

ORDER BY aa.eday

;

우욱님이 2013-01-21 23:13에 작성한 댓글입니다. Edit Ｘ