2 sinat 32635107 sinat_32635107 于 2016.03.17 12:11 提问

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

3个回答

ch21st
ch21st   2016.03.23 17:05
已采纳

试下下面的

 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

enpterexpress
enpterexpress   Rxr 2016.03.17 13:10
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
2 年多之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
2 年多之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
2 年多之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
2 年多之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
2 年多之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
2 年多之前 回复
ch21st
ch21st   2016.03.23 17:08

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

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
#hive#分时段统计消耗
有个需求,是要一天每半个小时,统计一次消耗累计,共48个数据点。 刚开始,想把全天数据拉出来,然后脚本搞定。最后,灵感一来,通过一个取巧的办法,可以简单的统计,不用脚本,如下: drop table t_1213_dq_tmp4; create table t_1213_dq_tmp4 as  select a.hour,case when a.minute from ( selec
SQLServer如何在特定时间范围内按时间段查询
做报表统计的时候会碰到从月初到月末的数据 这样很好统计 如果是按时间段来统计,有该如何处理 比如1号到30号,统计7:30到9:50并且11:00到11:40并且15:01到18:59 时间范围跨度大sql语句怎么来拼凑 举个例子说明 SELECT * FROM RecordLogInfo WHERE (1 = 1) AND (LogTime >= '2011-4-3 00:00:
按时间分组统计的SQL语句
如下表table1:   日期(exportDate)               数量(amount)    --------------                    -----------   14-2月 -08                       20   10-3月 -08                       2      14-4月 -08      
java实现一定规则下的时间段或单元数统计
前期统计根据一定规则来统计,目前规则是8点12点算一个单位,12点到次日8点算一个单位。和时间段内时间长短无关。演播室使用统计中加班时段为每天的00:00-8:00,12:00-14:30,18:00-24:00,以及周末节假日全天。如果任务单时间属于加班时间内,需要统计出加班时间。统计中正常使用时间和加班时间要分别统计。 比如一个节目单使用时间为7:00到9:00,则加班时间计1小时,正常时间计1小时
Quarts 设置每隔一定时间,执行一定次数
package com.converter.test; import java.text.SimpleDateFormat; import java.util.Date; import org.quartz.Job; import org.quartz.JobExecutionContext; import org.quartz.JobExecutionException
SQL SERVER 统计一段时间内每天的数据
SQL SERVER 统计一段时间内每天的数据处理思路 。 server统计一段时间的数据。 及sql server的时间格式化
SQL Server中按照时间进行分组统计
一个群里的哥们提的这样的需求,很简单,就是分组统计问题。一张表 tb_VisitGuid    VisitDate   VisitCount编号     访问时间     访问次数要求是能根据年月及年份进行访问次数统计,并能够根据访问次数进行排序,形式如  年
java中计算基本的时间段
学习在java中计算基本的时间段 概述 如果你知道怎样在java中使用日期,那么使用时间和它才不多一样简单。这篇文章告诉你怎样把他们的差别联系起来。Robert Nielsen还告诉你怎样使用java来计算抵达航班和制造过程的时间。 作者:Robert Nielsen 翻译:Coci
关于对数据库统计每个时间段内的数据的实现方式
sql新手的我今天遇到了一个问题关于数据库统计每个时间段内的数据的问题,先简化一下题目举个例子吧 表结构 访问记录表(vs_records)      id                                           time   0001                                   2005-12-1   14:12:16      000
SQL Server如何在特定时间范围内按时间段查询
做报表统计的时候会碰到从月初到月末的数据 这样很好统计 如果是按时间段来统计,有该如何处理 比如1号到30号,统计7:30到9:50并且11:00到11:40并且15:01到18:59 时间范围跨度大sql语句怎么来拼凑   举个例子说明   SELECT * FROM RecordLogInfo WHERE (1 = 1) AND (L