ansaurus

Question

Sql Server: Count records (including zero) per ten-minute intervals

Answer 1

+2 A:

Easy way to do this is to use a CTE and make a helper table with the start time interval, then just join to that table in the main query using between.

Did that make sense? I'll work on some example code in a bit.

DECLARE @time int
DECLARE @interval int
SELECT @time=96
SELECT @interval=10

DECLARE @count int
SELECT @count=1

;WITH daterange AS
(
   SELECT 1 as [id], Max(requestbegin) as maxr, Min(requestBegin) as minr
   FROM track_pagesubmit
), intervals as
(
   SELECT @count AS interval, minr as intervalpos
   FROM daterange
   WHERE [id] = 1
   UNION ALL
   SELECT interval+1 AS interval,
          DATEADD(MINUTE,@interval,intervalpos) as intervalpos
   FROM intervals
   JOIN daterange on [ID] = 1
   WHERE DATEADD(MINUTE,@interval,intervalpos) < maxr
)
SELECT interval, intervalpos, 
       COUNT(DISTINCT track_pagesubmit.popid) as popcount,
       COUNT(track_pagesubmit.id AS requestcount 
FROM intervals
LEFT JOIN track_pagesubmit ON requestbegin IS NOT NULL 
  AND requestBegin BETWEEN intervalpos AND DATEADD(ns,-1,DATEADD(MINUTE,@interval,intervalpos))
GROUP BY interval, intervalpos 
OPTION (MAXRECURSION 200)

Hogan 2010-01-24 02:40:05

You can't really use BETWEEN because if your data happens to fall on a boundary it will be included in two sets. IMHO should always use an open-ended range (>= begin and < end).

Aaron Bertrand 2010-01-24 04:42:11

@Aaron: See above, between worked fine. (Of course I "cheat" and subtract 1 from @interval when I do the add.)

Hogan 2010-01-24 04:52:24

Then you could miss data between x:59.003 and x:59.997. Why force yourself to do all of these math tricks when an open-ended range is more precise and less error-prone?

Aaron Bertrand 2010-01-24 10:07:04

@Aaron: Ok, I changed it to subtract a nano-second from the end time. I'm not sure, I think BETWEEN is easier to read (and thus maintain) but I will grant you that in this use-case the range may be better.

Hogan 2010-01-24 16:25:45

"Easier to read" is quite subjective. I think that when you have to start subtracting nanoseconds and worrying about rounding due to type conversion (what do you think happens to your rounding when you move to SMALLDATETIME?) the "benefits" of BETWEEN disappear quickly. This article is kind of long but this is one of the points I cover: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

Aaron Bertrand 2010-01-24 16:33:23

I agree, subjective and in this case probably better your way, but I leave it as written for the context of other readers.

Hogan 2010-01-24 17:06:07

Answer 2

A:

The recursive CTE always seems to outperform the numbers table, but both methods are traditionally used for this.

Cade Roux 2010-01-24 04:19:51

Answer 3

+1 A:

-- Because your sample data spans 27 hours:

DECLARE
    @hours TINYINT,
    @minute_interval TINYINT,
    @start SMALLDATETIME;

SELECT
    @hours = 27,
    @minute_interval = 10,
    @start = '20100122 13:00';

;WITH x AS 
(
    SELECT TOP (@hours * (60 / @minute_interval))
        n = ROW_NUMBER() OVER
        (ORDER BY column_id)
    FROM msdb.sys.columns
),
intervals(boundary) AS
(
    SELECT CONVERT
    (
      SMALLDATETIME,
      DATEADD(MINUTE, (-n * @minute_interval), @start)
    )
    FROM x
)
SELECT
    i.boundary,
    RequestCount = COUNT(d.id),
    DistinctPopIDs = COUNT(DISTINCT d.popid)
FROM
    intervals AS i
LEFT OUTER JOIN
    dbo.Track_PageSubmit AS d
    ON d.requestBegin >= i.boundary
    AND d.requestBegin < DATEADD(MINUTE, @minute_interval, i.boundary)
GROUP BY i.boundary
ORDER BY i.boundary;

Aaron Bertrand 2010-01-24 04:32:47

Sorry about that, I was just grabbing some representative data. I forgot to check the interval.

Jason Francis 2010-01-24 14:41:58

This worked beautifully. Thank you.

Jason Francis 2010-01-25 22:31:11

Answer 4

A:

This is a bit rough, but should work for you:

WITH Nbrs_2( n ) AS ( SELECT 1 UNION SELECT 0 ),
    Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
    Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
    Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
select n.n as TenMinuteInterval, count(case when t.requestBegin is null then null else 1 end) as Count
from (
    SELECT n - 1 as n
    FROM (SELECT ROW_NUMBER()  OVER (ORDER BY n)
    FROM Nbrs) D ( n )
    WHERE n <= 144
) n
left outer join track_pageSubmit t on n.n = floor((cast(requestBegin - convert(int, requestBegin) as decimal(10,3)) % 1) * 144) 
    and requestBegin between '2010-01-21' and '2010-01-22'
group by n.n

Output:

TenMinuteInterval Count
-----------------------
0                  0
1                  0
2                  0
3                  0
4                  0
5                  0
6                  0
7                  0
8                  0
9                  0
10                 0
11                 0
12                 0
13                 0
14                 0
15                 0
16                 0
17                 0
18                 0
19                 0
20                 0
21                 0
22                 0
23                 0
24                 0
25                 0
26                 0
27                 0
28                 0
29                 0
30                 0
31                 0
32                 0
33                 0
34                 0
35                 0
36                 0
37                 0
38                 0
39                 0
40                 0
41                 0
42                 0
43                 0
44                 0
45                 0
46                 0
47                 0
48                 0
49                 0
50                 0
51                 0
52                 0
53                 0
54                 0
55                 0
56                 0
57                 0
58                 0
59                 0
60                 0
61                 0
62                 0
63                 7
64                 0
65                 0
66                 0
67                 0
68                 0
69                 0
70                 0
71                 0
72                 0
73                 0
74                 0
75                 0
76                 0
77                 0
78                 0
79                 0
80                 0
81                 0
82                 0
83                 0
84                 0
85                 0
86                 0
87                 0
88                 0
89                 0
90                 0
91                 0
92                 0
93                 0
94                 0
95                 0
96                 0
97                 0
98                 0
99                 0
100                0
101                0
102                0
103                0
104                0
105                0
106                0
107                0
108                0
109                0
110                0
111                0
112                0
113                0
114                0
115                0
116                0
117                0
118                0
119                0
120                0
121                0
122                0
123                0
124                0
125                0
126                0
127                0
128                0
129                0
130                0
131                0
132                0
133                0
134                0
135                0
136                0
137                0
138                0
139                0
140                0
141                0
142                0
143                0

RedFilter 2010-01-24 04:33:51

Just ran this on the test data, got no results in count.

Hogan 2010-01-24 04:58:21

...because the where clause (`getdate() - requestBegin <= 1.0`) is filtering for the last 24 hours (as you requested), whereas the test data is older than that...

RedFilter 2010-01-24 15:15:12

I modified the `WHERE` clause to show data for 2010/01/21 - it returns count 7 for interval 63. Note intervals are zero-based in this query. If you want one-based, change `SELECT n - 1 as n` to `SELECT n as n`.

RedFilter 2010-01-24 15:25:15

Added sample output

RedFilter 2010-01-24 15:28:05

ansaurus

tags:

views:

answers:

Sql Server: Count records (including zero) per ten-minute intervals

related questions