SQL
Generating Sequential Data with SQL Recursive CTEs (e.g., Dates)
Create a virtual table of sequential numbers or dates on the fly using SQL recursive CTEs, useful for filling data gaps or generating reports.
WITH RECURSIVE DateSeries (report_date) AS (
SELECT '2023-01-01'::DATE AS report_date -- Anchor member: Starting date
UNION ALL
SELECT (report_date + INTERVAL '1 day')::DATE -- Recursive member: Next date
FROM DateSeries
WHERE report_date < '2023-01-07'::DATE -- Termination condition
)
SELECT report_date
FROM DateSeries;
How it works: A recursive Common Table Expression (CTE) allows a query to refer to itself, enabling the generation of sequential data like a series of dates or numbers. This snippet demonstrates how to create a virtual table `DateSeries` that lists each date from '2023-01-01' to '2023-01-07'. The 'anchor member' defines the starting point, and the 'recursive member' generates subsequent rows until the 'termination condition' is met, making it invaluable for time-series analysis or report scaffolding.