时间戳

首页 > 解决方案 > 如何根据更改的上一个和最后一个值构建新的日期列？

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

问题描述

我希望在 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 中使用的最佳函数以获得此结果的想法吗？先感谢您。

标签： mysqlsqlpresto

解决方案

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

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;

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

推荐阅读