时间戳

首页 > 解决方案 > 雪花：加入具有有效日期和旧记录的表显示为 NULL

join - 雪花：加入具有有效日期和旧记录的表显示为 NULL

问题描述

概括：

在雪花中，我有一个表，它记录了每隔一段时间更改一次的项目的最大数量。我希望能够加入该日期（有效日期）的最大项目数。这是最基本的“示例”，因为我的表格中的项目在被删除时会“过期”。

CREATE OR REPLACE TABLE ITEM
(
 Item VARCHAR(10),
 Quantity Number(5,0),
 EFFECTIVE_DATE DATE
)
;

CREATE OR REPLACE TABLE REPORT
(
 INVOICE_DATE DATE,
 ITEM VARCHAR(10)
)
;

INSERT INTO REPORT
VALUES
('2021-02-01', '100'),
('2021-09-10', '100')
;

INSERT INTO ITEM
VALUES
('100', '10', '2021-01-01'),
('101', '15', '2021-01-01'),
('100', '5', '2021-09-01')
;

SELECT * FROM REPORT t1
LEFT JOIN
(
SELECT * FROM ITEM
QUALIFY ROW_NUMBER() OVER (PARTITION BY ITEM ORDER BY EFFECTIVE_DATE desc) = 1
) t2 on t1.ITEM = t2.ITEM AND t1.INVOICE_DATE <= t2.EFFECTIVE_DATE
;

退货

INVOICE_DATE,ITEM,ITEM,QUANTITY,EFFECTIVE_DATE
2021-02-01,100,100,5,2021-09-01
2021-09-10,100,NULL,NULL,NULL

我该如何解决这个问题，以便我不再在我的加入中获得 NULL 条目。谢谢您阅读此篇！

我希望得到这样的结果

INVOICE_DATE,ITEM,ITEM,QUANTITY,EFFECTIVE_DATE
2021-02-01,100,100,10,2021-01-01
2021-09-10,100,100,5,2021-09-01

标签： joinsnowflake-cloud-data-platform

解决方案

问题在于您的数据和您的期望。您的查询是这样的：

SELECT * FROM REPORT t1
LEFT JOIN
(
SELECT * FROM ITEM
QUALIFY ROW_NUMBER() OVER (PARTITION BY ITEM ORDER BY EFFECTIVE_DATE desc) = 1
) t2 on t1.ITEM = t2.ITEM AND t1.INVOICE_DATE <= t2.EFFECTIVE_DATE
;

这要求 INVOICE_DATE 小于或等于项目的有效日期。但是，情况并非如此。 2021-09-10大于2021-09-01所以你不会得到连接命中，这就是你得到 NULL 的原因。这也是为什么您的其他记录返回的信息不符合您的预期。

推荐阅读