我有包含客戶及其交易型別的 SNOWFLAKE 資料庫表：
customer_id type
1 a
1 a
2 b
2 b
2 b
3 a
3 b
3 a
在輸出時，我需要不同的客戶 ID 和交易型別：
customer_id type
1 a
2 b
3 mixed
我的代碼不起作用：
SELECT
"customer_id",
CASE WHEN type = 'a' THEN "a"
WHEN type = 'b' THEN "b"
ELSE "mixed" END OVER (PARTITION BY "customer_id" ) AS "type"
FROM CUSTOMERS
uj5u.com熱心網友回復：
您需要聚合和一個CASE運算式來檢查typeeach的不同s 的數量customer_id：
SELECT customer_id,
CASE WHEN COUNT(DISTINCT type) = 1 THEN MAX(type) ELSE 'mixed' END AS type
FROM CUSTOMERS
GROUP BY customer_id;
撰寫CASE運算式的另一種方法是：
CASE WHEN MIN(type) = MAX(type) THEN MAX(type) ELSE 'mixed' END AS type
我認為這type不可為空。
uj5u.com熱心網友回復：
如果硬編碼的“混合”不是必須的，那么可以使用 LISTAGG：
SELECT customer_id, LISTAGG(DISTINCT type, ',') AS type
FROM customer
GROUP BY cutomer_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/353189.html