如何根据更改的上一个和最后一个值构建新的日期列?

Question

我希望在SQL中构建一个查询，其中我正在添加新的日期列，该列将返回价格更改的日期。

我当前的数据集现在如下所示：

Product_id  Current_date    Price
2001         11/1/19         57
2001         11/2/19         57
2001         11/3/19         58
2001         11/20/19        58
2001         11/21/19        60
2001         11/22/19        60
2001         11/29/19        60
2001         11/30/19        72
2001         11/29/19        72
2001         11/30/19        72

我想添加一个新列"更改日期"，该列将根据 - 如果价格没有更改返回上一个日期，如果价格已更改返回当前日期(

看起来像这样：

product_id  current_date    Price   **Changed_Date** (if price did not change return previous 
date, 
if price changed return current date)
2001           11/1/19     57     11/1/19
2001           11/2/19     57     11/1/19
2001           11/3/19     58     11/3/19
2001           11/20/1     60     11/20/19
2001           11/21/19    60     11/20/19
2001           11/22/19    60     11/20/19
2001           11/29/19    60     11/20/19
2001           11/28/19    72     11/28/19
2001           11/29/19    72     11/28/19
2001           11/30/19    72     11/28/19

任何人都可以帮助提供有关在SQL中使用的最佳函数以获得此结果的想法吗？ 提前谢谢你。

Answer 1

在您的示例数据中，价格只会增加。 如果是这种情况，最简单的方法是累积最小值：

select t.*,
min(current_date) over (partition by product_id, price) as changed_date
from t;

如果不是这种情况 - 特别是如果价格可以恢复到以前的价格 - 那么你就有一个缺口和孤岛问题。 在这种情况下，最简单的解决方案可能是行号的差异：

select t.*,
min(date) over (partition by product_id, price, (seqnum - seqnum_2)) as change_date
from (select t.*,
row_number() over (partition by product_id order by current_date) as seqnum,
row_number() over (partition by product_id, price order by current_date) as seqnum_2
from t
) t;

为什么这有效有点难以解释。 但是，如果您查看子查询的结果，您将看到行号的差异如何标识具有相同价格的相邻行。

Answer 2

非常感谢你们，伙计们！ @Gordon，我尝试了您推荐的示例并且正在部分工作，为什么我说部分是对于某些行不返回预期的结果，这可能与过度分区是如何完成

的有关？请参阅以下示例：

product_id   current_date        price             changed date
2001         10/4/19              60                10/4/19
2001         10/6/19              60                10/4/19
2001         10/7/19              60                10/4/19
2001         10/12/19             60                10/4/19
2001         10/13/19             57                10/13/19
2001         10/18/19             57                10/13/19
2001         10/20/19             57                10/13/19
-----did not work for the below dates (should have been 10/13/19 as changed date, because price did not change)                                                                  
2001        10/24/19              57                10/24/19                                         
2001        10/24/19              57                10/24/19
2001        10/25/19              57                10/24/19         

关于如何改变这一点的任何建议？ 再次感谢