SQL Server：在子查询中选择时间戳值接近值的行-解网

问：

我有一个日志表，其中包含 id、TimeStamp、Message，其中值有时包含“WARNING”。Message

SELECT TimeStamp 
FROM Logs 
WHERE Message LIKE '%WARNING%'

我需要的是该集合中时间两侧 5 分钟内的行。我知道 -5 分钟和 +5 分钟可以对单个值做到这一点。BETWEENDATEADD

如何为一组值执行此操作？我有办法将 CTE 和 UNION ALL 组合在一起，还是必须光标浏览结果集？

更新

我尝试了使用 CTE 的这种尝试。

declare @searchTerm varchar(50) = '%WARNING%';
declare @searchDate datetime = '2023-11-01';
declare @id int;
declare @eventTime datetime;
declare @lowRange int = -5;
declare @highRange int = 5;

WITH cteEvents as (
    select Id, EventTime from Veneer.dbo.Logs where EventMessage LIKE @searchTerm and EventTime > @searchDate
)
SELECT * 
    from Logs l
    left outer join cteEvents ev on l.Id=ev.Id 
    where l.EventTime between DATEADD(MINUTE, @lowRange, ev.EventTime) and DATEADD(MINUTE, @highRange, ev.EventTime)

最终结果仅为搜索词为 WARNING 的记录。该时间段内的周围记录均未显示。我试过了，然后加入了。我尝试了每一个都是第一个或最后一个。 TIA - 迈克INNERLEFTRIGHT

sql-server 联合 common-table-expression

该表大约有 1.5m 条记录，我感兴趣的结果集仅是最近几周的结果集 - 自 2023 年 11 月 1 日以来约为 25k。而且警告记录远比这少。我开发了一个解决方案，将警告记录放在临时表中，然后用光标浏览它并使用 dateadd 函数获取它周围的项目。选择 ~ 1500 条记录大约需要 2 秒。

答：

1赞 Alex 11/13/2023 #1

试试这个，不需要 CTE。获取所有 WARNING 日志，然后在 5 分钟内使用 BETWEEN 加入任何一侧的警告。

declare @logs table([sid] int not null identity, [timestamp] datetime, [message] nvarchar(100));

insert into @logs values(DATEADD(MINUTE, 20, CURRENT_TIMESTAMP), N'TEST1');
insert into @logs values(DATEADD(MINUTE, 15, CURRENT_TIMESTAMP), N'TEST2');
insert into @logs values(DATEADD(MINUTE, 10, CURRENT_TIMESTAMP), N'TEST3');
insert into @logs values(DATEADD(MINUTE, 5, CURRENT_TIMESTAMP), N'TEST4');
insert into @logs values(DATEADD(MINUTE, 0, CURRENT_TIMESTAMP), N'WARNING: TEST5');
insert into @logs values(DATEADD(MINUTE, -5, CURRENT_TIMESTAMP), N'TEST6');
insert into @logs values(DATEADD(MINUTE, -10, CURRENT_TIMESTAMP), N'TEST7');
insert into @logs values(DATEADD(MINUTE, -15, CURRENT_TIMESTAMP), N'TEST8');
insert into @logs values(DATEADD(MINUTE, -20, CURRENT_TIMESTAMP), N'TEST9');
insert into @logs values(DATEADD(MINUTE, -25, CURRENT_TIMESTAMP), N'WARNING: TEST10');

WITH CTE AS
(
    select [timestamp]--get distinct dates
    from
        @logs l
    where
        [message] like '%WARNING%'
    group by    
        [timestamp]
)
SELECT l.*
FROM 
    CTE lw
    inner join @logs l on 
        l.[timestamp] between DATEADD(MINUTE, -5, lw.[timestamp]) AND DATEADD(MINUTE, 5, lw.[timestamp])

我不确定，因为所有这些都具有精确到毫秒的离散时间戳。因此，group by 几乎可以肯定是单行，除非我们以某种方式对它进行四舍五入。我尝试了与您的加入类似的东西，但由于某种原因，它只显示了带有警告的那些。我再试一次，也许会更新问题。group by [timestamp]

0赞 Hey Mikey 11/16/2023 #2

我想通了。

declare @searchTerm varchar(50) = '%WARNING%';
declare @searchStart datetime = '2023-01-10';
declare @searchEnd datetime = '2023-11-01';
declare @lowRange int = -10;
declare @highRange int = 5;

WITH partOne as (
    select Id, EventTime from Veneer.dbo.Logs where EventMessage LIKE @searchTerm and EventTime between @searchStart and @searchEnd
),
partTwo as (
    select distinct evLogs.Id
    from partOne, Logs evLogs where evLogs.EventTime between DATEADD(MINUTE, @lowRange, partOne.EventTime) and DATEADD(MINUTE, @highRange, partOne.EventTime)
)
SELECT * from Logs inner join partTwo on Logs.id=partTwo.Id order by Logs.Id

“partOne”和“日志”（Logs）是在不连接的情况下选择的，否则将受到 partOne 中的“日志”（Logs）的限制。除非 EventTime 上有索引，否则运行速度非常慢。使用临时表和游标实际上更快。

在搜索日期和 19789 警告之间有一组334911记录： Temp 表和游标方法 - 1：15，无索引的 CTE 方法 - 3：03， CTE 与 EventTime 上的索引 - 0：01

编辑：经过仔细检查，我意识到结果有重复。在第二部分中对 Id 的选择添加了不同的选择，并在最终选择中加入。固定结果计数

话筒

上一个：如何在 typscript 中使用联合类型？[复制]

下一个：创建统一其他工作表的工作表

SQL Server：在子查询中选择时间戳值接近值的行

SQL Server : select rows with time stamp values near values in subquery

评论

评论