BigQuery/标准SQL中基于category字段实现字符串按指定次数重复填充

以下是可直接运行的实现代码，完全适配你的需求：

-- 自定义重复行数，此处修改为你需要的数值即可
DECLARE REPEAT_ROWS INT64 DEFAULT 10;

WITH ranked_data AS (
  SELECT 
    date,
    hour,
    minute,
    qty,
    category,
    -- 按时间维度排序生成稳定行号，避免顺序随机导致结果错误
    ROW_NUMBER() OVER(ORDER BY date, hour, minute) AS rn
  FROM `你的业务表名`
),
category_mapping AS (
  SELECT
    *,
    -- 取当前行之前最近的一个非空category值
    LAST_VALUE(category IGNORE NULLS) OVER(
      ORDER BY rn 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_valid_category,
    -- 取上述非空category所在的行号
    LAST_VALUE(IF(category IS NOT NULL, rn, NULL) IGNORE NULLS) OVER(
      ORDER BY rn 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_valid_rn
  FROM ranked_data
)
SELECT
  date,
  hour,
  minute,
  qty,
  category,
  CASE 
    WHEN rn - latest_valid_rn <= REPEAT_ROWS THEN latest_valid_category
    ELSE NULL 
  END AS category_repetition
FROM category_mapping

逻辑说明 #

1. 第一步先给所有行按时间顺序生成稳定的唯一行号，保证数据顺序和业务时序一致，避免原无排序ROW_NUMBER的随机问题
2. 利用BigQuery窗口函数支持的IGNORE NULLS参数，直接回填每行对应的最近一次非空category值、以及该值所在的行号，无需复杂自连接
3. 最终通过行号差值判断当前行是否在指定的重复范围内，符合条件则填充对应category值，否则返回null

常见调整说明 #

• 如需修改重复行数，直接修改开头DECLARE REPEAT_ROWS INT64 DEFAULT 10的默认值即可
• 如需按业务维度分组（比如不同商品分别计算），可在两个OVER子句中添加PARTITION BY 分组字段即可

内容的提问来源于stack exchange，提问作者julius.86