Google BigQuery中为每个品牌生成连续日期序列的方法

核心实现逻辑：在你原有单日期序列生成逻辑的基础上，将全量日期序列与品牌维度表做交叉连接，即可一次性为所有品牌匹配完整的连续日期段，无需额外循环逻辑。

可直接运行的代码示例 #

WITH  
  -- 从业务表取日期序列的起止边界
  first_date AS (
    SELECT MIN(first_listing_date) AS day 
    FROM `myexplorer-1575814757622.carsome.daily_listing`
  ),
  last_date AS (
    SELECT MAX(first_listing_date) AS day 
    FROM `myexplorer-1575814757622.carsome.daily_listing`
  ),
  -- 生成覆盖全区间的连续日期序列
  full_date_series AS (
    SELECT date_arr AS my_date 
    FROM UNNEST(
      GENERATE_DATE_ARRAY((SELECT day FROM first_date), (SELECT day FROM last_date))
    ) AS date_arr
  ),
  -- 替换为你实际的品牌维度数据源即可
  brand_dim AS (
    SELECT 'Brand-A' AS car_brand
    UNION ALL SELECT 'Brand-B' AS car_brand
    UNION ALL SELECT 'Brand-C' AS car_brand
  )
-- 交叉连接自动为每个品牌关联全量日期
SELECT 
  b.car_brand,
  d.my_date
FROM brand_dim b
CROSS JOIN full_date_series d
ORDER BY b.car_brand, d.my_date;

返回结果格式 #

运行后每个品牌都会对应完整的连续日期区间，示例片段如下：

使用注意事项 #

• 若品牌数据已存储在实体表中，直接将brand_dim公共表表达式替换为对应品牌表的查询语句即可，例如SELECT DISTINCT car_brand FROM 你的品牌表存储路径
• 若需要按周/月等其他粒度生成日期序列，给GENERATE_DATE_ARRAY传入第三个间隔参数即可，例如GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 MONTH)即为按月生成序列

内容的提问来源于stack exchange，提问作者Ong K.S