时间戳

首页 > 解决方案 > 日期列表 - 查找日期之间的老化

sql - 日期列表 - 查找日期之间的老化

问题描述

我正在编写一个查询以从case_history表中检索老化数据（来自 salesforce）。由于团队正在处理案例，案例会经历新的、工作的、质量检查等不同的阶段，请更正和关闭。

Case_id  |  Edit_Date           |   Field  |    Old_Value      |    New_Value    
---------------------------------------------------------------------------------------------------
xvywiqhn |  2019-12-12 08:05:59 |   Status |    Quality Check  |    Closed
xvywiqhn |  2019-12-12 07:21:55 |   Status |    Please Correct |    Quality Check
xvywiqhn |  2019-12-09 08:32:40 |   Status |    Quality Check  |    Please Correct
xvywiqhn |  2019-12-09 08:08:49 |   Status |    Working        |    Quality Check
xvywiqhn |  2019-12-09 07:35:24 |   Status |    New            |    Working

我正在尝试以获取下一个日期和 new_value 的方式创建列，以便我可以在该阶段老化。

Case_id  |  Edit_Date           | Old_Value      | New_Value      | Completion_Date     | End_Stage      
--------------------------------------------------------------------------------------------------------
xvywiqhn |  2019-12-12 08:05:59 | Quality Check  | Closed         | NULL                | NULL 
xvywiqhn |  2019-12-12 07:21:55 | Please Correct | Quality Check  | 2019-12-12 08:05:59 | Closed
xvywiqhn |  2019-12-09 08:32:40 | Quality Check  | Please Correct | 2019-12-12 07:21:55 | Quality Check
xvywiqhn |  2019-12-09 08:08:49 | Working        | Quality Check  | 2019-12-09 08:32:40 | Please Correct
xvywiqhn |  2019-12-09 07:35:24 | New            | Working        | 2019-12-09 08:08:49 | Quality Check

有没有办法我可以做到这一点？

SELECT
    case_id,
    edit_date,
    old_value,
    new_value,
    "getting next edit_date",
    "getting new_value for next edit_date"
FROM case_history

标签： sqlsalesforceamazon-redshift

解决方案

我们可以在LAG这里尝试使用该功能：

SELECT
    case_id,
    edit_date,
    old_value,
    new_value,
    LAG(edit_date) OVER (PARTITION BY case_id ORDER BY edit_date) completion_date,
    LAG(new_value) OVER (PARTITION BY case_id ORDER BY edit_date) end_stage
FROM case_history
ORDER BY
    case_id,
    edit_date;

推荐阅读