如何查找缺失的数字以及Oracle表中存在的数字

mehul 发表于 Dev

我正在尝试为一位客户获取数据，但是他的数据中缺少令牌。我尝试了以下查询来获取丢失的数据并将其插入到一个转储表中，但是想找到一种更优化的方法，在其中找到丢失的令牌以及表中存在的令牌。

SELECT MinToken + 1 Level 
 FROM (SELECT Min(Token) AS MINTOKEN,  MAX(Token) AS MAXTOKEN
         FROM dmp_SellerOrders 
        Where OrderNo = 1
          AND TradeDate = '27-Oct-20')
CONNECT BY LEVEL < MAXTOKEN - MINTOKEN
MINUS
SELECT TOKEN 
  FROM dmp_SellerOrders
 Where (OrderNo, TranserialNo) IN (SELECT OrderNo, MAX(TranserialNo) 
                                     FROM dmp_SellerOrders 
                                   GROUP BY OrderNo)
   AND TradeDate = '27-Oct-20';

表中的实际数据看起来像这样，

Original Order  OrderNo TranserialNo    Token   Qty Price
                   1    1               25      100 100
                   1    1               26      100 100
                   1    1               27      100 100
                   1    1               28      100 100
                   1    1               30      100 100
                   1    1               31      100 100

Order Price Modified  OrderNo TranserialNo  Token    Qty    Price
                      1       2             25       100    200
                      1       2             26       100    200
                      1       2             27       100    200
                      1       2             28       100    200
                      1       2             30       100    200
                      1       2             31       100    200

我需要通过分组数量来显示数据，如下所示，

OrderNo      MinToken      MaxToken      Qty  Price
1             25            28           100  200
1             29            29           0    0 
1             30            31           100  200

但是，如果我将数量分组，那么我就会失败了，

OrderNo      MinToken      MaxToken      Qty  Price
1             25            31           100  200
1             29            29           0    0

谁能帮助我，我如何获得预期的输出。

问候，Mehul

Marmite轰炸机

这里的简化解决方案，而orderNo并TradeDate说明概念。

您在后续子查询中执行以下步骤

获取所有令牌和order表的外部联接以获取完整序列
找到LAG该qty列的
grp_id如果有休息，则设置为1。即，如果previosqty为null且当前不为null，反之亦然-否则为0
累积grp_id使用分析形式SUM

这里的结果与样本数据

     TOKEN        QTY      PRICE    QTY_LAG     GRP_ID CUM_GRP_ID
---------- ---------- ---------- ---------- ---------- ----------
        25        100        100                     1          1
        26        100        100        100          0          1
        27        100        100        100          0          1
        28        100        100        100          0          1
        29                              100          1          2
        30        100        100                     1          3

最后一步是一个简单的GROUP BY上qty, price具有添加cummulated组IDcum_grp_id其将所述非相邻的基团。

询问

with orders as (
select 25 token, 100 qty, 100 price from dual union all
select 26 token, 100 qty, 100 price from dual union all
select 27 token, 100 qty, 100 price from dual union all
select 28 token, 100 qty, 100 price from dual union all
select 30 token, 100 qty, 100 price from dual union all
select 31 token, 100 qty, 100 price from dual),
tokens as (
select min(token) min_token, max(token) max_token from orders),
all_tokens as (
select min_token - 1 + level token from tokens
connect by level <= max_token - min_token),
grp as (
select
 t.token,
 o.qty, o.price, 
 lag(o.qty) over (order by t.token) as qty_lag
from  all_tokens t
left outer join orders o
on t.token = o.token),
grp2 as (
select 
 token, qty, price, qty_lag,
 case when qty is null and qty_lag is null or qty is not null and qty_lag is not null then 0 else 1 end as grp_id
from grp),
grp3 as (
select 
  token, qty, price, qty_lag, grp_id,
  sum(grp_id) over (order by token) cum_grp_id
from grp2)
select  min(token), max(token), qty, price
from grp3
group by cum_grp_id, qty, price
order by 1

结果

MIN(TOKEN) MAX(TOKEN)        QTY      PRICE
---------- ---------- ---------- ----------
        25         28        100        100
        29         29                      
        30         30        100        100

如有需要，通过使用orderNo和/或划分分析函数进行调整tradeDate。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。