sql - 在SQL中查找连续的日期对

Question

我在这里有一个问题，看起来有点像我在搜索中找到的问题，但是有针对稍微不同的问题的解决方案，重要的是，这些问题在SQL 2000中不起作用。

我有一个很大的表，里面有很多冗余数据，我试图将这些数据简化为有用的条目。这是一个历史记录表，它的工作方式是：如果两个条目本质上是重复的，并且在按日期排序时是连续的，则可以删除后者。当从该条目的生效日期到下一个非重复条目之间的某个日期请求历史数据时，将使用来自较早条目的数据。

数据看起来像这样：

id     user_id effective_date important_value useless_value
1      1       1/3/2007       3               0
2      1       1/4/2007       3               1
3      1       1/6/2007       NULL            1
4      1       2/1/2007       3               0
5      2       1/5/2007       12              1
6      3       1/1/1899       7               0

对于此样本集，如果 user_id和 important_value相同，我们将考虑两个连续的行重复。从此样本集中，我们将只删除 id = 2的行，保留从1-3-2007开始的信息，表明 important_value在1-6-2007发生了变化，然后在2再次显示了相关的变化。 -1-2007。

我当前的方法笨拙且耗时，我知道必须有更好的方法。我编写了一个脚本，该脚本使用游标遍历 user_id值（因为它将大表分解为可管理的部分），并为该用户创建仅包含行的临时表。然后，要获取连续的条目，它将获取临时表，并在临时表中没有其他条目且日期介于两个日期之间的情况下将其连接到自身。在下面的伪代码中， UDF_SameOrNull是一个函数，如果传入的两个值相同或均为NULL，则返回1。

WHILE (@@fetch_status <> -1)
BEGIN
  SELECT * FROM History INTO #history WHERE user_id = @UserId

  --return entries to delete
  SELECT h2.id
  INTO #delete_history_ids
  FROM #history h1
  JOIN #history h2 ON
    h1.effective_date < h2.effective_date
    AND dbo.UDF_SameOrNull(h1.important_value, h2.important_value)=1
  WHERE NOT EXISTS (SELECT 1 FROM #history hx WHERE hx.effective_date > h1.effective_date and hx.effective_date < h2.effective_date)

  DELETE h1
  FROM History h1
  JOIN #delete_history_ids dh ON
    h1.id = dh.id 

  FETCH NEXT FROM UserCursor INTO @UserId
END 

它也循环遍历同一组重复项，直到没有重复项为止，因为取出行会创建可能是重复项的新连续对。为了简单起见，我将其省略。

不幸的是，我必须使用SQL Server 2000来完成此任务，并且我非常确定它不支持ROW_NUMBER（）来更优雅地查找连续条目。

谢谢阅读。对于任何不必要的背景故事或伪代码中的错误，我深表歉意。

Answer 1

好的，我想我想出了这个，很好的问题！

首先，我假设effective_date列不会与user_id重复。我认为，如果不是这种情况，可以对其进行修改以使其起作用-因此，请让我们知道是否需要考虑这一点。

该过程基本上采用值表，并在等于的user_id和important_value以及先前的effective_date上进行自联接。然后，我们在user_id上再做1个自联接，通过验证在这2个记录之间没有effective_date记录来有效地检查上述2个联接记录是否是连续的。

目前，这只是一条select语句-它应选择所有要删除的记录。因此，如果您验证返回的数据正确，只需将select *更改为delete tcheck。

如果您有任何问题，请告诉我。

select 
    * 
from 
    History tcheck
    inner join History tprev
        on  tprev.[user_id] = tcheck.[user_id]
            and tprev.important_value = tcheck.important_value
            and tprev.effective_date < tcheck.effective_date
    left join History checkbtwn
        on  tcheck.[user_id] = checkbtwn.[user_id]
            and checkbtwn.effective_date < tcheck.effective_date
            and checkbtwn.effective_date > tprev.effective_date
where
    checkbtwn.[user_id] is null