计算指定窗口上的滚动计数
杨豪
问题内容：
问题答案：
样本数据可能会有助于解释我想做的事情，而不是解释它，因此，我将从此开始。
这是我目前正在使用的数据：
+-------------------------+--------------+
| 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分钟的时间内滚动显示此数据中的出现次数。该数据的预期结果如下：
+-------------------------+--------------+------------------+
| 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 ()（或其他任何可能的解决方案）解决方案。
这可能吗？还是子查询是正确的解决方案？
问题答案：
一种方法-如果表很大，可能比嵌套循环在一个范围上连接的性能更好-一种方法是首先创建一个数字表…
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
类似资料：
-
问题内容： 考虑一下您有一些不均匀的时间序列数据： 假设我想在1ms的时间内进行滚动求和： 目前，我将所有内容都放回多头，并在cython中进行，但是在纯熊猫中有可能吗？我知道您可以执行.asfreq（’U’）之类的操作，然后填充并使用传统函数，但是一旦行数超过玩具数量，就无法缩放。 作为参考，这是一个骇人的，不是快速的Cython版本： 在更大的系列上对此进行演示： 问题答案： 您可以使用求和和
-
问题内容： 我正在寻找一种类似的各种功能的方法，但我希望滚动计算的窗口由一个值范围（例如，DataFrame列的值范围）定义，而不是由窗口中的行数。 例如，假设我有以下数据： 如果执行类似的操作，则会得到一个滚动总和，其中每个窗口包含5行。但是我想要的是一个滚动总和，其中每个窗口都包含的一定范围的值。也就是说，我希望能够执行类似的操作，并得到一个结果，其中第一个窗口包含所有介于1和5之间的行，然后
-
在Apache Flink中使用滑动时间窗口时，当窗口滑动时会重新计算窗口中的许多元组/元素。例如，假设一个窗口大小为5秒，滑动时间为1秒，则窗口内容的80%与上一个窗口的内容相同。 考虑一个数据流S，其元组由时间戳和整数值组成: , , , , , , ,... 假设t1、t2、t3、...表示连续时间戳，其中t2-t1=1秒。给定S，窗口大小为5秒、滑动1秒的Flink窗口化ProcessWi
-
问题内容： 考虑 我想获取滚动窗口3的最大值的索引 我想要的是 我做了什么 这显然不是我想要的 问题答案： 没有简单的方法可以执行此操作，因为传递给rolling-applied函数的参数是一个普通的numpy数组，而不是pandas Series，因此它不了解索引。此外，滚动函数必须返回浮点结果，因此，如果它们不是浮点的，则不能直接返回索引值。 这是一种方法： 这个想法是采用argmax值，并通
-
问题内容： 我是一名高中计算机科学系的学生，今天遇到一个问题： 程序说明：掷骰子的人相信，掷三个骰子，十个比掷九个更容易。您可以编写一个证明或否定这一信念的程序吗？ 让计算机计算所有可能的投掷三个骰子的方法：1 + 1 + 1，1 + 1 + 2，1 + 1 + 3，依此类推。将这些可能性中的每一个相加，然后看看有多少会给出九个有多少给十。如果多给十，那么信念就会得到证明。 我很快想出了一种蛮力解