sql server-SQLServer 中如何按一定间隔时间分组统计时间段内出现次数

问题描述

SQLServer 中如何按一定间隔时间分组统计时间段内出现次数

我的原始数据是这样的

|ID |time |Status
|215CSZ00 |2015/11/30 23:53:41 |2
|21CUG0HB |2015/11/30 23:54:17 |1
|21CUG0HB |2015/11/30 23:54:22 |1
|21UA75CS |2015/11/30 23:54:54 |1
|215CSZ00 |2015/11/30 23:56:17 |2
|21ZCYPUY |2015/11/30 23:57:06 |2
|2102200A |2015/11/30 23:57:45 |1
|21NNACHH|2015/11/30 23:58:20 |1
|21UA75CS |2015/11/30 23:58:27 |2

每三分钟一间隔统计状态1的出现次数

|count |start |end
|3 |2015/11/30 23:53:00 |2015/11/30 23:56:00
|2 |2015/11/30 23:56:00 |2015/11/30 23:59:00
|0 |2015/11/30 23:59:00 |2015/12/1 0:02:00

解决方案

试下下面的

 WITH tb(ID ,[time],[Status]) AS (
    select '215CSZ00','2015/11/30 23:53:41','2' union all
    select '21CUG0HB','2015/11/30 23:54:17','1' union all
    select '21CUG0HB','2015/11/30 23:54:22','1' union all
    select '21UA75CS','2015/11/30 23:54:54','1' union all
    select '215CSZ00','2015/11/30 23:56:17','2' union all
    select '21ZCYPUY','2015/11/30 23:57:06','2' union all
    select '2102200A','2015/11/30 23:57:45','1' union all
    select '21NNACHH','2015/11/30 23:58:20','1' union all
    select '21UA75CS','2015/11/30 23:58:27','2' union ALL
    select '21UA75CS','2015/12/1 1:58:27','1'
)
SELECT sv.number, DATEADD(minute, sv.number*3,max(t.mintime)) AS starttime,DATEADD(minute, (sv.number+1)*3,max(t.mintime))  AS EndTime
               ,SUM(CASE WHEN t.[time] BETWEEN DATEADD(minute, sv.number*3,t.mintime) AND DATEADD(minute, (sv.number+1)*3,t.mintime)  THEN 1 ELSE 0 END)
FROM MASTER.dbo.spt_values AS sv LEFT JOIN
 (
    SELECT * ,min([time])OVER(PARTITION BY 1) as mintime, max([time])OVER(PARTITION BY 1) AS maxtime
    FROM tb WHERE [Status]='1'
) t ON  sv.[type]='P'
WHERE  DATEADD(minute, sv.number*3,t.mintime)<=t.maxtime
GROUP BY sv.number

