BigQuery执行SQL报错：Column name period is ambiguous问题求助

执行目标SQL时出现错误：

Column name period is ambiguous at [13:22]

原SQL代码：

SELECT 
  date,
  SUM(CASE WHEN period = 7  THEN users END) as days_07,
  SUM(CASE WHEN period = 14 THEN users END) as days_14,
  SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
  SELECT
    dates.date as date,  
    periods.period as period,
    EXACT_COUNT_DISTINCT(activity.user_pseudo_id) as users
  FROM `rayn-deen-app.analytics_317927526.events_*` as activity
  CROSS JOIN (SELECT  DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY) as date FROM `rayn-deen-app.analytics_317927526.events_*` GROUP BY date) as dates
  CROSS JOIN (SELECT period FROM (SELECT 7 as period), 
                (SELECT 14 as period),(SELECT 30 as period)) as periods
  WHERE dates.date >= activity.date 
  AND INTEGER(FLOOR(DATEDIFF(dates.date, activity.date)/periods.period)) = 0
  GROUP BY 1,2
)
GROUP BY date
ORDER BY date DESC

问题根源 #

错误出在生成periods表的子查询中：

CROSS JOIN (SELECT period FROM (SELECT 7 as period), 
                (SELECT 14 as period),(SELECT 30 as period)) as periods

用逗号连接多个子查询会生成包含多列同名period的结果集，导致SELECT period时无法确定引用哪一列，触发列名歧义错误。

修复方案 #

将逗号连接的子查询改为UNION ALL合并，生成单一列的结果集：

修改后的完整SQL：

SELECT 
  date,
  SUM(CASE WHEN period = 7  THEN users END) as days_07,
  SUM(CASE WHEN period = 14 THEN users END) as days_14,
  SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
  SELECT
    dates.date as date,  
    periods.period as period,
    EXACT_COUNT_DISTINCT(activity.user_pseudo_id) as users
  FROM `rayn-deen-app.analytics_317927526.events_*` as activity
  CROSS JOIN (SELECT  DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY) as date FROM `rayn-deen-app.analytics_317927526.events_*` GROUP BY date) as dates
  CROSS JOIN (
    SELECT 7 as period UNION ALL
    SELECT 14 as period UNION ALL
    SELECT 30 as period
  ) as periods
  WHERE dates.date >= activity.date 
  AND INTEGER(FLOOR(DATEDIFF(dates.date, activity.date)/periods.period)) = 0
  GROUP BY 1,2
)
GROUP BY date
ORDER BY date DESC

说明 #

UNION ALL会将三个独立的单值行合并成一个包含3行的结果集，每行只有一个period列，彻底消除列名歧义问题，符合SQL标准写法，也适配BigQuery的语法要求。

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