選擇并洗掉時間戳差異<1天的SQL記錄

我需要從日期差小于 1 天的徽章記錄中選擇或洗掉記錄如果 24 小時記憶體在 2 條或更多記錄，則所有記錄都必須洗掉，但只有一條。（第一個或最后一個，這不重要）一個選擇查詢來找到它們是可以的，所以我可以手動洗掉它們。

只有相同徽章編號的記錄必須進行比較和洗掉。

這可能使用 TSQL 嗎？

例子：

 ------------------ -------------- 
| TimeStamp        | Badge        |
 ------------------ -------------- 
| 19-10-2021 10:18 | Badge1       |
| 20-10-2021 12:18 | Badge1       |
| 22-10-2021 13:23 | Badge1       |  
| 22-10-2021 11:18 | Badge1       |  <--- remove
| 22-10-2021 13:18 | Badge1       |  <--- remove
| 23-10-2021 14:18 | Badge1       |
| 21-10-2021 09:18 | Badge12      |
| 23-10-2021 10:18 | Badge12      |
| 23-10-2021 23:18 | Badge12      |  <--- remove
| 25-10-2021 12:18 | Badge12      |
 ------------------ --------- ---- 

uj5u.com熱心網友回復：

由于任何給定記錄狀態對所有先前記錄的狀態（具有相同的徽章值）的鏈式依賴關系，我認為沒有單一的陳述句解決方案。下面使用游標單步執行記錄，同時跟蹤先前保留的值。

DECLARE @Data TABLE (Id INT IDENTITY(1,1) PRIMARY KEY, TimeStamp DATETIME, Badge VARCHAR(100))
INSERT @Data
VALUES
    ('2021-10-19 10:18', 'Badge1'),
    ('2021-10-20 12:18', 'Badge1'),
    ('2021-10-22 13:23', 'Badge1'),  
    ('2021-10-22 11:18', 'Badge1'), -- remove ??? (prior record is out of sequence)
    ('2021-10-22 13:18', 'Badge1'), -- remove
    ('2021-10-23 14:18', 'Badge1'),
    ('2021-10-21 09:18', 'Badge12'),
    ('2021-10-23 10:18', 'Badge12'),
    ('2021-10-23 23:18', 'Badge12'), -- remove
    ('2021-10-25 12:18', 'Badge12')

DECLARE @Id INT
DECLARE @TimeStamp DATETIME
DECLARE @Badge VARCHAR(100)
DECLARE @PriorTimeStamp DATETIME = NULL
DECLARE @PriorBadge VARCHAR(100) = NULL

DECLARE Csr CURSOR FOR 
    SELECT Id, Timestamp, Badge
    FROM @Data
    ORDER BY Badge, Timestamp, Id
    --ORDER BY Badge, Id
OPEN Csr

FETCH NEXT FROM Csr INTO @Id, @TimeStamp, @Badge
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Badge = @PriorBadge AND @TimeStamp < DATEADD(DAY, 1, @PriorTimeStamp)
    BEGIN
        DELETE FROM @Data WHERE CURRENT OF CSR
    END
    ELSE BEGIN -- Keep
        SET @PriorBadge = @Badge
        SET @PriorTimeStamp = @TimeStamp
    END

    FETCH NEXT FROM Csr INTO @Id, @TimeStamp, @Badge
END

CLOSE Csr
DEALLOCATE Csr

SELECT *
FROM @data
ORDER BY ID

結果：

請注意，由于日期升序，保留了“2021-10-22 13:23”值，并洗掉了“2021-10-22 11:18”值。如果原始資料順序很重要，請交換上述ORDER BY條款。

請參閱

之后，我們要做的就是選擇 DeltaMinutes >= 1440 分鐘的行，這是一天中的分鐘數。如果您將 >= 更改為 < ，那么它將顯示距上一行不到 1 天的行。

   WITH cte AS
(
 SELECT *
        ,DeltaMinutes = DATEDIFF(mi,LAG(TimeStamp,1,DATEADD(dd,-2,TimeStamp)) OVER (PARTITION BY Badge ORDER BY TimeStamp),TimeStamp) 
   FROM #TestTable
)
 SELECT TimeStamp,Badge
   FROM cte 
  WHERE DeltaMinutes >= 1440 --Minutes in a day.
  ORDER BY  CONVERT(INT,SUBSTRING(Badge,6,5)) --Sort badge numbers in numeric instead of string order.
           ,TimeStamp
;

結果看起來像這樣......

當然，LAG 僅在 2012 年及以后可用。如果您使用的是 2008/2008R2 或更低版本，我們可以使用另一個非遞回技巧。

And, with all of that, I have to say that I think your spec may be incorrect, as @TN implies. I think what you really want is to keep the first row (for example) and remove all rows from the output that are within 1440 minutes of THAT row. The next row that's at least 1440 minutes after that first row would become the new "anchor" row and all rows less than 1440 minutes after THAT row would be removed from the output. If that's what you really meant, then post back and we'll solve that problem in a similar manner.

And with the current data, you probably won't see any difference but if the data changes, you will.

uj5u.com熱心網友回復：

謝謝@SOS，

ps 我實際上不可能洗掉這些行，因為它們是“歷史”的一部分。

標籤：sql服务器 tsql sql-server-2008

上一篇：從同一檔案中定義的包中匯入符號

下一篇：從同一檔案中定義的包中匯入符號