sql - 计算指定窗口的滚动计数-6ren

sql - 计算指定窗口的滚动计数

转载作者：行者123 更新时间：2023-12-01 12:18:33

示例数据可能有助于解释我正在尝试做的事情，而不是解释它，所以我将从它开始。

这是我目前正在处理的数据:

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

我正在尝试获取此数据在 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 ()(或任何其他可能的解决方案)解决方案。

这可能吗？或者子查询是正确的解决方案吗？

最佳答案

一种方法 - 如果表很大，这可能比范围内的嵌套循环连接表现更好 - 是首先创建一个数字表......

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

关于sql - 计算指定窗口的滚动计数，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46330253/

文章推荐： haskell - Haskell 示例中的函数组合

文章推荐： haskell - 将 Haskell Int 值转换为 SBV 约束的常量

文章推荐： lua 命令 lua -e "print("hello") 输出为 nil

文章推荐： java - LeapMotion 项目的 Java 运行时环境检测到 fatal error

java - 指定 "other"
我正在我的 java 作业中使用 GUI，并且我必须指定 JCheckBox 中的其他内容。除了这个小要求，其他的我都完成了。我不太确定如何解决这个问题，我查阅了我的书并尝试在线研究要求: 一系列复
javascript - 指定 for 循环的终点有哪些优点和缺点？
在各种语言中(我将在这里使用 JavaScript，但我已经在 PHP 和 C++ 中以及可能在其他地方看到过它)，似乎有几种构造简单 for 循环的方法。版本 1 如下: var top = doc
javascript - css 指定 < >
有没有一种方法可以使用 CSS 指定每次“小于符号”(在键盘上 M 的右侧)或“大于符号”出现在文本中时，它应该被替换为分别是“小于”或“大于”的实际词？最佳答案 CSS 不能作用于(不能修改，即)
kerberos - 指定 SPN 的正确格式是什么？
首先，使用 setspn 命令为用户注册服务主体名称。 setspn -a CS/dummy@abc.com dummyuser setspn -l dummyuser 给出输出为 CS/dummy@
javascript - 指定 SFSafariViewController 用户代理
我在指定从 SFSafariViewController 访问时遇到问题，因为它具有与 Safari 浏览器完全相同的用户代理。我要做的是仅在 webview 内显示图片，如果在普通浏览器上查看，则
r - 指定 CFA，其中湍流是外生相关性的总和
我正在尝试用 R 语言在 lavaan 中指定一个奇怪的模型。该模型如下所示: 我的规范尝试如下所示。我发现难以实现的是将观察到的变量的唯一误差固定为唯一项的两个相关性的总和。例如，项目 y*1,2
reactjs - 指定 axios 响应数据类型
我正在构建 API 以将我的 React 应用程序与我的后端服务连接起来，我想使用 typescript 来指定 data 的类型在我的 Axios 请求中。如何在不修改其他字段的情况下更新 Axio
z3 - 指定 Z3 的初始模型值
如何为模型指定初始“软”值？该初始模型是解决类似查询的结果，并且该模型很可能具有正确的部分，甚至对于当前查询可能是正确的。目前，我正在通过增量求解和 hard/soft constraints 对此
java - 指定 Kafka 生产者的分区数
我有来自网页的以下代码 https://cwiki.apache.org/confluence/display/KAFKA/0.8.0+Producer+Example 似乎缺少的是如何配置分区数。我
Neo4jClient:指定 Cypher 解析器版本？
有没有办法在每个查询的基础上在 Neo4jClient 中指定 Cypher 解析器的版本，如 here 所述? 谢谢! 最佳答案如果您将 Neo4jClient 更新到最新版本(> 1.0.0.6
r - 指定 `curve` 绘图高度？
我有以下代码生成四个图，但它们最终被压扁(见下图)。我该如何解决这个问题？ par(mfrow=c(2,2)) curve(.5*exp(-.5*x),from=0,to=10,main="f(x)"
coldfusion - 指定 JDBC 数据库驱动程序最大线程数
我有一个 ColdFusion 10 服务器。我正在使用 JDBC 驱动程序连接到 db2 数据库。我偶然发现了这个笔记。这个设置在哪里？我还查看了 neo*.xml 文件，但没有看到任何 db 驱动
jquery - 指定 jQuery 验证插件中验证器的顺序
我想知道是否可以指定验证器的运行顺序。目前，我编写了一个自定义验证器，检查它是否为 [a-zA-Z0-9]+ 以确保登录验证我们的规则，并编写了一个远程验证器以确保登录可用，但目前远程验证器已启动在
iphone - 指定 iPhone 应用程序的最低内存要求
我的应用程序需要至少 40MB 的 RAM，因此早期的 iPhone(例如 3G、第一个 iPod touch 版本)就没有它(它们为我的应用程序提供的最大内存约为 20MB)。有没有正确的方法来禁用
java - 指定 ZonedDateTime 的时区而不更改实际日期
我有一个保存日期(不是当前日期)的 Date 对象，我需要以某种方式指定该日期为 UTC，然后将其转换为“欧洲/巴黎”，即 +1 小时。 public static LocalDateTime toL
caching - 指定 varnish 后端而不缓存
我想问你在 Varnish 代码中如何在没有缓存的情况下将请求传递到后端。我知道我可以做到并且正在发挥作用: if (req.url ~ "(\?|&)(something|somethin
module - 指定 gfortran 应在其中查找模块的目录
我目前基于模块编译程序(如主程序 foo 依赖于模块 bar )如下: gfortran -c bar.f90 gfortran -o foo.exe foo.f90 bar.o 这在 foo.f90
javascript - 指定 Meteor 包依赖项的正确方法
我正在尝试创建一个依赖于另一个 meteor 包的新 meteor 包。当我尝试 meteor add mypackage 时，出现以下错误。为什么 Meteor 不添加 mypackage 并引入它
rust - 指定 Rust 闭包生命周期
我正在制作执行器/ react 器，同时发现这是一个终生的问题。它与 async/Future 无关，可以在没有 async 糖的情况下进行复制。 use std::future::Future; s
cassandra - 指定 cqlsh 输出时区
我在 cassandra 中有一个表，其数据类型为时间戳。我正在使用 cqlsh 从数据库中获取数据，并希望更改我的时间戳列输出的输出格式。我研究了一下，发现我可以通过更改以下文件来更改时间戳输出格式

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 计算指定窗口的滚动计数