SQL mimicking analytic LEAD/LAG function with some restrictions-CodePudding

There is a table named test, with one column named amount (number datatype). There is no PK for this table, and amounts can be repeated. The table's DDL is below: (created for testing purposes in Oracle 18c xe)

create table test (amount number(20));
insert into test values (20);
insert into test values (10);
insert into test values (30);
insert into test values (20);
insert into test values (10);
insert into test values (40);
insert into test values (15);
insert into test values (40);

The goal is to mimick the LEAD analytical function results ordered by amount, but no analytic (incl. ranking and window functions) can be used. PSM (incl MYSQL stored features, PL/SQL, T-SQL etc.) or some kind of identity tables can neither be used.

The desired output is shown in lead_rows_analytic_amount column:

select 
amount,
lead(amount) over (order by amount) as lead_rows_analytic_amount
from test t1;

actual result:
amount  lead_rows_analytic_amount
10      10
10      15
15      20
20      20
20      30
30      40
40      40
40

What are some elegant ways to achieve the result taking into account the restrictions set? The DB is irrelevant here, if the restrictions apply.

I am attaching a stupidly clumsy and direct solution I came up with, but the goal is to get something more elegant (ignoring the performance).

with initial_rn as (
select
 amount,t1.rowid,
 ( select count (*)
    from test t2
    where
      t1.amount >= t2.amount
 ) as rn
from test t1
)
,prep_table as (
select t1.*,nvl2(repeating_rn,1,0) as repeating_rn_tag,
nvl(( SELECT max(rn)
FROM initial_rn t2
where t2.rn < t1.rn
),0) AS lag_rn
from initial_rn t1
left join (select rn as repeating_rn
from initial_rn
group by rn
having count(*) > 1) t2 on t1.rn = t2.repeating_rn
)
,final_rn as (
select t1.amount,case when repeating_rn_tag = 0 then rn else lag_rn   
( select count (*)
    from prep_table t2
    where
      t1.rowid >= t2.rowid and t1.repeating_rn_tag = 1 and t2.repeating_rn_tag = 1 and t1.rn = t2.rn
      )
      end as final_rn
from prep_table t1
)


select t1.*,
lead(amount) over (order by amount) as lead_rows_analytic_amount,
(select min(amount)
from test t2
where t2.amount > t1.amount
) as lead_range_amount,
(SELECT MIN(amount)
FROM final_rn t2
where t2.final_rn > t1.final_rn
) AS lead_amount
from final_rn t1 
order by amount
;

CodePudding user response：

In Oracle, you can use:

SELECT CASE WHEN LEVEL = 1 THEN amount ELSE PRIOR amount END AS amount,
       CASE WHEN LEVEL = 1 THEN NULL   ELSE amount       END AS lead_amount
FROM   (
  SELECT amount,
         ROWNUM AS rn
  FROM   (
    SELECT amount
    FROM   test
    ORDER BY amount
  )
)
WHERE LEVEL = 2
OR    LEVEL = 1 AND CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR rn   1 = rn

More generally, you can use:

WITH ordered_amounts (amount) AS (
  SELECT amount
  FROM   test
  ORDER BY amount
),
indexed_amounts (amount, idx) AS (
  SELECT amount,
         ROWNUM -- Or any function that gives sequentially increasing values
  FROM   ordered_amounts
)
SELECT i.amount,
       nxt.amount AS lead_amount
FROM   indexed_amounts i
       LEFT OUTER JOIN indexed_amounts nxt
       ON (i.idx   1 = nxt.idx)

Which, for the sample data, both output:

AMOUNT LEAD_AMOUNT

10 10

10 15

15 20

20 20

20 30

30 40

40 40

40 null

AMOUNT	LEAD_AMOUNT
10	10
10	15
15	20
20	20
20	30
30	40
40	40
40	null

db<>fiddle here

CodePudding user response：

Ok so just throwing this out there as something you could do, using JSON functionality (support exists in most RDBMS)

This is SQL server syntax:

with v as (
    select *
    from OpenJson(
        (select Concat('[',String_Agg(amount,',') 
            within group (order by amount),']')from test)
        )
)
select value, (
    select value 
    from v v2 
    where v2.[key]=v.[key] 1
 ) as lead_rows_analytic_amount
from v

Example fiddle

CodePudding user response：

To contribute to this wonderful collection of solutions how to avoid window functions, I feel it's worth mention Oracle model clause:

with test as (
  select column_value as amount
  from table(sys.ku$_vcnt(20,10,30,20,10,40,15,40)) -- or your table, I'm just lazy to create fiddle
)
select amount, lead_amount
from (
  select *
  from (select amount, 0 as lead_amount from test order by amount)
  model
    dimension by (rownum as rn)
    measures (amount, lead_amount)
    rules (amount[any] = amount[cv(rn)], lead_amount[any] = amount[cv(rn)   1])
)
order by amount

(Not sure if it is helpful for you, compared with window functions.)

CodePudding user response：

If you had a primary key (any table should have):

select a.*, (select min(r.amount)
             from #test r
             where ((r.id <> a.id and r.amount > a.amount) 
                    OR 
                    (r.id > a.id and r.amount=a.amount)
                    )
            ) as NextVal
from #test a
order by a.amount, a.id