分组统计查询整点时间段。。同一条记录，能被分到两个不同的组吗？？

[draculamx]

我来简单说一下：有一张表，里面有一个时间字段，其他字段无所谓都是数据。这张表，从整点开始，每10分钟记录一次数据。现在要查询统计按天，月，年。就看一下按天统计吧。假设有10个数据字段，前面8个，都好说，直接按小时分组，查询统计他们的AVG，像这样：

select min(rectime),avg(field1),avg(field2)... from table where rectime>='2019-06-08' and rectime<'2019-06-09' group by hourid

这样，就完成了把数据按每个小时分组，并且统计的过程

现在有剩余两个字段，这两个字段中的值，是递增的，统计的要求是：每个小时的最大值，减去每个小时的最小值。也就是每个小时的增量。用SQL语句表示，也就是max(field1)-min(field1)

但是有一个问题，数据记录是10分钟一次，那么统计分组的时候，以8点到9点时间段为例，该组的时间范围就是08:00:00到08:50:00，那么最后有10分钟统计不到，因为每个小时段内的最后10分钟数据在下一个分组中，也就是说，你如果要统计8点到9点这个小时段的增量，那么正确的统计范围应该是08:00:00到09:00:00

用09:00:00的值减去08:00:00的值，得到这个小时的增量。

但是现在数据分组无法实现，因为如果你将08:00:00分在上一组，那么下一组的第一个必然是08:10:00，因为同一条记录，不可能被分配到两个组。。。

我现在就想用一条SQL语句，来完成这个统计，不知道是否可行。。。

我原来的语句是这样的：

