使用SQL语句按照一定时间间隔填充时间的方法_MsSqlserver

我们在工作当中经常会遇到填充时间轴的问题，我整理了一份通用“按固定时间间隔补齐时间轴”的sql做法合集，覆盖常见数据库（postgresql、mysql、sql server、sqlite、bigquery、snowflake）。你可以选用与你环境匹配的版本。思路都一样：

先生成一条连续的时间序列（按分钟/小时/天等间隔）；
用这条时间序列和你的数据left join，对缺失点补 0 或空值；
再做需要的聚合（如每5分钟求和/计数/均值）。

1) postgresql / amazon redshift（推荐，最简洁）

postgresql 原生有 generate_series，写法非常优雅。

示例：每5分钟补齐一次，统计每5分钟事件数

with ts as (
  select generate_series(
           timestamp '2025-10-01 00:00:00',
           timestamp '2025-10-02 00:00:00',
           interval '5 minute'
         ) as bucket
),
events as (
  -- 你的原始数据表，假设字段：event_time (timestamp)
  select date_trunc('minute', event_time) as minute_ts
  from public.event_log
  where event_time >= '2025-10-01 00:00:00'
    and event_time <  '2025-10-02 00:00:00'
)
select
  ts.bucket,
  coalesce(cnt.c, 0) as event_count
from ts
left join (
  select date_trunc('minute', minute_ts) - (extract(minute from minute_ts)::int % 5) * interval '1 minute'
           as bucket_5m,
         count(*) as c
  from events
  group by 1
) cnt
on ts.bucket = cnt.bucket_5m
order by ts.bucket;

如果你的数据已经是整分，就可以把上面的“对5分钟分箱”的表达式简化成 date_trunc('minute', event_time) 再对 interval '5 minute' 进行对齐。

每天/每小时序列只需把 interval '5 minute' 换成 interval '1 hour' 或 interval '1 day'，同时聚合逻辑改为 date_trunc('hour'/'day', ...)。

2) mysql 8.0+（使用递归cte）

mysql 没有内置的 generate_series，我们用递归cte造序列。

示例：每15分钟补齐一次

with recursive ts as (
  select timestamp('2025-10-01 00:00:00') as bucket
  union all
  select bucket + interval 15 minute
  from ts
  where bucket < '2025-10-02 00:00:00'
),
events as (
  select event_time
  from event_log
  where event_time >= '2025-10-01 00:00:00'
    and event_time <  '2025-10-02 00:00:00'
),
agg as (
  select
    -- 把 event_time 对齐到 15分钟的时间桶
    from_unixtime(floor(unix_timestamp(event_time) / (15*60)) * (15*60)) as bucket_15m,
    count(*) as c
  from events
  group by 1
)
select
  ts.bucket,
  coalesce(agg.c, 0) as event_count
from ts
left join agg
  on ts.bucket = agg.bucket_15m
order by ts.bucket
option max_recursion_depth = 100000; -- 如有需要可调整

性能提示：长时间跨度建议用“辅助数字表/日历表/时间维表”替代递归；或者先生成按天的序列再在应用层扩展。

3) sql server（两种做法：递归cte 或 tally/numbers 表）

3.1 递归cte

with ts as (
  select cast('2025-10-01t00:00:00' as datetime2) as bucket
  union all
  select dateadd(minute, 10, bucket)
  from ts
  where bucket < cast('2025-10-02t00:00:00' as datetime2)
),
agg as (
  select dateadd(minute,
                 datediff(minute, 0, event_time) / 10 * 10, 0) as bucket_10m,
         count(*) as c
  from dbo.eventlog
  where event_time >= '2025-10-01t00:00:00'
    and event_time <  '2025-10-02t00:00:00'
  group by dateadd(minute, datediff(minute, 0, event_time) / 10 * 10, 0)
)
select ts.bucket,
       isnull(agg.c, 0) as event_count
from ts
left join agg
  on ts.bucket = agg.bucket_10m
order by ts.bucket
option (maxrecursion 0);

3.2 numbers/tally 表（更高效，推荐生产）

先准备一个连续整数表（可持久化）。随后：

declare @start datetime2 = '2025-10-01t00:00:00';
declare @end   datetime2 = '2025-10-02t00:00:00';

with ts as (
  select dateadd(minute, n*5, @start) as bucket
  from dbo.numbers
  where dateadd(minute, n*5, @start) <= @end
)
-- 其余与上面 left join 聚合同理

4) sqlite（递归cte）

with recursive ts(bucket) as (
  select datetime('2025-10-01 00:00:00')
  union all
  select datetime(bucket, '+5 minutes')
  from ts
  where bucket < '2025-10-02 00:00:00'
),
agg as (
  select
    datetime(strftime('%s', event_time) / (5*60) * (5*60), 'unixepoch') as bucket_5m,
    count(*) as c
  from event_log
  where event_time >= '2025-10-01 00:00:00'
    and event_time <  '2025-10-02 00:00:00'
  group by 1
)
select ts.bucket, ifnull(agg.c, 0) as event_count
from ts
left join agg on ts.bucket = agg.bucket_5m
order by ts.bucket;

5) bigquery（原生数组函数，非常方便）

with ts as (
  select
    ts as bucket
  from unnest(
    generate_timestamp_array(
      timestamp('2025-10-01 00:00:00+00'),
      timestamp('2025-10-02 00:00:00+00'),
      interval 15 minute
    )
  ) as ts
),
agg as (
  select
    timestamp_trunc(event_time, minute) - 
      interval mod(extract(minute from event_time), 15) minute as bucket_15m,
    count(*) as c
  from `project.dataset.event_log`
  where event_time >= timestamp('2025-10-01 00:00:00+00')
    and event_time <  timestamp('2025-10-02 00:00:00+00')
  group by 1
)
select ts.bucket, ifnull(agg.c, 0) as event_count
from ts
left join agg
  on ts.bucket = agg.bucket_15m
order by ts.bucket;

6) snowflake（使用 generator）

with params as (
  select
    to_timestamp('2025-10-01 00:00:00') as start_ts,
    to_timestamp('2025-10-02 00:00:00') as end_ts,
    5 as step_min
),
ts as (
  select
    dateadd(minute, seq4()*step_min, start_ts) as bucket
  from params,
       table(generator(rowcount => 100000000)) -- 上限要能覆盖区间长度
  qualify bucket <= (select end_ts from params)
),
agg as (
  select
    date_trunc('minute', event_time) - 
      (date_part(minute, event_time) % 5) * interval '1 minute' as bucket_5m,
    count(*) as c
  from event_log
  where event_time >= (select start_ts from params)
    and event_time <  (select end_ts   from params)
  group by 1
)
select ts.bucket, coalesce(agg.c, 0) as event_count
from ts
left join agg on ts.bucket = agg.bucket_5m
order by ts.bucket;