Блог

У меня есть следующая структура

 CREATE TABLE t (
    entry int,
    mode int,
    device_id int,
  );
  
INSERT INTO t (entry, mode, device_id)
VALUES 
(0, 0, 0), 
(1, 1, 0), -- mode 1 count 1
(2, 1, 0),
(3, 2, 0),
(4, 1, 0), -- mode 1 count 1
(5, 0, 0),

(0, 0, 1),
(1, 2, 1), -- mode 2 count 1
(2, 0, 1),
(3, 2, 1), -- mode 2 count 1
(4, 2, 1),
(5, 0, 1);
 

И я хотел бы получить совокупный подсчет того, когда определенный режим использовался устройством. Режим подсчета 1 для устройства 0 должен выдавать значения

0, 1, 1, 1, 2, 2

Потому что счетчик не увеличивается при повторении значений (т. Е. режим не изменился).

Аналогично, режим подсчета 2 для устройства 1 должен давать

0, 1, 1, 2, 2, 2

поскольку наблюдаются 2 отчетливых изменения в этом режиме.

Как бы я сделал это в MS SQL?

Одним из способов было бы использовать LAG и CASE выражение, чтобы проверить, является ли режим желаемым и отличается ли он от последней строки. Затем вы можете использовать накопительный COUNT для создания «групп»:

 DECLARE @Device int = 1,
        @Mode int = 2;
        
WITH Counters AS(
    SELECT entry,
           mode,
           device_id,
           CASE WHEN t.mode = @Mode AND t.mode != LAG(t.mode,1,t.mode) OVER (PARTITION BY t.device_id ORDER BY t.entry ASC) THEN 1 END Counter 
    FROM dbo.t
    WHERE t.device_id = @Device)
SELECT C.entry,
       C.mode,
       C.device_id,
       COUNT(Counter) OVER (PARTITION BY C.device_id ORDER BY C.entry ASC) AS CumulativeCount
FROM Counters C
ORDER BY C.entry;
 

db<>скрипка