能否使用PARTITION BY统计？BigQuery计算用户自然会话前非自然访问次数

你需要基于BigQuery的会话表实现两个能力：

1. 统计每个用户在首次自然（organic）会话前的非自然（not organic）会话总次数
2. 新增标识列，非自然会话后出现自然会话（转化）后返回include，否则返回exclude

你提供的表结构示例：

你给出的Excel实现公式：
=IF(AND((COUNTIF($B$2:B2,FALSE))>=1,(IF(COUNTIF($B$2:B2,FALSE)>=1,COUNTIFS($B$2:B2,TRUE,$C$2:C2,">1"),0))>=1),"include","exclude")

以下为BigQuery标准SQL实现，假设你的会话表字段如下：

• user_id：用户唯一标识，可替换为你实际使用的用户标识字段（如cookie_id）
• event_order：单用户下的会话发生顺序，数值越小会话越早
• is_organic：布尔类型，TRUE代表自然会话，FALSE代表非自然会话，若你实际为字符串字段可自行调整判断逻辑

WITH session_mid AS (
  SELECT
    *,
    -- 取每个用户第一次自然会话的顺序号，无自然会话则为NULL
    MIN(IF(is_organic, event_order, NULL)) OVER(PARTITION BY user_id) AS first_organic_order,
    -- 累计到当前行的非自然会话数
    COUNTIF(NOT is_organic) OVER(PARTITION BY user_id ORDER BY event_order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_non_organic
  FROM `你的项目ID.你的数据集名.你的会话表名`
)
SELECT
  * EXCEPT(first_organic_order),
  -- 生成include/exclude标识
  CASE
    WHEN cumulative_non_organic >= 1 AND first_organic_order IS NOT NULL AND event_order >= first_organic_order THEN 'include'
    ELSE 'exclude'
  END AS include_flag,
  -- 统计该用户首次自然会话前的非自然会话总次数
  COUNTIF(NOT is_organic AND event_order < first_organic_order) OVER(PARTITION BY user_id) AS non_organic_cnt_before_first_organic
FROM session_mid
ORDER BY user_id, event_order

• 先用CTE计算每个用户的首次自然会话位置，以及逐行累计的非自然会话数，和你Excel公式中滑动范围统计的逻辑完全对齐
• 生成include标识时同时满足三个条件：用户有过至少1次非自然会话、用户存在自然会话、当前会话发生在首次自然会话之后（包含首次自然会话）
• 最后的统计列直接按用户维度聚合计算首次自然会话前的非自然会话总数，无需额外嵌套查询

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