number starttime EndTime (No column name)
0 2015-11-30 23:54:17.000 2015-11-30 23:57:17.000 3
23 2015-12-01 01:03:17.000 2015-12-01 01:06:17.000 0
3 2015-12-01 00:03:17.000 2015-12-01 00:06:17.000 0
26 2015-12-01 01:12:17.000 2015-12-01 01:15:17.000 0
6 2015-12-01 00:12:17.000 2015-12-01 00:15:17.000 0
29 2015-12-01 01:21:17.000 2015-12-01 01:24:17.000 0
9 2015-12-01 00:21:17.000 2015-12-01 00:24:17.000 0
15 2015-12-01 00:39:17.000 2015-12-01 00:42:17.000 0
32 2015-12-01 01:30:17.000 2015-12-01 01:33:17.000 0
12 2015-12-01 00:30:17.000 2015-12-01 00:33:17.000 0
35 2015-12-01 01:39:17.000 2015-12-01 01:42:17.000 0
21 2015-12-01 00:57:17.000 2015-12-01 01:00:17.000 0
38 2015-12-01 01:48:17.000 2015-12-01 01:51:17.000 0
1 2015-11-30 23:57:17.000 2015-12-01 00:00:17.000 2
18 2015-12-01 00:48:17.000 2015-12-01 00:51:17.000 0
41 2015-12-01 01:57:17.000 2015-12-01 02:00:17.000 1
27 2015-12-01 01:15:17.000 2015-12-01 01:18:17.000 0
7 2015-12-01 00:15:17.000 2015-12-01 00:18:17.000 0
24 2015-12-01 01:06:17.000 2015-12-01 01:09:17.000 0
30 2015-12-01 01:24:17.000 2015-12-01 01:27:17.000 0
10 2015-12-01 00:24:17.000 2015-12-01 00:27:17.000 0
4 2015-12-01 00:06:17.000 2015-12-01 00:09:17.000 0
36 2015-12-01 01:42:17.000 2015-12-01 01:45:17.000 0
13 2015-12-01 00:33:17.000 2015-12-01 00:36:17.000 0
33 2015-12-01 01:33:17.000 2015-12-01 01:36:17.000 0
39 2015-12-01 01:51:17.000 2015-12-01 01:54:17.000 0
16 2015-12-01 00:42:17.000 2015-12-01 00:45:17.000 0
19 2015-12-01 00:51:17.000 2015-12-01 00:54:17.000 0
25 2015-12-01 01:09:17.000 2015-12-01 01:12:17.000 0
5 2015-12-01 00:09:17.000 2015-12-01 00:12:17.000 0
22 2015-12-01 01:00:17.000 2015-12-01 01:03:17.000 0
2 2015-12-01 00:00:17.000 2015-12-01 00:03:17.000 0
31 2015-12-01 01:27:17.000 2015-12-01 01:30:17.000 0
11 2015-12-01 00:27:17.000 2015-12-01 00:30:17.000 0
28 2015-12-01 01:18:17.000 2015-12-01 01:21:17.000 0
8 2015-12-01 00:18:17.000 2015-12-01 00:21:17.000 0
17 2015-12-01 00:45:17.000 2015-12-01 00:48:17.000 0
34 2015-12-01 01:36:17.000 2015-12-01 01:39:17.000 0
40 2015-12-01 01:54:17.000 2015-12-01 01:57:17.000 0
20 2015-12-01 00:54:17.000 2015-12-01 00:57:17.000 0
14 2015-12-01 00:36:17.000 2015-12-01 00:39:17.000 0
37 2015-12-01 01:45:17.000 2015-12-01 01:48:17.000 0

解决方案二：

SqlServer 根据字段分类汇总信息

解决方案三：

原理是，先取得最小和最大时间，然后将最小和最大四件按照每3分钟分成时间段，然后统计每个时间段的值，我为了测试，最后增加了时间范围，所有结果会很多，没有数据的时间段也列出了

时间： 2024-10-27 23:09:21

sql server-SQLServer 中如何按一定间隔时间分组统计时间段内出现次数的相关文章

SQL Server 2005 中的批编译、重新编译和计划缓存问题（2）

查询计划缓存及各种 SET 选项(与 showplan 相关及其他) 各种 SET 选项--多数与 showplan 相关--以多种复杂的方式影响着查询计划和执行上下文的编译.缓存和重用.下表汇总了相关的详细信息. 应按如下顺序阅读该表中的内容.批处理通过表中第一列所指定的特定模式提交给 SQL Server.已提交的批处理的计划缓存中可能存在.也可能不存在已缓存的查询计划.第 2 列和第 3 列描述了存在已缓存的查询计划时的情况:第 4 列和第 5 列说明了不存在已缓存的查询计划时的情况.在每

SQL Server连接中的三个最常见错误:

server|错误 SQL Server连接中的三个最常见错误: 一."SQL Server 不存在或访问被拒绝" 这个是最复杂的,错误发生的原因比较多,需要检查的方面也比较多. 一般说来,有以下几种可能性: 1,SQL Server名称或IP地址拼写有误2,服务器端网络配置有误3,客户端网络配置有误 要解决这个问题,我们一般要遵循以下的步骤来一步步找出导致错误的原因. ============= 首先,检查网络物理连接 =============ping <服务器IP地址/服务

