Hive SQL如何标记首次累计消费达100美元的订单行

你的核心需求是：对每个用户，首次累计消费≥100美元的订单行标记为1，后续达标行按1、2、3…连续编号，未达标行显示null。

原语句的问题 #

你之前的语句中，row_number() OVER (partition BY customer_number ORDER BY order_row)是对用户所有订单行全局编号，因此过滤出达标行后，显示的是全局序号（如示例中的3、4），而非达标组内的起始编号。

正确实现方式 #

以下两种方法均可满足需求：

方法一：条件累加计数

利用嵌套CASE生成达标标记，再通过窗口累加实现连续编号：

CASE 
    WHEN cumulative_spend >= 100 THEN 
        SUM(CASE WHEN cumulative_spend >= 100 THEN 1 ELSE 0 END) 
        OVER (PARTITION BY customer_id ORDER BY order_row)
    ELSE NULL 
END AS transaction_threshold_reached

• 内层CASE将达标行标记为1，未达标行标记为0
• 外层窗口函数对每个用户按order_row顺序累加标记值，达标行从1开始递增，未达标行返回null

方法二：分组内重新编号

将用户订单拆分为达标/未达标两组，在达标组内单独生成row_number：

CASE 
    WHEN cumulative_spend >= 100 THEN 
        ROW_NUMBER() 
        OVER (PARTITION BY customer_id, CASE WHEN cumulative_spend >= 100 THEN 1 ELSE 0 END ORDER BY order_row)
    ELSE NULL 
END AS transaction_threshold_reached

• 按customer_id和达标状态分组，达标组内的row_number从1开始重新计数
• 通过外层CASE控制未达标行显示null

示例验证 #

应用上述方法后，你的示例数据结果如下：

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