LAG 함수 질문이 있습니다.(수정) 0 8 1,273

by 힘내라김대리 [SQLServer] mssql query sql server [2021.11.18 13:34:57]


어제 LAG 함수를 pajama 님께서 알려주셔서 유용하게 사용하고 있습니다.

 

그런데, 여기서 질문이 더 있는데요,  아래과 같이 데이터가 있다고 가정합니다.

cust_key는 C00000001 뿐만아니라 C00000002, C00000003 ,,,, 쭉 있을 것이고

seq는 'reserv_date' 를 기준으로 오름차순으로 정렬했습니다.

 

seq reserv_date visit_date cancel_date cust_key
1 2010-08-01 2010-08-07 null C00000001
2 2010-09-11 2010-09-13 null C00000001
3 2016-08-14 null 2016-09-01 C00000001
4 2017-08-16 null 2017-08-18 C00000001
5 2020-11-10 2020-11-15 null C00000001
6 2020-11-11 2020-11-15 null C00000001
7 2021-10-01 2021-10-04 null C00000001
1 2015-11-05 2015-11-06 null C00000002
2 2016-11-01 2016-11-30 null C00000002

 

이렇게 visit_date가 다 들어있는 경우에는

 

seq visit_date visit_date_prv cust_key
1 2010-08-07 null C00000001
2 2010-09-13 2010-08-07 C00000001
3 2016-02-13 2010-09-13 C00000001
4 2020-11-15 2016-02-13 C00000001
5 2020-11-15 2020-11-15 C00000001
6 2021-10-04 2020-11-15 C00000001

 

lag를 사용하여, 직전에 방문한날짜(visit_date)를 찾으면 아래와 같이 결과가 나옵니다.

 

lag(visit_date, 1, null) over(partition by cust_key order by visit_date)

 

seq reserv_date visit_date cancel_date visit_date_prv cust_key
1 2010-08-01 2010-08-07 null null C00000001
2 2010-09-11 2010-09-13 null 2010-08-07 C00000001
3 2016-08-14 null 2016-09-01 null C00000001
4 2017-08-16 null 2017-08-18 null C00000001
5 2020-11-10 2020-11-15 null 2010-09-13 C00000001
6 2020-11-11 2020-11-15 null 2020-11-15 C00000001
7 2021-10-01 2021-10-04 null 2020-11-15 C00000001
1 2015-11-05 2015-11-06 null null C00000002
2 2016-11-01 2016-1130 null 2015-11-06 C00000002

 

이렇게 나옵니다.

 

3번째에 visit_date_prv 컬럼에 null 값을 '2010-09-13' 으로 채우려고 하는데,,, 어떻게 할 수 있을까요??

visit_date_prv 컬럼의 3번쨰, 4번째 행이 null로 나오게 됩니다.

 

원하는 결과값은 아래와 같이 나와야하는데, 우주민님께서 알려주신 쿼리로는 null이 여러개인경우 하나만 null 값이 변경이 됩니다.

 

seq reserv_date visit_date cancel_date visit_date_prv cust_key
1 2010-08-01 2010-08-07 null null C00000001
2 2010-09-11 2010-09-13 null 2010-08-07 C00000001
3 2016-08-14 null 2016-09-01 2010-09-13 C00000001
4 2017-08-16 null 2017-08-18 2010-09-13 C00000001
5 2020-11-10 2020-11-15   2010-09-13 C00000001
6 2020-11-11 2020-11-15   2020-11-15 C00000001
7 2021-10-01 2021-10-04   2020-11-15 C00000001

 

*****************수정했습니다.

by 마농 [2021.11.18 14:04:48]

올려주신 결과가 이상합니다.
쿼리가 정렬기준이 뭔지 궁금하고 예시도 틀린 것 같습니다.
정렬 기준이 visit_date 라면 null 이 마지막으로 들어가게 됩니다. (Oracle)
그렇다면? 3번행의 lag 값도 null 이 아닌 6번행의 2021-10-04 이 나올 것입니다.
정렬 기준이 eq 라면?
3번행의 lag 값도 null 이 아닌 2번행의 2021-09-13 이 나올 것이고
4번행의 lag 값이 null 이 나올 것입니다.


by 마농 [2021.11.18 14:11:50]

아! MSSQL 이군요?
MSSQL 은 NULL 이 먼저 나옵니다.?
MSSQL 이면 결과가 이해가 가네요.
visit_date 로 정렬한 LAG 를 구하고 최종결과는 다시 eq 로 정렬한 듯 하네요.
정렬기준이 뭔지 명확하게 결정하신 후에 (visit_date 인지? eq 인지?)
다시 질문해 주셔야 할 것 같습니다.


by 우주민 [2021.11.18 14:38:30]
WITH TABLE_T AS (
SELECT '1' AS seq, '2010-08-07' AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '2' AS seq, '2010-09-13' AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '3' AS seq, null               AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '4' AS seq, '2020-11-15' AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '5' AS seq, '2020-11-15' AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '6' AS seq, '2021-10-04' AS visit_date, 'C00000001' AS cust_key
)
, TABLE_E AS (
SELECT seq, cust_key
,CASE WHEN visit_date IS NULL THEN LAG(visit_date) OVER (PARTITION BY cust_key ORDER BY seq) ELSE visit_date END AS visit_date
FROM TABLE_T T1
)
SELECT seq, cust_key, visit_date
,LAG(visit_date) OVER (PARTITION BY cust_key ORDER BY seq)
FROM TABLE_E
ORDER BY seq

 

날짜 부분에서...

 - null 인 부분에 전 라인의 날짜를 넣는 한 단계가 추가 되면 될거 같습니다.

 + order by 를 visit_date 가 아닌 seq 로 설정해야 할듯 하네요.


