识别 SQL 表中的最新步骤序列

您需要按配方和步骤对行进行分区，然后使用 ROW_NUMBER（） 窗口函数为其分区中的每一行分配一个编号，按时间戳降序排序

  SELECT 
recipe, timestamp, step,
CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY recipe, step ORDER BY timestamp DESC) = 1 THEN 'true'
    ELSE 'false'
END AS current

从 recipe_steps ORDER BY 配方，时间戳 DESC;

你正朝着正确的方向前进。

从您的差异 cte 开始，我将 FALSE 分配给> 5 个时间戳。

SELECT *,
       CASE WHEN difference > 5 THEN 'FALSE' ELSE 'TRUE' END AS firstFalse
  FROM diff

使用自连接，我将 FALSE 分配给“firstFalse”之前的每个日期。这将返回所有为 false 的时间戳。

   SELECT f1.recipe,
          f1.step,
          MIN(f2.t_stamp) firstFalse,
          f1.t_stamp,
          'FALSE' AS Curr
     FROM false1 f1
     JOIN false1 f2 ON f1.recipe = f2.recipe AND f1.step = f2.step AND f2.firstFalse = 'FALSE'
    WHERE f1.t_stamp <= f2.t_stamp
 GROUP BY f1.recipe, f1.step, f1.t_stamp

最后，我用左联接把它们放在一起。

SELECT f1.recipe,
       f1.t_stamp AS [timestamp],
       f1.step,
       COALESCE(f2.curr, 'TRUE') AS [current]  
  FROM false1 f1
  LEFT JOIN false2 f2 ON f1.recipe = f2.recipe AND f1.step = f2.step AND f1.t_stamp = f2.t_stamp 
 ORDER BY [current] desc, f1.recipe asc, f1.step asc, f1.t_stamp asc

小提琴的整个过程

WITH diff AS 
  (SELECT recipe,
          t_stamp,
          step,
          DATEDIFF(SECOND, t_stamp, lag(t_stamp) OVER (PARTITION BY recipe, step ORDER BY t_stamp DESC)) AS difference
     FROM recipes
  )
, false1 AS
  (SELECT *,
          CASE WHEN difference > 5 THEN 'FALSE' ELSE 'TRUE' END AS firstFalse
     FROM diff
  )
, false2 AS
  (
   SELECT f1.recipe,
          f1.step,
          MIN(f2.t_stamp) firstFalse,
          f1.t_stamp,
          'FALSE' AS Curr
     FROM false1 f1
     JOIN false1 f2 ON f1.recipe = f2.recipe AND f1.step = f2.step AND f2.firstFalse = 'FALSE'
    WHERE f1.t_stamp <= f2.t_stamp
 GROUP BY f1.recipe, f1.step, f1.t_stamp
  )
  
SELECT f1.recipe,
       f1.t_stamp AS [timestamp],
       f1.step,
       COALESCE(f2.curr, 'TRUE') AS [current]  
  FROM false1 f1
  LEFT JOIN false2 f2 ON f1.recipe = f2.recipe AND f1.step = f2.step AND f1.t_stamp = f2.t_stamp 
 ORDER BY [current] desc, f1.recipe asc, f1.step asc, f1.t_stamp asc

您可以使用窗口函数和一些匹配技巧通过几个步骤获得所需的结果：

步骤 1。根据您的定义，使用窗口函数计算配方重新启动时的提取行，这意味着它是配方的第 1 步行，并且它与上一步的时间差大于 5 秒。

第 2 步：使用 windwo 函数并标记recipe_restart点来获得recipe_restart_bucket，因为您想按降序获得结果，因此我们做了一个数学技巧来按降序将 sum（#restart） - sum （#seenrestartsofar） 转换为桶索引。

第 3 步：当 bucket_index=0 时添加 current_flag（最近一个按降序排列）

以下是所有步骤的查询：

with recipe_steps_time_diff AS (
select
    recipe,
    timestamp,
    step,
    case when step <> 'step_1' or datediff(SECOND, lag(timestamp) over(partition by recipe,step order by timestamp), timestamp) <=5 then 0 else 1 end as recipe_restart 
from
   recipe_steps
),
recipe_steps_restart_bucket AS (
select
    recipe,
    timestamp,
    step,
    sum(recipe_restart) over (partition by recipe) - sum(recipe_restart) over (partition by recipe order by timestamp) as recipe_restart_bucket
from
    recipe_steps_time_diff
)
select
    recipe,
    timestamp,
    step,
    case when recipe_restart_bucket = 0 then 'True' else 'False' end as current_flag   
from
    recipe_steps_restart_bucket
order by recipe_restart_bucket, recipe, timestamp desc