Oracle SQL loop LEAD() through partition

Question

I have a set that looks something like this

ID  date_IN  date_out
1    1/1/18   1/2/18
1    1/3/18   1/4/18
1    1/5/18   1/8/18
2    1/1/18   1/5/18
2    1/7/18   1/9/18

I began by

SELECT ID, date_IN, Date_out, lead(date_out) over ( partition by (ID)
order by ID) as next_out
From table

And get something like this...

ID  date_IN  date_out  next_out
1    1/1/18   1/2/18   1/4/18
1    1/3/18   1/4/18   1/8/18
1    1/5/18   1/8/18    Null
2    1/1/18   1/5/18    1/9/18
2    1/7/18   1/9/18   Null

The problem I’m going to to have is that in my actual data many of the ID’s have A LOT of entries. The goal is to have all of the date_out’s appear on one row per ID....

ID  date_IN  date_out  next_out  next_out1  etc.   etc.
1    1/1/18   1/2/18   1/4/18     1/8/18     X      X
2    1/1/18   1/5/18   1/7/18     X         Null    Null

Is there a way to loop the lead() through the entire partition, order by ID drop everything but the first row then move on to the next ID?

A SQL query needs to have a specific set of columns in the result set. `etc.` is not a valid representation of a column, so I don't think you can do what you want with a basic `SELECT` query. — Gordon Linoff, Apr 17 '18 at 01:46
@a.s.1 - You need PL/SQL or dynamic SQL for this as you need to know how many times to execute your LEAD() - lead(date_out, 1)... lead(date_out, 2).... and so on. — Art, Apr 17 '18 at 18:06

jose_bacoy · Answer 1 · 2018-04-17T01:38:32.243

0

No need to do a loop but use the offset option. Below is lifted from the documentation.

offset
Optional. It is the physical offset from the current row in the table.
If this parameter is omitted, the default is 1.

example; lead(date_out) means next value
lead(date_out, 2) means 2nd row after current row
lead(date_out, 3) 3rd row after current row and so on.

in your code; use below snippet;

lead(date_out) over ( partition by (ID) order by ID) as next_out,
lead(date_out, 2) over ( partition by (ID) order by ID) as next_out2,
lead(date_out, 3) over ( partition by (ID) order by ID) as next_out3

edited Apr 17 '18 at 01:38

answered Apr 17 '18 at 01:24

jose_bacoy

12,227
1
20
38

There is a potential problem with this, in that if you hard code things like `LEAD(date_out, 2)`, you might be taking data from another `ID` value, if the current `ID` does not have the expected number of rows. – Tim Biegeleisen Apr 17 '18 at 01:28
I should have posted the entire code, I did not put the over partition order by since it is the same with what he wrote. to answer your comment, it will have null value if there is no 3rd row from current row and will not take other dates from diffrent id. – jose_bacoy Apr 17 '18 at 01:35
I thought about that, but some of the ID’s have as many as 200 entries. Your answer would mean I would have to do an offset per row. That is why I was curious about some sort of loop. I should have pointed out how many were in some of the partitions, sorry. – a.s.1 Apr 17 '18 at 01:37
if that is the case then you need a procedure then insert the data into another table then do a select on that table. inside the procedure, YOU CAN DO A FOR LOOP – jose_bacoy Apr 17 '18 at 01:42
Would it be easier to transpose the next out column onto the ID? – a.s.1 Apr 17 '18 at 01:50

score 0 · Answer 2 · answered Apr 17 '18 at 01:27

Here is one approach, which assumes that you only expect to have a maximum of three date pairs per ID. You may assign a row number and then aggregate by ID:

WITH cte AS (
    SELECT ID, date_IN, date_out,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date_IN) rn
    FROM yourTable
)

SELECT
    ID,
    MAX(CASE WHEN rn = 1 THEN date_IN END) AS date_IN,
    MAX(CASE WHEN rn = 1 THEN date_out END) AS date_out,
    MAX(CASE WHEN rn = 2 THEN next_IN END) AS next_in_1,
    MAX(CASE WHEN rn = 2 THEN date_out END) AS next_out_2,
    MAX(CASE WHEN rn = 3 THEN date_IN END) AS next_in_2,
    MAX(CASE WHEN rn = 3 THEN date_out END) AS next_out_2
FROM cte
GROUP BY ID

Sunny · Answer 3 · 2018-04-17T04:21:16.143

   WITH TAB AS(
SELECT 1 ID,  CAST('2018/01/01' AS DATE) DATE_IN, CAST('2018/01/02' AS DATE) DATE_OUT FROM DUAL
UNION
SELECT 1, CAST('2018/01/03' AS DATE)   , CAST('2018/01/04' AS DATE) FROM DUAL
UNION
SELECT 1, CAST('2018/01/05' AS DATE)   , CAST('2018/01/08' AS DATE) FROM DUAL
UNION
SELECT 1, CAST('2018/01/09' AS DATE)   , CAST('2018/01/10' AS DATE) FROM DUAL
UNION
SELECT 1, CAST('2018/01/11' AS DATE)   , CAST('2018/01/12' AS DATE) FROM DUAL
UNION
SELECT 2, CAST('2018/01/01' AS DATE)   , CAST('2018/01/05' AS DATE) FROM DUAL
UNION
SELECT 2, CAST('2018/01/07' AS DATE)   , CAST('2018/01/09' AS DATE) FROM DUAL
) --select * from tab;
, LEAF_CALC AS(   --CONNECTING THE DATE_OUTS
SELECT 
ID
,SYS_CONNECT_BY_PATH(DATE_OUT, '$') HRCHY
, LEVEL LVL
, CONNECT_BY_ISLEAF ISLEAF
FROM TAB
CONNECT BY PRIOR DATE_OUT < DATE_IN 
START WITH ID = 1
) --SELECT * FROM LEAF_CALC;
, DATA_SORT AS( --ADDING ALL DATE_OUTS IN 1 ROW
SELECT
P.ID, P.HRCHY
FROM LEAF_CALC P,
(SELECT ID, MAX(LVL) MAXLVL FROM
LEAF_CALC
GROUP BY ID) C
WHERE P.ID = C.ID 
AND P.LVL = C.MAXLVL
)--SELECT * FROM DATA_SORT
--SEGREGATING ALL DATES USING REGEXP_SUBSTR
SELECT
ID
, REGEXP_SUBSTR(HRCHY, '[^$]+', 1, 1) DATE_IN
, REGEXP_SUBSTR(HRCHY, '[^$]+', 1, 2) NEXT_OUT
, REGEXP_SUBSTR(HRCHY, '[^$]+', 1, 3) NEXT_OUT2
, COALESCE(REGEXP_SUBSTR(HRCHY, '[^$]+', 1, 4), 'NA') NEXT_OUT3
, COALESCE(REGEXP_SUBSTR(HRCHY, '[^$]+', 1, 5), 'NA') NEXT_OUT4
FROM DATA_SORT;

Oracle SQL loop LEAD() through partition

3 Answers3