Syntax of hierarchy query

Database
Enthusiast

Syntax of hierarchy query

hi,

i've data like that :

log_id / url_id / dat_begin

log1 / 1 / 2016-01-01 06:00:59.500000

log1 / 5 / 2016-01-01 06:01:53.500000

log1 / 8 / 2016-01-01 06:03:27.500000

log2 / 1 / 2016-01-01 10:00:59.500000

log2 / 7 / 2016-01-01 10:10:59.500000

i search how to have this result :

log_id / url_id / dat_begin / dat_end / duration

log1 / 8 / 2016-01-01 06:03:27.500000 / null / null

log1 / 5 / 2016-01-01 06:01:53.500000 / 2016-01-01 06:03:27.500000 / 00:01:34.000000

log1 / 1 / 2016-01-01 06:00:59.500000 / 2016-01-01 06:01:53.500000 / 00:00:54.000000

log2 / 7 / 2016-01-01 10:10:59.500000 / null / null

log2 / 1 / 2016-01-01 10:00:59.500000 / 2016-01-01 10:10:59.500000 / 00:10:00.000000

where dat_end is the previous dat_begin of log in time

and duration is the difference between dat_begin and dat_end

i don't know where searching, i read "with recursive" syntax but i'm not sure

thx for your response

have a good day

2 REPLIES
Senior Apprentice

Re: Syntax of hierarchy query

No need for recursion:

SELECT log_id, url_id, dat_begin,
MAX(dat_begin) -- previous row's dat_begin
OVER (PARTITION BY log_id
ORDER BY dat_begin DESC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS dat_end,
dat_end - dat_begin DAY(4) TO SECOND
FROM tab
Enthusiast

Re: Syntax of hierarchy query

thanx a lot Dieter