如何在BigQuery SQL中为门店-供应站数据生成Serviced_Days列？

针对你的需求，我们可以通过窗口函数结合数组生成函数来实现Serviced_Days列的计算，核心思路是为每个Store-Supply_Site分组内的配送日，确定其覆盖的连续日期范围（含跨周情况），再将范围转为逗号分隔的字符串。

实现代码 #

WITH grouped_data AS (
  SELECT
    Store,
    Supply_Site,
    Weekday,
    -- 标记分组内配送日的排序位置
    ROW_NUMBER() OVER (PARTITION BY Store, Supply_Site ORDER BY Weekday) AS rn,
    -- 获取分组内第一个配送日（用于处理跨周的最后一个配送日）
    FIRST_VALUE(Weekday) OVER (PARTITION BY Store, Supply_Site ORDER BY Weekday) AS first_weekday,
    -- 获取当前配送日的下一个配送日（无下一个则返回NULL）
    LEAD(Weekday) OVER (PARTITION BY Store, Supply_Site ORDER BY Weekday) AS next_weekday
  FROM your_table_name -- 替换为你的实际表名
),
days_sequence AS (
  SELECT
    Store,
    Supply_Site,
    Weekday,
    -- 生成服务日期数组：有下一个配送日则覆盖到下一个的前一天，无下一个则覆盖到周末再绕回第一个配送日的前一天
    CASE
      WHEN next_weekday IS NOT NULL THEN
        GENERATE_ARRAY(Weekday, next_weekday - 1)
      ELSE
        ARRAY_CONCAT(GENERATE_ARRAY(Weekday, 7), GENERATE_ARRAY(1, first_weekday - 1))
    END AS serviced_days_array
  FROM grouped_data
)
SELECT
  Store,
  Supply_Site,
  Weekday,
  ARRAY_TO_STRING(serviced_days_array, ',') AS Serviced_Days
FROM days_sequence
ORDER BY Store, Supply_Site, Weekday;

代码说明 #

1. 分组与上下文获取：通过窗口函数PARTITION BY Store, Supply_Site对数据分组，用LEAD()获取当前配送日的下一个配送日，FIRST_VALUE()记录分组内第一个配送日，为跨周范围计算做准备。
2. 日期范围生成：
   • 如果存在下一个配送日，直接生成从当前日到下一日前一天的连续数组
   • 如果是分组内最后一个配送日，生成从当前日到周日（7）的数组，再拼接从周一（1）到第一个配送日前一天的数组，实现跨周覆盖
3. 数组转字符串：用ARRAY_TO_STRING()将生成的日期数组转为逗号分隔的字符串，得到最终的Serviced_Days列。

测试验证 #

将代码中的your_table_name替换为你的数据表名后执行，会完全匹配你给出的期望结果：

• 1000-999的配送日1会生成1,2,3，配送日4生成4,5,6,7
• 1000-900的配送日6生成6,7,1,2
• 2000-999的配送日4生成4,5,6,7,1,2,3

内容的提问来源于stack exchange，提问作者Blake O'Sullivan