我正在嘗試使用它們之間共享的兩個 id（患者 ID、遭遇 ID）將一個資料框連接到另一個資料框。兩個資料幀都在這些 id 上建立索引。
這是LHS：
tnx_prophy=# \d diagnosis
Table "public.diagnosis"
Column | Type | Collation | Nullable | Default
------------------------------- ------ ----------- ---------- ---------
patient_id | text | | |
encounter_id | text | | |
code_system | text | | |
code | text | | |
principal_diagnosis_indicator | text | | |
date | text | | |
Indexes:
"idx_pt_enc_dx" btree (patient_id, encounter_id)
這是RHS：
tnx_prophy=# \d encounter
Table "public.encounter"
Column | Type | Collation | Nullable | Default
-------------- ------ ----------- ---------- ---------
encounter_id | text | | |
patient_id | text | | |
type | text | | |
enc_type | text | | |
Indexes:
"idx_pt_enc_enc" btree (patient_id, encounter_id)
資料集很大（~500m 行？），但我的 UPDATE 和 JOIN 函式似乎比我想要的要長得多。是的，我想更新（不僅僅是生成一個臨時表）
tnx_prophy=# ALTER TABLE diagnosis ADD COLUMN enc_type text;
ALTER TABLE
tnx_prophy=# UPDATE diagnosis
tnx_prophy-# SET enc_type = encounter.enc_type
tnx_prophy-# FROM encounter
tnx_prophy-# WHERE (diagnosis.patient_id, diagnosis.encounter_id) = (encounter.patient_id, encounter.encounter_id);
關于如何更快地做到這一點的任何建議？還是我在這里弄亂了語法？如果有人可以提供幫助，非常感謝！
uj5u.com熱心網友回復：
\i tmp.sql
CREATE TABLE diagnosis
( patient_id text
, encounter_id text
-- , code_system text
-- , code text
, principal_diagnosis_indicator text
-- , date text
);
CREATE INDEX idx_pt_enc_dx ON diagnosis (patient_id, encounter_id);
CREATE TABLE encounter
( encounter_id text
, patient_id text
, type text
, enc_type text
);
CREATE INDEX idx_pt_enc_enc ON encounter (patient_id, encounter_id);
INSERT INTO diagnosis(patient_id, encounter_id, principal_diagnosis_indicator) VALUES
(1,1, 'influenza')
,(1,1, 'cancer')
,(2,1, 'influenza')
,(2,1, 'cancer')
;
INSERT INTO encounter(patient_id, encounter_id, enc_type) VALUES
( 1,1, 'OMG')
,( 1,1, 'WTF')
,( 2,1, 'WTF')
,( 2,1, 'OMG')
;
ALTER TABLE diagnosis ADD COLUMN enc_type text;
EXPLAIN ANALYZE
UPDATE diagnosis dst
SET enc_type = src.enc_type
FROM encounter src
WHERE (dst.patient_id, dst.encounter_id) = (src.patient_id, src.encounter_id)
AND dst.enc_type IS DISTINCT FROM src.enc_type -- both columns are NULLABLE
;
SELECT * FROM diagnosis;
結果：
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
INSERT 0 4
INSERT 0 4
ALTER TABLE
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Update on diagnosis dst (cost=0.30..47.59 rows=7 width=140) (actual time=0.383..0.385 rows=0 loops=1)
-> Merge Join (cost=0.30..47.59 rows=7 width=140) (actual time=0.139..0.232 rows=8 loops=1)
Merge Cond: ((dst.patient_id = src.patient_id) AND (dst.encounter_id = src.encounter_id))
Join Filter: (dst.enc_type IS DISTINCT FROM src.enc_type)
-> Index Scan using idx_pt_enc_dx on diagnosis dst (cost=0.15..21.15 rows=520 width=134) (actual time=0.066..0.082 rows=4 loops=1)
-> Index Scan using idx_pt_enc_enc on encounter src (cost=0.15..21.15 rows=520 width=102) (actual time=0.051..0.086 rows=7 loops=1)
Planning Time: 1.278 ms
Execution Time: 0.858 ms
(8 rows)
patient_id | encounter_id | principal_diagnosis_indicator | enc_type
------------ -------------- ------------------------------- ----------
1 | 1 | cancer | WTF
1 | 1 | influenza | WTF
2 | 1 | cancer | OMG
2 | 1 | influenza | OMG
(4 rows)
好好看看
Merge Join (cost=0.30..47.59 rows=7 width=140) (actual time=0.139..0.232 rows=8 loops=1)
line：更新了8行，但是只有4條記錄！！發生這種情況是因為查找表中的搜索鍵不是unique. 每條記錄更新兩次，（并且順序未定義......）！
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/441833.html
標籤：sql PostgreSQL sql更新 左连接