DuckDB实战：时间序列数据分析与滚动聚合

DuckDB实战：时间序列数据分析与滚动聚合

深入讲解DuckDB中时间序列分析的三大核心技巧：date_trunc分组聚合、generate_series填充缺失时间戳、以及窗口函数实现滚动窗口计算。包含完整SQL示例和业务场景。

在数据分析的日常工作中，时间序列分析是最常见也最具挑战性的任务之一。无论是电商平台的销售趋势监控、金融领域的行情分析，还是IoT设备的传感器数据解读，都离不开对时间维度数据的深度挖掘。

DuckDB 提供了强大的时间序列分析能力，本文将以真实的业务场景为例，演示如何使用 date_trunc、generate_series 和窗口函数来实现高效的滚动聚合分析。

场景一：按时间粒度聚合——date_trunc 的妙用

业务需求

某电商平台需要统计每日、每周和每月的订单总额及订单数量，以便生成多维度的经营报表。传统做法是在应用层做时间分组，但在 DuckDB 中，我们可以直接用 SQL 完成。

数据准备

假设我们有一张订单表 orders，包含以下字段：

CREATE TABLE orders (
    order_id BIGINT,
    customer_id BIGINT,
    order_date TIMESTAMP,
    amount DECIMAL(12, 2),
    category VARCHAR
);

插入示例数据：

INSERT INTO orders VALUES
(1, 101, '2026-06-01 10:30:00', 299.99, 'electronics'),
(2, 102, '2026-06-01 14:15:00', 59.90, 'books'),
(3, 103, '2026-06-02 09:00:00', 1299.00, 'electronics'),
(4, 101, '2026-06-02 16:45:00', 89.50, 'clothing'),
(5, 104, '2026-06-03 11:20:00', 450.00, 'home'),
(6, 105, '2026-06-08 08:30:00', 199.99, 'books'),
(7, 102, '2026-06-09 13:00:00', 780.00, 'electronics'),
(8, 106, '2026-06-15 10:00:00', 45.00, 'clothing'),
(9, 103, '2026-06-15 15:30:00', 320.00, 'home'),
(10, 101, '2026-06-22 09:45:00', 560.00, 'electronics');

按日、周、月聚合

-- 按日聚合
SELECT
    date_trunc('day', order_date) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY date_trunc('day', order_date)
ORDER BY day;

-- 按周聚合（ISO周）
SELECT
    date_trunc('week', order_date) AS week_start,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    ROUND(AVG(amount), 2) AS avg_order_value
FROM orders
GROUP BY date_trunc('week', order_date)
ORDER BY week_start;

-- 按月聚合
SELECT
    date_trunc('month', order_date) AS month_start,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    ROUND(AVG(amount), 2) AS avg_order_value
FROM orders
GROUP BY date_trunc('month', order_date)
ORDER BY month_start;

关键知识点

date_trunc 支持的时间粒度包括：year、quarter、month、week、day、hour、minute、second。这对于灵活的多维度报表生成非常有用。

场景二：填充缺失时间段——generate_series 的力量

业务痛点

上面的按日聚合结果中，如果某天没有订单，这一天就不会出现在结果集中。但在制作趋势图表时，我们需要连续的时间轴，缺失的日期应该显示为零值。

解决方案

DuckDB 的 generate_series 函数可以生成连续的时间序列，配合 LEFT JOIN 即可填充缺失值。

WITH date_range AS (
    SELECT generate_series(
        date_trunc('day', MIN(order_date)),
        date_trunc('day', MAX(order_date)),
        INTERVAL '1 day'
    ) AS day
    FROM orders
),
daily_sales AS (
    SELECT
        date_trunc('day', order_date) AS day,
        COUNT(*) AS order_count,
        COALESCE(SUM(amount), 0) AS total_amount
    FROM orders
    GROUP BY date_trunc('day', order_date)
)
SELECT
    dr.day,
    COALESCE(ds.order_count, 0) AS order_count,
    COALESCE(ds.total_amount, 0.00) AS total_amount
FROM date_range dr
LEFT JOIN daily_sales ds ON dr.day = ds.day
ORDER BY dr.day;

这个查询的执行逻辑是：

date_range CTE 使用 generate_series 生成从最早订单日期到最晚订单日期的每一天
daily_sales CTE 计算有订单的每日销售数据
通过 LEFT JOIN 将两者关联，缺失的日期自然补零

按小时粒度的填充

同样的方法可以扩展到更细的时间粒度：

WITH hour_range AS (
    SELECT generate_series(
        date_trunc('hour', MIN(order_date)),
        date_trunc('hour', MAX(order_date)),
        INTERVAL '1 hour'
    ) AS hour_slot
    FROM orders
),
hourly_sales AS (
    SELECT
        date_trunc('hour', order_date) AS hour_slot,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY date_trunc('hour', order_date)
)
SELECT
    h.hour_slot,
    COALESCE(s.order_count, 0) AS order_count,
    COALESCE(s.total_amount, 0.00) AS total_amount
FROM hour_range h
LEFT JOIN hourly_sales s ON h.hour_slot = s.hour_slot
ORDER BY h.hour_slot;

场景三：滚动窗口分析——移动平均与累计指标

业务需求

