如何基于客户订单日期分组计算Adj_Value列？SQL实现求助

现有一张Customer表，数据集如下：

需要基于客户的OrderDate计算新列Adj_Value，规则如下：

• 同一客户的OrderDate不同时，Adj_Value与Orig_Value相同（例如CustID=A）；
• 同一客户的OrderDate相同时，若OrderType=Bulk的Orig_Value不为空，则Adj_Value取该值（例如CustID=B）；
• 同一客户的OrderDate相同时，若OrderType=Bulk的Orig_Value为空，则Adj_Value取OrderType=Individual的值（例如CustID=C）；

优先级：OrderType="Bulk"（非空）> "Individual" > "Other"

预期输出如下：

尝试用SQL窗口函数实现但未得到预期结果，以下是正确的实现方案：

方法一：使用FIRST_VALUE窗口函数 #

通过窗口函数按优先级排序，直接提取符合条件的优先值：

SELECT 
    CustID,
    OrderDate,
    OrderType,
    Orig_Value,
    FIRST_VALUE(CASE 
                    WHEN OrderType = 'Bulk' AND Orig_Value <> 0 THEN Orig_Value
                    WHEN OrderType = 'Individual' THEN Orig_Value
                END) OVER (
                    PARTITION BY CustID, OrderDate 
                    ORDER BY 
                        CASE OrderType 
                            WHEN 'Bulk' THEN 1 
                            WHEN 'Individual' THEN 2 
                            ELSE 3 
                        END
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                ) AS Adj_Value
FROM Customer;

方法二：分组计算优先值后关联 #

先通过分组获取每个客户+日期对应的目标值，再与原表关联：

WITH PriorityValues AS (
    SELECT 
        CustID,
        OrderDate,
        -- 按优先级取非空值
        COALESCE(
            MAX(CASE WHEN OrderType = 'Bulk' AND Orig_Value <> 0 THEN Orig_Value END),
            MAX(CASE WHEN OrderType = 'Individual' THEN Orig_Value END)
        ) AS TargetVal
    FROM Customer
    GROUP BY CustID, OrderDate
)
SELECT 
    c.CustID,
    c.OrderDate,
    c.OrderType,
    c.Orig_Value,
    -- 单条记录直接用原数值，多条记录取分组计算的目标值
    CASE 
        WHEN COUNT(*) OVER (PARTITION BY c.CustID, c.OrderDate) = 1 THEN c.Orig_Value
        ELSE pv.TargetVal
    END AS Adj_Value
FROM Customer c
JOIN PriorityValues pv 
    ON c.CustID = pv.CustID 
    AND c.OrderDate = pv.OrderDate;

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