如何在Technolutions SLATE中将查询上次运行时间设为日期参数？

需求背景 #

• 使用Technolutions SLATE平台的SQL查询，需要仅拉取当前查询上次运行之后新增的记录
• 平台自带的「排除已被任何查询拉取过的记录」设置不符合需求
• 当前查询仅能拉取最近1天的数据，尝试run相关关键词无效，需修改SQL实现目标

原查询代码 #

/* Material Received (with Date) */
(a.[id] IN (select a.[id] 
            from [material] m 
            inner join [application] a on (a.[person] = m.[record]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (convert(date, m.[updated]) between dateadd(day, -1, convert(date, getdate())) and convert(date, getdate())) 

            union all 

            select [record] 
            from [material] 
            where ([key] in ('app')) 
              and ([record] is not null) 
              and (convert(date, [updated]) between dateadd(day, -1, convert(date, getdate())) and convert(date, getdate())) 

            union all 

            select a.[id] 
            from [material] m 
            inner join [school] s on (s.[id] = m.[record]) 
            inner join [application] a on (a.[person] = s.[record]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (convert(date, m.[updated]) between dateadd(day, -1, convert(date, getdate())) and convert(date, getdate())) 

            union all 

            select a.[id] 
            from [material] m 
            inner join [application.reference] ar on (ar.[id] = m.[record]) 
            inner join [application] a on (a.[id] = ar.[application]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (convert(date, m.[updated]) between dateadd(day, -1, convert(date, getdate())) and convert(date, getdate())) 

            union all 

            select a.[id] 
            from [material] m 
            inner join [school.report] sr on (sr.[id] = m.[record]) 
            inner join [application] a on (a.[id] = sr.[application]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (convert(date, m.[updated]) between dateadd(day, -1, convert(date, getdate())) and convert(date, getdate()))
           )
)

修改方案 #

方案1：利用SLATE内置查询运行时间变量（推荐） #

SLATE支持在查询中直接引用当前查询的上次运行时间，使用语法{{query.last_run}}，替换原查询中的日期范围条件即可：

修改后的完整SQL：

/* Material Received (with Date) - 仅拉取上次查询运行后新增记录 */
(a.[id] IN (select a.[id] 
            from [material] m 
            inner join [application] a on (a.[person] = m.[record]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (m.[updated] >= convert(datetime, '{{query.last_run}}')) 

            union all 

            select [record] 
            from [material] 
            where ([key] in ('app')) 
              and ([record] is not null) 
              and ([updated] >= convert(datetime, '{{query.last_run}}')) 

            union all 

            select a.[id] 
            from [material] m 
            inner join [school] s on (s.[id] = m.[record]) 
            inner join [application] a on (a.[person] = s.[record]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (m.[updated] >= convert(datetime, '{{query.last_run}}')) 

            union all 

            select a.[id] 
            from [material] m 
            inner join [application.reference] ar on (ar.[id] = m.[record]) 
            inner join [application] a on (a.[id] = ar.[application]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (m.[updated] >= convert(datetime, '{{query.last_run}}')) 

            union all 

            select a.[id] 
            from [material] m 
            inner join [school.report] sr on (sr.[id] = m.[record]) 
            inner join [application] a on (a.[id] = sr.[application]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (m.[updated] >= convert(datetime, '{{query.last_run}}'))
           )
)

• 核心改动：把原查询中convert(date, m.[updated]) between ...的条件，替换为m.[updated] >= convert(datetime, '{{query.last_run}}')，精准过滤上次运行后的新增记录
• 首次运行说明：{{query.last_run}}会默认取一个较早的系统初始时间，确保首次运行能拉取所有符合条件的历史数据，之后每次运行仅拉取新增内容

方案2：手动维护运行时间（备用） #

如果内置变量无法使用，可通过自定义表手动维护查询的上次运行时间：

1. 在SLATE中创建自定义表query_run_history，字段包括query_id（查询唯一标识）和last_run_time（上次运行时间）
2. 修改SQL，先获取上次运行时间，再作为过滤条件，最后更新运行时间：

示例代码（简化版）：

-- 获取上次运行时间，首次运行则设为初始日期
DECLARE @last_run DATETIME = (SELECT last_run_time FROM query_run_history WHERE query_id = '你的查询ID');
IF @last_run IS NULL SET @last_run = '2000-01-01';

-- 主查询（仅展示第一部分，其余UNION ALL模块同理修改日期条件）
(a.[id] IN (select a.[id] 
            from [material] m 
            inner join [application] a on (a.[person] = m.[record]) 
            where (m.[key] in ('app')) 
              and (m.[record] is not null) 
              and (m.[updated] >= @last_run) 
            -- 其余UNION ALL部分省略，按同样规则修改日期条件
           )
)

-- 更新上次运行时间
UPDATE query_run_history SET last_run_time = GETDATE() WHERE query_id = '你的查询ID';
IF @@ROWCOUNT = 0 INSERT INTO query_run_history (query_id, last_run_time) VALUES ('你的查询ID', GETDATE());

内容的提问来源于stack exchange，提问作者Reese Johnson IronLaserCoyote