SQL模仿分析LEAD/LAG函式，但有一些限制

有一個名為test的表，其中有一列名為amount（數字資料型別）。此表沒有PK，金額可以重復。該表的 DDL 如下：（在 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);

目標是模仿按數量排序的 LEAD 分析函式結果，但不能使用分析（包括排名和視窗函式）。PSM（包括 MYSQL 存盤功能、PL/SQL、T-SQL 等）或某種身份表都不能使用。

所需的輸出顯示在lead_rows_analytic_amount列中：

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  

考慮到設定的限制，有哪些優雅的方法可以實作結果？如果有限制，資料庫在這里是無關緊要的。

我附上了我想出的愚蠢笨拙和直接的解決方案，但目標是獲得更優雅的東西（忽略性能）。

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
;

uj5u.com熱心網友回復：

在 Oracle 中，您可以使用：

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

更一般地，您可以使用：

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)

其中，對于樣本資料，兩者都輸出：

數量	LEAD_AMOUNT
10	10
10	15
15	20
20	20
20	30
30	40
40	40
40	空值

db<>在這里擺弄

uj5u.com熱心網友回復：

好的，所以只需將其作為您可以做的事情，使用 JSON 功能（大多數 RDBMS 中都存在支持）

這是 SQL 服務器語法：

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

示例小提琴

uj5u.com熱心網友回復：

對于如何避免視窗函式的解決方案，我覺得值得一提的是 Oraclemodel子句：

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

（與視窗函式相比，不確定它是否對您有幫助。）

uj5u.com熱心網友回復：

如果你有一個主鍵（任何表都應該有）：

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

標籤：

上一篇：如何在Postgresql中使用對int陣列執行動態查詢

下一篇：如何獲取每個日期中存在的值？