Oracle SQL，用于根据特定值查找所有符合条件的事务处理

l2osamch 于 2023-06-29 发布在 Oracle

关注(0)|答案(3)|浏览(169)

我需要查找可根据特定金额开单的所有合格行（基于限额的可用余额）。
样本数据：

ITEM    AMOUNT  
1       30      
2       40      
3       60      
4       -60     
5       20      
6       20      
7       5       
8       35

假设可用余额为100美元。我希望在标记了Elig_For_Inv = Y的地方选择下面的行。请注意，项目# 3标记为Y，因为下一行是贷方，它唐斯了总额。还要求按项目编号升序查找符合条件的项目。

ITEM    AMOUNT  ELIG_FOR_INV
1       30      Y
2       40      Y
3       60      Y
4       -60     Y
5       20      Y
6       20      N
7       5       Y
8       35      N

30+40+60-60+20+5=95 < 100
我试着用运行总数来标记符合条件的项目，并将其与可用余额进行比较。

with t1 as (select 1 item, 30 amount from dual 
 union select 2 item, 40 amount from dual 
 union select 3 item, 60 amount from dual 
 union select 4 item, -60 amount from dual 
 union select 5 item, 20 amount from dual
 union select 6 item, 20 amount from dual 
 union select 7 item, 5 amount from dual 
 union select 8 item, 35 amount from dual ) 
 select t1.*, 
 CASE
   WHEN 100 - SUM ( amount ) over ( order by item rows unbounded preceding ) >= 0 THEN 'Y' 
 ELSE 'N' 
 END Elig_For_Inv from t1;

我的输出低于额定值。

ITEM    AMOUNT  ELIG_FOR_INV
1       30      Y
2       40      Y
3       60      N
4       -60     Y
5       20      Y
6       20      N
7       5       N
8       35      N

预期输出：

ITEM    AMOUNT  ELIG_FOR_INV
1       30      Y
2       40      Y
3       60      Y
4       -60     Y
5       20      Y
6       20      N
7       5       Y
8       35      N

oracle

来源：https://stackoverflow.com/questions/76533100/oracle-sql-to-find-all-eligible-transactions-against-specific-value

3条答案

按热度按时间

vltsax251#

在Oracle 12中，您可以使用MATCH_RECOGNIZE执行逐行处理：

SELECT item, amount, elig_for_inv
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY item
  MEASURES
    classifier() AS ELIG_FOR_INV
  ALL ROWS PER MATCH
  PATTERN ( ^ (Y|N)+ )
  DEFINE
    Y AS SUM(Y.amount) + LEAST(COALESCE(NEXT(Y.amount), 0), 0) <= 100
      OR SUM(Y.amount) + Y.amount <= 100,
    N AS SUM(Y.amount) + N.amount > 100
)

其中，对于样本数据：

CREATE TABLE table_name (ITEM, AMOUNT) AS
SELECT 1,  30 FROM DUAL UNION ALL
SELECT 2,  40 FROM DUAL UNION ALL
SELECT 3,  60 FROM DUAL UNION ALL
SELECT 4, -60 FROM DUAL UNION ALL
SELECT 5,  20 FROM DUAL UNION ALL
SELECT 6,  20 FROM DUAL UNION ALL
SELECT 7,   5 FROM DUAL UNION ALL
SELECT 8,  35 FROM DUAL;

输出：
| 金额|ELIG_FOR_INV| ELIG_FOR_INV |
| - -----|- -----| ------------ |
| 三十|Y轴| Y |
| 四十|Y轴| Y |
| 六十|Y轴| Y |
| 六十|Y轴| Y |
| 二十个|Y轴| Y |
| 二十个|不适用| N |
| 5个|Y轴| Y |
| 三十五|不适用| N |
fiddle

赞(0）回复(0）举报 2023-06-29

ctrmrzij2#

可以使用MODEL子句：

SELECT item, amount, elig_for_inv
FROM   table_name
MODEL
  DIMENSION BY (item)
  MEASURES (
    amount,
    0 AS y_amount,
    'N' AS elig_for_inv
  )  
  RULES AUTOMATIC ORDER (
    elig_for_inv[item] =
      CASE
      WHEN COALESCE(y_amount[cv()-1], 0)
           + amount[cv()]
           + COALESCE(LEAST(amount[cv() + 1], 0), 0)
           <= 100
      THEN 'Y'
      ELSE 'N'
      END,
    y_amount[item] =
      CASE
      WHEN elig_for_inv[cv()] = 'Y'
      THEN amount[cv()]
      ELSE 0
      END + COALESCE(y_amount[cv()-1], 0)
  );

其中，对于样本数据：

CREATE TABLE table_name (ITEM, AMOUNT) AS
SELECT 1,  30 FROM DUAL UNION ALL
SELECT 2,  40 FROM DUAL UNION ALL
SELECT 3,  60 FROM DUAL UNION ALL
SELECT 4, -60 FROM DUAL UNION ALL
SELECT 5,  20 FROM DUAL UNION ALL
SELECT 6,  20 FROM DUAL UNION ALL
SELECT 7,   5 FROM DUAL UNION ALL
SELECT 8,  35 FROM DUAL;