如何合并記錄更改歷史記錄的表的行以獲得特定時刻的行的樣子？

我有以下 2 個選擇：

SELECT * FROM public.app_user WHERE id = 'e31b55bf';
 -------- ---- ----------- ----- ----- -------------------------- 
|id      |name|email      |role |bio  |created_at                |
 -------- ---- ----------- ----- ----- -------------------------- 
|e31b55bf|Jon |jon@app.com|admin|Hello|2022-01-01 00:00:00.000000|
 -------- ---- ----------- ----- ----- -------------------------- 

SELECT * FROM history.app_user WHERE id = 'e31b55bf';
 -------- ---- -------------- -------- ---- -------------------------- 
|id      |name|email         |role    |bio |updated_at                |
 -------- ---- -------------- -------- ---- -------------------------- 
|e31b55bf|ASDF|test          |NULL    |NULL|2022-01-02 00:00:00.000000|
|e31b55bf|Test|test@gmail.com|basic   |NULL|2022-01-03 00:00:00.000000|
|e31b55bf|NULL|NULL          |standard|asdf|2022-01-04 00:00:00.000000|
|e31b55bf|NULL|NULL          |mod     |NULL|2022-01-05 00:00:00.000000|
 -------- ---- -------------- -------- ---- -------------------------- 

public.app_user包含我的應用程式的用戶，并history.app_user包含第一個行的先前值的記錄。在上面的示例中，用戶e31b55bf在 1 月 5 日之前是 mod 而不是管理員，在 4 日之前是具有 bio“asdf”的標準用戶，具有電子郵件“[email protected]”的名為“Test”的基本用戶3號之前...

我想提出一個 SELECT 或 FUNCTION 來告訴我該行在特定時間點的樣子。我相信我已經完成了它，但我的解決方案看起來比它應該的更復雜。將其轉換為其他表也很乏味：例如public.project和history.project，列完全不同的地方。我相信存在一個更清潔、更易于閱讀和撰寫的解決方案。SQL 向導可以在這里幫助我嗎？

public.app_user我當前的解決方案包括用非空值覆寫當前行的值：

SELECT t.uuid,
       t1.name,
       t2.email,
       t3.role,
       t4.bio
FROM (
     SELECT uuid,
            MIN(CASE WHEN name IS NOT NULL THEN updated_at END)       AS name_date,
            MIN(CASE WHEN email IS NOT NULL THEN updated_at END)      AS email_date,
            MIN(CASE WHEN role IS NOT NULL THEN updated_at END)       AS role_date,
            MIN(CASE WHEN bio IS NOT NULL THEN updated_at END)        AS bio_date
     FROM history.app_user
     WHERE updated_at > '2022-01-03 12:00:00.000000' -- Date to check
     GROUP BY uuid
     ) t
     LEFT JOIN history.app_user t1 ON t1.updated_at = t.name_date
     LEFT JOIN history.app_user t2 ON t2.updated_at = t.email_date
     LEFT JOIN history.app_user t3 ON t3.updated_at = t.role_date
     LEFT JOIN history.app_user t4 ON t4.updated_at = t.bio_date

uj5u.com熱心網友回復：

解決方案1：視窗函式

SELECT DISTINCT ON (uuid)
     , uuid
     , (array_agg(name) FILTER (WHERE name IS NOT NULL) OVER w)[1] AS name
     , (array_agg(email) FILTER (WHERE email IS NOT NULL) OVER w)[1] AS email
     , (array_agg(role) FILTER (WHERE role IS NOT NULL) OVER w)[1] AS role
     , (array_agg(bio) FILTER (WHERE bio IS NOT NULL) OVER w)[1] AS bio 
  FROM history.app_user
 WHERE updated_at > '2022-01-03 12:00:00.000000' -- Date to check
WINDOW w AS (PARTITION BY uuid ORDER BY updated_at)

array_agg()是一個聚合函式，這里用作視窗函式。

FILTER (WHERE condition) 用于從選定行中排除 NULL 值。

視窗是與手冊中描述的當前行關聯的行的子集，即與子句uuid中所述相同的所有現有行。PARTITION BY該ORDER BY子句允許將最早的非空值放在結果陣列的第一個位置，并由[1].

WHERE在這里使用視窗函式的主要問題是我們得到的行數與子句過濾的行數一樣多。該DISTINCT ON ()子句從最終結果中排除冗余行。

在我們的案例中，第二個解決方案基于與aggregate functiona 而不是 a相同的函式。window function

解決方案2：聚合函式

SELECT uuid
     , (array_agg(name ORDER BY updated_at) FILTER (WHERE name IS NOT NULL))[1] AS name
     , (array_agg(email ORDER BY updated_at) FILTER (WHERE email IS NOT NULL))[1] AS email
     , (array_agg(role ORDER BY updated_at) FILTER (WHERE role IS NOT NULL))[1] AS role
     , (array_agg(bio ORDER BY updated_at) FILTER (WHERE bio IS NOT NULL))[1] AS bio 
  FROM history.app_user
 WHERE updated_at > '2022-01-03 12:00:00.000000' -- Date to check
 GROUP BY uuid

標籤：

上一篇：節點GET請求不會顯示在html頁面上，僅加載-不回傳錯誤

下一篇：通過Debezium將MySQL復制到PostgreSQL時資料型別資訊丟失