运营团队希望看到过去7天的销售额移动平均值，以及从月初到当天的累计销售额。这可以帮助他们识别趋势变化，而不是被单日波动所误导。

7日移动平均

SELECT
    date_trunc('day', order_date) AS sale_date,
    SUM(amount) AS daily_revenue,
    ROUND(
        AVG(SUM(amount)) OVER (
            ORDER BY date_trunc('day', order_date)
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS ma_7day,
    COUNT(*) OVER (
        ORDER BY date_trunc('day', order_date)
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS days_in_window
FROM orders
GROUP BY date_trunc('day', order_date)
ORDER BY sale_date;

累计销售额（YTD）

SELECT
    date_trunc('day', order_date) AS sale_date,
    SUM(amount) AS daily_revenue,
    ROUND(SUM(SUM(amount)) OVER (
        ORDER BY date_trunc('day', order_date)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ), 2) AS cumulative_revenue,
    ROUND(AVG(SUM(amount)) OVER (
        ORDER BY date_trunc('day', order_date)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ), 2) AS avg_daily_revenue
FROM orders
GROUP BY date_trunc('day', order_date)
ORDER BY sale_date;

滑动窗口增长率

WITH daily_stats AS (
    SELECT
        date_trunc('day', order_date) AS sale_date,
        SUM(amount) AS daily_revenue
    FROM orders
    GROUP BY date_trunc('day', order_date)
)
SELECT
    sale_date,
    daily_revenue,
    LAG(daily_revenue, 1) OVER (ORDER BY sale_date) AS prev_day_revenue,
    ROUND(
        CASE WHEN LAG(daily_revenue, 1) OVER (ORDER BY sale_date) > 0
            THEN ((daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY sale_date))
                  / LAG(daily_revenue, 1) OVER (ORDER BY sale_date)) * 100
            ELSE NULL
        END,
        2
    ) AS day_over_day_growth_pct
FROM daily_stats
ORDER BY sale_date;

这里使用了 LAG 窗口函数来获取前一天的销售额，进而计算日环比增长率。

场景四：周期对比分析

同比与环比

在商业分析中，周期对比是非常常见的需求。

WITH monthly_sales AS (
    SELECT
        date_trunc('month', order_date) AS month_start,
        SUM(amount) AS monthly_revenue,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY date_trunc('month', order_date)
)
SELECT
    month_start,
    monthly_revenue,
    order_count,
    LAG(monthly_revenue, 1) OVER (ORDER BY month_start) AS prev_month_revenue,
    ROUND(
        CASE WHEN LAG(monthly_revenue, 1) OVER (ORDER BY month_start) > 0
            THEN ((monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY month_start))
                  / LAG(monthly_revenue, 1) OVER (ORDER BY month_start)) * 100
            ELSE NULL
        END,
        2
    ) AS mom_growth_pct,
    LEAD(monthly_revenue, 1) OVER (ORDER BY month_start) AS next_month_revenue
FROM monthly_sales
ORDER BY month_start;

LAG 获取前一个周期的值用于环比，LEAD 获取下一个周期的值用于预测参考。

性能优化建议

1. 分区裁剪

对于大规模时间序列数据，建议按时间分区：

-- 创建按月份分区的表
CREATE TABLE orders_partitioned (
    order_id BIGINT,
    customer_id BIGINT,
    order_date TIMESTAMP,
    amount DECIMAL(12, 2),
    category VARCHAR
) PARTITION BY (order_date);

-- DuckDB 会自动利用分区进行裁剪
SELECT * FROM orders_partitioned
WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01';

2. 使用 STRIPE_METADATA

对于 Parquet 文件存储的时间序列数据，DuckDB 可以利用 stripe metadata 快速定位数据范围：

-- DuckDB 自动读取 Parquet 文件的 stripe metadata
SELECT date_trunc('month', order_date) AS month,
       SUM(amount) AS revenue
FROM read_parquet('/data/sales/*.parquet')
WHERE order_date >= '2026-01-01'
GROUP BY month;

3. 预聚合表

对于频繁查询的场景，可以创建预聚合表：

CREATE TABLE daily_sales_summary AS
SELECT
    date_trunc('day', order_date) AS sale_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY date_trunc('day', order_date);

总结

DuckDB 在时间序列分析方面表现出色，主要得益于以下几个核心函数：

函数	用途	示例
`date_trunc`	时间粒度截断	`date_trunc('day', ts)`
`generate_series`	生成连续时间序列	`generate_series(start, end, interval)`
`LAG/LEAD`	前后行访问	`LAG(value, 1) OVER (ORDER BY time)`
窗口框架	滚动计算	`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`

掌握这些工具，你就能应对绝大多数时间序列分析场景。

更多 DuckDB 实战技巧，请关注 DuckDB Lab（duckdblab.org）

📺 Watch video tutorials → Olap Studio YouTube

Subscribe for more DuckDB & AI automation tutorials

⚠️ 本站为独立社区项目，与 DuckDB 基金会及 DuckDB 官方项目无任何从属、背书或赞助关系。

"DuckDB" 是 DuckDB 基金会的注册商标，本站仅以事实描述方式使用该名称。

本站内容仅供教育与社区推广用途，不构成任何商业服务。