Teradata SQL 条件聚合

Question

我有下表，其中包含一些客户一个月的数据，我需要根据每列值的条件聚合该表，

条件

• 如果客户的稳定性有> = 2 “非常不稳定”状态则在 Value_1 中返回 1 else 0
• 如果客户在整个月内至少有一条值为 1 的记录，则返回 1 else

|Cust_ID|Date    |Stability     |Value_1|
|-------+--------+--------------+-------|
|123    |3/1/2022|Unstable      |1      |
|123    |3/2/2022|Very Unstable |0      |
|123    |3/3/2022|Stable        |1      |
|123    |3/4/2022|Ver Stable    |NULL   |
|123    |3/5/2022|Unstable      |NULL   |
|123    |3/6/2022|Very Unstable |0      |
|123    |3/7/2022|Unstable      |0      |
|123    |3/8/2022|Very Unstable |0      |
|…      |…       |…             |…      |
|123    |3/31/2022|Very Unstable|0      |

0结果表如下：

|Cust_ID|Stability|Value_1|
|-------+---------+-------|
|123    |1        |1      |

Answer 1

这似乎与您的描述相符：

SELECT Cust_ID
   -- If the customer's stability have >= 2 of "Very Unstable" status 
   -- then return 1 else 0
  ,CASE
     WHEN COUNT(CASE WHEN Stability = 'Very Unstable' THEN 1 END) >= 2 
     THEN 1 
     ELSE 0
   END AS STABILITY_COUNT
  -- In Value_1 if the customer has at least one record with
  -- the value: 1 along the entire month then return 1 else 0
  ,MAX(Value_1) Value_1_m
FROM ST_TABLE
GROUP BY 1