我有一张如下表:**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
谢谢你