场景和步骤“获取最后一次更改"问题

问题描述

我有下表抽象了一些场景及其按 START_DATEdesc"排序的步骤.

I have following table abstracting some scenarios and their steps ordered by START_DATE "desc".

SCENARIO_KEY	STEP_KEY	START_DATE	END_DATE	PREVIOUS_SCENARIO_KEY	PREVIOUS_STEP_KEY
128	44	20xx04x4	0	120	44
120	44	20xx03x3	20xx04x4	120	38
120	38	20xx11x0	20xx03x3	121	38
121	38	20xx07x2	20xx11x0	120	44
120	44	20xx07x1	20xx07x2	120	38

在最后一次更改为值为 44 (step_key = 44) 的步骤后，我需要获取第一个场景密钥(或其他标识符).

I need to get first scenario_key (or another identifier) after last change to step with value 44 (step_key = 44).

因此，此查询的结果应该是上次更改后的某个标识符 - 上次更改后值 44 (20xx03x3) 或上次更改后 SCENARIO_KEY (120) 的第一行的 START_DATE.

So the result of this query should be some identifier of the last change - START_DATE of the first row with value 44 after last change (20xx03x3) or SCENARIO_KEY after last change (120).

简而言之，我需要能够识别最后更改为44"的行步骤键.

In short, I need to be able to identify the line of last change to "44" step key.

是否有任何分析功能(或其他查询)来实现这一点?你能给建议吗?

Is there any analytical function (or another query) to achieve this? Can you advice?

推荐答案

嗯...您可以使用以下方法获取最后一个非 44 日期之后的最早 44 日期:

Hmmm . . . you can get the earliest 44 date after the last non-44 date using:

select min(start_date)
from t
where t.start_date > (select max(t2.start_date)
                      from t t2
                      where t2.step_key <> 44
                     );

我认为这就是您要的.

您也可以使用窗口函数.假设最近的一行是44":

You can use window functions too. Assuming that the most recent row is "44":

select t.*
from (select t.*,
             row_number() over (order by start_date) as seqnum,
             row_number() over (partition by step_key order by start_date) as seqnum_sk
      from t
     ) t
where step_key = 44 and seqnum = seqnum_sk
order by start_date
fetch first 1 row only;

这篇关于场景和步骤“获取最后一次更改"问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！