CODEWITHSUNDEEP
sqlsql-server
mark
mark

Reputation: 62836

How to calculate the number of rows using a time based sliding window?

I have the following simple schema:

CREATE TABLE Data (ts DATETIME)
INSERT INTO Data VALUES
('2015-11-11 06:20:41.937'),
('2015-11-11 06:20:42.017'),
('2015-11-11 06:20:42.060'),
('2015-11-11 06:20:44.837'),
('2015-11-11 06:20:44.853'),
('2015-11-11 06:20:45.640'),
('2015-11-11 06:20:46.197'),
('2015-11-11 06:20:46.233'),
('2015-11-11 06:21:59.613'),
('2015-11-11 06:21:59.613'),
('2015-11-11 06:22:17.377'),
('2015-11-11 06:23:40.187'),
('2015-11-11 06:24:40.227'),
('2015-11-11 06:24:40.227')

In reality, there are more columns, of course.

Anyway, I am trying to figure out how can I compute for every row the count of rows in a 10 seconds interval starting with that row. For the example data the result should be:

ts                      Count
2015-11-11 06:20:41.937 8
2015-11-11 06:20:42.017 7
2015-11-11 06:20:42.060 6
2015-11-11 06:20:44.837 5
2015-11-11 06:20:44.853 4
2015-11-11 06:20:45.640 3
2015-11-11 06:20:46.197 2
2015-11-11 06:20:46.233 1
2015-11-11 06:21:59.613 2
2015-11-11 06:21:59.613 2
2015-11-11 06:22:17.377 1
2015-11-11 06:23:40.187 3
2015-11-11 06:24:40.227 2
2015-11-11 06:24:40.227 2

Any ideas?

Upvotes: 2

Views: 72

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use CROSS APPLY:

SELECT
    ts, cnt
FROM Data d
CROSS APPLY(
    SELECT COUNT(*)
    FROM Data
    WHERE ts BETWEEN d.ts AND DATEADD(SECOND, 10, d.ts)
) AS x(cnt)
ORDER BY d.ts

Upvotes: 1

Igor Borisenko
Igor Borisenko

Reputation: 3866

Try this

select d.ts, (select count(*) cnt
              from data d2
              where d2.ts>=d.ts
                    and d2.ts<dateadd(second,10,d.ts)) cnt
from data d

Upvotes: 2

Related Questions