使用 SQL Server 2005中的 CLR 集成

本文描述了数据库应用程序开发人员和架构师如何利用 SQL Server 2005 中的 CLR 集成功能.本文对基于 CLR 的编程方式与 SQL Server 中支持的现有编程模型(如 TransacT-SQL 和扩展存储过程)进行了比较,并且强调了各自相对的优缺点.还提供了一组选择合适的编程替代方法的高级指导,以及一些示例和代码示例. 一.简介 Microsoft 通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CLR),SQL Server 2005显

Microsoft SQL Server 2005 中的 XML 支持（3）

四.SQL Server 2005 中的客户端 XML 处理 XML 数据类型的客户端支持.NET 框架 V2.0 中的 ADO.NET XML 支持 在 SqlDataReader.GetSqlXml() 方法的 System.Data.SqlTypes 命名空间中,XML 数据类型是作为类 SqlXml 公开的.可以使用 SqlXml.CreateReader() 函数从 SqlXml 对象获得 XmlReader. 类型化 XML 列的 XML 架构集合的名称由三部分组成,可以从 XML

SQL Server 2005 中的批编译、重新编译和计划缓存问题（4）

下面,考虑以下 T-SQL 代码段:-- dbo.someTable will be used to populate a temp table-- subsequently.create table dbo.someTable (a int not null, b int not null)godeclare @i intset @i = 1while (@i <= 2000)begin insert into dbo.someTable values (@i, @i+5) set @i =

Microsoft SQL Server 2005 中的 XML 选项

本文介绍 Visual Studio 2005/SQL Server 2005 环境中用于处理 XML 数据的三个选项,包括使用方案以及指导用户在各选项之间进行选择的指南. 一.System.Xml.SQLXML 和 XML 数据类型简介 本节简要介绍 Microsoft SQL Server 2000 中提供的 XML 支持的发展历史,并概要介绍 Microsoft Visual Studio 2005/SQL Server 2005 环境中提供的用于处理 XML 和关系数据的三个选项.这三个

SQL Server 2005 中的商务智能和数据仓库（2）

例如,设想一个"客户"维度.关系型源表有八列: • 客户键 • 客户名称 • 年龄 • 性别 • 电子邮件 • 城市 • 地区 • 国家 相应的 Analysis Services 维度应具有七个属性: • 客户(整型键.以"客户名称"作为名称) • 年龄.性别.电子邮件.城市.地区.国家 数据中存在一种自然层次结构,{国家.地区.城市.客户}.出于导航目的,应用程序开发人员可以选择创建第二个层次结构:{年龄.性别}.商务用户并没有看到这两个层次结构行为方式之间有何

SQL Server连接中的四个最常见错误

server|错误 SQL Server连接中的四个最常见错误: 一."SQL Server 不存在或访问被拒绝" 这个是最复杂的,错误发生的原因比较多,需要检查的方面也比较多. 一般说来,有以下几种可能性: 1,SQL Server名称或IP地址拼写有误 2,服务器端网络配置有误 3,客户端网络配置有误 要解决这个问题,我们一般要遵循以下的步骤来一步步找出导致错误的原因. ============= 首先,检查网络物理连接 ============= ping <服务器IP地址

在 SQL Server 2005 中使用表值函数来实现空间数据库

server|函数|数据|数据库 Gyorgy Fekete 和 Alex Szalay约翰霍普金丝大学 Jim GrayMicrosoft(联系作者) 适用于Microsoft SQL Server 2005 摘要:本文说明了如何使用 C# 和表值函数将空间搜索函数("邻近点的点"和"多边形内的点")添加到 Microsoft SQL Server 2005.使用此库可以在不编写任何特殊代码的情况下向应用程序中添加空间搜索.此库实现了来自约翰霍普金丝大学的公共域