Сравнение строк в таблице оракула и сопоставление обновлений

У меня есть таблица, такая как:

**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.

Ниже приведен желаемый результат:

 **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 не совпадали, потому что он может соответствовать только другому нульу.

Я надеюсь, что смогу выполнить это с помощью одного оператора SQL.

То, что я сейчас делаю, – это, наверное, самый худший подход: я загружаю в pandas dataframe в python, а затем прохожу через каждую строку, сравнивая их.

 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 

спасибо

4 Solutions collect form web for “Сравнение строк в таблице оракула и сопоставление обновлений”

Непроверенный, но что-то вроде этого, используя только 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'; 

Вы можете получить количество пар buy-sell за 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'; 

Запустите этот запрос, чтобы узнать, как номера строк назначаются для покупки-продажи.

 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 

вот еще одна перспектива добавить к остальным. Это касается только соответствующей части, а не части обновления или слияния. Недавно у меня была аналогичная проблема, когда мне нужно было найти записи, сопоставляемые с датой и местоположением транзакции, но поступали из двух разных источников. В этом случае записи должны быть отсортированы так, чтобы подобные записи были вместе. Внутренний запрос сравнивает запись с предыдущей и после нее и захватывает ее, если они совпадают. Затем внешний запрос определяет, соответствуют ли они критериям «различия». Надеюсь это поможет.

 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'); 
 merge into mytable t3 using (select t1.*, count(*) over (partition by tdate,product,price,quantity,field) as field2 from ( select mytable.*, row_number() over (partition by mytable.tdate,mytable.product,mytable.price,mytable.quantity,mytable.buysell order by id) field from mytable) t1) t2 on (t2.id=t3.id and t2.field2='2') when matched then update set status='Matched'; 
  • Подключение к серверу Sql с помощью Python 3 в Windows
  • Как проверить существование строки в SQLite с помощью Python?
  • Как увидеть реальный SQL-запрос в Python cursor.execute
  • Как указать таблицы FROM в подзапросах SQLAlchemy?
  • Использование DATEADD в sqlalchemy
  • Почему slowmany замедляется в Python MySQLdb?
  • Поиск по нескольким таблицам (лучшие практики)
  • Формат каталога исходного текста смешанного языка
  • Postgresql не удалось запустить
  • Python SQL Query Performance
  • Модели Django - SELECT DISTINCT (foo) Из таблицы слишком медленно
  • Python - лучший язык программирования в мире.