sql - 在雪花视图中将日期范围拆分为新记录

Question

我有一个包含开始日期和结束日期的表，我需要将记录拆分为按天计算，这需要进入视图。

| PersonID  | CompanyID    | Start_DT    | End_DT    |
|-----------|--------------|-------------|-----------|
| A12       | abc          | 05-01-2020  | 05-03-2020|
| B23       | def          | 06-08-2020  | 06-14-2020|

| PersonID  | CompanyID    | New_DT      |
|-----------|--------------|-------------|
| A12       | abc          | 05-01-2020  | ==> A12 Start Date is 05-01-2020 and End Date is 05-03-2020. So there are 3 records generated in New_DT
| A12       | abc          | 05-02-2020  | 
| A12       | abc          | 05-03-2020  | 
| B23       | def          | 06-08-2020  | 
| B23       | def          | 06-09-2020  | 
| B23       | def          | 06-10-2020  | 
| B23       | def          | 06-11-2020  | 
| B23       | def          | 06-12-2020  | 
| B23       | def          | 06-13-2020  | 
| B23       | def          | 06-14-2020  |   

如何在 View 中实现这一点？

标签： sqlsnowflake-cloud-data-platformsql-viewsql-date-functionssqldatetime

Answer 1

您可以使用递归 CTE：

with cte as (
      select PersonID, CompanyID, Start_DT as new_dt, End_DT  
      from t
      union all
      select PersonID, CompanyID, dateadd(day, 1, new_dt), End_DT  
      from cte
      where new_dt < end_dt
     )
select PersonID, CompanyID, new_dt
from cte
option (maxrecursion 0);

如果您的期限超过 100 天，则需要添加option (maxrecursion 0).

这是一个 db<>fiddle。