如何在PostgreSQL/Vertica中基于日期范围做补全与插值？

Let's walk through how to fill in the missing dates and interpolate the cumulative values for your dataset. The approach will generate the full date range, pair it with all your dimension combinations, then fill in the missing values exactly as you need.

Step-by-Step SQL Implementation #

WITH temp_data AS (
 SELECT '2017-01-03'::DATE AS e_date, 'uk'::VARCHAR AS cnt, 1::int AS cnt_id, 10::int AS numbers, 10::int AS cumulative_num
 UNION ALL
 SELECT '2017-01-05'::DATE AS e_date, 'uk'::VARCHAR AS cnt, 1::int AS cnt_id, 20::int AS numbers, 30::int AS cumulative_num
 UNION ALL
 SELECT '2017-01-07'::DATE AS e_date, 'uk'::VARCHAR AS cnt, 1::int AS cnt_id, 40::int AS numbers, 70::int AS cumulative_num
 UNION ALL
 SELECT '2017-01-03'::DATE AS e_date, 'fr'::VARCHAR AS cnt, 2::int AS cnt_id, 100::int AS numbers, 100::int AS cumulative_num
 UNION ALL
 SELECT '2017-01-05'::DATE AS e_date, 'fr'::VARCHAR AS cnt, 2::int AS cnt_id, 200::int AS numbers, 300::int AS cumulative_num
 UNION ALL
 SELECT '2017-01-07'::DATE AS e_date, 'fr'::VARCHAR AS cnt, 2::int AS cnt_id, 500::int AS numbers, 800::int AS cumulative_num
),
-- Generate full date range (works in both PostgreSQL and Vertica)
date_range AS (
 SELECT generate_series('2017-01-01'::DATE, '2017-01-08'::DATE, '1 day'::INTERVAL)::DATE AS e_date
 -- Fallback recursive CTE if generate_series isn't available in your Vertica setup:
 -- SELECT '2017-01-01'::DATE AS e_date
 -- UNION ALL
 -- SELECT e_date + 1 FROM date_range WHERE e_date < '2017-01-08'::DATE
),
-- Get unique dimension pairs from your source data
dimensions AS (
 SELECT DISTINCT cnt, cnt_id FROM temp_data
),
-- Create all possible date-dimension combinations
full_dataset AS (
 SELECT dr.e_date, d.cnt, d.cnt_id
 FROM date_range dr
 CROSS JOIN dimensions d
)
-- Join with source data and fill missing values
SELECT 
 fd.e_date,
 fd.cnt,
 fd.cnt_id,
 COALESCE(td.numbers, 0) AS num,
 -- Forward-fill cumulative_num, default to 0 for dates before first entry
 COALESCE(
   LAST_VALUE(td.cumulative_num) OVER (
     PARTITION BY fd.cnt_id 
     ORDER BY fd.e_date 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   ),
   0
 ) AS cumulative_num
FROM full_dataset fd
LEFT JOIN temp_data td 
 ON fd.e_date = td.e_date 
 AND fd.cnt = td.cnt 
 AND fd.cnt_id = td.cnt_id
ORDER BY fd.cnt_id, fd.e_date;

Key Details Explained #

• date_range: Generates every date in your target range. Use the generate_series version for simplicity, or the recursive CTE if your Vertica environment has restrictions on generate_series.
• dimensions: Extracts unique cnt/cnt_id pairs to ensure every dimension gets the full date range coverage.
• full_dataset: Cross joins dates and dimensions to create all required rows before filling in actual data.
• Value Filling:
  • num (formerly numbers) uses COALESCE to replace missing values with 0, matching your expected output.
  • cumulative_num uses the LAST_VALUE window function to carry forward the last known non-null value. We wrap it in COALESCE to set the initial missing dates (like 2017-01-01 and 2017-01-02) to 0.

Performance Tip #

Use UNION ALL instead of UNION in the temp_data CTE—since we don't need to deduplicate rows, this will run faster and avoid unnecessary processing.

内容的提问来源于stack exchange，提问作者axnet