如何在Big Query中高效计算事件概率并生成全排列表

步骤1：生成包含所有排列的全量表（表B） #

要得到所有Site、周一至周三的Day、Event Type的全排列，先提取三个维度的唯一值再做笛卡尔积，之后关联原表标记事件是否发生：

WITH all_dimensions AS (
  -- 提取所有唯一站点
  SELECT DISTINCT Site FROM `你的项目.你的数据集.表A`
  CROSS JOIN
  -- 指定周一至周三（如果Day是字符串格式，若为日期类型需调整）
  SELECT Day FROM UNNEST(['Monday', 'Tuesday', 'Wednesday']) AS Day
  CROSS JOIN
  -- 提取所有唯一事件类型
  SELECT DISTINCT `Event Type` FROM `你的项目.你的数据集.表A`
),
full_table AS (
  SELECT
    ad.Site,
    ad.Day,
    ad.`Event Type`,
    -- 原表有对应记录则标记为1，否则为0
    IF(tA.Site IS NOT NULL, 1, 0) AS is_occurred
  FROM all_dimensions ad
  LEFT JOIN `你的项目.你的数据集.表A` tA
    ON ad.Site = tA.Site
    AND ad.Day = tA.Day
    AND ad.`Event Type` = tA.`Event Type`
)

步骤2：计算各事件的发生概率 #

基于全量表，按站点和事件类型分组，用发生天数除以总天数（周一至周三共3天）得到概率：

SELECT
  Site,
  `Event Type`,
  -- 保留两位小数计算概率
  ROUND(SUM(is_occurred) / 3, 2) AS occurrence_probability
FROM full_table
GROUP BY Site, `Event Type`
ORDER BY Site, `Event Type`

补充说明 #

• 如果Day是日期格式（如2024-05-20），可以用EXTRACT(DAYOFWEEK FROM Day)筛选周一至周三（BigQuery中DAYOFWEEK返回2为周一，4为周三，筛选条件为BETWEEN 2 AND 4），维度生成部分要改成从原表提取符合条件的日期。
• 替换代码中你的项目.你的数据集.表A为实际的项目、数据集和表名。
• 若原表存在同一Site+Day+Event Type的重复记录，关联前要对表A做去重：SELECT DISTINCT Site, Day, Event Type FROM ...

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