时间戳

首页 > 解决方案 > 为 Redshift SQL 中两个不同列的日期之间的每一天创建一个新行

sql - 为 Redshift SQL 中两个不同列的日期之间的每一天创建一个新行

问题描述

我正在使用 Redshift 中的表格。它包含带有一些信息和两个日期（开始日期和结束日期）的行。我似乎无法想出一种方法来为开始日期和结束日期之间的每一天创建一个新行。例如，这里的代码制作一个简单的表格：

CREATE TEMPORARY TABLE dates (name VARCHAR(50), start_date DATETIME, end_date DATETIME)

INSERT INTO dates
VALUES
       ('Peter F.','2018-03-01','2018-03-05'),
       ('Sam R.','2018-04-17', '2018-04-20');

SELECT * FROM dates;

我该如何从

name          | start_date                | end_date                   | 
Peter F.      | 2018-04-17 00:00:00.000000| 2018-04-20 00:00:00.000000 |
Sam R.        | 2018-03-01 00:00:00.000000| 2018-03-05 00:00:00.000000 |

至：

name          | start_date                | end_date                   | 
Peter F.      | 2018-04-17 00:00:00.000000| 2018-04-20 00:00:00.000000 |
Peter F.      | 2018-04-18 00:00:00.000000| 2018-04-20 00:00:00.000000 |
Peter F.      | 2018-04-19 00:00:00.000000| 2018-04-20 00:00:00.000000 |
Peter F.      | 2018-04-20 00:00:00.000000| 2018-04-20 00:00:00.000000 |
Sam R.        | 2018-03-01 00:00:00.000000| 2018-03-05 00:00:00.000000 |
Sam R.        | 2018-03-02 00:00:00.000000| 2018-03-05 00:00:00.000000 |
Sam R.        | 2018-03-03 00:00:00.000000| 2018-03-05 00:00:00.000000 |
Sam R.        | 2018-03-04 00:00:00.000000| 2018-03-05 00:00:00.000000 |
Sam R.        | 2018-03-05 00:00:00.000000| 2018-03-05 00:00:00.000000 |

我尝试使用这样的递归 CTE：

WITH cte
     AS (SELECT name,
                start_date,
                end_date
         FROM   dates
         UNION ALL
         SELECT name,
                Dateadd(day, 1, start_date),
                end_date
         FROM   cte
         WHERE  start_date < end_date)
SELECT *
FROM   cte

但这给了我一个错误：

[Amazon](500310) Invalid operation: relation "cte" does not exist;

希望得到一些专家的建议，因为我真的很想能够在 SQL 中做到这一点......提前谢谢你！

标签： sqldateamazon-redshiftrecursive-query

解决方案

Redshift 不支持递归公用表表达式。

一种方法是建立一个数字表：

create table nums(n int);
insert into nums values(0), (1), (2), (3), ...

然后，您可以将 numbers 表与原始表连接以生成预期结果：

select
    d.name,
    dateadd(day, n.n, d.start_date) start_date,
    d.end_date
from dates d
inner join nums n 
    on dateadd(day, n.n, d.start_date) <= d.end_date

您还可以直接在查询中将数字列为派生表，或row_number()针对大表使用。

推荐阅读