SQL Server 计算当前行和下一行之间的 datediff 的最佳方法？

发布于 2024-07-05 13:22:14 字数 455 浏览 4 评论 0原文

我有以下粗略结构：

Object -> Object Revisions -> Data

数据可以在多个对象之间共享。

我想做的是清除旧的对象修订。我想保留第一个、活跃的、广泛的修订，以便保留一段时间内的最后一个更改。数据可能在 2 天内发生很大变化，然后数月内保持不变，因此我想保留更改开始之前的最后修订以及新集的最终更改。

我目前正在使用游标和临时表来保存更改之间的 ID 和日期，以便我可以选择要摆脱的容易实现的目标。这意味着使用 @LastID、@LastDate、更新和插入临时表等...

是否有一种更简单/更好的方法来计算初始结果集中当前行和下一行之间的日期差异，而不使用游标和临时表？

我使用的是 sql server 2000，但对 2005、2008 的任何新功能感兴趣，这些功能也可以帮助解决这个问题。

原文

I've got the following rough structure:

Object -> Object Revisions -> Data

The Data can be shared between several Objects.

What I'm trying to do is clean out old Object Revisions. I want to keep the first, active, and a spread of revisions so that the last change for a time period is kept. The Data might be changed a lot over the course of 2 days then left alone for months, so I want to keep the last revision before the changes started and the end change of the new set.

I'm currently using a cursor and temp table to hold the IDs and date between changes so I can select out the low hanging fruit to get rid of. This means using @LastID, @LastDate, updates and inserts to the temp table, etc...

Is there an easier/better way to calculate the date difference between the current row and the next row in my initial result set without using a cursor and temp table?

I'm on sql server 2000, but would be interested in any new features of 2005, 2008 that could help with this as well.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦与时光遇 2024-07-12 13:22:14

如果标识列是连续的，则可以使用以下方法：

SELECT curr.*, DATEDIFF(MINUTE, prev.EventDateTime,curr.EventDateTime) Duration FROM DWLog curr join DWLog prev on prev.EventID = curr.EventID - 1< /代码>

回复收藏 0 原文

野味少女 2024-07-12 13:22:14

这是示例 SQL。如果您有 Identity 列，则可以使用它来代替“ActivityDate”。

SELECT DATEDIFF(HOUR, prev.ActivityDate, curr.ActivityDate)
  FROM MyTable curr
  JOIN MyTable prev
    ON prev.ObjectID = curr.ObjectID
  WHERE prev.ActivityDate =
     (SELECT MAX(maxtbl.ActivityDate)
        FROM MyTable maxtbl
        WHERE maxtbl.ObjectID = curr.ObjectID
          AND maxtbl.ActivityDate < curr.ActivityDate)

我可以删除“prev”，但假设您需要其中的 ID 才能删除，请将其放在那里。

Here is example SQL. If you have an Identity column, you can use this instead of "ActivityDate".

SELECT DATEDIFF(HOUR, prev.ActivityDate, curr.ActivityDate)
  FROM MyTable curr
  JOIN MyTable prev
    ON prev.ObjectID = curr.ObjectID
  WHERE prev.ActivityDate =
     (SELECT MAX(maxtbl.ActivityDate)
        FROM MyTable maxtbl
        WHERE maxtbl.ObjectID = curr.ObjectID
          AND maxtbl.ActivityDate < curr.ActivityDate)

I could remove "prev", but have it there assuming you need IDs from it for deleting.

回复收藏 0 原文

欲拥i 2024-07-12 13:22:14

这是我到目前为止所得到的，我想在接受答案之前再多花一点时间。

DECLARE @IDs TABLE 
(
  ID int , 
  DateBetween int
)

DECLARE @OID int
SET @OID = 6150

-- Grab the revisions, calc the datediff, and insert into temp table var.

INSERT @IDs
SELECT ID, 
       DATEDIFF(dd, 
                (SELECT MAX(ActiveDate) 
                 FROM ObjectRevisionHistory 
                 WHERE ObjectID=@OID AND 
                       ActiveDate < ORH.ActiveDate), ActiveDate) 
FROM ObjectRevisionHistory ORH 
WHERE ObjectID=@OID


-- Hard set DateBetween for special case revisions to always keep

 UPDATE @IDs SET DateBetween = 1000 WHERE ID=(SELECT MIN(ID) FROM @IDs)

 UPDATE @IDs SET DateBetween = 1000 WHERE ID=(SELECT MAX(ID) FROM @IDs)

 UPDATE @IDs SET DateBetween = 1000 
 WHERE ID=(SELECT ID 
           FROM ObjectRevisionHistory 
           WHERE ObjectID=@OID AND Active=1)


-- Select out IDs for however I need them

 SELECT * FROM @IDs
 SELECT * FROM @IDs WHERE DateBetween < 2
 SELECT * FROM @IDs WHERE DateBetween > 2

我希望扩展它，以便我可以保留最多的修订，并删除旧的修订，同时仍然保留第一个、最后一个和活动的修订。通过 select top 和 order by 子句应该很容易，嗯...并将 ActiveDate 放入临时表中。

我让彼得的例子可以工作，但将其修改为子选择。我把两者都弄乱了，sql 跟踪显示子选择执行的读取较少。但这确实有效，当我的声望足够高时，我会投票给他。

Here's what I've got so far, I wanted to give this a little more time before accepting an answer.

DECLARE @IDs TABLE 
(
  ID int , 
  DateBetween int
)

DECLARE @OID int
SET @OID = 6150

-- Grab the revisions, calc the datediff, and insert into temp table var.

INSERT @IDs
SELECT ID, 
       DATEDIFF(dd, 
                (SELECT MAX(ActiveDate) 
                 FROM ObjectRevisionHistory 
                 WHERE ObjectID=@OID AND 
                       ActiveDate < ORH.ActiveDate), ActiveDate) 
FROM ObjectRevisionHistory ORH 
WHERE ObjectID=@OID


-- Hard set DateBetween for special case revisions to always keep

 UPDATE @IDs SET DateBetween = 1000 WHERE ID=(SELECT MIN(ID) FROM @IDs)

 UPDATE @IDs SET DateBetween = 1000 WHERE ID=(SELECT MAX(ID) FROM @IDs)

 UPDATE @IDs SET DateBetween = 1000 
 WHERE ID=(SELECT ID 
           FROM ObjectRevisionHistory 
           WHERE ObjectID=@OID AND Active=1)


-- Select out IDs for however I need them

 SELECT * FROM @IDs
 SELECT * FROM @IDs WHERE DateBetween < 2
 SELECT * FROM @IDs WHERE DateBetween > 2

I'm looking to extend this so that I can keep at maximum so many revisions, and prune off the older ones while still keeping the first, last, and active. Should be easy enough through select top and order by clauses, um... and tossing in ActiveDate into the temp table.

I got Peter's example to work, but took that and modified it into a subselect. I messed around with both and the sql trace shows the subselect doing less reads. But it does work and I'll vote him up when I get my rep high enough.

回复收藏 0 原文