如何按时间维度分组配方并筛选指定同名配方的历史数据

需求说明 #

现有如下格式的表数据，需按时间排序后输出当前配方的上一个配方的全量数据。其中存在名称相同的配方B和D，但仅需提取配方B的全量数据。

测试数据与原测试代码 #

测试表与数据插入代码 #

declare @tm_variab table (Timecreated datetime,Recipe_Name varchar(80))

insert into @tm_variab select '2022-10-18 16:50:47' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:42' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:37' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:32' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:27' , 'TR1674FSHY'  ----- 当前配方(A)
insert into @tm_variab select '2022-10-18 16:50:22' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:17' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:12' , 'TR1674FSHY'

insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'
insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk' ---   配方(B)
insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'
insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'

insert into @tm_variab select '2022-10-18 16:49:47' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:42' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:37' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:32' , 'TR19556ECDRE' ----   配方(C)
insert into @tm_variab select '2022-10-18 16:49:27' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:22' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:17' , 'TR19556ECDRE'

insert into @tm_variab select '2022-10-18 16:48:07' , 'TR1674FSkk'  ---   配方(D)
insert into @tm_variab select '2022-10-18 16:48:07' , 'TR1674FSkk'

原测试SQL代码 #

;WITH CTE AS
(
SELECT 
 Timecreated
 ,Recipe_Name
 , ROW_NUMBER() OVER(PARTITION BY Recipe_Name ORDER BY Timecreated DESC) AS rn
 FROM @tm_variab
 )

, newfinal as (
SELECT TOP(3)
 Timecreated,
 Recipe_Name
 --, ROW_NUMBER() OVER(order BY Timecreated) AS rnii
 FROM CTE WHERE rn=1
 --ORDER BY Timecreated DESC
 )

select Timecreated, Recipe_Name
,ROW_NUMBER() OVER(order BY Timecreated) AS rnii
into #final 
From newfinal 
order by Timecreated desc

select * from #final mm inner join @tm_variab kk on  kk.Recipe_Name=mm.Recipe_Name and kk.Recipe_Name ='TR1674FSkk'  
drop table #final

优化后的解决方案 #

原代码逻辑不够精准，无法准确筛选出当前配方的上一个配方（配方B）。以下是更贴合需求的实现：

核心思路 #

1. 定位当前配方（A）的最早时间节点，仅考虑该时间之前的配方记录
2. 对符合条件的记录按配方分组，取每个配方的最新时间点，再按时间倒序排名
3. 提取排名第一的配方（即当前配方的上一个配方）的全量数据

实现代码 #

declare @tm_variab table (Timecreated datetime,Recipe_Name varchar(80))

-- 插入测试数据（同上，省略重复代码）
insert into @tm_variab select '2022-10-18 16:50:47' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:42' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:37' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:32' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:27' , 'TR1674FSHY'  ----- 当前配方(A)
insert into @tm_variab select '2022-10-18 16:50:22' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:17' , 'TR1674FSHY'
insert into @tm_variab select '2022-10-18 16:50:12' , 'TR1674FSHY'

insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'
insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk' ---   配方(B)
insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'
insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'

insert into @tm_variab select '2022-10-18 16:49:47' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:42' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:37' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:32' , 'TR19556ECDRE' ----   配方(C)
insert into @tm_variab select '2022-10-18 16:49:27' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:22' , 'TR19556ECDRE'
insert into @tm_variab select '2022-10-18 16:49:17' , 'TR19556ECDRE'

insert into @tm_variab select '2022-10-18 16:48:07' , 'TR1674FSkk'  ---   配方(D)
insert into @tm_variab select '2022-10-18 16:48:07' , 'TR1674FSkk'

-- 1. 获取当前配方(A)的最早时间
DECLARE @CurrentRecipeEarliestTime DATETIME
SELECT @CurrentRecipeEarliestTime = MIN(Timecreated) 
FROM @tm_variab 
WHERE Recipe_Name = 'TR1674FSHY'

-- 2. 筛选当前配方之前的配方，分组取最新时间并排名
WITH PreviousRecipes AS (
    SELECT 
        Recipe_Name,
        MAX(Timecreated) AS LatestTime
    FROM @tm_variab
    WHERE Timecreated < @CurrentRecipeEarliestTime
    GROUP BY Recipe_Name
),
RankedRecipes AS (
    SELECT 
        Recipe_Name,
        LatestTime,
        ROW_NUMBER() OVER(ORDER BY LatestTime DESC) AS RankNum
    FROM PreviousRecipes
)
-- 3. 提取上一个配方的全量数据
SELECT tv.*
FROM @tm_variab tv
JOIN RankedRecipes rr ON tv.Recipe_Name = rr.Recipe_Name
WHERE rr.RankNum = 1;

效果说明 #

该代码会精准定位到当前配方A的上一个配方B，并输出其所有记录，自动排除更早的同名配方D，完全符合需求。

内容的提问来源于stack exchange，提问作者Baskar Lakshmi