如何在BigQuery中为列的连续相同值生成序列计数？

针对你需要新增列统计page_group连续相同值序列计数的需求，可以通过窗口函数组合实现，以下是具体解决方案：

实现思路 #

核心是先识别连续相同的page_group分组，再在每个分组内生成递增计数：

• 用LAG()函数对比当前行与上一行的page_group，判断是否属于同一连续组
• 用SUM()累加分组标志，生成唯一的连续组ID
• 在每个组内用ROW_NUMBER()生成组内的连续计数

完整SQL语句 #

WITH t AS (
  SELECT * FROM UNNEST([
    STRUCT(1017911 AS session_id, 'Home Page' AS page_group, 'Home Page' AS page_name, 1 AS steps_flow),
    (1017911, 'Site Search', 'Site Search', 2),
    (1017911, 'Range - PIP', 'Wall shelves', 3),
    (1017911, 'Range - PIP', 'Wall shelves', 4),
    (1017911, 'Range - PIP', 'Wall shelves', 5),
    (1017911, 'Range - PIP', 'Wall shelves', 6),
    (1017911, 'Site Search', 'Site Search', 7),
    (1017911, 'Site Search', 'Site Search', 8),
    (1017911, 'Ideas', 'Ideas', 9),
    (1017911, 'Range - PLP', 'EKTORP series', 10),
    (1017911, 'Range - PIP', 'Sofas', 11)
  ])
),
grouped AS (
  SELECT
    *,
    -- 标记连续组的起始：当前page_group与上一行不同时记1，否则0
    CASE WHEN page_group != LAG(page_group) OVER (PARTITION BY session_id ORDER BY steps_flow) THEN 1 ELSE 0 END AS group_start,
    -- 累加group_start得到每个连续组的唯一ID
    SUM(CASE WHEN page_group != LAG(page_group) OVER (PARTITION BY session_id ORDER BY steps_flow) THEN 1 ELSE 0 END) OVER (PARTITION BY session_id ORDER BY steps_flow) AS continuous_group_id
  FROM t
)
SELECT
  *,
  -- 在每个连续组内生成递增计数，即你需要的f0列
  ROW_NUMBER() OVER (PARTITION BY session_id, continuous_group_id ORDER BY steps_flow) AS f0
FROM grouped
ORDER BY steps_flow;

关键函数说明 #

• LAG(page_group) OVER (PARTITION BY session_id ORDER BY steps_flow)：获取当前会话中前一行的page_group，用于判断是否连续
• SUM(...) OVER (...)：通过累加分组起始标志，给每个连续相同的page_group分配唯一的组ID
• ROW_NUMBER() OVER (...)：在每个连续组内按steps_flow排序，生成1、2、3...的连续计数

执行上述语句后，你会得到包含f0列的结果，其中连续的Range - PIP,Wall shelves对应的f0会依次为1、2、3、4，完全符合你的需求。

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