我正在尋找從資料框中upsert（更新/插入）到 Oracle 表的查詢pandas。搜索后，我得到了這條merge陳述句，它在密鑰存在時更新值，否則它將插入。
merge into table_A using dual on ( key_column = '123' )
when matched then
update set date_column = TO_TIMESTAMP('2021-01-05 00:00:11.644', 'YYYY-MM-DD HH24:MI:SS.FF3'), OR = 'MZM'
when NOT matched then
insert (key_column, date_column, OR) values( '456',TO_TIMESTAMP('2021-04-05 00:00:11.644', 'YYYY-MM-DD HH24:MI:SS.FF3'),'MZM')
我需要的解決方案
更新
我想根據我插入的記錄中應該小于或等于update的條件max(date_column)進行記錄。key30 daysdate_column
用于插入
我想為key已經存在的 a 插入一個新行，但該date_column鍵的最后一個可用記錄 .ie 不應該在 30 天內。
我試過的
merge into table_A using dual on ( key_column = '123' )
when matched then
update set date_column = TO_TIMESTAMP('2021-12-31 00:00:11.644', 'YYYY-MM-DD HH24:MI:SS.FF3'), ORA = 'MZM'
where (select trunc(TO_TIMESTAMP('2021-01-05 00:00:11.644', 'YYYY-MM-DD HH24:MI:SS.FF3')) - trunc(max(date_column))days from table_A where key = '123') <= 30
when NOT matched then
insert (key_column, date_column, ORA) values( '123',TO_TIMESTAMP('2021-12-31 00:00:11.644', 'YYYY-MM-DD HH24:MI:SS.FF3'),'MZM')
where (select trunc(TO_TIMESTAMP('2022-12-31 00:00:11.644', 'YYYY-MM-DD HH24:MI:SS.FF3')) - trunc(max(date_column))days from table_A where key = '123') > 30 ;
但它不起作用:(。
我不能從 pandas 做到這一點，因為資料量很大。
表娛樂腳本
CREATE TABLE "FDSMLDBUSER"."FDS_upsert" ("key" VARCHAR2(255 BYTE) NOT NULL ENABLE, "date_column" TIMESTAMP (6) NOT NULL ENABLE, "ORA" VARCHAR2(100 BYTE))
insert into "FDSMLDBUSER"."FDS_upsert" ("key", "date_column", ora) values ('123',TO_TIMESTAMP('2021-12-31 00:00:11.644', 'YYYY-MM-DD HH24:MI:SS.FF3'),'MZM')
Can I do this using upsert keyword in oracle where the statement updates when WHERE clause is True or it will insert ?
I appreciate your help so much.
General Question:
Can there be more than one row for a key in the past 30 days in the table? No, we should only have one record per key for past 30 days and that should be the latest one. if any key is older than 30 days then we need to insert new row for that key.
Can there be future dates in the table? Fortunately No.
I use executemany method of cx_oracle to insert records to database where I convert pandas dataframe to dictionary to enable column name inside it. Please find the code below:
connection = cx_Oracle.connect(f'{db_user}/{db_pwd}@{db_host}:{dbport}/{db_service_name}',encoding='UTF-16', nencoding='UTF-16')
cursor = connection.cursor()
parameters = secondary.to_dict(orient='records')
query = config.get('FDS_APAMA_RAWDATA', 'fds_raw_data')
cursor.prepare(query)
cursor.executemany(None,parameters)
for row in cursor:
print(row[0])
con.commit()
uj5u.com熱心網友回復：
您可以LEFT OUTER JOIN將新資料與現有資料相匹配，并查找 30 天內是否存在某行，如果存在，則使用ROW_NUMBER分析函式查找最新匹配行并使用ROWID偽列關聯更新：
MERGE INTO table_A dst
USING (
SELECT d.*,
a.ROWID AS rid,
ROW_NUMBER() OVER (ORDER BY a.date_column DESC NULLS LAST) AS rn
FROM ( SELECT '123' AS key_column,
TIMESTAMP '2023-01-05 00:00:11.644' AS date_column,
'MZM' AS ora
FROM DUAL ) d
LEFT OUTER JOIN table_A a
ON ( a.key_column = d.key_column
AND a.date_column BETWEEN d.date_column - INTERVAL '30' DAY
AND d.date_column INTERVAL '30' DAY
)
) src
ON ( src.rid = dst.ROWID AND src.rn = 1)
WHEN MATCHED THEN
UPDATE
SET date_column = src.date_column,
ora = src.ora
WHEN NOT MATCHED THEN
INSERT (key_column, date_column, ora)
VALUES (src.key_column, src.date_column, src.ora);
其中，對于樣本資料：
CREATE TABLE table_a (
key_column VARCHAR2(255 BYTE) NOT NULL ENABLE,
date_column TIMESTAMP (6) NOT NULL ENABLE,
ORA VARCHAR2(100 BYTE)
);
INSERT INTO table_a (key_column, date_column, ora)
values ('123', TIMESTAMP '2022-12-31 00:00:11.644', 'MZM');
該行更新為：
KEY_COLUMN DATE_COLUMN 奧拉 123 2023-01-05 00:00:11.644000 MZM
db<>在這里擺弄
uj5u.com熱心網友回復：
如果最近 30 天內的鍵存在一行，則要更新此行，否則要插入新行。
on ( key_column = '123' )
不足以找到該行。因此，請在表格中查找對應鍵和日期范圍的行。例如：
merge into table_a
using (select 123 as key, date '2022-01-05' as new_date from dual) src
on (table_a.key_column = src.key and date_column >= src.new_date - interval '30' day)
when matched then
update set date_column = src.new_date, ora = 'MZM'
when not matched then
insert (key_column, date_column, ora) values(src.key, src.new_date, 'MZM')
;
更新
如圖所示，Oracle 禁止更新 MERGE 陳述句的 ON 子句中的列。我認為這是一個缺陷。但是，至少他們提出了一個錯誤。
解決此問題的一種簡單方法是在 PL/SQL 塊中使用 update 和 insert 而不是 merge 陳述句：如果行存在，則更新該行。
begin
update table_a
set date_column = :new_date, ora = 'MZM'
where key = :key and date_column >= :new_date - interval '30' day;
if sql%rowcount = 0 then
insert into table_a (key_column, date_column, ora) values(:key, :new_date, 'MZM')
end if;
end;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/443321.html