將最新日期添加到資料透視查詢

我有一張桌子

USER_ID    TYPE     VALUE    AUDIT_USER   AUDIT_DATE
1          FIELD1   234565   TEST_USER1   1/21/2022
1          FIELD2   TEST1    TEST_USER2   2/11/2022
2          FIELD2   TEST34   TEST_USER1   3/1/2022
2          FIELD3   678ABC   TEST_USER1   2/22/2022

我想將資料顯示為：

USER_ID  FIELD1    FIELD2    FIELD3
1        234565    TEST1 
2                  TEST34    678ABC

我能夠使用 PIVOT 查詢來實作這一點：

SELECT USER_ID, FIELD1, FIELD2, FIELD3
        FROM 
        (SELECT USER_ID, TYPE, VALUE FROM TABLE1
        ) PIVOT
        (
            MAX(value)
            FOR type IN ('FIELD1' as FIELD1, 'FIELD2' as FIELD2, 'FIELD3' as FIELD3)
        )

現在我需要將最新更新日期和最新審核用戶添加到組合中，以便我的資料如下所示：

USER_ID  FIELD1    FIELD2    FIELD3   LATEST_AUDIT_DATE  LATEST_AUDIT_USER
1        234565    TEST1              2/11/2022          TEST_USER2
2                  TEST34    678ABC   3/1/2022           TEST_USER1

所以我將查詢更改為

SELECT USER_ID, FIELD1, FIELD2, FIELD3, LAST_AUDIT_USER, LAST_AUDIT_DATE
        FROM 
        (SELECT t1.USER_ID, 
                t1.TYPE, 
                 t1.VALUE,                     
                 (SELECT MAX(AUDIT_DATE) FROM TABLE1 WHERE USER_ID=t1.USER_ID)  LAST_AUDIT_DATE,
                 (SELECT MAX(AUDIT_USER) FROM TABLE1 WHERE USER_ID=t1.USER_ID AND AUDIT_DATE=((SELECT MAX(AUDIT_DATE) FROM TABLE1 WHERE USER_ID=t1.USER_ID)) LAST_AUDIT_USER
                  FROM TABLE1 t1 GROUP BY USER_ID, TYPE, VALUE
        ) PIVOT
        (
            MAX(value)
            FOR type IN ('FIELD1' as FIELD1, 'FIELD2' as FIELD2, 'FIELD3' as FIELD3)
        )

這給了我想要的結果

USER_ID  FIELD1    FIELD2    FIELD3   LATEST_AUDIT_DATE  LATEST_AUDIT_USER
1        234565    TEST1              2/11/2022          TEST_USER2
2                  TEST34    678ABC   3/1/2022           TEST_USER1

但我想知道是否有更好的方法來完成同樣的事情，因為我的查詢似乎過于復雜

uj5u.com熱心網友回復：

您可以過濾行以僅獲取每個user_id,type組的最新行，然后使用條件聚合：

SELECT user_id,
       MAX(CASE type WHEN 'FIELD1' THEN value END) AS field1,
       MAX(CASE type WHEN 'FIELD2' THEN value END) AS field2,
       MAX(CASE type WHEN 'FIELD3' THEN value END) AS field3,
       MAX(audit_date) AS latest_audit_date,
       MAX(audit_user) KEEP (DENSE_RANK LAST ORDER BY audit_date)
         AS latest_audit_user
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY user_id, type ORDER BY audit_date DESC) AS rn
  FROM   table1 t
  WHERE  type IN ('FIELD1', 'FIELD2', 'FIELD3')
)
WHERE  rn = 1
GROUP BY user_id

其中，對于樣本資料：

CREATE TABLE table1 (USER_ID, TYPE, VALUE, AUDIT_USER, AUDIT_DATE) AS
SELECT 1, 'FIELD1', '234565', 'TEST_USER1', DATE '2022-01-21' FROM DUAL UNION ALL
SELECT 1, 'FIELD2', 'TEST1',  'TEST_USER2', DATE '2022-02-11' FROM DUAL UNION ALL
SELECT 2, 'FIELD2', 'TEST34', 'TEST_USER1', DATE '2022-03-01' FROM DUAL UNION ALL
SELECT 2, 'FIELD3', '678ABC', 'TEST_USER1', DATE '2022-02-22' FROM DUAL;

輸出：

用戶身份	現場1	FIELD2	FIELD3	LATEST_AUDIT_DATE	LATEST_AUDIT_USER
1	234565	測驗1	空值	2022-02-11 00:00:00	TEST_USER2
2	空值	測驗34	678ABC	2022-03-01 00:00:00	TEST_USER1

db<>在這里擺弄

uj5u.com熱心網友回復：

您可以嘗試ROW_NUMBER在子查詢中使用視窗函式，然后執行條件聚合函式

查詢 1：

SELECT USER_ID,
       MAX(CASE WHEN TYPE = 'FIELD1' THEN VALUE END)  FIELD1 , 
       MAX(CASE WHEN TYPE = 'FIELD2' THEN VALUE END)   FIELD2, 
       MAX(CASE WHEN TYPE = 'FIELD3' THEN VALUE END)   FIELD3,
       MAX(CASE WHEN rn = 1 THEN AUDIT_DATE END) LATEST_AUDIT_DATE,
       MAX(CASE WHEN rn = 1 THEN AUDIT_USER END) LATEST_AUDIT_USER
FROM (
    SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY AUDIT_DATE DESC) rn
    FROM TABLE1  t1
) t1
GROUP BY USER_ID

結果：

| USER_ID | FIELD1 | FIELD2 | FIELD3 | LATEST_AUDIT_DATE | LATEST_AUDIT_USER |
|---------|--------|--------|--------|-------------------|-------------------|
|       1 | 234565 |  TEST1 | (null) |         2/11/2022 |        TEST_USER2 |
|       2 | (null) | TEST34 | 678ABC |          3/1/2022 |        TEST_USER1 |

標籤：sql 甲骨文

上一篇：如何從字串中動態提取子字串？

下一篇：雪花查詢將一行與所有前一行進行比較