统计，求指定时段内的平均值、最大（最小）值，以及最值出现的时刻

最新推荐文章于 2022-08-20 14:45:35 发布

原创最新推荐文章于 2022-08-20 14:45:35 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #null #character #integer #table #float

数据库专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一个针对SCADA系统的SQL查询方案，旨在通过分组获取每组内的最大值和最小值及其对应的时间点。示例中使用了具体的数据表和记录来展示如何实现这一查询过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

题：

/* ============================================================ */ 
/*  Table: t_scadaTMeas                                                  */ 
/* 分组，求最大（最小）值，以及该值出现的时刻。若最值多次出现，取第一次出现的时刻。 */ 
/* ============================================================ */ 
create table t_scadaTMeas 
( 
    dtScadaTime  datetime              not null, 
    nAloge      float                null    , 
    iEquimentId  integer              not null, 
    cMeascode    character(8)          not null, 
    constraint PK_T_SCADAHOURMEAS primary key (dtScadaTime, iEquimentId, cMeascode) 
) 
go 
insert into t_scadaTMeas values('2007/11/20 01:01:00',    1,  1, 'a1') 
insert into t_scadaTMeas values('2007/11/20 01:01:03',    3,  1, 'a1') 
insert into t_scadaTMeas values('2007/11/20 01:59:03',    3,  1, 'a1') 

insert into t_scadaTMeas values('2007/11/20 05:01:00',    3,  1, 'a1') 
insert into t_scadaTMeas values('2007/11/20 05:01:03',    2,  1, 'a1') 
insert into t_scadaTMeas values('2007/11/20 05:59:03',    1,  1, 'a1') 

insert into t_scadaTMeas values('2007/11/20 03:11:00',    1,  2, 'a2') 
insert into t_scadaTMeas values('2007/11/20 03:21:03',    2,  2, 'a2') 
insert into t_scadaTMeas values('2007/11/20 03:59:03',    1,  2, 'a2') 

insert into t_scadaTMeas values('2007/11/20 23:01:00',    3,  3, 'a3') 
insert into t_scadaTMeas values('2007/11/20 23:01:03',    3,  3, 'a3') 
insert into t_scadaTMeas values('2007/11/20 23:59:03',    3,  3, 'a3') 

/* 
=== 查询结果 
cMeascode  iEquimentId  ScadaTime              max_val  time_at_maxVal          min_val  time_at_minVal 
a1          1            '2007/11/20 01:00:00'        3  '2007/11/20 01:01:03'        1    '2007/11/20 01:01:00' 
a1          1            '2007/11/20 05:00:00'        3  '2007/11/20 05:01:00'        1    '2007/11/20 05:59:03' 
a2          2            '2007/11/20 03:00:00'        2  '2007/11/20 03:21:03'        1    '2007/11/20 03:11:00' 
a3          1            '2007/11/20 23:00:00'        3  '2007/11/20 23:01:00'        3    '2007/11/20 23:01:00' 
*/

解：select
    b.cMeascode,b.iEquimentId,b.ScadaTime+':00:00',
    max_val=b.MaxnAloge,
    [time_at_maxVal]=max(case when a.nAloge=b.MaxnAloge then a.dtScadaTime end),
    min_val=b.MinnAloge,
    [time_at_minVal]=min(case when a.nAloge=b.MinnAloge then a.dtScadaTime end)
from
    t_scadaTMeas a
join
    (select cMeascode,iEquimentId,ScadaTime=convert(varchar(13),dtScadaTime,120),max(nAloge)MaxnAloge,min(nAloge)MinnAloge
    from t_scadaTMeas group by cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)) b
    on a.cMeascode=b.cMeascode and a.iEquimentId=b.iEquimentId and convert(varchar(13),a.dtScadaTime,120)=b.ScadaTime and a.nAloge in(b.MaxnAloge,b.MinnAloge)
group by b.cMeascode,b.iEquimentId,b.ScadaTime,b.MinnAloge,b.MaxnAloge

/*
cMeascode iEquimentId                     max_val                time_at_maxVal          min_val                time_at_minVal
--------- ----------- ------------------- ---------------------- ----------------------- ---------------------- -----------------------
a1        1           2007-11-20 01:00:00 3                      2007-11-20 01:01:03.000 1                      2007-11-20 01:01:00.000
a1        1           2007-11-20 05:00:00 3                      2007-11-20 05:01:00.000 1                      2007-11-20 05:59:03.000
a2        2           2007-11-20 03:00:00 2                      2007-11-20 03:21:03.000 1                      2007-11-20 03:11:00.000
a3        3           2007-11-20 23:00:00 3                      2007-11-20 23:01:00.000 3                      2007-11-20 23:01:00.000

(4 個資料列受到影響)
*/