比较oracle表中的行并更新匹配的行

Question

我有一张如下表：

**ID      tDate        Product    Price    Quantity    BuySell    Status**
  1     10-May-17       pppp       $12        20         Buy       Null
  2     12-May-17       tttt       $10        20         Sell      Null
  3     12-May-17       tttt       $10        20         Buy       Null
  4     18-May-17       pppp       $14        20         Sell      Null
  5     18-May-17       pppp       $14        20         Buy       Null
  6     18-May-17       pppp       $14        20         Sell      Null

我需要更新名为STATUS的字段，并将其设置为“Matched”，只要找到一对具有相同的tDate、product、price和quantity，而不等于BuySell。在

以下是预期结果：

^{pr2}$

请注意#6如何不匹配，因为它只能与另一个null匹配。在

我希望我可以用一个SQL语句来执行此操作。在

我现在所做的可能是最糟糕的方法： 我用python将其加载到pandas数据帧中，然后遍历每一行进行比较。在

s = "SELECT ID, Account, product, Price, tDate, BuySell, Qty" + \
    "FROM Table " + \
    "WHERE Status IS NULL " + \
    "ORDER BY Account, product, tDate, Price, Qty"

df = pd.read_sql(s, conn)

for i in range(len(df.index)-1):

    if df.iloc[i, 1] == df.iloc[i+1, 1]  \
        and df.iloc[i, 2] == df.iloc[i+1, 2] \
        and df.iloc[i, 3] == df.iloc[i+1, 3] \
        and df.iloc[i, 4] == df.iloc[i+1, 4] \
        and df.iloc[i, 5] != df.iloc[i+1, 5] \
        and df.iloc[i, 6] == df.iloc[i+1, 6]:

        s = "UPDATE Temp_Fees " + \
            "SET Strategy = 'UNALLOCATED \ CANCELLED' " + \
            "WHERE ID = " + str(df.iloc[i,0]) + \
            " OR ID = " + str(df.iloc[i + 1, 0])

        #custom function that will execute and commit statement
        bb.EXECUTE(s)

        #avoid reading a matched row 
        i = i + 1

谢谢你

Answer 1

您可以获得每个tdate的买卖对数，并更新这些行。在

MERGE INTO tablename dst
USING (select t.*,count(*) over(partition by tDate,Product,Price,Quantity,rn) as cnt 
       from (select t.*,row_number() over(partition by tDate,Product,Price,Quantity,buysell order by id) as rn
             from tablename t) t
       ) src
ON (src.id = dst.id AND src.cnt=2)
WHEN MATCHED THEN
UPDATE SET Status = 'Matched';

运行此查询以查看行号是如何分配给买卖的。在

^{pr2}$

Answer 2

这里有另一个观点可以补充到其他观点中。这只处理匹配部分，而不是更新或合并部分。最近我遇到了一个类似的问题，我需要查找与事务日期和位置匹配的记录，但是这些记录来自两个不同的来源。在这种情况下，必须已经对记录进行排序，以便将类似的记录放在一起。内部查询将记录与之前和之后的记录进行比较，如果它们匹配，则获取它。然后外部查询确定它们是否满足“差异”条件。希望这有帮助。在

select sbs.trnsid, sbs.amount, sbs.transaction_date, sbs.posted_date, sbs.srcid, 
        sbs.credited_flag, sbs.accid, sbs.compid, sbs.badgeid, sbs.locid, sbs.date_credited, 
        sbs.searchable, sbs.priortime, sbs.nexttime, sbs.priorsource, sbs.nextsource 
    from 
    (select trnsid, amount, transaction_date, posted_date, srcid, credited_flag,
      accid, compid, badgeid, locid, date_credited, transaction_date||locid as searchable,
      lag(transaction_date||locid, 1) over (order by accid) as priortime,
      lead(transaction_date||locid, 1) over (order by accid) as nexttime, 
    lag(srcid, 1) over (order by accid) as priorsource, 
    lead(srcid, 1) over (order by accid) as nextsource
    from transactions_table
    where accid = v_acct
      and transaction_date >= to_date('10/01/2016 00:00:00', 'mm/dd/yyyy hh24:mi:ss') 
      and transaction_date <= to_date('04/23/2017 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
      and srcid in ('B', 'S') order by accid, transaction_date, locid) sbs
    where (sbs.searchable = sbs.nexttime and sbs.srcid = 'S' and sbs.nextsource = 'B')
   or (sbs.searchable = sbs.priortime and sbs.srcid = 'B' and sbs.priorsource = 'S');

Answer 3

未经测试，但仅使用SQL：

MERGE INTO your_table dst
USING (
  SELECT ROW_NUMBER() OVER (
             PARTITION BY tDate, Product, Price, Quantity, BuySell
             ORDER BY ID
           ) AS idx,
         COUNT( CASE BuySell WHEN 'Buy' THEN 1 END ) OVER (
             PARTITION BY tDate, Product, Price, Quantity
           ) AS num_buy,
         COUNT( CASE BuySell WHEN 'Sell' THEN 1 END ) OVER (
             PARTITION BY tDate, Product, Price, Quantity
           ) AS num_sell
  FROM   your_table
) src
ON ( src.ROWID = dst.ROWID AND src.idx <= LEAST( src.num_buy, src.num_sell ) )
WHEN MATCHED THEN
  UPDATE SET Status = 'Matched';