DELPHI盒子
!实时搜索: 盒子论坛 | 注册用户 | 修改信息 | 退出
检举帖 | 全文检索 | 关闭广告 | 捐赠
技术论坛
 用户名
 密  码
自动登陆(30天有效)
忘了密码
≡技术区≡
DELPHI技术
移动应用开发
Web应用开发
数据库专区
报表专区
网络通讯
开源项目
论坛精华贴
≡发布区≡
发布代码
发布控件
文档资料
经典工具
≡事务区≡
网站意见
盒子之家
招聘应聘
信息交换
论坛信息
最新加入: mmp369
今日帖子: 1
在线用户: 11
导航: 论坛 -> 数据库专区 斑竹:liumazi,waterstone  
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/8 19:31:29
标题:
被一个分组统计给整懵逼了。。。搞了一下午还没搞出来 浏览:402
加入我的收藏
楼主: 数据库结构在下面附件的图中

我来简单说一下:有一张表,里面有一个时间字段,其他字段无所谓都是数据。这张表,从整点开始,每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 strftime('%H', rectime) 

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

现在有剩余两个字段,这两个字段中的值,是递增的,统计的要求是:每个小时的最大值,减去每个小时的最小值。也就是每个小时的增量。用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语句,来完成这个统计,不知道是否可行。。。
此帖子包含附件:
PNG 图像
大小:23.6K
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/8 19:33:58
1楼: 我原来的语句是这样的:

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 strftime('%H', rectime) 

这样统计的结果就是,每个小时会漏掉10分钟的增量统计。。。
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 wk_knife (wk_knife) ★☆☆☆☆ -
盒子活跃会员
2019/6/8 21:39:01
2楼: 同表关联一下:

select t.*,tt.* from table t left join table tt on  求整点时间(t.rect_time)= tt.rect_time-1/24。

效果就是在原表右边关联了整点大1的记录。以八点为例,就是属于8开头的6条记录都关联了9:00整的记录。
然后,左边8:00到8:50的6条记录分别与右边的9:00整的记录比大小,再对结果求最大最小。就变相的实现了8:00-9:00的7条记录分组比大小。
----------------------------------------------
-
作者:
男 wk_knife (wk_knife) ★☆☆☆☆ -
盒子活跃会员
2019/6/8 21:49:41
3楼: 用oracle写就大致是这个意思
select to_char(recttime,'yyyy-mm-dd hh24'), Max(case when field1>f1 then field1 else f1 end) maxfield1
from(
select t.*, tt.recttime t1, tt.field1 f1, tt.field2 f2 from table t
left join table tt   on to_date(to_char(t.recttime,'yyyy-mm-dd hh24'),'YYYY-mm-dd hh24') = tt.recttime-1/24)
group by to_char(recttime,'yyyy-mm-dd hh24')
----------------------------------------------
-
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/8 22:01:38
4楼: 我SQL不咋滴,你的代码我得消化一下,但是我看你的文字,里面有“记录比大小”,其实我不需要比较大小,因为那两个字段数据是递增的,所以我只需要用9:00的值减去8:00的值就可以达到我的目的了
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 wk_knife (wk_knife) ★☆☆☆☆ -
盒子活跃会员
2019/6/8 22:37:52
5楼: 嗯,看到你问题里说的递增了。
不过因为你要做分组操作,除分组字段,其他字段都必须是统计操作。所以类似比大小Max的操作还更方便些,而且你的avg操作也要改成(sum(field1)+Max(f1))/7,要不还是少统计一条记录。
----------------------------------------------
-
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/8 22:51:13
6楼: 额。。。我用的是SQLITE,现在这张表里,有6万多条数据,我为了做测试添加的,每条记录间隔10分钟,就像我一楼的截图一样,其实也就是从2019年1月1日 00:00:00开始,每10分钟增加一条记录,一共增加了365天的,也就是一年的记录,因为我要做日,月,年报表。。

我试着用你的方法left join,结果一跑就卡的不动了。。。。

我的需求就是,假设表一共有12个字段,除开id和rectime,还剩10个,这10个中,有8个是要求每个小时的平均值,还有两个字段,是要求,每个小时的最大值减去最小值

能不能用一条SQL语句完成。
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/8 22:59:54
7楼: 求平均值和每小时的增量,时间段都是整点,例如8:00-9:00,9:00-10:00....

现在的难点就是:用group by无法把数据按照整点来分组,如果用group by按照小时来分组,分出来的结果是 8:00-8:50,9:00-9:50...

这个查询结果,是直接送到FastReport生成报表的,所以最好是能在一条SQL语句中完成

还有效率问题,一年就有6万多条数据,时间长了以后,如果SQL语句效率不行的话,生成报表的速度就会很慢。。。
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/9 1:37:51
8楼: 嗯。。。还是用一条语句搞定了,并不复杂,还是自己SQL平时用的少,不熟悉。。。
但是这条语句的执行效率很低,在这张6万多条记录的表中,当查询的日期在末尾,也就是12月份的时候,执行一次需要4秒,当查询日期在开头,也就是1月份的时候,很快。

