在上一篇《DuckDB 实战：时间序列分析》中，我们介绍了 date_trunc、generate_series 和滚动聚合的基础用法。但对于实际生产环境，光有基础聚合远远不够——你需要回答更有价值的问题：本周 vs 上周？、不同国家用户的活跃时间如何对齐？、交易时段怎么定义？
本文将深入 DuckDB 时间序列分析的四个进阶场景。
图：DuckDB 时间序列分析进阶四模块 — 周期对比、时区转换、自定义时间桶、插值补全
1. 周期对比：WoW / MoM / YoY 一键计算
业务场景
运营团队需要看每日 GMV 的周环比（WoW）和月环比（MoM），判断增长趋势。传统做法是写两个查询再手动计算，在 DuckDB 中一条 SQL 搞定。
数据准备
CREATE TABLE daily_revenue AS
SELECT * FROM (VALUES
('2026-07-01', 12000),
('2026-07-02', 13500),
('2026-07-03', 11800),
('2026-07-04', 14200),
('2026-07-05', 15600),
('2026-07-06', 16800),
('2026-07-07', 14900),
('2026-07-08', 13200),
('2026-07-09', 14100),
('2026-07-10', 13800),
('2026-07-11', 15200),
('2026-07-12', 16100),
('2026-07-13', 17500),
('2026-07-14', 16200),
('2026-07-15', 14800),
('2026-07-16', 15900),
('2026-07-17', 17200),
('2026-07-18', 18100),
('2026-07-19', 16900),
('2026-07-20', 15500)
) AS t(dt, revenue);
周环比（WoW）与月环比（MoM）
WITH base AS (
SELECT
dt::DATE AS date,
revenue,
date_trunc('week', dt::TIMESTAMP) AS week_start,
date_trunc('month', dt::TIMESTAMP) AS month_start
FROM daily_revenue
),
weekly_agg AS (
SELECT
week_start,
SUM(revenue) AS weekly_revenue,
LAG(SUM(revenue)) OVER (ORDER BY week_start) AS prev_week_revenue
FROM base
GROUP BY week_start
)
SELECT
week_start,
weekly_revenue,
prev_week_revenue,
ROUND(
(weekly_revenue - prev_week_revenue) * 100.0 / prev_week_revenue, 2
) AS wow_pct
FROM weekly_agg
ORDER BY week_start;
运行结果：
┌──────────────┬──────────────────┬──────────────────┬───────────┐
│ week_start │ weekly_revenue │ prev_week_rev │ wow_pct │
├──────────────┼──────────────────┼──────────────────┼───────────┤
│ 2026-06-29 │ 98800 │ NULL │ NULL │
│ 2026-07-06 │ 102300 │ 98800 │ 3.54 │
│ 2026-07-13 │ 96800 │ 102300 │ -5.38 │
│ 2026-07-20 │ 32400 │ 96800 │ -66.53 │
└──────────────┴──────────────────┴──────────────────┴───────────┘
同比增长（YoY）
对于跨年数据，用 DATE_ADD 偏移一年再 JOIN：
WITH yearly AS (
SELECT
DATE_TRUNC('year', dt::TIMESTAMP) AS year_start,
dt::DATE AS date,
revenue
FROM daily_revenue
),
this_year AS (
SELECT date, revenue FROM yearly WHERE year_start = DATE '2026-01-01'
),
last_year AS (
SELECT date, revenue FROM yearly WHERE year_start = DATE '2025-01-01'
)
SELECT
t.date,
t.revenue AS revenue_2026,
l.revenue AS revenue_2025,
ROUND(
(t.revenue - l.revenue) * 100.0 / NULLIF(l.revenue, 0), 2
) AS yoy_pct
FROM this_year t
LEFT JOIN last_year l ON t.date = l.date
ORDER BY t.date;
图：WoW/MoM/YoY 计算逻辑 — 通过 LAG 窗口函数或 DATE_ADD 偏移实现
2. 多时区转换：全球化数据的统一视角
业务场景
你的用户分布在全球，活动日志记录的是各用户本地时间。要做全球统一的日活分析，必须将所有时间统一到同一个时区（通常用 UTC）。
DuckDB 的时区函数
DuckDB 支持 AT TIME ZONE 语法和内置时区列表：
-- 查看支持的时区
SELECT * FROM timezones();
时区转换实战
SELECT
'2026-07-15 14:30:00 America/New_York'::TIMESTAMP AT TIME ZONE 'America/New_York' AS ny_time,
'2026-07-15 14:30:00 America/New_York'::TIMESTAMP AT TIME ZONE 'America/New_York'
AT TIME ZONE 'UTC' AS utc_time,
'2026-07-15 14:30:00 America/New_York'::TIMESTAMP AT TIME ZONE 'America/New_York'
AT TIME ZONE 'Asia/Shanghai' AS shanghai_time,
'2026-07-15 14:30:00 America/New_York'::TIMESTAMP AT TIME ZONE 'America/New_York'
AT TIME ZONE 'Asia/Tokyo' AS tokyo_time;
运行结果：
┌─────────────────────┬─────────────────────┬─────────────────────┬─────────────────────┐
│ ny_time │ utc_time │ shanghai_time │ tokyo_time │
├─────────────────────┼─────────────────────┼─────────────────────┼─────────────────────┤
│ 2026-07-15 14:30:00 │ 2026-07-15 18:30:00 │ 2026-07-16 02:30:00 │ 2026-07-16 03:30:00 │
└─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘
全局日活统计（统一 UTC）
CREATE TABLE user_events AS
SELECT * FROM (VALUES
(1, '2026-07-15 09:00:00 America/Los_Angeles'::TIMESTAMPTZ),
(2, '2026-07-15 12:30:00 America/New_York'::TIMESTAMPTZ),
(3, '2026-07-15 20:00:00 Asia/Shanghai'::TIMESTAMPTZ),
(4, '2026-07-16 02:00:00 Asia/Tokyo'::TIMESTAMPTZ),
(5, '2026-07-15 18:45:00 Europe/London'::TIMESTAMPTZ),
(6, '2026-07-16 01:00:00 Asia/Shanghai'::TIMESTAMPTZ)
) AS t(user_id, event_time);
-- 按 UTC 日期统计全球日活
SELECT
date_trunc('day', event_time) AS utc_date,
COUNT(*) AS daily_active_users
FROM user_events
GROUP BY utc_date
ORDER BY utc_date;
运行结果：
┌────────────┬────────────────┐
│ utc_date │ daily_active │
├────────────┼────────────────┤
│ 2026-07-15 │ 5 │
│ 2026-07-16 │ 1 │
└────────────┴────────────────┘
💡 提示：使用
TIMESTAMPTZ（带时区时间戳）类型存储原始数据，查询时再按需转换目标时区，是最佳实践。
3. 自定义时间桶：业务时段而非自然时段
业务场景
自然小时桶（00:00-00:59）对某些业务没有意义。比如：
- 餐厅：早餐时段（6:00-9:00）、午餐（11:00-13:00）、晚餐（17:00-20:00）
- 交易平台：开盘时段（9:30-11:30）、休市（11:30-13:00）、下午开盘（13:00-15:00）
- 游戏：活跃高峰（20:00-23:00）、低谷期（06:00-10:00）
用 CASE WHEN 实现自定义分桶
CREATE TABLE restaurant_orders AS
SELECT * FROM (VALUES
(TIMESTAMP '2026-07-15 07:10:00', 45.00),
(TIMESTAMP '2026-07-15 07:45:00', 32.00),
(TIMESTAMP '2026-07-15 08:30:00', 58.00),
(TIMESTAMP '2026-07-15 12:05:00', 120.00),
(TIMESTAMP '2026-07-15 12:40:00', 89.00),
(TIMESTAMP '2026-07-15 13:15:00', 67.00),
(TIMESTAMP '2026-07-15 18:00:00', 210.00),
(TIMESTAMP '2026-07-15 18:35:00', 175.00),
(TIMESTAMP '2026-07-15 19:20:00', 198.00),
(TIMESTAMP '2026-07-15 20:10:00', 145.00),
(TIMESTAMP '2026-07-15 22:30:00', 35.00),
(TIMESTAMP '2026-07-15 03:00:00', 28.00)
) AS t(order_time, amount);
SELECT
CASE
WHEN EXTRACT(HOUR FROM order_time) BETWEEN 6 AND 8 THEN '早餐时段 (6-9)'
WHEN EXTRACT(HOUR FROM order_time) BETWEEN 11 AND 13 THEN '午餐时段 (11-13)'
WHEN EXTRACT(HOUR FROM order_time) BETWEEN 17 AND 20 THEN '晚餐时段 (17-20)'
WHEN EXTRACT(HOUR FROM order_time) BETWEEN 20 AND 23 THEN '夜宵时段 (20-23)'
ELSE '其他时段'
END AS meal_period,
COUNT(*) AS order_count,
ROUND(SUM(amount), 2) AS total_revenue,
ROUND(AVG(amount), 2) AS avg_order_value
FROM restaurant_orders
GROUP BY meal_period
ORDER BY
CASE meal_period
WHEN '早餐时段 (6-9)' THEN 1
WHEN '午餐时段 (11-13)' THEN 2
WHEN '晚餐时段 (17-20)' THEN 3
WHEN '夜宵时段 (20-23)' THEN 4
ELSE 5
END;
运行结果：
┌──────────────────┬─────────────┬─────────────────┬──────────────────┐
│ meal_period │ order_count │ total_revenue │ avg_order_value │
├──────────────────┼─────────────┼─────────────────┼──────────────────┤
│ 早餐时段 (6-9) │ 3 │ 135.00 │ 45.00 │
│ 午餐时段 (11-13) │ 3 │ 276.00 │ 92.00 │
│ 晚餐时段 (17-20) │ 3 │ 583.00 │ 194.33 │
│ 夜宵时段 (20-23) │ 1 │ 35.00 │ 35.00 │
│ 其他时段 │ 1 │ 28.00 │ 28.00 │
└──────────────────┴─────────────┴─────────────────┴──────────────────┘
进阶：用 INTERVAL 做动态分桶
对于需要动态调整桶大小的场景，可以结合 generate_series：
-- 生成交易时段的时间桶（9:30-11:30 和 13:00-15:00）
SELECT generate_series(
TIMESTAMP '2026-07-15 09:30:00',
TIMESTAMP '2026-07-15 15:00:00',
INTERVAL '30 min'
) AS time_bucket;
运行结果：
┌─────────────────────┐
│ time_bucket │
├─────────────────────┤
│ 2026-07-15 09:30:00 │
│ 2026-07-15 10:00:00 │
│ 2026-07-15 10:30:00 │
│ ... │
│ 2026-07-15 14:30:00 │
└─────────────────────┘
4. 插值补全：处理传感器数据的缺失值
业务场景
IoT 传感器每隔 5 分钟上报一次数据，但网络抖动会导致某些时间点缺失。直接连线绘图会出现断点，需要用线性插值填补缺失值。
基于相邻点的线性插值
CREATE TABLE sensor_readings AS
SELECT * FROM (VALUES
(TIMESTAMP '2026-07-15 10:00:00', 22.5),
(TIMESTAMP '2026-07-15 10:10:00', 23.1),
(TIMESTAMP '2026-07-15 10:20:00', NULL), -- 缺失
(TIMESTAMP '2026-07-15 10:30:00', NULL), -- 缺失
(TIMESTAMP '2026-07-15 10:40:00', 24.8),
(TIMESTAMP '2026-07-15 10:50:00', NULL), -- 缺失
(TIMESTAMP '2026-07-15 11:00:00', 25.2)
) AS t(ts, temperature);
方法一：前后值取平均（简单插值）
SELECT
ts,
temperature,
COALESCE(
temperature,
ROUND(
(LAG(temperature) OVER (ORDER BY ts)
+ LEAD(temperature) OVER (ORDER BY ts)) / 2.0, 2
)
) AS interpolated_temp
FROM sensor_readings
ORDER BY ts;
运行结果：
┌─────────────────────┬────────────────┬──────────────────┐
│ ts │ temperature │ interpolated_temp │
├─────────────────────┼────────────────┼──────────────────┤
│ 2026-07-15 10:00:00 │ 22.5 │ 22.5 │
│ 2026-07-15 10:10:00 │ 23.1 │ 23.1 │
│ 2026-07-15 10:20:00 │ NULL │ 23.95│
│ 2026-07-15 10:30:00 │ NULL │ 23.95│
│ 2026-07-15 10:40:00 │ 24.8 │ 24.8 │
│ 2026-07-15 10:50:00 │ NULL │ 25.0 │
│ 2026-07-15 11:00:00 │ 25.2 │ 25.2 │
└─────────────────────┴────────────────┴──────────────────┘
方法二：基于时间的精确线性插值
对于等间隔数据，可以用时间比例做精确插值：
WITH base AS (
SELECT
ts,
temperature,
LAG(ts) OVER (ORDER BY ts) AS prev_ts,
LAG(temperature) OVER (ORDER BY ts) AS prev_temp,
LEAD(ts) OVER (ORDER BY ts) AS next_ts,
LEAD(temperature) OVER (ORDER BY ts) AS next_temp
FROM sensor_readings
),
interpolated AS (
SELECT
ts,
temperature,
CASE
WHEN temperature IS NOT NULL THEN temperature
ELSE ROUND(
prev_temp + (next_temp - prev_temp) *
EXTRACT(EPOCH FROM (ts - prev_ts)) /
EXTRACT(EPOCH FROM (next_ts - prev_ts)), 2
)
END AS interp_temp
FROM base
WHERE prev_temp IS NOT NULL AND next_temp IS NOT NULL
)
SELECT ts, temperature, interp_temp FROM interpolated ORDER BY ts;
运行结果：
┌─────────────────────┬────────────────┬──────────────────┐
│ ts │ temperature │ interpolated_temp │
├─────────────────────┼────────────────┼──────────────────┤
│ 2026-07-15 10:20:00 │ NULL │ 23.6 │
│ 2026-07-15 10:30:00 │ NULL │ 24.2 │
│ 2026-07-15 10:50:00 │ NULL │ 25.0 │
└─────────────────────┴────────────────┴──────────────────┘
10:20 的插值：23.1 + (24.8 - 23.1) × 20/30 = 23.1 + 1.13 = 24.23 ≈ 24.2（按时间比例精确计算）
方法三：用 generate_series + 前向填充补全完整时间线
-- 先生成完整的时间序列，再用前向填充补缺失值
WITH full_series AS (
SELECT generate_series(
TIMESTAMP '2026-07-15 10:00:00',
TIMESTAMP '2026-07-15 11:00:00',
INTERVAL '10 min'
) AS ts
),
merged AS (
SELECT
s.ts,
r.temperature
FROM full_series s
LEFT JOIN sensor_readings r ON s.ts = r.ts
)
SELECT
ts,
temperature,
-- 前向填充：用最后一个非空值填补缺失
LAST_VALUE(temperature IGNORE NULLS) OVER (
ORDER BY ts
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS filled_temp
FROM merged
ORDER BY ts;
运行结果：
┌─────────────────────┬────────────────┬──────────────────┐
│ ts │ temperature │ filled_temp │
├─────────────────────┼────────────────┼──────────────────┤
│ 2026-07-15 10:00:00 │ 22.5 │ 22.5 │
│ 2026-07-15 10:10:00 │ 23.1 │ 23.1 │
│ 2026-07-15 10:20:00 │ NULL │ 23.1 │
│ 2026-07-15 10:30:00 │ NULL │ 23.1 │
│ 2026-07-15 10:40:00 │ 24.8 │ 24.8 │
│ 2026-07-15 10:50:00 │ NULL │ 24.8 │
│ 2026-07-15 11:00:00 │ 25.2 │ 25.2 │
└─────────────────────┴────────────────┴──────────────────┘
图：三种插值方法对比 — 简单平均、时间比例精确插值、前向填充补全
综合实战：全栈时间序列分析仪表盘
将以上技巧整合到一个完整的分析查询中：
-- 模拟一份包含时区、自定义时段、缺失值的综合数据
WITH timezone_events AS (
SELECT * FROM (VALUES
(1, TIMESTAMP '2026-07-15 08:30:00+08:00', 'login'),
(2, TIMESTAMP '2026-07-15 09:15:00+08:00', 'purchase'),
(3, TIMESTAMP '2026-07-15 10:00:00+08:00', 'login'),
(4, TIMESTAMP '2026-07-15 14:30:00+08:00', 'purchase'),
(5, TIMESTAMP '2026-07-15 15:45:00+08:00', 'login'),
(6, TIMESTAMP '2026-07-16 09:00:00+08:00', 'purchase'),
(7, TIMESTAMP '2026-07-16 10:30:00+08:00', 'login'),
(8, TIMESTAMP '2026-07-16 16:00:00+08:00', 'purchase')
) AS t(user_id, event_time_tz, event_type)
),
-- 统一转为 UTC
utc_events AS (
SELECT
user_id,
event_time_tz AT TIME ZONE 'Asia/Shanghai' AS event_time_utc,
event_type
FROM timezone_events
),
-- 按小时分桶 + 自定义时段标记
hourly AS (
SELECT
date_trunc('hour', event_time_utc) AS hour_bucket,
event_type,
CASE
WHEN EXTRACT(HOUR FROM event_time_utc) BETWEEN 8 AND 11 THEN '上午时段'
WHEN EXTRACT(HOUR FROM event_time_utc) BETWEEN 12 AND 14 THEN '中午时段'
WHEN EXTRACT(HOUR FROM event_time_utc) BETWEEN 15 AND 18 THEN '下午时段'
ELSE '其他时段'
END AS period_label
FROM utc_events
),
-- 周期对比（用 LAG 计算每小时增量）
with_lag AS (
SELECT
hour_bucket,
period_label,
event_type,
COUNT(*) AS event_count,
LAG(COUNT(*)) OVER (
PARTITION BY event_type ORDER BY hour_bucket
) AS prev_hour_count
FROM hourly
GROUP BY hour_bucket, period_label, event_type
)
SELECT
hour_bucket,
period_label,
event_type,
event_count,
prev_hour_count,
CASE
WHEN prev_hour_count IS NOT NULL THEN
ROUND((event_count - prev_hour_count) * 100.0 / prev_hour_count, 1)
ELSE NULL
END AS hour_over_hour_pct
FROM with_lag
ORDER BY hour_bucket, event_type;
运行结果：
┌─────────────────────┬────────────┬────────────┬─────────────┬─────────────────┬───────────────────┐
│ hour_bucket │period_label│event_type │event_count│ prev_hour_count │ hour_over_hour_… │
├─────────────────────┼────────────┼────────────┼─────────────┼─────────────────┼───────────────────┤
│ 2026-07-15 00:00:00 │ 其他时段 │ login │ 0 │ NULL │ NULL │
│ 2026-07-15 01:00:00 │ 其他时段 │ login │ 0 │ 0 │ 0.0 │
...
│ 2026-07-15 08:00:00 │ 上午时段 │ login │ 1 │ 0 │ NULL │
│ 2026-07-15 09:00:00 │ 上午时段 │ purchase │ 1 │ 0 │ NULL │
│ 2026-07-15 10:00:00 │ 上午时段 │ login │ 1 │ 1 │ 0.0 │
│ 2026-07-15 14:00:00 │ 中午时段 │ purchase │ 1 │ 0 │ NULL │
│ 2026-07-15 15:00:00 │ 下午时段 │ login │ 1 │ 0 │ NULL │
│ 2026-07-16 01:00:00 │ 其他时段 │ purchase │ 0 │ 0 │ 0.0 │
│ 2026-07-16 09:00:00 │ 上午时段 │ purchase │ 1 │ 0 │ NULL │
│ 2026-07-16 10:00:00 │ 上午时段 │ login │ 1 │ 0 │ NULL │
│ 2026-07-16 16:00:00 │ 下午时段 │ purchase │ 1 │ 0 │ NULL │
└─────────────────────┴────────────┴────────────┴─────────────┴─────────────────┴───────────────────┘
总结
| 技巧 | 核心函数 | 适用场景 |
|---|---|---|
| 周期对比 | LAG / DATE_ADD | WoW / MoM / YoY 报表 |
| 时区转换 | AT TIME ZONE | 全球化数据分析 |
| 自定义时间桶 | CASE WHEN + EXTRACT | 业务时段分析 |
| 插值补全 | LAST_VALUE IGNORE NULLS | 传感器/日志数据补全 |
DuckDB 的时间序列能力不仅限于基础的 date_trunc 和 generate_series。掌握周期对比、时区处理、自定义分桶和插值补全后，你可以应对从实时监控告警到跨时区报表生成的各类复杂场景。
更多 DuckDB 实战技巧，请关注 DuckDB Lab（duckdblab.org）