引用双列的CASE WHEN语句实现账号交易最终状态判定需求

多个账号对应交易结果为successful或fail，部分账号存在多条记录，例如account_number为413655的账号同时包含成功和失败两条结果。
需求：新增名为Distinct_count的字段，只要同一账号下存在任意一条成功记录，该账号所有条目的对应字段均显示success；仅当账号下所有记录结果均为失败时，该字段才显示fail。

现有查询语句中，需要修改的逻辑部分如下：

(CASE WHEN outcome = 'Successful' AND outcome = 'Fail' OR outcome = 'Successful' THEN 'Successful'
ELSE 'Fail' END)
AS distinct_count

原有逻辑问题 #

原有逻辑仅能判断当前行的交易结果，无法关联同一账号下的其他行记录，因此完全无法满足需求。

修改方案 #

使用窗口函数按account_number分组，判断分组内是否存在任意一条成功记录即可，推荐两种实现方式：

方式1：直接改写原CASE逻辑（无需调整整体结构） #

将原有标注的distinct_count逻辑替换为以下代码即可：

CASE 
    -- 按账号分组，判断组内是否存在成功记录
    WHEN MAX(CASE WHEN activity_code IN ('SHPS','SBPS','SOPS','SHCS','SBCS') THEN 1 ELSE 0 END) 
         OVER (PARTITION BY CASE WHEN len(b.loan_nbr) > 3 THEN b.loan_nbr ELSE b.lease_nbr END) = 1
    THEN 'success'
    ELSE 'fail'
END AS Distinct_count

注：因为多数数据库不支持在同层级SELECT中引用刚定义的别名，所以分区字段直接复用account_number的计算逻辑。

方式2：用CTE封装基础逻辑（可读性更高） #

如果允许调整整体查询结构，推荐用CTE先计算出account_number和outcome，再用窗口函数判断，代码更易维护：

WITH base_data AS (
    SELECT 
        date_dim_id,
        B.user_key,
        B.product_type,
        B.dealer_nbr,
        CASE B.make
            WHEN 'TOYOTA' THEN 'TOYOTA'
            WHEN 'SUBARU' THEN 'SUBARU'
            WHEN 'LEXUS' THEN 'LEXUS'
            ELSE 'OTHER'
        END AS make_category,
        C.dealer_name,
        C.zone,
        activity_date,
        activity_time,
        activity_code,
        CASE WHEN len(b.loan_nbr) > 3 THEN b.loan_nbr ELSE b.lease_nbr END AS account_number,
        CASE WHEN activity_code IN ('SHPS','SBPS','SOPS','SHCS','SBCS') THEN 'Successful' ELSE 'Fail' END AS outcome
    FROM dm_business_ops_tcci.v_tcci_collection_activity_fact A
    LEFT JOIN dm_business_ops_tcci.v_tcci_collection_account_dim B
        ON A.collection_account_dim_id = B.collection_account_dim_id
    LEFT JOIN dm_business_ops_tcci.v_tcci_dealer_dim C
        ON A.dealer_dim_id = C.dealer_dim_id
    WHERE activity_code IN ('SBCS','SOPF','SBPS','SOPS','SHPF','SHPS','SBCF','SBPF','SHCF','SHCS')
)
SELECT 
    *,
    CASE 
        WHEN MAX(CASE WHEN outcome = 'Successful' THEN 1 ELSE 0 END) OVER (PARTITION BY account_number) = 1
        THEN 'success'
        ELSE 'fail'
    END AS Distinct_count
FROM base_data

内容的提问来源于stack exchange，提问作者Jay P