Google BigQuery：生成全年日期-用户全量组合表及查询优化

我明白你想要生成2019年所有日期与所有唯一用户的全组合，仅在有对应记录时填充Attribute字段，其余为null，还希望避免原来方案中两次关联sample表的冗余操作。咱们可以换个更高效的思路，只需要一次提取唯一用户、一次生成全排列、一次关联数据就搞定：

优化后的完整查询 #

#standardSQL
WITH sample AS (
  SELECT DATE('2019-01-01') date, 'user1@example.com' user, 'apple' attribute
  UNION ALL
  SELECT DATE('2019-02-01'), 'user2@example.com', 'pear'
  UNION ALL
  SELECT DATE('2019-03-01'), 'user1@example.com', 'carrot'
  UNION ALL
  SELECT DATE('2019-03-01'), 'user2@example.com', 'orange'
),
-- 第一步：从sample表提取所有唯一用户，仅扫描一次sample表
unique_users AS (
  SELECT DISTINCT user FROM sample
),
-- 第二步：生成2019年所有日期与所有唯一用户的全排列组合
all_date_user_pairs AS (
  SELECT d.date, u.user
  FROM `bigquery-public-data.utility_eu.date_greg` d
  CROSS JOIN unique_users u
  WHERE d.year = 2019
)
-- 第三步：仅一次LEFT JOIN关联sample表，匹配对应属性值
SELECT adu.date, adu.user, s.attribute
FROM all_date_user_pairs adu
LEFT JOIN sample s 
  ON adu.date = s.date 
  AND adu.user = s.user
ORDER BY adu.date, adu.user;

为什么这个方案更优？ #

对比你原来的方案，这个写法解决了两个核心问题：

1. 避免无效计算：原来的方案先把sample的每条记录和所有日期做CROSS JOIN（会生成大量重复行），再通过DISTINCT去重，这中间做了很多无用的计算。而优化方案先提取唯一用户（仅2行数据），再和日期表做CROSS JOIN，直接生成准确的全排列组合（365天×2用户=730行），没有冗余数据。
2. 减少对sample表的无效扫描：整个流程只需要两次访问sample表（一次提取唯一用户，一次LEFT JOIN匹配数据），而且两次都是必要的、高效的操作，不像原方案那样做了无意义的CROSS JOIN后再去重。

如果追求极致的“仅一次扫描sample表”，也可以用窗口函数先收集所有用户，但这种写法需要额外做GROUP BY去重，性能反而不如上面的方案，所以更推荐第一种优化写法。

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