样本数据可能会有助于解释我想做的事情,而不是解释它,因此,我将从此开始。
这是我目前正在使用的数据:
+-------------------------+--------------+ | CallStart | CallDuration | +-------------------------+--------------+ | 2017-09-15 09:15:15.313 | 00:01:28 | | 2017-09-15 09:15:15.317 | 00:01:45 | | 2017-09-15 09:16:45.603 | 00:01:31 | | 2017-09-15 09:17:00.637 | 00:01:24 | | 2017-09-15 09:18:20.853 | 00:01:42 | | 2017-09-15 09:18:25.870 | 00:01:24 | | 2017-09-15 11:27:05.117 | 00:00:59 | | 2017-09-15 11:31:16.053 | 00:01:18 | | 2017-09-15 11:34:41.627 | 00:01:00 | | 2017-09-15 12:16:45.413 | 00:01:01 | | 2017-09-15 12:18:15.820 | 00:01:05 | | 2017-09-15 12:30:43.607 | 00:01:04 | | 2017-09-15 12:31:48.817 | 00:00:55 | | 2017-09-15 12:35:14.563 | 00:00:59 | | 2017-09-15 12:42:10.947 | 00:00:43 | | 2017-09-15 12:56:28.807 | 00:01:14 | | 2017-09-15 13:05:10.643 | 00:00:37 | | 2017-09-15 13:20:08.400 | 00:00:37 | | 2017-09-15 14:30:12.607 | 00:00:59 | | 2017-09-15 14:31:22.807 | 00:00:49 | | 2017-09-15 15:19:47.240 | 00:01:07 | | 2017-09-15 16:04:47.753 | 00:00:55 | | 2017-09-15 16:58:08.080 | 00:00:55 | | 2017-09-15 17:05:04.557 | 00:00:50 | | 2017-09-15 17:20:42.753 | 00:00:58 | | 2017-09-15 17:28:09.140 | 00:01:05 | | 2017-09-15 17:39:46.690 | 00:00:38 | | 2017-09-15 17:40:21.957 | 00:01:05 | | 2017-09-15 17:43:47.570 | 00:01:08 | | 2017-09-15 17:47:23.390 | 00:01:05 | | 2017-09-15 17:47:28.410 | 00:00:56 | | 2017-09-15 17:51:59.380 | 00:01:04 | +-------------------------+--------------+
我正在尝试COUNT(*)在15分钟的时间内滚动显示此数据中的出现次数。该数据的预期结果如下:
COUNT(*)
+-------------------------+--------------+------------------+ | CallStart | CallDuration | DropsIn15Minutes | +-------------------------+--------------+------------------+ | 2017-09-15 09:15:15.313 | 00:01:28 | 1 | | 2017-09-15 09:15:15.317 | 00:01:45 | 2 | | 2017-09-15 09:16:45.603 | 00:01:31 | 3 | | 2017-09-15 09:17:00.637 | 00:01:24 | 4 | | 2017-09-15 09:18:20.853 | 00:01:42 | 5 | | 2017-09-15 09:18:25.870 | 00:01:24 | 6 | | 2017-09-15 11:27:05.117 | 00:00:59 | 1 | | 2017-09-15 11:31:16.053 | 00:01:18 | 2 | | 2017-09-15 11:34:41.627 | 00:01:00 | 3 | | 2017-09-15 12:16:45.413 | 00:01:01 | 1 | | 2017-09-15 12:18:15.820 | 00:01:05 | 2 | | 2017-09-15 12:30:43.607 | 00:01:04 | 3 | | 2017-09-15 12:31:48.817 | 00:00:55 | 3 | | 2017-09-15 12:35:14.563 | 00:00:59 | 3 | | 2017-09-15 12:42:10.947 | 00:00:43 | 4 | | 2017-09-15 12:56:28.807 | 00:01:14 | 2 | | 2017-09-15 13:05:10.643 | 00:00:37 | 2 | | 2017-09-15 13:20:08.400 | 00:00:37 | 2 | | 2017-09-15 14:30:12.607 | 00:00:59 | 1 | | 2017-09-15 14:31:22.807 | 00:00:49 | 2 | | 2017-09-15 15:19:47.240 | 00:01:07 | 1 | | 2017-09-15 16:04:47.753 | 00:00:55 | 1 | | 2017-09-15 16:58:08.080 | 00:00:55 | 1 | | 2017-09-15 17:05:04.557 | 00:00:50 | 2 | | 2017-09-15 17:20:42.753 | 00:00:58 | 1 | | 2017-09-15 17:28:09.140 | 00:01:05 | 2 | | 2017-09-15 17:39:46.690 | 00:00:38 | 2 | | 2017-09-15 17:40:21.957 | 00:01:05 | 3 | | 2017-09-15 17:43:47.570 | 00:01:08 | 3 | | 2017-09-15 17:47:23.390 | 00:01:05 | 4 | | 2017-09-15 17:47:28.410 | 00:00:56 | 5 | | 2017-09-15 17:51:59.380 | 00:01:04 | 6 | +-------------------------+--------------+------------------+
样本数据:
Create Table #Calls ( CallStart DateTime, CallDuration Time(0) ); Insert Into #Calls Values (N'2017-09-15T09:15:15.313', N'00:01:28'), (N'2017-09-15T09:15:15.317', N'00:01:45'), (N'2017-09-15T09:16:45.603', N'00:01:31'), (N'2017-09-15T09:17:00.637', N'00:01:24'), (N'2017-09-15T09:18:20.853', N'00:01:42'), (N'2017-09-15T09:18:25.87', N'00:01:24'), (N'2017-09-15T11:27:05.117', N'00:00:59'), (N'2017-09-15T11:31:16.053', N'00:01:18'), (N'2017-09-15T11:34:41.627', N'00:01:00'), (N'2017-09-15T12:16:45.413', N'00:01:01'), (N'2017-09-15T12:18:15.82', N'00:01:05'), (N'2017-09-15T12:30:43.607', N'00:01:04'), (N'2017-09-15T12:31:48.817', N'00:00:55'), (N'2017-09-15T12:35:14.563', N'00:00:59'), (N'2017-09-15T12:42:10.947', N'00:00:43'), (N'2017-09-15T12:56:28.807', N'00:01:14'), (N'2017-09-15T13:05:10.643', N'00:00:37'), (N'2017-09-15T13:20:08.4', N'00:00:37'), (N'2017-09-15T14:30:12.607', N'00:00:59'), (N'2017-09-15T14:31:22.807', N'00:00:49'), (N'2017-09-15T15:19:47.24', N'00:01:07'), (N'2017-09-15T16:04:47.753', N'00:00:55'), (N'2017-09-15T16:58:08.08', N'00:00:55'), (N'2017-09-15T17:05:04.557', N'00:00:50'), (N'2017-09-15T17:20:42.753', N'00:00:58'), (N'2017-09-15T17:28:09.14', N'00:01:05'), (N'2017-09-15T17:39:46.69', N'00:00:38'), (N'2017-09-15T17:40:21.957', N'00:01:05'), (N'2017-09-15T17:43:47.57', N'00:01:08'), (N'2017-09-15T17:47:23.39', N'00:01:05'), (N'2017-09-15T17:47:28.41', N'00:00:56'), (N'2017-09-15T17:51:59.38', N'00:01:04');
我可以通过以下方式 使它 起作用:
Select CallStart, CallDuration, DropsIn15Minutes = ( Select Count(*) From #Calls C2 Where C2.CallStart Between DateAdd(Minute, -15, C1.CallStart) And C1.CallStart ) From #Calls C1
但是,我想避免使用子查询,而建议使用COUNT(*) OVER ()(或其他任何可能的解决方案)解决方案。
COUNT(*) OVER ()
这可能吗?还是子查询是正确的解决方案?
一种方法-如果表很大,可能比嵌套循环在一个范围上连接的性能更好-一种方法是首先创建一个数字表…
CREATE TABLE dbo.Numbers ( N INT PRIMARY KEY ); WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) -- 1*10^1 or 10 rows , E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows , E4(N) AS (SELECT 1 FROM E2 a, E2 b) -- 1*10^4 or 10,000 rows , E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^8 or 100,000,000 rows INSERT INTO dbo.Numbers SELECT TOP (60*60*24) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E8;
然后使用以下内容。
WITH Calls AS (SELECT *, --pre-truncate all call starts to second precision CallStart_sec = DATEADD(SECOND, DATEDIFF(SECOND, '20000101', CallStart), '20000101') FROM #Calls), PreAgg AS (SELECT CallStart_sec, COUNT(*) AS Cnt FROM Calls GROUP BY CallStart_sec), Dates(D) --Todo - something else other than hardcoding the dates AS (SELECT CAST('2017-09-15' AS DATETIME2)), RT AS (SELECT *, Cume = SUM(Cnt) OVER (ORDER BY DATEADD(SECOND, N.N, Dates.D) ROWS BETWEEN 900 PRECEDING AND CURRENT ROW) FROM Dates INNER JOIN dbo.Numbers N ON N.N BETWEEN 0 AND 86399 LEFT JOIN PreAgg P ON P.CallStart_sec = DATEADD(SECOND, N.N, Dates.D)) SELECT C.CallStart_sec AS CallStart, CallDuration, DropsIn15Minutes = Cume FROM Calls C JOIN RT ON RT.CallStart_sec = C.CallStart_sec