Completing Missing Time Series in DuckDB: generate_series + LAST_VALUE in Action

Your boss wants “daily sales data for last week,” but your table only has dates with transactions. What do you do with the missing days?

This is one of the most classic problems data analysts face: gaps in time series data.

When you export sales records from a business database, typically only dates with actual transactions appear. Days with zero activity simply don’t exist in the table. But your boss wants data for every single day — even days with zero sales need to show up.

This article covers three time series completion strategies in DuckDB, from simple zero-filling to advanced forward fill and linear interpolation.

The Problem Scenario: Missing Sales Data

Suppose you have a sales table recording daily revenue:

CREATE TABLE sales AS
SELECT * FROM VALUES
 ('2024-01-01', 1500),
 ('2024-01-02', 2300),
 ('2024-01-04', 1800),
 ('2024-01-05', 3200)
AS t(day, amount);

Notice: January 3rd has no transaction record, so it doesn’t exist in the table.

If you aggregate directly by date, you get:

day | amount
-----------+-------
2024-01-01 | 1500
2024-01-02 | 2300
2024-01-04 | 1800
2024-01-05 | 3200

The gap on Jan 3 breaks trend lines, skews moving averages, and creates visual discontinuities in charts.

Step One: Generate a Complete Date Sequence

DuckDB’s generate_series() function is the starting point for solving this problem. It can produce a continuous date sequence:

WITH dates AS (
 SELECT unnest(generate_series(
 DATE '2024-01-01', 
 DATE '2024-01-05', 
 INTERVAL '1' DAY
 )) AS day
)
SELECT * FROM dates;

Output:

 day 
-------------------
 2024-01-01
 2024-01-02
 2024-01-03
 2024-01-04
 2024-01-05

Key points:

• generate_series(start, end, step) produces a sequence from start to end
• unnest() expands the generated array into rows
• Intervals can be INTERVAL '1' DAY, INTERVAL '1' HOUR, etc.

Step Two: LEFT JOIN to Fill Data

After generating a complete date sequence, use LEFT JOIN to attach actual sales data:

WITH dates AS (
 SELECT unnest(generate_series(
 DATE '2024-01-01', 
 DATE '2024-01-05', 
 INTERVAL '1' DAY
 )) AS day
)
SELECT d.day, COALESCE(s.amount, 0) AS amount
FROM dates d
LEFT JOIN sales s ON d.day = s.day;

Output:

 day | amount
------------------+-------
 2024-01-01 | 1500
 2024-01-02 | 2300
 2024-01-03 | 0 ← filled with zero
 2024-01-04 | 1800
 2024-01-05 | 3200

COALESCE(s.amount, 0) replaces NULL with 0. This is simple and effective for count-type metrics (like order count, visits).

Strategy One: Zero-Filling (COALESCE)

Best for: Order counts, visits, event counts — metrics that can reasonably be assumed to be zero.

WITH dates AS (
 SELECT unnest(generate_series(
 DATE '2024-01-01', 
 DATE '2024-01-05', 
 INTERVAL '1' DAY
 )) AS day
)
SELECT d.day, COALESCE(s.amount, 0) AS amount
FROM dates d
LEFT JOIN sales s ON d.day = s.day;

Strategy Two: Forward Fill (Last Observation Carried Forward)

Best for: Stock prices, inventory levels, device states — metrics that don’t change abruptly.

If the price on Jan 2 was 2300 and on Jan 4 was 1800, the most reasonable estimate for Jan 3 is 2300 (carry forward the last observation), not 0 or 2050 (average).

DuckDB’s LAST_VALUE(...) IGNORE NULLS window function implements this perfectly:

