CODEWITHSUNDEEP
sqlsql-server
komodovaran_
komodovaran_

Reputation: 2012

Cumulative counts for distinct sequence encounters

I have the following structure

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);

And I would like a cumulative count of when a certain mode was used by a device. Counting mode 1 for device 0 should yield the values

0, 1, 1, 1, 2, 2

Because the counter doesn't increment on repeat values (i.e. the mode didn't change).

Similarly, counting mode 2 for device 1 should yield

0, 1, 1, 2, 2, 2

as 2 distinct changes to this mode are observed.

How would I do this in MS SQL?

Upvotes: 0

Views: 32

Answers (1)

Thom A
Thom A

Reputation: 95689

One method would be to use LAG and a CASE expression to check if the mode is the desired one, and if it is different to the last row. Then you can use a cumulative COUNT to create the "groups":

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<>fiddle

Upvotes: 1

Related Questions