基于年度周度评级表计算用户绩效的BigQuery技术问题

关联子查询报错通常是因为返回多行结果，或是BigQuery对这类子查询的执行限制导致的。改用JOIN的方式更稳妥，还能提升查询性能。

假设周映射规则为固定偏移（比如用户任务周N对应绩效表的N-7周） #

直接通过JOIN关联两张表的SQL实现如下：

WITH user_kpi_with_target_week AS (
  SELECT
    year,
    week,
    task,
    user,
    kpi,
    -- 这里按示例设为当前周减7，可根据实际规则调整
    week - 7 AS target_week
  FROM
    `你的项目.你的数据集.user_kpi_table`
)
SELECT
  uk.year,
  uk.week,
  uk.task,
  uk.user,
  uk.kpi,
  -- 用绩效表的Base和Diff计算评级，也可直接用阈值（如示例的5.0、5.5）
  CASE
    WHEN uk.kpi < p.base THEN 'top'
    WHEN uk.kpi BETWEEN p.base AND p.base + p.diff THEN 'average'
    ELSE 'low'
  END AS rating
FROM
  user_kpi_with_target_week uk
LEFT JOIN
  `你的项目.你的数据集.performance_table` p
ON
  uk.task = p.task
  AND uk.year = p.year  -- 若跨年需调整逻辑，比如周数减7后跨年要修正年份
  AND uk.target_week = p.week

如果周映射是任务对应固定周（比如任务A固定用第31周标准） #

先创建任务-标准周的映射关系，再关联计算：

WITH task_week_mapping AS (
  SELECT 'A' AS task, 31 AS target_week UNION ALL
  SELECT 'B' AS task, 25 AS target_week  -- 按需添加更多任务映射
),
user_kpi_with_mapping AS (
  SELECT
    uk.year,
    uk.week,
    uk.task,
    uk.user,
    uk.kpi,
    twm.target_week
  FROM
    `你的项目.你的数据集.user_kpi_table` uk
  JOIN
    task_week_mapping twm
  ON
    uk.task = twm.task
)
SELECT
  uk.year,
  uk.week,
  uk.task,
  uk.user,
  uk.kpi,
  CASE
    WHEN uk.kpi < 5.0 THEN 'top'
    WHEN uk.kpi BETWEEN 5.0 AND 5.5 THEN 'average'
    ELSE 'low'
  END AS rating
FROM
  user_kpi_with_mapping uk
LEFT JOIN
  `你的项目.你的数据集.performance_table` p
ON
  uk.task = p.task
  AND uk.year = p.year
  AND uk.target_week = p.week

关键注意事项 #

• 跨年周数处理：如果周数偏移后小于1，需将年份减1，周数调整为当年最后一周（比如2024年第1周减7对应2023年第52/53周），可借助DATE_TRUNC和DATE_ADD通过日期计算，避免直接加减周数的误差。
• 去重处理：确保绩效表中（任务、年份、周数）的组合唯一，若有重复，需用DISTINCT或聚合函数（如MAX/MIN）筛选标准数据。

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