Recursive query 사용하는 방법

Question

sequence의 빈칸을 바로 전의 값으로 채우고자 합니다. ID Page Timestamp Sequence Orestes Login 152356 1 Orestes Account view 152368 Orestes Transfer 152380 Orestes Account view 162382 2 Orestes Loan 162393 Antigone Login 152382 1 Antigone Transfer 152390 Orestes Login 172356 1 Orestes Account view 172368 Orestes Transfer 172380 위의 데이터를 ID Page Timestamp Sequence Orestes Login 152356 1 Orestes Account view 152368 1 Orestes Transfer 152380 1 Orestes Account view 162382 2 Orestes Loan 162393 2 Antigone Login 152382 1 Antigone Transfer 152390 1 Orestes Login 172356 1 Orestes Account view 172368 1 Orestes Transfer 172380 1 이렇게 엑셀에서 끌기하듯이 공백전의 값으로 매기려고 합니다.방법을 찾았는데아래와 같이 계속 반복하면 공백을 하나씩 채우는데 계속 반복해야 하네요.recursive query를 사용하면 해결이 가능할 것 같은데 아무리 테스트해봐도 방법을 못찾겠습니다.어떻게하면 가능할까요?with r1as(select id, page, timestamp, lag(sequence) over (partition id order by timestamp) as sequence from log)r2as(select id, page, timestamp, sequence from log)insert into test1select a.id, a.page, a.timestamp, case when a.sequence is not null then a.sequence when b.sequence is not null then b.sequence else a.sequence endfrom r1 a join r2 b on a.id&#61;b.id and a.timestamp&#61;b.timestamp;create table test2 like test1;with r1as(select id, page, timestamp, lag(sequence) over (partition id order by timestamp) as sequence from test1)r2as(select id, page, timestamp, sequence from test1)insert into test2select a.id, a.page, a.timestamp, case when a.sequence is not null then a.sequence when b.sequence is not null then b.sequence else a.sequence endfrom r1 a join r2 b on a.id&#61;b.id and a.timestamp&#61;b.timestamp;create table test3 like test2

Recursive query 사용하는 방법

0개의 답변

#hive 목록