by 마농 [2021.11.18 15:09:18]

Oracle 의 경우 IGNORE NULLS 라는 구문을 넣어 가능한데요.
LAG(visit_date) IGNORE NULLS OVER(PARTITION BY cust_key ORDER BY seq) visit_date_pre
MSSQL 의 경우 이 구문이 안먹히네요.

우주민님의 방법은 NULL 이 한번만 나오는 경우엔는 적용 가능합니다.
다만 NULL 이 연속으로 나오는 경우에는 안됩니다.

그리고.
왜 널이 나오는지? 정렬기준이 뭔지?
정렬 결과가 어떻게 나와야 할지? 등이 좀 더 명확하게 정해져야 합니다.


by 힘내라김대리 [2021.11.18 17:33:20]

좀 더 자세한 설명이 필요한것 같아서 수정했습니다.


by 우주민 [2021.11.18 17:57:38]
WITH TABLE_1 AS (
SELECT '1' AS seq, '2010-08-07' AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '2' AS seq, '2010-09-13' AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '3' AS seq, null               AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '4' AS seq, null AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '5' AS seq, '2020-11-15' AS visit_date, 'C00000001' AS cust_key UNION ALL
SELECT '6' AS seq, '2021-10-04' AS visit_date, 'C00000001' AS cust_key
)
, TABLE_2 AS (
SELECT T1.seq , MAX(T2.seq) as seq_2
FROM TABLE_1 T1
, TABLE_1 T2
WHERE T2.visit_date is not null
AND T1.seq >= T2.seq
AND T1.cust_key = T2.cust_key  
GROUP BY T1.seq
)
, TABLE_3 AS (
SELECT T2.seq, T2.visit_date, T3.visit_date AS visit_date_2 , T2.cust_key
FROM TABLE_2 T1
,TABLE_1 T2
,TABLE_1 T3
WHERE T1.seq = T2.seq
AND T1.SEQ_2 = T3.seq
)
SELECT seq, cust_key, visit_date,visit_date_2
,LAG(visit_date_2) OVER (PARTITION BY cust_key ORDER BY seq) AS visit_date_prv
FROM TABLE_3
ORDER BY seq

 

단계가 많이 생기긴 했는데....

CROSS JOIN 을 이용해서 처리한 방법 입니다.

조건이 늘어날 수록 복잡해지네요.

... 급하게 만든거라 정확한지 검수가 부족할 수 있습니다... 컨셉만 확인해주시길...


by 뉴비디비 [2021.11.18 22:07:08]
WITH AA (seq,reserv_date,visit_date, cancel_date, cust_key) AS (
	SELECT 1, '2010-08-01', '2010-08-07',	NULL,	'C00000001' UNION ALL 
	SELECT 2, '2010-09-11', '2010-09-13',	NULL,	'C00000001' UNION ALL 
	SELECT 3, '2016-08-14',		NULL	,'2016-09-01','C00000001' UNION ALL 
	SELECT 4, '2017-08-16',		NULL,	 '2017-08-18','C00000001' UNION ALL 
	SELECT 5, '2020-11-10', '2020-11-15',	NULL,	'C00000001' UNION ALL 
	SELECT 6, '2020-11-11', '2020-11-15',	NULL,	'C00000001' UNION ALL 
	SELECT 7, '2021-10-01', '2021-10-04',	NULL,	'C00000001' UNION ALL 
	SELECT 1, '2015-11-05', '2015-11-06',	NULL,	'C00000002' UNION ALL 
	SELECT 2, '2016-11-01', '2016-11-30',	NULL,	'C00000002'
)
SELECT 
	seq,reserv_date	, visit_date , cancel_date
	, LAG(visit_date2) OVER(PARTITION BY cust_key ORDER BY cust_key, reserv_date) visit_date_prv
	, cust_key
FROM ( 
	SELECT 
		seq,reserv_date	, visit_date , cancel_date, cust_key
		, CASE 
			WHEN visit_date IS NULL THEN MAX(visit_date) OVER (PARTITION BY cust_key ORDER BY cust_key, reserv_date) 
			ELSE visit_date 
		 END AS visit_date2
	FROM AA 
) BB
ORDER BY cust_key, reserv_date

 


by 마농 [2021.11.19 09:21:55]
WITH t (seq, reserv_date, visit_date, cancel_date, cust_key) AS
(
          SELECT 1, '2010-08-01', '2010-08-07',  null       , 'C00000001'
UNION ALL SELECT 2, '2010-09-11', '2010-09-13',  null       , 'C00000001'
UNION ALL SELECT 3, '2016-08-14',  null       , '2016-09-01', 'C00000001'
UNION ALL SELECT 4, '2017-08-16',  null       , '2017-08-18', 'C00000001'
UNION ALL SELECT 5, '2020-11-10', '2020-11-15',  null       , 'C00000001'
UNION ALL SELECT 6, '2020-11-11', '2020-11-15',  null       , 'C00000001'
UNION ALL SELECT 7, '2021-10-01', '2021-10-04',  null       , 'C00000001'
UNION ALL SELECT 1, '2015-11-05', '2015-11-06',  null       , 'C00000002'
UNION ALL SELECT 2, '2016-11-01', '2016-11-30',  null       , 'C00000002'
)
SELECT seq
     , cust_key
     , reserv_date
     , visit_date
     , cancel_date
     , MAX(dt) OVER(PARTITION BY cust_key, grp) reserv_date_pre
  FROM (SELECT seq
             , cust_key
             , reserv_date
             , visit_date
             , cancel_date
             , LAG(visit_date) OVER(PARTITION BY cust_key ORDER BY reserv_date) dt
             , COUNT(visit_date) OVER(PARTITION BY cust_key ORDER BY reserv_date
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) grp
          FROM t
        ) a
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입