使用 SQL Server 2012 LAG [英] Using SQL Server 2012 LAG

问题描述

我正在尝试使用 SQL Server 2012 LAG 函数编写查询，以从我的 [Order] 表中检索数据，其中一行与前一行之间的日期时间差异小于等于 2 分钟.

I am trying to write a query using SQL Server 2012 LAG function to retrieve data from my [Order] table where the datetime difference between a row and the previous row is less than equal to 2 minutes.

我期待的结果是

1234    April, 28 2012 09:00:00

1234    April, 28 2012 09:01:00

1234    April, 28 2012 09:03:00

5678    April, 28 2012 09:40:00

5678    April, 28 2012 09:42:00

5678    April, 28 2012 09:44:00

但我看到了

1234    April, 28 2012 09:00:00

1234    April, 28 2012 09:01:00

1234    April, 28 2012 09:03:00

5678    April, 28 2012 09:40:00

5678    April, 28 2012 09:42:00

5678    April, 28 2012 09:44:00

91011   April, 28 2012 10:00:00

不应返回最后一行.这是我尝试过的:SQL Fiddle

The last row should not be returned. Here is what I have tried: SQL Fiddle

有人有想法吗?

推荐答案

好的，首先我添加了一行来向您展示其他人的答案不起作用但他们现在将其删除的地方.

Okay first of all I added a row to show you where someone else's answer doesn't work but they deleted it now.

现在是我查询中的逻辑.你说你希望每一行都在另一行的两分钟内.这意味着你不仅要向后看，还要用 LEAD() 向前看.在您的查询中，您在上一次为 NULL 时返回，因此它只返回每个 OrderNumber 的第一个值，无论它是对还是错.偶然地，需要返回每个 OrderNumber 的第一个值，直到您到达它中断的最后一个 OrderNumber.我的查询更正了这一点，应该适用于您的所有数据.

Now for the logic in my query. You said you want each row that is within two minutes of another row. That means you have to look not only backwards, but also forwards with LEAD(). In your query, you returned when previous time was NULL so it simply returned the first value of each OrderNumber regardless if it was right or wrong. By chance, the first values of each of your OrderNumbers needed to be returned until you get to the last OrderNumber where it broke. My query corrects that and should work for all your data.

CREATE TABLE [Order]  
    (
            OrderNumber    VARCHAR(20) NOT NULL
        ,   OrderDateTime   DATETIME NOT NULL
    );

    INSERT [Order] (OrderNumber, OrderDateTime) 
    VALUES
        ('1234', '2012-04-28 09:00:00'),
        ('1234', '2012-04-28 09:01:00'),
        ('1234', '2012-04-28 09:03:00'),
        ('5678', '2012-04-28 09:40:00'),
        ('5678', '2012-04-28 09:42:00'),
        ('5678', '2012-04-28 09:44:00'),
        ('91011', '2012-04-28 10:00:00'),
        ('91011', '2012-04-28 10:25:00'),
        ('91011', '2012-04-28 10:27:00');

with Ordered as (
  select
    OrderNumber,
    OrderDateTime,
    LAG(OrderDateTime,1) over (
      partition by OrderNumber
      order by OrderDateTime
    ) as prev_time,
    LEAD(OrderDateTime,1) over (
      partition by OrderNumber
      order by OrderDateTime
    ) as next_time
  from [Order]
)

SELECT  OrderNumber,
        OrderDateTime
FROM Ordered
WHERE   DATEDIFF(MINUTE,OrderDateTime,next_time) <= 2  --this says if the next value is less than or equal to two minutes away return it
        OR DATEDIFF(MINUTE,prev_time,OrderDateTime) <= 2 --this says if the prev value is less than or equal to 2 minutes away return it

结果(记得我加了一行):

Results(Remember I added a row):

OrderNumber          OrderDateTime
-------------------- -----------------------
1234                 2012-04-28 09:00:00.000
1234                 2012-04-28 09:01:00.000
1234                 2012-04-28 09:03:00.000
5678                 2012-04-28 09:40:00.000
5678                 2012-04-28 09:42:00.000
5678                 2012-04-28 09:44:00.000
91011                2012-04-28 10:25:00.000
91011                2012-04-28 10:27:00.000

这篇关于使用 SQL Server 2012 LAG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！