05_查每一天的first_value及last_value值,并计算标准差

Matrixdb - 05查每一天的first_value及last_value值,并计算标准差

作者

shidb

日期

2021-05-13

标签

05查每一天的first_value及last_value值,并计算标准差

创建插件

create extension matrixts ;

建表及插入数据

create table t_sensor(
ts timestamp,
temp float);

insert into t_sensor select t,random()*100 from generate_series('2021-01-01'::date,'2021-12-31'::date,'1 day') as t ;
insert into t_sensor select t,random()*100 from generate_series('2021-01-01'::date,'2021-12-31'::date,'1 day') as t ;
insert into t_sensor select t,random()*100 from generate_series('2021-01-01'::date,'2021-12-31'::date,'1 day') as t ;

查看数据样例

select * 
from t_sensor 
where ts>='2021-01-01' and ts<='2021-01-05' 
order by 1 asc;

         ts          |        temp        
---------------------+--------------------
 2021-01-01 00:00:00 |   71.8015190388499
 2021-01-01 00:00:00 | 63.486212374267126
 2021-01-01 00:00:00 |  70.91826008314719
 2021-01-02 00:00:00 |   71.4020209487007
 2021-01-02 00:00:00 |   59.2465096839927
 2021-01-02 00:00:00 | 57.372795700993606

取每一天得第一个值和最后一个值

select a.ts,
first_value(a.temp) over(partition by a.ts order by a.ts asc),
last_value(a.temp) over(partition by a.ts order by a.ts asc rows between unbounded preceding and unbounded following)
from (
select time_bucket(interval '1 day',ts) as ts,temp
from t_sensor
where ts>='2021-01-01' and ts<='2021-01-05' 
order by 1 asc) a ;
         ts          |    first_value     |     last_value     
---------------------+--------------------+--------------------
 2021-01-01 00:00:00 |   71.8015190388499 |  70.91826008314719
 2021-01-01 00:00:00 |   71.8015190388499 |  70.91826008314719
 2021-01-01 00:00:00 |   71.8015190388499 |  70.91826008314719
 2021-01-02 00:00:00 |   71.4020209487007 | 57.372795700993606
 2021-01-02 00:00:00 |   71.4020209487007 | 57.372795700993606
 2021-01-02 00:00:00 |   71.4020209487007 | 57.372795700993606
 2021-01-03 00:00:00 | 1.1092143910147456 |  50.22444328867657
 2021-01-03 00:00:00 | 1.1092143910147456 |  50.22444328867657
 2021-01-03 00:00:00 | 1.1092143910147456 |  50.22444328867657
 2021-01-04 00:00:00 | 34.349012488451436 |  8.795154478600153
 2021-01-04 00:00:00 | 34.349012488451436 |  8.795154478600153
 2021-01-04 00:00:00 | 34.349012488451436 |  8.795154478600153
 2021-01-05 00:00:00 | 40.136026895945065 | 18.006777436564647
 2021-01-05 00:00:00 | 40.136026895945065 | 18.006777436564647
 2021-01-05 00:00:00 | 40.136026895945065 | 18.006777436564647
(15 rows)

取每一天得第一条

select c.ts,c.f_temp,c.l_temp
from (select row_number() over(partition by b.ts) as rn,b.ts,b.f_temp,b.l_temp
from (select a.ts,
first_value(a.temp) over(partition by a.ts order by a.ts asc) as f_temp,
last_value(a.temp) over(partition by a.ts order by a.ts asc rows between unbounded preceding and unbounded following) as l_temp
from (
select time_bucket(interval '1 day',ts) as ts,temp
from t_sensor
where ts>='2021-01-01' and ts<='2021-01-05' 
order by 1 asc) a ) b ) c 
where c.rn=1;
         ts          |       f_temp       |       l_temp       
---------------------+--------------------+--------------------
 2021-01-01 00:00:00 |   71.8015190388499 |  70.91826008314719
 2021-01-02 00:00:00 |   71.4020209487007 | 57.372795700993606
 2021-01-03 00:00:00 | 1.1092143910147456 |  50.22444328867657
 2021-01-04 00:00:00 | 34.349012488451436 |  8.795154478600153
 2021-01-05 00:00:00 | 40.136026895945065 | 18.006777436564647
(5 rows)

计算标准差

select stddev(c.f_temp),stddev(c.l_temp)
from (select row_number() over(partition by b.ts) as rn,b.ts,b.f_temp,b.l_temp
from (select a.ts,
first_value(a.temp) over(partition by a.ts order by a.ts asc) as f_temp,
last_value(a.temp) over(partition by a.ts order by a.ts asc rows between unbounded preceding and unbounded following) as l_temp
from (
select time_bucket(interval '1 day',ts) as ts,temp
from t_sensor
where ts>='2021-01-01' and ts<='2021-01-05' 
order by 1 asc) a ) b ) c 
where c.rn=1;
       stddev       |      stddev       
--------------------+-------------------
 29.458434663208738 | 26.52395750014408
(1 row)

视频版

https://member.bilibili.com/platform/upload-manager/article

如需入群沟通交流，请扫码添加好友