比较oracle表中的行并更新匹配的行 [英] Compare Rows in oracle table and update matching ones

问题描述

我有一个如下表格:

**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的字段，并将其设置为匹配"，无论找到的一对tDate，产品，价格和数量相等，还是不等于BuySell.

I need to update the field named STATUS, and set it to 'Matched', wherever a pair is found with equal tDate, product, price, and quantity, and NOT equal BuySell.

以下是理想的结果:

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

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

Notice How #6 did not match, because it can only match with another null.

我希望我可以用一条SQL语句执行此操作.

I am hoping i can perform this with a single SQL statement.

我现在正在做的可能是最糟糕的方法: 我使用python将其加载到pandas数据帧中，然后在每一行中进行比较.

What i am doing right now is probably the worst approach: I load into a pandas dataframe in python, and then i loop through each row comparing them.

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

谢谢

推荐答案

未经测试，但仅使用SQL的情况如下:

Untested but something like this using only 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';

这篇关于比较oracle表中的行并更新匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！