Featured image of post DuckDB in Action: Time Series Analysis and Rolling Aggregations
实战

DuckDB in Action: Time Series Analysis and Rolling Aggregations

Deep dive into three core time series analysis techniques in DuckDB: date_trunc grouping, generate_series for filling gaps, and window functions for rolling calculations. With complete SQL examples and real business scenarios.

中文

Time series analysis is one of the most common yet challenging tasks in data analysis. Whether it’s monitoring sales trends in e-commerce platforms, analyzing market data in finance, or interpreting sensor data from IoT devices, deep mining of time-dimension data is essential.

DuckDB provides powerful time series analysis capabilities. This article demonstrates how to use date_trunc, generate_series, and window functions to achieve efficient rolling aggregation analysis through real business scenarios.

Scenario 1: Aggregation by Time Granularity — The Power of date_trunc

Business Requirement

An e-commerce platform needs to calculate daily, weekly, and monthly order totals and quantities to generate multi-dimensional business reports. While traditionally done in the application layer, DuckDB lets us accomplish this directly in SQL.

Data Setup

Assume we have an orders table with the following fields:

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

Sample data:

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');

Daily, Weekly, Monthly Aggregation

-- Daily aggregation
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;
-- Weekly aggregation (ISO week)
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;
-- Monthly aggregation
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;

Key Takeaway

date_trunc supports time granularities including: year, quarter, month, week, day, hour, minute, second. This flexibility is invaluable for multi-dimensional reporting.

Scenario 2: Filling Missing Time Periods — The Power of generate_series

The Pain Point

In the daily aggregation results above, days without orders won’t appear in the result set. However, when creating trend charts, we need a continuous timeline — missing dates should show zero values.

Solution

DuckDB’s generate_series function can generate continuous time sequences. Combined with LEFT JOIN, it fills in missing values seamlessly.

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;

The query logic is:

  1. The date_range CTE uses generate_series to create every day from the earliest to the latest order date
  2. The daily_sales CTE calculates sales data for days that have orders
  3. A LEFT JOIN connects them, with missing dates naturally filled with zeros

Hourly Granularity

The same approach scales to finer time granularity:

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;

Scenario 3: Rolling Window Analysis — Moving Averages and Cumulative Metrics

Business Requirement

The operations team wants to see the 7-day moving average of sales revenue and cumulative revenue from the beginning of the month. This helps identify trend changes rather than being misled by single-day fluctuations.

7-Day Moving Average

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;

Year-to-Date Cumulative Revenue

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;

Rolling Window Growth Rate

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;

Here, the LAG window function accesses the previous day’s revenue to calculate the day-over-day growth rate.

Scenario 4: Period-over-Period Comparison

Month-over-Month Analysis

Period comparison is a very common requirement in business analytics.

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 retrieves the previous period’s value for MoM comparison, while LEAD retrieves the next period’s value for forecasting reference.

Performance Optimization Tips

1. Partition Pruning

For large-scale time series data, consider partitioning by time:

-- Create a partitioned table by month
CREATE TABLE orders_partitioned (
    order_id BIGINT,
    customer_id BIGINT,
    order_date TIMESTAMP,
    amount DECIMAL(12, 2),
    category VARCHAR
) PARTITION BY (order_date);

-- DuckDB automatically leverages partition pruning
SELECT * FROM orders_partitioned
WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01';

2. Stripe Metadata Utilization

For time series data stored in Parquet files, DuckDB can leverage stripe metadata for fast data location:

-- DuckDB automatically reads 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. Pre-Aggregated Tables

For frequently queried scenarios, create pre-aggregated tables:

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);

Summary

DuckDB excels in time series analysis, thanks to these core functions:

FunctionPurposeExample
date_truncTime granularity truncationdate_trunc('day', ts)
generate_seriesGenerate continuous time seriesgenerate_series(start, end, interval)
LAG/LEADAccess previous/next rowsLAG(value, 1) OVER (ORDER BY time)
Window FrameRolling calculationsROWS BETWEEN 6 PRECEDING AND CURRENT ROW

Master these tools, and you can handle the vast majority of time series analysis scenarios.

For more DuckDB tips and tricks, follow DuckDB Lab (duckdblab.org).

← Back to Posts
Previous DuckDB in Action: Memory Management & Performance Tuning — Mastering memory_limit, threads, and partition table optimization

📺 Watch video tutorials → Olap Studio YouTube

Subscribe for more DuckDB & AI automation tutorials

© 2026 Olap Studio - DuckDB Tutorials & Data Analysis
Built with Hugo
Theme Stack designed by Jimmy

⚠️ This site is an independent community project, not affiliated with, endorsed by, or sponsored by the DuckDB Foundation or official DuckDB project.

"DuckDB" is a registered trademark of the DuckDB Foundation. This site uses the name solely for factual description purposes.

All content is for educational and community promotion purposes only and does not constitute any commercial service.