基于#standardSQL调整分区排名逻辑 求获取目标结果的SQL语句

看起来你需要的是同一个计数器（COUNTERID）下，按状态的连续出现批次来分组排名，而不是把所有相同状态的记录都合并到一个分区里排序。原SQL的分区方式会把所有同COUNTERID+status的记录归为一组，导致后续出现的同状态会延续之前的编号，这和你期望的结果不符。

下面是调整后的Standard SQL语句，完全匹配你给出的示例结果：

WITH grouped_history AS (
  SELECT 
    COUNTERID,
    status,
    transaction_time,
    -- 生成连续状态的分组ID：当当前状态与上一行不同时，开启新分组
    SUM(CASE WHEN prev_status != status THEN 1 ELSE 0 END) OVER (
      PARTITION BY COUNTERID 
      ORDER BY transaction_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS status_group
  FROM (
    SELECT 
      COUNTERID,
      status,
      transaction_time,
      -- 获取上一行的状态，用于判断是否开启新分组
      LAG(status) OVER (PARTITION BY COUNTERID ORDER BY transaction_time) AS prev_status
    FROM COUNTER_HISTORY
  ) t
)
SELECT 
  COUNTERID,
  status,
  transaction_time,
  ROW_NUMBER() OVER (
    PARTITION BY COUNTERID, status_group 
    ORDER BY transaction_time
  ) AS RANK
FROM grouped_history
ORDER BY COUNTERID, transaction_time;

逻辑拆解： #

• 第一步：标记上一行状态：内层子查询用LAG(status)函数，针对每个COUNTERID按时间排序，拿到当前记录的上一条记录的状态值，这样我们就能判断当前记录是否属于一个新的状态批次。
• 第二步：生成连续状态分组：在CTE里，用SUM()窗口函数累计分组ID：如果当前状态和上一行状态不一样，就加1生成新的分组ID，这样连续相同的状态会被归为同一个分组，而后续再次出现的同状态会被分到新的组里。
• 第三步：计算组内排名：最后以COUNTERID和status_group作为分区，用ROW_NUMBER()按时间排序生成排名，这样每个新的状态批次都会从1开始编号，完美实现你要的效果。

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