select min(rectime),avg(field1),avg(field2),max(field3）-min(field3) from table where rectime>='2019-06-08' and rectime<'2019-06-09' group by hourid

这样统计的结果就是，每个小时会漏掉10分钟的增量统计。。。

[ダ雨夹雪リ]

你这个是记录数据的时间和要统计的时间延迟了10分钟,你把rectiome减10分钟,再按小时分组用最大值-最小值就可以了

CREATE TABLE #T
(
	id INT IDENTITY(1,1),
	rectiome DATETIME,
	value INT
)
 
INSERT INTO #T(rectiome,value) VALUES
('2019-01-01 00:00:00',1),
('2019-01-01 00:10:00',2),
('2019-01-01 00:20:00',3),
('2019-01-01 01:00:00',6),
('2019-01-01 01:10:00',8),
('2019-01-01 01:20:00',4),
('2019-01-01 01:40:00',3),
('2019-01-01 02:00:00',8),
('2019-01-01 02:10:00',1),
('2019-01-01 04:10:00',4),
('2019-01-01 07:10:00',9)

WITH ct
AS
(
SELECT  CONVERT(varchar(10), DATEADD(mi,-10,rectiome),121) AS ymd,DATEPART(hh, DATEADD(mi,-10,rectiome)) AS hh,value  FROM #T
) 
SELECT ymd,hh,MAX(ct.value)-MIN(value) AS zl  FROM ct   
GROUP  BY ymd,hh

[draculamx]

这个查询是要按照时间范围来筛选的，比如筛选条件是2019-01-01，如果按照你的做法，减去10分钟，('2019-01-01 00:00:00',1),那这条记录不是被划分到2018-12-31日去了？？

[RINK_1]

逢整点的数据，只有在求增量的时候，才需要同时隶属两个组，而求平均值的时候，就不需要？

[draculamx]

求平均值的时候也要啊，假设这表有10个字段，其中两个求每个整点之间的增量，其他8个字段，是求两个整点之间的平均值

[ダ雨夹雪リ]

2019-01-01 00:00:00 这个数据本来就是 2018-12-31 23:50 后产生的呀
难道你计算的时候,要重复这个数据?
2019-01-01 08:00:00
2019-01-01 08:10:00
2019-01-01 08:20:00
2019-01-01 08:30:00
2019-01-01 08:40:00
2019-01-01 08:50:00
2019-01-01 09:00:00
你不觉得这个8点到9点有70分钟了么?
不该是>=8:00 到 <9:00才对么 或者 >8:00 到<=9:00

[RINK_1]

引用 4 楼 draculamx 的回复:

求平均值的时候也要啊，假设这表有10个字段，其中两个求每个整点之间的增量，其他8个字段，是求两个整点之间的平均值

那求平均值的时候，就相当于每小时有7个时间点，就像#5说的那样？如果是这样，那你把逢整点的那条记录减去一分钟后，和原来的表数据并在一起，再根据小时来进行分组求平均值和最大、最小值。

[draculamx]

引用 5 楼 雨夹雪 的回复:

2019-01-01 00:00:00 这个数据本来就是 2018-12-31 23:50 后产生的呀
难道你计算的时候,要重复这个数据?
2019-01-01 08:00:00
2019-01-01 08:10:00
2019-01-01 08:20:00
2019-01-01 08:30:00
2019-01-01 08:40:00
2019-01-01 08:50:00
2019-01-01 09:00:00
你不觉得这个8点到9点有70分钟了么?
不该是>=8:00 到 <9:00才对么 或者 >8:00 到<=9:00

那我举个例子吧，假设现在这个字段统计的是进场的人流量

2019-01-01 08:00:00 100
2019-01-01 08:10:00 200
2019-01-01 08:20:00 300
2019-01-01 08:30:00 400
2019-01-01 08:40:00 500
2019-01-01 08:50:00 600
2019-01-01 09:00:00 700

现在问，8点到9点，一共进去多少人？是不是700-100，进去了600个人？？？如果统计的时候只计算 8:00 到 8:50的记录，这个小时的时间段，是不是少统计了100个人？？？

[draculamx]

引用 6 楼 RINK_1 的回复:

[quote=引用 4 楼 draculamx 的回复:]
求平均值的时候也要啊，假设这表有10个字段，其中两个求每个整点之间的增量，其他8个字段，是求两个整点之间的平均值

那求平均值的时候，就相当于每小时有7个时间点，就像#5说的那样？如果是这样，那你把逢整点的那条记录减去一分钟后，和原来的表数据并在一起，再根据小时来进行分组求平均值和最大、最小值。[/quote]

对的，我现在就是用的这种方法，在插入整点数据的时候，把时间减去一秒钟，插入的值不变，先插入这个时间点，再插入整点时间，这样两条记录，数值相同，时间相差一秒钟，但是统计的时候就方便了。

但是这种方法有一个很小的隐患，就是会丢失一秒钟的数据，当然，前提是数据写入频率小于一秒钟。。应该很少见吧。。。我这个因为是10分钟写一次，所以完全可以忽略不计

[RINK_1]

引用 8 楼 draculamx 的回复:

[quote=引用 6 楼 RINK_1 的回复:] [quote=引用 4 楼 draculamx 的回复:] 求平均值的时候也要啊，假设这表有10个字段，其中两个求每个整点之间的增量，其他8个字段，是求两个整点之间的平均值

那求平均值的时候，就相当于每小时有7个时间点，就像#5说的那样？如果是这样，那你把逢整点的那条记录减去一分钟后，和原来的表数据并在一起，再根据小时来进行分组求平均值和最大、最小值。[/quote] 对的，我现在就是用的这种方法，在插入整点数据的时候，把时间减去一秒钟，插入的值不变，先插入这个时间点，再插入整点时间，这样两条记录，数值相同，时间相差一秒钟，但是统计的时候就方便了。 但是这种方法有一个很小的隐患，就是会丢失一秒钟的数据，当然，前提是数据写入频率小于一秒钟。。应该很少见吧。。。我这个因为是10分钟写一次，所以完全可以忽略不计[/quote] 即使是频率小于一秒，为啥会丢失一秒钟的数据？

[ダ雨夹雪リ]

2019-01-01 08:00:00 100 2019-01-01 08:10:00 200 2019-01-01 08:20:00 300 2019-01-01 08:30:00 400 2019-01-01 08:40:00 500 2019-01-01 08:50:00 600 2019-01-01 09:00:00 700 看你这个数据是按天按10分钟累计的数据,你这个直接用整点的时间相减就对了

[draculamx]

2019-01-01 07:59:59 100
2019-01-01 08:00:00 100
2019-01-01 08:10:00 200
2019-01-01 08:20:00 300
2019-01-01 08:30:00 400
2019-01-01 08:40:00 500
2019-01-01 08:50:00 600
2019-01-01 08:59:59 700
2019-01-01 09:00:00 700

这就是我现在的解决办法。。。。我觉得还行。。统计的时候SQL语句也不复杂。。。