sql - 从 Redshift 中的表中获取缺失的日期

Question

我在 Redshift 中有一个表，其中有一个日期列和一些其他数据:

+------------+-------+
|    Date    | Value |
+------------+-------+
| 2016-09-25 |     1 |
| 2016-09-28 |     2 |
| 2016-09-29 |     3 |
+------------+-------+

我想从此表中获取“缺失”的日期。因此，在上面的示例中，如果我想获取从 7 天前到现在的所有缺失日期，我想生成一个返回的查询:

2016-09-22
2016-09-23
2016-09-24
2016-09-26
2016-09-27

我尝试使用 generate_series(..) 函数生成所有日期，然后根据我的表中已存在的日期过滤掉它们。所以，像这样:

    select CURRENT_DATE + i as MyDate
from generate_series(date '2016-09-22'- CURRENT_DATE, date '2016-09-29' - CURRENT_DATE ) i 
where MyDate not in (select [Date] from MyTable)

在这种情况下，我有错误“MyDate”不存在。我也试过使用和 EXCEPT 子句，然后产生错误:

INFO:  Function "generate_series(integer,integer)" not supported.

[Err] ERROR:  Specified types or functions (one per INFO message) not supported on Redshift tables.

我尝试过的其他各种方法都会产生该错误，我可以单独运行 generate_series(..) 但如果我尝试使用任何其他子句等，它就会失败。

这在 Redshift 中可以实现吗？

Answer 1

Redshift 不完全支持 generate_series，我发现您可以单独使用它，但它生成的数据无法以任何方式与任何其他 redshift 功能结合。

您最好的选择是创建一个每天一行的 redshift 表，并按如下方式在连接中使用该表

select date from dates_table
where date not in (select date from my_table)

在一个表中有这个日期列表的可用来源，这个表通常被称为日期维度，并在星型模式数据仓库中使用。

例如 sisense date dimension

使用 excel 生成日期列表，然后另存为 CSV 可能同样容易

使用 redshift COPY 命令将日期加载到 redshift 表中。