Un moyen efficace de regrouper une chronologie OU de reconstruire un numéro de lot

Question

Je travaille sur un grand ensemble de données (150k / jour) d'une base de données de testeurs. Chaque ligne contient des données sur un test spécifique du produit. Chaque testeur insère les résultats de son test.

Je veux faire des mesures comme le taux de réussite et d'échec sur une équipe par produit et par testeur. Le problème, c'est qu'aucun numéro de lot n'est attribué et que je ne peux donc pas effectuer cette sélection facilement.

Considère le sous-sélectionné donné de la table entière :

 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

Mon objectif est de regrouper les données selon les critères suivants

 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

Un simple GROUP BY orderID ne fera pas l'affaire, alors j'ai trouvé la solution suivante

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

J'ai laissé de côté le WHERE qui sélectionnent la bonne date et le bon testeur. Cela fonctionne, mais semble très inefficace. J'ai travaillé avec de petites bases de données, mais je suis nouveau dans SQL Server 2017.

J'apprécie beaucoup votre aide !

Answer 1

Vous pouvez utiliser les fonctions de la fenêtre pour cela :

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

• Le premier cte attribue un "drapeau de changement" à chaque ligne où la valeur a changé.
• Le deuxième cte utilise une somme courante pour convertir les 1 et les 0 en un nombre qui peut être utilisé pour regrouper les lignes.
• Enfin, vous numérotez les lignes dans chaque groupe et sélectionnez la première ligne par groupe.

Démonstration sur DB Fiddle

Answer 2

Vous pouvez utiliser une approche cumulative :

select min(id) as id, max(tBegin), orderid, count(*) 
from (select h.*,
             row_number() over (order by id) as seq1,
             row_number() over (partition by orderid order by id) as seq2
      from history h
     ) h
group by orderid, (seq1 - seq2)
order by id;