基于快递员班次起止时间生成小时数组统计活跃快递员数量：epoch时间返回与分组失败问题求助

我来帮你搞定这两个问题，咱们一步步来拆解：

问题1：查询返回epoch时间 #

你看到的epoch时间应该是客户端把TIMESTAMP类型自动转成了epoch秒数显示。咱们可以通过格式化时间戳，把它转换成人类可读的小时格式（比如YYYY-MM-DD HH:00:00），这样就不会显示成一串数字了。

问题2：无法按生成的hours数组分组 #

直接按数组分组根本行不通，因为每个班次生成的hours数组都是独立的，没法把不同班次里的同一个小时合并统计。正确的做法是用UNNEST把数组拆成单独的行——每个小时对应一行，这样就能按单个小时来分组计数了。

完整的SQL语句 #

WITH shift_hours AS (
  SELECT
    -- 把每个班次覆盖的小时数组拆成单行
    UNNEST(GENERATE_TIMESTAMP_ARRAY(
      CAST(fss.start_time_local AS TIMESTAMP),
      CAST(fss.end_time_local AS TIMESTAMP),
      INTERVAL 1 HOUR
    )) AS hour_timestamp,
    fss.sys_scheduled_shift_id
  FROM just-data-warehouse.delco_analytics_team_dwh.fact_scheduled_shifts AS fss
)
SELECT
  -- 格式化时间为可读的小时格式，避免显示epoch
  FORMAT_TIMESTAMP('%Y-%m-%d %H:00:00', hour_timestamp) AS hour,
  -- 统计每个小时的活跃快递员数量
  COUNT(DISTINCT sys_scheduled_shift_id) AS active_couriers
FROM shift_hours
GROUP BY hour_timestamp, hour
ORDER BY hour_timestamp;

关键细节说明 #

1. UNNEST的作用：把每个班次生成的小时数组“展开”，每个小时变成单独的一行记录，这样就能对每个小时进行分组统计了。
2. 时间格式化：用FORMAT_TIMESTAMP把时间戳转成YYYY-MM-DD HH:00:00的格式，彻底解决epoch显示问题。
3. 边界处理：GENERATE_TIMESTAMP_ARRAY是包含开始时间、不包含结束时间的。比如如果班次是9:00到12:00，只会生成9:00、10:00、11:00三个时间点。如果你需要包含12:00这个小时（统计11:00-12:00的时段），可以把结束时间加1小时：

   CAST(fss.end_time_local AS TIMESTAMP) + INTERVAL 1 HOUR
   

4. 去重统计：用COUNT(DISTINCT sys_scheduled_shift_id)确保同一个快递员的班次不会被重复统计（因为一个班次会拆成多个小时行）。如果你的数据里每个班次唯一对应一个快递员，也可以直接用COUNT(*)，但用DISTINCT更稳妥。

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