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

关注

原创最新推荐文章于 2026-05-13 06:27:15 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

结合ENVI与ArcMAP的NC数据处理(均值、最大最小值等)心得记录在科研过程中遇到了处理NC数据的问题，虽然Matlab可以很方便的处理NC数据，但是ArcMap与ENVI可以提供可视化的操作，也比较方便，所以这个方法还是值得记录的。此前基础薄弱的博主对ArcMap的modelbuilder并不熟悉，所以一把辛酸泪地一个个鼠标点击来处理450多幅影像。希望这篇文章的存在，能够让更多地信遥感人减轻工作负担。一、前言从NOAA数据库下载到了1982-2019的月均SST数据为NC格式。虽然可以在属性表里根据时间选择影像，此时在ArcMap中会被认为是单幅灰度栅阅读详情

题：

/* ============================================================ */ 
/*  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 個資料列受到影響)
*/

收录于