안녕하세요~
SQL 질문 (행간 연산..문제)가 있어서 글을 올려요..ㅠ
고민하다가 도무지 안풀리네요......
우선 데이터셋은 아래와 같습니다
데이터셋은
userid, code, startYN, endYN, timet 가 있습니다.
한 유저가 startYN = Y 에서 부터 다음 endYN = Y 을 만날때까지
처음 startYN=Y 에 있던 code 와 timet 를 그대로 넣어주는 결과를 만들어 내고 싶습니다.
행단위 프로세스로 보면
1. 유저A가 startYN=Y 를 확인하여 code값, timet값을 저장
2. 위에서 저장한 code값, timet값을 다음 endYN=Y 가 나올때까지 동일하게 입력한다.
3. 다음 startYN=Y 를 확인하여 위 과정을 반복한다.
결국 해당 프로세스 결과 다음과 같은 결과를 얻는 것이 목표입니다.
해당 데이터셋을 얻을수 있는 쿼리 with절로 첨부합니다..ㅠㅠ
감사합니다.
with t as ( select 'A' userid, '001' code, 'Y' startYN, 'Y' endYN, 1 timet union all select 'A' userid, '' code, 'N' startYN, 'N' endYN, 2 timet union all select 'A' userid, '001' code, 'N' startYN, 'N' endYN, 3 timet union all select 'A' userid, '' code, 'N' startYN, 'N' endYN, 4 timet union all select 'A' userid, '' code, 'N' startYN, 'Y' endYN, 5 timet union all select 'A' userid, '002' code, 'Y' startYN, 'Y' endYN, 6 timet union all select 'A' userid, '' code, 'N' startYN, 'N' endYN, 7 timet union all select 'A' userid, '002' code, 'N' startYN, 'N' endYN, 8 timet union all select 'A' userid, '' code, 'N' startYN, 'N' endYN, 9 timet union all select 'A' userid, '' code, 'N' startYN, 'Y' endYN, 10 timet union all select 'A' userid, '001' code, 'Y' startYN, 'N' endYN, 11 timet union all select 'A' userid, '' code, 'N' startYN, 'N' endYN, 12 timet union all select 'A' userid, '' code, 'N' startYN, 'Y' endYN, 13 timet union all select 'A' userid, '003' code, 'Y' startYN, 'Y' endYN, 14 timet union all select 'A' userid, '' code, 'N' startYN, 'N' endYN, 15 timet union all select 'A' userid, '' code, 'N' startYN, 'N' endYN, 16 timet union all select 'A' userid, '' code, 'N' startYN, 'N' endYN, 17 timet union all select 'A' userid, '' code, 'N' startYN, 'Y' endYN, 18 timet ) select * from t t order by timet
아래처럼 하시면 될 듯..
with cte_sample as (
select 'A' as user_id, '001' as code, 'Y' as is_start, 'Y' as is_end, 1 as seq_no union all
select 'A', '', 'N', 'N', 2 union all
select 'A', '001', 'N', 'N', 3 union all
select 'A', '', 'N', 'N', 4 union all
select 'A', '', 'N', 'Y', 5 union all
select 'A', '002', 'Y', 'Y', 6 union all
select 'A', '', 'N', 'N', 7 union all
select 'A', '002', 'N', 'N', 8 union all
select 'A', '', 'N', 'N', 9 union all
select 'A', '', 'N', 'Y', 10 union all
select 'A', '001', 'Y', 'N', 11 union all
select 'A', '', 'N', 'N', 12 union all
select 'A', '', 'N', 'Y', 13 union all
select 'A', '003', 'Y', 'Y', 14 union all
select 'A', '', 'N', 'N', 15 union all
select 'A', '', 'N', 'N', 16 union all
select 'A', '', 'N', 'N', 17 union all
select 'A', '', 'N', 'Y', 18
)
select *
from cte_sample as a
left join lateral (
select code, seq_no as result
from cte_sample
where user_id = a.user_id
and is_start = 'Y'
and seq_no <= a.seq_no
order by seq_no desc
limit 1
) as b
on 1 = 1
;