Redshift中基于起始日期生成递增日期行：用户订阅表按月拆分需求实现

Hey there! I know splitting subscription data into individual monthly rows can be a pain in Redshift since generate_series isn't consistently reliable. Let's walk through a solid workaround using a number series table—this approach works every time, even without generate_series.

The Approach #

Since we can't rely on generate_series, we'll create a temporary (or permanent) list of sequential numbers to act as month offsets. We'll join this list to your subscription table, then calculate each month's start date and adjust the payment amount as needed.

Full SQL Solution #

First, here's the query that will produce exactly the output you're looking for:

WITH numbers AS (
    -- Create a list of numbers starting at 0, extend this if you have longer subscriptions
    SELECT 0 AS n UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
)
SELECT 
    t.userid,
    -- Only show the paid amount for the first month, set others to 0
    CASE WHEN n.n = 0 THEN t.amount_paid ELSE 0 END AS amount_paid,
    -- Calculate the start date for each month by adding the offset
    DATEADD(month, n.n, t.start_date) AS start_date,
    t.months
FROM your_subscription_table t
-- Join to get one row per month in the subscription term
JOIN numbers n ON n.n < t.months
ORDER BY userid, start_date;

Breakdown of the Query #

• Numbers CTE: This creates a temporary list of integers starting at 0. If you have subscriptions longer than 6 months, just add more SELECT X UNION ALL lines to cover the maximum possible months value in your table.
• DATEADD: This function shifts the original start_date by n months to get the first day of each subsequent month in the subscription.
• CASE Statement: Ensures only the first month (offset 0) shows the actual amount_paid—all other months get set to 0, matching your desired output.
• Join Condition: n.n < t.months guarantees we generate exactly months rows per original record (e.g., a 3-month subscription becomes 3 rows).

Optimize for Larger Datasets #

If you're working with a lot of data or have long-term subscriptions, create a permanent number series table instead of using a CTE—it's more efficient:

-- Create a permanent number table (adjust the range to fit your needs)
CREATE TABLE number_series (n INT PRIMARY KEY);

-- Insert sequential numbers (you can automate this with a loop if needed)
INSERT INTO number_series VALUES
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
(11), (12), ..., (120); -- Covers up to 10 years of subscriptions

Then modify the original query to use this table instead of the CTE:

SELECT 
    t.userid,
    CASE WHEN ns.n = 0 THEN t.amount_paid ELSE 0 END AS amount_paid,
    DATEADD(month, ns.n, t.start_date) AS start_date,
    t.months
FROM your_subscription_table t
JOIN number_series ns ON ns.n < t.months
ORDER BY userid, start_date;

Verify the Output #

Using your sample data, this query will return exactly what you're expecting:

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