我想創建一個列，顯示它是order_status基于 TRUE 還是 FALSE的最大值created_at。
有沒有辦法在雪花中沒有子查詢的情況下實作這一點？
這是我的示例資料：
WITH t1 AS (
SELECT 'A' AS id, 'created' AS status, '2021-05-18 18:30:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'created' AS status, '2021-05-19 11:30:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'pending' AS status, '2021-05-19 12:00:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'successful' AS status, '2021-05-20 18:30:00'::timestamp AS created_at
)
uj5u.com熱心網友回復：
一個帶殼的 row_number 可以作業
SELECT id, status, created_at
, CASE
WHEN 1 = ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC)
THEN 'TRUE'
ELSE 'FALSE'
END is_final_order_status
FROM t1
uj5u.com熱心網友回復：
使用視窗 MAX：
WITH t1(id, status, created_at) AS (
SELECT 'A', 'created', '2021-05-18 18:30:00'::timestamp UNION ALL
SELECT 'A', 'created', '2021-05-19 11:30:00'::timestamp UNION ALL
SELECT 'A', 'pending', '2021-05-19 12:00:00'::timestamp UNION ALL
SELECT 'A', 'successful', '2021-05-20 18:30:00'::timestamp AS created_at
)
SELECT *, created_at = MAX(created_at) OVER(PARTITION BY ID) AS is_final_order_status
FROM t1;
輸出：
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/363120.html