sql - 如何根据条件从1条记录创建更多记录（SQL）(How to create more records from 1 record based on condition (SQL))

Question

I am trying to create more records from 1 record based on some conditions.

(我试图根据某些条件从1条记录创建更多记录。)

For example: If the user purchased a monthly package, the table will create records for each week until the Date Ended.

(例如：如果用户购买了每月套餐，则该表将创建直到截止日期为止的每周的记录。)

From this table:

(从此表：)

|Date Purchased| Date Ended| Package|
|2019-1-1      | 2019-2-1  | 1Month |

To this:

(对此：)

|Date Purchased| Date Ended| Package|
|2019-1-1      | 2019-2-1  | 1Month |
|2019-1-8      | 2019-2-1  | 1Month |
|2019-1-15     | 2019-2-1  | 1Month |
|2019-1-23     | 2019-2-1  | 1Month |

Any ideas on how to do this in SQL?

(关于如何在SQL中执行此操作的任何想法？)

Thanks

(谢谢)

  ask by Shang Rong translate from so

Answer 1

You can use GENERATE_DATE_ARRAY() and UNNEST() :

(您可以使用GENERATE_DATE_ARRAY()和UNNEST() ：)

SELECT dte, t.ended, t.package
FROM (select t,
             GENERATE_DATE_ARRAY(t.purchased, t.ended, interval 1 week) as dtes
      from (select date('2019-01-01') as purchased, date('2019-02-01') as ended,  '1MOnth' as package
           ) t
     ) t CROSS JOIN
     UNNEST(t.dtes) dte