SQL中统计两次_hit类规则触发间decline_rule出现次数的实现方案咨询

看起来你已经找对方向了——窗口函数确实是解决这类时序分组问题的核心，我来帮你把思路补全，一步步实现你要的结果：

核心思路 #

我们需要把每个decline_rule关联到它之前最近的那个_hit规则，同时确定这个_hit规则的“有效期”（直到下一个_hit规则出现），然后就能统计每个_hit规则对应的decline次数，最后再做汇总。

具体实现步骤 #

下面用CTE（公共表表达式）分步骤实现，每一步都有明确的作用：

第一步：给所有记录标记并关联所属的_hit规则

首先，我们给每条记录添加上最近的上一个_hit规则的信息（规则名、触发时间），同时标记当前记录是否是_hit规则：

WITH rule_with_hit_context AS (
    SELECT
        timestamp,
        customer_id,
        rule_name,
        -- 标记当前是否是_hit规则
        CASE WHEN rule_name LIKE '%_hit%' THEN 1 ELSE 0 END IS_HIT_RULE,
        -- 向前填充最近的_hit规则名，直到下一个_hit规则出现
        LAST_VALUE(CASE WHEN rule_name LIKE '%_hit%' THEN rule_name END) 
            OVER (PARTITION BY customer_id ORDER BY timestamp 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ASSOCIATED_HIT_RULE,
        -- 向前填充最近的_hit规则的时间戳
        LAST_VALUE(CASE WHEN rule_name LIKE '%_hit%' THEN timestamp END) 
            OVER (PARTITION BY customer_id ORDER BY timestamp 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ASSOCIATED_HIT_TIMESTAMP
    FROM table_a
),

第二步：提取_hit规则并统计对应的decline次数

接下来，我们只保留_hit规则的记录，然后关联对应的decline_rule统计数据，同时处理没有后续decline的情况（比如customer2的quick_hit_rule）：

hit_rule_with_decline_stats AS (
    SELECT
        -- 提取_hit规则的日期（按你的需求取日期部分）
        DATE(ASSOCIATED_HIT_TIMESTAMP) AS date,
        ASSOCIATED_HIT_RULE AS hit_rule_name,
        customer_id,
        -- 统计当前_hit规则下的decline次数：关联到这个_hit规则的decline记录数
        COUNT(CASE WHEN rule_name = 'decline_rule' THEN 1 END) AS decline_transaction_count,
        -- 标记这个客户是否有decline（用于后续统计客户数）
        MAX(CASE WHEN rule_name = 'decline_rule' THEN 1 ELSE 0 END) AS has_decline
    FROM rule_with_hit_context
    -- 只保留_hit规则本身，以及它之后到下一个_hit规则之前的所有记录
    GROUP BY DATE(ASSOCIATED_HIT_TIMESTAMP), ASSOCIATED_HIT_RULE, customer_id, ASSOCIATED_HIT_TIMESTAMP
),

第三步：按日期和_hit规则名汇总最终结果

最后，按照你需要的最终表结构，汇总每个日期下每个_hit规则的总次数、总decline交易数、总客户数：

final_summary AS (
    SELECT
        date,
        hit_rule_name,
        -- 统计_hit规则的触发次数
        COUNT(DISTINCT ASSOCIATED_HIT_TIMESTAMP) AS hit_rule_count,
        -- 汇总所有对应的decline交易数
        SUM(decline_transaction_count) AS total_decline_transactions,
        -- 统计有decline的客户数
        SUM(has_decline) AS total_decline_customers
    FROM hit_rule_with_decline_stats
    GROUP BY date, hit_rule_name
)
SELECT * FROM final_summary ORDER BY date, hit_rule_name;

结果验证 #

用你提供的示例数据，这个查询会输出你期望的最终结果：

补充说明 #

• LAST_VALUE窗口函数在这里的作用是“向前填充”，确保每个decline_rule都能关联到最近的上一个_hit规则；
• 如果你的SQL方言不支持LAST_VALUE的ROWS BETWEEN语法，可以用LAG结合窗口函数的分组逻辑替代，核心思路是给每个客户的_hit规则分配一个组ID，然后把decline_rule分到对应的组里；
• 注意处理同一时间戳出现多个_hit规则的情况（如果有的话），可以根据实际业务调整分组逻辑。

内容来源于stack exchange