时间戳

首页 > 解决方案 > 集群时间线或重建批号的有效方法

sql - 集群时间线或重建批号的有效方法

问题描述

我正在处理一个测试数据库的大型数据集（150k / 天）。每行包含有关产品特定测试的数据。每个测试人员插入他的测试结果。

我想在每个产品和测试人员的班次中进行一些测量，例如通过失败率。问题是没有分配批号，所以我不能轻易选择。

考虑整个表的给定子选择：

 id   tBegin                orderId   
------------------------------------
 1    2018-10-20 00:00:05   1
 2    2018-10-20 00:05:15   1
 3    2018-10-20 01:00:05   1
 10   2018-10-20 10:03:05   3
 12   2018-10-20 11:04:05   8
 20   2018-10-20 14:15:05   3
 37   2018-10-20 18:12:05   1

我的目标是将数据聚类到以下

 id   tBegin                orderId   pCount 
--------------------------------------------
 1    2018-10-20 00:00:05   1         3
 10   2018-10-20 10:03:05   3         1
 12   2018-10-20 11:04:05   8         1
 20   2018-10-20 14:15:05   3         1
 37   2018-10-20 18:12:05   1         1

一个简单GROUP BY orderID的不会做的伎俩，所以我想出了以下

SELECT 
  MIN(c.id) AS id,
  MIN(c.tBegin) AS tBegin,
  c.orderId,
  COUNT(*) AS pCount
FROM (
    SELECT t2.id, t2.tBegin, t2.orderId,
      ( SELECT TOP 1 t.id
        FROM history t
        WHERE t.tBegin > t2.tBegin
          AND t.orderID <> t2.orderID
          AND <restrict date here further>
        ORDER BY t.tBegin 
       ) AS nextId
    FROM history t2 
) AS c
WHERE <restrict date here>
GROUP BY c.orderID, c.nextId

我省略了WHERE选择正确日期和测试仪的 s。这行得通，但它的接缝效率很低。我曾使用过小型数据库，但我是 SQL Server 2017 的新手。

非常感谢您的帮助！

标签： sqlsql-serverdatetimegroup-bysql-server-2017

解决方案

您可以为此使用窗口函数：

DECLARE @t TABLE (id INT, tBegin DATETIME, orderId INT);
INSERT INTO @t VALUES
(1 , '2018-10-20 00:00:05', 1),
(2 , '2018-10-20 00:05:15', 1),
(3 , '2018-10-20 01:00:05', 1),
(10, '2018-10-20 10:03:05', 3),
(12, '2018-10-20 11:04:05', 8),
(20, '2018-10-20 14:15:05', 3),
(37, '2018-10-20 18:12:05', 1);

WITH cte1 AS (
    SELECT *, CASE WHEN orderId = LAG(orderId) OVER (ORDER BY tBegin) THEN 0 ELSE 1 END AS chg
    FROM @t
), cte2 AS (
    SELECT *, SUM(chg) OVER(ORDER BY tBegin) AS grp
    FROM cte1
), cte3 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY tBegin) AS rn
    FROM cte2
)
SELECT *
FROM cte3
WHERE rn = 1
  • 第一个 cte 为值更改的每一行分配一个“更改标志”
  • 第二个 cte 使用运行总和将 1 和 0 转换为可用于对行进行分组的数字
  • 最后，您对每组中的行进行编号并选择每组的第一行

DB Fiddle 上的演示

推荐阅读