WITH dates AS (
 SELECT unnest(generate_series(
 DATE '2024-01-01', 
 DATE '2024-01-05', 
 INTERVAL '1' DAY
 )) AS day
)
SELECT d.day,
 LAST_VALUE(s.amount IGNORE NULLS) OVER (
 ORDER BY d.day 
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS forward_filled
FROM dates d
LEFT JOIN sales s ON d.day = s.day;

Output:

 day | forward_filled
-------------------+---------------
 2024-01-01 | 1500
 2024-01-02 | 2300
 2024-01-03 | 2300 ← forward filled
 2024-01-04 | 1800
 2024-01-05 | 3200

Key Parameters Explained

1. IGNORE NULLS: Tells the window function to skip NULL values and take the nearest non-null value
2. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Defines the window range as “from the first row to the current row”
3. Without IGNORE NULLS, the window function returns NULL when encountering a NULL value

Why Do You Need IGNORE NULLS?

Standard SQL window functions break accumulation when encountering NULL. Adding IGNORE NULLS makes LAST_VALUE skip the NULLs produced by the JOIN and always take the most recent observed value.

Strategy Three: Linear Interpolation

Best for: Sensor readings, temperature data, traffic metrics — data that changes smoothly over time.

Linear interpolation assumes the change between two known data points is linear, calculating missing values through a formula:

WITH dates AS (
 SELECT unnest(generate_series(
 DATE '2024-01-01', 
 DATE '2024-01-05', 
 INTERVAL '1' DAY
 )) AS day
),
joined AS (
 SELECT d.day, s.amount
 FROM dates d
 LEFT JOIN sales s ON d.day = s.day
),
with_lag_lead AS (
 SELECT 
 day,
 amount,
 LAG(amount) OVER (ORDER BY day) AS prev_amount,
 LEAD(amount) OVER (ORDER BY day) AS next_amount,
 LAG(day) OVER (ORDER BY day) AS prev_day,
 LEAD(day) OVER (ORDER BY day) AS next_day
 FROM joined
)
SELECT 
 day,
 CASE 
 WHEN amount IS NOT NULL THEN amount
 WHEN prev_amount IS NOT NULL AND next_amount IS NOT NULL THEN
 prev_amount + (next_amount - prev_amount) * 
 (day - prev_day::DATE)::INTEGER / 
 ((next_day - prev_day::DATE)::INTEGER + 1)
 WHEN prev_amount IS NOT NULL THEN prev_amount
 WHEN next_amount IS NOT NULL THEN next_amount
 ELSE 0
 END AS interpolated
FROM with_lag_lead;

Output:

 day | interpolated
-------------------+-------------
 2024-01-01 | 1500
 2024-01-02 | 2300
 2024-01-03 | 2050.0 ← linear interpolation
 2024-01-04 | 1800
 2024-01-05 | 3200

Calculation: (2300 + (1800 - 2300) * 1 / 2) = 2300 - 250 = 2050

Comparison of Three Strategies

Advanced: Grouped Time Series Completion

In real-world scenarios, you often need to complete time series per product, region, or user. DuckDB supports this too:

WITH dates AS (
 SELECT unnest(generate_series(
 DATE '2024-01-01', 
 DATE '2024-01-05', 
 INTERVAL '1' DAY
 )) AS day
),
products AS (
 SELECT * FROM VALUES ('Phone'), ('Laptop'), ('Tablet') AS t(name)
),
date_product AS (
 SELECT d.day, p.name
 FROM dates d
 CROSS JOIN products p
),
joined AS (
 SELECT dp.day, dp.name, s.amount
 FROM date_product dp
 LEFT JOIN sales s ON dp.day = s.day
)
SELECT 
 day,
 name,
 LAST_VALUE(amount IGNORE NULLS) OVER (
 PARTITION BY name ORDER BY day
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS forward_filled
FROM joined;

The key here is PARTITION BY name — each product gets its own independent forward fill timeline.

Comparison with Traditional Tools

Performance Tips

• For small datasets (< 1M rows), all methods above are very efficient
• For large datasets, consider adding partition filters to generate_series to reduce Cartesian product size
• If the time range is large (e.g., year-long), consider generating weekly or monthly sequences first, then refining as needed

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。