最终我还是放弃了这一条SQL语句,因为效率不行,转而采用另一个方案,这个方案在一张表中有550万条记录,数据库文件达到4个多G的情况下,执行相同的查询,需要4秒左右。。不过我好想忘记测靠后的数据了,我当时也是查的开头的数据。。
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 bjlg (蓝天) ★☆☆☆☆ -
盒子活跃会员
2019/6/10 6:54:22
9楼: 你本身的想法就是有问题,
你说数据是要8:-9:的,你统计了8:00-9:00的,如果按照你的做法,其实8:00的数据其实是7:50-8:00的,你每次统计都是最后多加了上一次的10分钟的数据
----------------------------------------------
http://delphi-z.ru>http://delphi.icm.edu.pl/ftp/http://delphi-z.ru
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/10 9:21:05
10楼: 这个数据,是按照客户要求进行记录的,数据是从各种仪表传过来的,每10分钟记录一次到数据库。
其中,有两个字段的值,数值是递增的,会一直增大,类似于表当中的id主键。
统计的时候,客户要求统计这两个字段,每个小时的增量,也就是从8:00到9:00,增加了多少,所以我才会有这个需求。
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/10 9:24:38
11楼: 一楼中的截图,那两个字段并非递增字段,由于字段较多,而且那两个递增字段在最末尾,所以只截取了表格中的左边一小部分
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 a5824 (Return) ▲▲▲▲△ -
注册会员
2019/6/10 10:56:52
12楼: 问题要学会分解,我们在上面再加一列,比如范围的开始时间,这列的值就是这个时间的前一个小时,过滤条件筛选一下,只取整点的(分钟数是00的),结果再去关联查询一下时间,然后相减,结果不就出来了吗?
----------------------------------------------
作者:
男 hdcopy (hdcopy) ▲▲▲▲▲ -
普通会员
2019/6/10 13:31:30
13楼: sqlite不熟, 如果是3.25后的版本,是支持lead和lag的,
也可以参考下http://www.itpub.net/thread-2092136-1-1.html
----------------------------------------------
-
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/10 13:38:32
14楼:
问题要学会分解,我们在上面再加一列,比如范围的开始时间,这列的值就是这个时间的前一个小时,过滤条件筛选一下,只取整点的(分钟数是00的),结果再去关联查询一下时间,然后相减,结果不就出来了吗?



这个可以的,在增加的一列里面,整点的时候,直接写上上一个小时的数值就行了
id  rectime          v1    v-1
 2  2019-01-02:00:00     25    12(这个值就是2019-01-01:00:00)的值)

这个字段“v-1”,统计的时候直接拿来用就行了

只要是自己写的数据,都好办,还有其他方法比这个更好

但是还有一种情况,就是数据库并不是由我来写入,而是其他人(例如各种组态软件),这时候我只能读,那么就要用到比较复杂的查询了
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/10 13:48:52
15楼: select rectime,min(rectime),( select field76 from tab3 i where i.rectime=datetime(o.rectime,'+1 hours') )-min(field76) from tab3 o where rectime>='2019-12-01 00:00:00' and rectime<'2019-12-02 00:00:00' group by strftime('%H', rectime)

这就是我昨天捣鼓出来的,看上去也不难,但是我SQL很菜,搞了好久

这条语句,就可以在不修改表结构,不额外添加数据的情况下(也就是数据库对你来说是只读的),完成我的需求
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/10 13:50:18
16楼: 使用这种复杂查询的时候,记得把rectime,添加索引,这样查询速度可以提高几十倍,刚开始这查询比较慢,在6万多条数据的时候,添加了索引之后,速度非常快
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 a5824 (Return) ▲▲▲▲△ -
注册会员
2019/6/10 14:40:04
17楼: 还有更高效的方法,update可以带变量更新,只要先把整时数据放到临时表里,然后update一下,结果就出来了
----------------------------------------------
作者:
男 a5824 (Return) ▲▲▲▲△ -
注册会员
2019/6/10 14:42:34
18楼: 上面的问题我解答一下,不管这个数据库是不是你操作的,你查询都不会影响他的,select后面本来就可以新增加列,只要你不去Update他,都不会有任何变化,我说的那列,就是根据rectime 计算出来的上一个小时列,不会改别人的数据库
----------------------------------------------
作者:
男 draculamx (draculamx) ▲▲△△△ -
注册会员
2019/6/10 15:12:08
19楼:
: 上面的问题我解答一下,不管这个数据库是不是你操作的,你查询都不会影响他的,select后面本来就可以新增加列,只要你不去Update他,都不会有任何变化,我说的那列,就是根据rectime 计算出来的上一个小时列,不会改别人的数据库



那你说的这个,正好就是我上面那段代码,一模一样的功能。。。
----------------------------------------------
C++ builder 用户前来摸鱼。。。
作者:
男 inbreak (入侵) ★☆☆☆☆ -
盒子活跃会员
2019/6/10 15:36:33
10楼: MSSQL2008
此帖子包含附件:
PNG 图像
大小:58.0K
----------------------------------------------
我是菜鸟,己经搞了十多年了,但是我仍然很菜。
作者:
男 sczhyq (旺财) ▲▲▲▲△ -
注册会员
2019/6/10 15:38:34
20楼: 数据库里的时间值还包括毫秒,只不过默认不会展现出来
----------------------------------------------
我84砖家
作者:
男 hdcopy (hdcopy) ▲▲▲▲▲ -
普通会员
2019/6/11 14:09:18
21楼: select a.*,dvalue-LAG(dvalue,1,0) OVER(ORDER BY id) AS C from testa a where to_char(cdate, 'mi') = '00' and
cdate between to_date('2019-1-1', 'yyyy-mm-dd') and to_date('2019-1-2', 'yyyy-mm-dd')

oracle的语法,测试数据总数200W,日期从2001-1-1到2039年,
只有3个字段: id, cdate, dvalue; 没有加任何索引;
返回全年数据约120S,如果像上面那样取随便一天的,0.2-0.3S
----------------------------------------------
-
信息
登陆以后才能回复
Copyright © 2CCC.Com 盒子论坛 v2.1 版权所有 页面执行39.0625毫秒 RSS