用PostgreSQL描绘人生的高潮、尿点、低谷 - 窗口/帧 or 斜率/导数/曲率/微积分?

19 minute read

背景

人生就像一场戏,有高潮,有尿点,有低谷。如果用曲线来描述漫漫人生路的话,怎么找出高潮、尿点、低谷呢?

其实类似的场景还有很多,比如来自传感器(比如人身上可以探测的指标就有很多)的监控数据;服务器的监控数据;温度,湿度的变化数据;等等,都可以数字化,用曲线来表示。

找高潮、尿点、低谷有什么好的方法吗?

如果你是刚毕业不久的学生,可能会往学校里学到的知识去思考,怎么找曲线的拐点?

导数、微积分、斜率、曲率、面积?

没错,PostgreSQL可以算这些,math相关的函数里有,如果没有,也可以写UDF来实现。

如果你把这些都忘了,还有什么方法能找出高潮、尿点、低谷么?

当然,还是有方法的,也是PostgreSQL,它支持窗口,帧的查询。

高潮、尿点、低谷

用几张图来表示一下什么是高潮、尿点、低谷?

不要以为我是算命的,我其实是搞PostgreSQL的。

pic

这段曲线表示了一段奋力拼搏,快速到达人生巅峰,然后在巅峰期有些许的抖动,一段时间之后遭遇打击急速掉入谷底的情况,由于底子好,东山再起也很快,不过此命注定要在一波三折中度过,最后持续的保持在平滑的巅峰状态。

pic

而这一段人生属于不积跬步无以至千里的模式,主人公慢慢的积累,终于到达了人生的巅峰,而且在巅峰期主人公应该是想继续突破,很可惜,只是一个小的突破,马上有跌下来了,在过了一段时间,主人公可能遭遇了不可逆转的挫折,进入了人生的谷底之后,就一蹶不振,甚至还想过自杀,不过还好躲过一劫,可惜此命太薄,后来也只能郁郁不得终。

pic

这一段有意思了,也是属于不积跬步无以至千里的模式,主人公慢慢的积累,终于到达了人生的巅峰。但是好景不长,可能娶了个即会败家又不旺夫的败家娘们,慢慢的开始走下坡路,直到精尽人亡。

pic

这个主人公绝对属于可以拍电视剧的传奇人物类型,可能是富豪家族的小孩出生后被医院抱错的情况,从小在屌丝家庭长大,后来机缘巧合又回到了富豪家庭,因此经历了急速的从屌丝到高富帅的过程。但是可能不习惯富豪家的勾心斗角,被可恶的后妈搞走,又从高富帅摔入谷底。还好亲爸在世时的庇护下,经历了富豪家的一波三折,中间还出现过几次小的高峰,可惜好景不长,后来还是逐渐的衰退。

pic

最后这一段一定是一个从屌丝到人生赢家的励志故事,主人公在屌丝家庭长大,经过自己的坚持不懈的努力,到达了人生的巅峰,整个过程非常的平滑,还创造了不少小的高潮,直到风光逝去。

看了这么多曲线,我们怎么把拐点找出来?

曲线分析

曲线的分辨率

在单位时间内采样的点越多,分辨率就越高。

对于分辨率较低的曲线,看起来可能就不像曲线了,是一些跳跃的点,按斜率找到有意义的拐点也比较麻烦,也是难点之一。

曲线的长度

在历史的长河中,一个人的详细经历或故事(除了一些对历史有重大影响的人)可能是不起眼的,但是对于这个人来说,他的经历,他遇到的人和事,就可以抓出很多值得说道的点来。

所以说我们要分析曲线的拐点,和曲线的长度也是有莫大的关系的,你是要看1年的曲线拐点呢,还是要看1天的曲线拐点?

在1天的曲线中能找出来10个拐点,但是这一天的数据可能在一年的曲线中出现在一个很平淡的段落中,那么可能就一个点都找不到。

曲线的总体落差

即曲线的总体落差,假设我们要分析一条1年的曲线,总体落差指的是1年的数据中最大值和最小值的差。

总体落差可以描述一段数据的高潮和低谷的差异,可以作为判断关键点的一个评判系数。

曲线的区间落差

区间落差,指以某个点为中心,它的辐射半径内的最大值和最小值的落差。可以用来评判这个点所处的区域中的落差,判断平凡人生路中的小尖刺,比如在平淡期的一些小的波动,小高潮或者小低谷。

曲线的总体高低水位

掌握了整体曲线的落差后,其实就可以设定高低水位,或者按大局来设定高低水位。

比如股市,有大盘走势,有个股走势。

什么是低水位,什么是高水位,是有参照的情况下来设置的。

打个比方,一组监控数据,监控的是服务器的负载,服务器有32核,那么我们就可以根据这些参照设定负载的高低水位,比如低水位为1以下,高水位为24以上。

数据库算高潮、低谷、尿点的实践

需要用到PostgreSQL的窗口查询和帧,用来实现对以上曲线分析的计算。

窗口

窗口指计算的数据区间,指与当前row在同一个分组中的数据。

帧也是指计算的数据区间,但是是在分组内的指定,可以根据当前row设定辐射半径,或者当前row的前后分开设置辐射范围。

例子

求凸点和凹点其实可以用前后值的大小比较就可以了,使用lead()和lag()窗口函数 即可。

但是有其缺陷,没有落差,没有意义。

没有范围数据,无法检测渐进数据的凹凸。

创建测试表:

create table wind (id serial, val numeric);

insert into wind (val) select mod((trunc(100000*random()+210000*random()))::int, 900)+round((100*random())::numeric, 2) from generate_series(1,10000);

postgres=# select * from wind;
  id   |  val   
-------+--------
     1 | 701.20
     2 | 511.14
     3 | 241.83
     4 | 552.72
     5 | 507.11
     6 |  62.66
     7 | 343.72
     8 | 260.89
     9 | 785.54
    10 | 483.94
    11 | 330.08
    12 | 716.85
    13 | 897.21
    14 | 493.07
    15 | 492.86
    16 | 742.36
    17 | 890.77
    18 |  62.47
    19 | 815.17
    20 | 695.46
    21 | 963.36
    22 | 178.90
    23 | 818.06
    24 | 337.37
    25 | 738.30
    26 |  90.01
    27 | 391.66
......

query 1

select * from 
(
  select id, 
    val, 
    min(val) over(order by id rows between 10 preceding and current row) as left_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径的左边
    max(val) over(order by id rows between 10 preceding and current row) as left_max,  -- 左边相邻10个点的最大值(包括当前点), 辐射半径的左边
    min(val) over(order by id rows between current row and 10 following) as right_min, -- 右边相邻10个点的最小值(包括当前点), 辐射半径的右边
    max(val) over(order by id rows between current row and 10 following) as right_max, -- 右边相邻10个点的最大值(包括当前点), 辐射半径的右边
    min(val) over() as global_min, -- 全局的最小值(包括当前点)
    max(val) over() as global_max  -- 全局的最大值(包括当前点)
    from wind
) t
where 
(  -- 找出全局谷底开始
(  
left_max - val > ((global_max-global_min)*0.5)  -- 左边相邻10个点的最大值 - 当前值 > 全局落差的50% (可能导致无法找到平滑变化的谷底)
and 
val = left_min  -- 拐点
)

and

(
right_max - val > ((global_max-global_min)*0.5)  -- 右边相邻10个点的最大值 - 当前值 > 全局落差的50% (可能导致无法找到平滑变化的谷底)
and 
val = right_min  -- 拐点
)
)  -- 找出全局谷底结束

or

(  -- 找出全局高潮开始
(
val - left_min > ((global_max-global_min)*0.5)  -- 当前值 - 左边相邻10个点的最小值 > 全局落差的50% (可能导致无法找到平滑变化的高潮)
and 
val = left_max  -- 拐点
)

and

(
val - right_min > ((global_max-global_min)*0.5)  -- 当前值 - 右边相邻10个点的最小值  > 全局落差的50% (可能导致无法找到平滑变化的高潮)
and 
val = right_max  -- 拐点
)
)  -- 找出全局高潮开始
;

结果

  id  |  val   | left_min | left_max | right_min | right_max | global_min | global_max 
------+--------+----------+----------+-----------+-----------+------------+------------
    6 |  62.66 |    62.66 |   701.20 |     62.66 |    897.21 |       1.50 |     996.57
   18 |  62.47 |    62.47 |   897.21 |     62.47 |    963.36 |       1.50 |     996.57
   21 | 963.36 |    62.47 |   963.36 |     90.01 |    963.36 |       1.50 |     996.57
   44 |  57.54 |    57.54 |   884.70 |     57.54 |    949.44 |       1.50 |     996.57
   45 | 949.44 |    57.54 |   949.44 |    291.63 |    949.44 |       1.50 |     996.57
   62 |  54.09 |    54.09 |   841.67 |     54.09 |    835.75 |       1.50 |     996.57
   76 | 892.74 |   186.73 |   892.74 |     43.92 |    892.74 |       1.50 |     996.57
   83 |  43.92 |    43.92 |   892.74 |     43.92 |    828.41 |       1.50 |     996.57
   98 | 961.65 |   108.82 |   961.65 |    222.69 |    961.65 |       1.50 |     996.57
  112 |  72.93 |    72.93 |   778.41 |     72.93 |    689.00 |       1.50 |     996.57
  126 |  44.19 |    44.19 |   829.42 |     44.19 |    900.01 |       1.50 |     996.57
  141 | 950.97 |    44.80 |   950.97 |     80.02 |    950.97 |       1.50 |     996.57
......
 9924 | 879.23 |    80.56 |   879.23 |     73.87 |    879.23 |       1.50 |     996.57
 9930 |  73.87 |    73.87 |   879.23 |     73.87 |    858.93 |       1.50 |     996.57
 9950 | 941.34 |   117.90 |   941.34 |     61.66 |    941.34 |       1.50 |     996.57
 9953 |  61.66 |    61.66 |   941.34 |     61.66 |    872.60 |       1.50 |     996.57
 9974 | 916.83 |   278.73 |   916.83 |    147.24 |    916.83 |       1.50 |     996.57
 9986 | 957.28 |   147.24 |   957.28 |     87.52 |    957.28 |       1.50 |     996.57
 9994 |  87.52 |    87.52 |   957.28 |     87.52 |    726.31 |       1.50 |     996.57
(945 rows)

功能

可以找到全局高潮、低谷

可以找到平滑变化的拐点(设置10,或者更大,越大,越能找出平滑变化的拐点)

缺陷

无法找到平滑变化后正式进入平滑高潮或低谷期(>10)的点。

无法找到在平滑高潮或低谷期中的小波峰和波谷(即局部高潮或低谷)。

query 2

select * from 
(
  select id, 
    val, 
    min(val) over(order by id rows between 10 preceding and current row) as left_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径的左边 
    max(val) over(order by id rows between 10 preceding and current row) as left_max,  -- 左边相邻10个点的最大值(包括当前点), 辐射半径的左边 
    min(val) over(order by id rows between current row and 10 following) as right_min,  -- 右边相邻10个点的最小值(包括当前点), 辐射半径的右边 
    max(val) over(order by id rows between current row and 10 following) as right_max,  -- 右边相邻10个点的最大值(包括当前点), 辐射半径的右边 
    min(val) over(order by id rows between 10 preceding and 10 following) as range_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径两边全部 
    max(val) over(order by id rows between 10 preceding and 10 following) as range_max,  -- 右边相邻10个点的最大值(包括当前点), 辐射半径两边全部 
    min(val) over() as global_min, -- 全局的最小值(包括当前点) 
    max(val) over() as global_max  -- 全局的最大值(包括当前点) 
  from wind
) t
where 
(  -- 找越过低水位后的局部低谷开始
 (
 left_max - val > ((range_max-range_min)*0.5)   -- 左边相邻10个点的最大值 - 当前值 > 局部落差的50%
 and 
 val = left_min  -- 拐点 
 )

 and

 (
 right_max - val > ((range_max-range_min)*0.5)   -- 右边相邻10个点的最大值 - 当前值 > 局部落差的50%
 and 
 val = right_min  -- 拐点
 )

 and 

 (val - global_min)::numeric/(global_max-global_min+0.0000001) <= 0.1      -- 当前点必须是低水位线外的点,低水位线为全局低谷上浮10%的值
)  -- 找越过低水位后的局部低谷结束

or

(  -- 找越过高水位后的局部高潮开始
 (
 val - left_min > ((range_max-range_min)*0.5)   -- 当前值 - 左边相邻10个点的最小值 > 局部落差的50%
 and 
 val = left_max  -- 拐点
 )

 and

 (
 val - right_min > ((range_max-range_min)*0.5)   -- 当前值 - 右边相邻10个点的最小值 > 局部落差的50%
 and 
 val = right_max  -- 拐点
 )

 and 

 (global_max - val)::numeric/(global_max-global_min+0.0000001) <= 0.1      -- 当前点必须是高水位线外的点,高水位线为全局高潮下调10%的值
)  -- 找越过高水位后的局部高潮结束
;

结果

  id  |  val   | left_min | left_max | right_min | right_max | range_min | range_max | global_min | global_max 
------+--------+----------+----------+-----------+-----------+-----------+-----------+------------+------------
    6 |  62.66 |    62.66 |   701.20 |     62.66 |    897.21 |     62.66 |    897.21 |       1.50 |     996.57
   18 |  62.47 |    62.47 |   897.21 |     62.47 |    963.36 |     62.47 |    963.36 |       1.50 |     996.57
   21 | 963.36 |    62.47 |   963.36 |     90.01 |    963.36 |     62.47 |    963.36 |       1.50 |     996.57
   44 |  57.54 |    57.54 |   884.70 |     57.54 |    949.44 |     57.54 |    949.44 |       1.50 |     996.57
   45 | 949.44 |    57.54 |   949.44 |    291.63 |    949.44 |     57.54 |    949.44 |       1.50 |     996.57
   62 |  54.09 |    54.09 |   841.67 |     54.09 |    835.75 |     54.09 |    841.67 |       1.50 |     996.57
   83 |  43.92 |    43.92 |   892.74 |     43.92 |    828.41 |     43.92 |    892.74 |       1.50 |     996.57
   98 | 961.65 |   108.82 |   961.65 |    222.69 |    961.65 |    108.82 |    961.65 |       1.50 |     996.57
  112 |  72.93 |    72.93 |   778.41 |     72.93 |    689.00 |     72.93 |    778.41 |       1.50 |     996.57
  126 |  44.19 |    44.19 |   829.42 |     44.19 |    900.01 |     44.19 |    900.01 |       1.50 |     996.57
......
 9913 |  34.37 |    34.37 |   871.17 |     34.37 |    786.24 |     34.37 |    871.17 |       1.50 |     996.57
 9930 |  73.87 |    73.87 |   879.23 |     73.87 |    858.93 |     73.87 |    879.23 |       1.50 |     996.57
 9950 | 941.34 |   117.90 |   941.34 |     61.66 |    941.34 |     61.66 |    941.34 |       1.50 |     996.57
 9953 |  61.66 |    61.66 |   941.34 |     61.66 |    872.60 |     61.66 |    941.34 |       1.50 |     996.57
 9974 | 916.83 |   278.73 |   916.83 |    147.24 |    916.83 |    147.24 |    916.83 |       1.50 |     996.57
 9986 | 957.28 |   147.24 |   957.28 |     87.52 |    957.28 |     87.52 |    957.28 |       1.50 |     996.57
 9994 |  87.52 |    87.52 |   957.28 |     87.52 |    726.31 |     87.52 |    957.28 |       1.50 |     996.57
(691 rows)

功能

可以覆盖到越过高水位或低水位的点

可以找到越过高水位或低水位后,平滑过渡的高潮或低谷的点

可以找到在平滑高潮或低谷期中的小波峰和波谷(即局部高潮或低谷)。

缺陷

无法找到单边骤变的点,例如新增一下骤变点。

postgres=# select * from wind order by id desc limit 10;
  id   |  val   
-------+--------
 10000 | 440.70
  9999 | 465.07
  9998 | 243.10
  9997 | 117.50
  9996 | 527.88
  9995 | 726.31
  9994 |  87.52
  9993 | 347.65
  9992 | 527.00
  9991 | 206.62
(10 rows)

postgres=# insert into wind (val) values (1),(2),(3),(4),(5),(6),(6),(7),(1),(8);
INSERT 0 10

query 3

select * from 
(
  select id, 
    val, 
    min(val) over(order by id rows between 10 preceding and current row) as left_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径的左边 
    max(val) over(order by id rows between 10 preceding and current row) as left_max,  -- 左边相邻10个点的最大值(包括当前点), 辐射半径的左边 
    min(val) over(order by id rows between current row and 10 following) as right_min,  -- 右边相邻10个点的最小值(包括当前点), 辐射半径的右边 
    max(val) over(order by id rows between current row and 10 following) as right_max,  -- 右边相邻10个点的最大值(包括当前点), 辐射半径的右边 
    min(val) over(order by id rows between 10 preceding and 10 following) as range_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径两边全部 
    max(val) over(order by id rows between 10 preceding and 10 following) as range_max,  -- 右边相邻10个点的最大值(包括当前点), 辐射半径两边全部 
    -- avg(val) over(order by id rows between 10 preceding and current row) as range_left_avg, -- 左局部平均值
    -- stddev(val) over(order by id rows between 10 preceding and current row) as range_left_stddev, -- 左局部采样标准方差
    -- avg(val) over(order by id rows between current row and 10 following) as range_right_avg, -- 右局部平均值
    -- stddev(val) over(order by id rows between current row and 10 following) as range_right_stddev, -- 右局部采样标准方差
    min(val) over() as global_min, -- 全局的最小值(包括当前点)
    max(val) over() as global_max  -- 全局的最大值(包括当前点)
    from wind
) t
where 
(  -- 开始 找出单边,左陡右平缓,谷底
(  
left_max - val > ((global_max-global_min)*0.5)  -- 左边相邻10个点的最大值 - 当前值 > 全局落差的50% 
and 
val = left_min  -- 拐点
)

and

(
(right_max-right_min)/(range_max-range_min+0.00000001) < 0.05  -- 要求越平缓,设置0.05的值越低。
)
)  -- 结束 找出单边,左陡右平缓,谷底

or

(  -- 开始 找出单边,右陡左平缓,谷底
(  
right_max - val > ((global_max-global_min)*0.5)  -- 右边相邻10个点的最大值 - 当前值 > 全局落差的50% 
and 
val = right_min  -- 拐点
)

and

(
(left_max-left_min)/(range_max-range_min+0.00000001) < 0.05  -- 要求越平缓,设置0.05的值越低。
)
)  -- 结束 找出单边,右陡左平缓,谷底

or

(  -- 开始 找出单边,左陡右平缓,高潮
(  
val - left_min > ((global_max-global_min)*0.5)  -- 当前值 - 左边相邻10个点的最小值 > 全局落差的50% 
and 
val = left_max  -- 拐点
)

and

(
(right_max-right_min)/(range_max-range_min+0.00000001) < 0.05  -- 要求越平缓,设置0.05的值越低。
)
)  -- 结束 找出单边,左陡右平缓,高潮

or

(  -- 开始 找出单边,右陡左平缓,高潮
(  
val - right_min > ((global_max-global_min)*0.5)  -- 左边相邻10个点的最大值 - 当前值 > 全局落差的50% 
and 
val = right_max  -- 拐点
)

and

(
(left_max-left_min)/(range_max-range_min+0.00000001) < 0.05  -- 要求越平缓,设置0.05的值越低。
)
)  -- 结束 找出单边,右陡左平缓,高潮
;

  id   | val | left_min | left_max | right_min | right_max | range_min | range_max | global_min | global_max 
-------+-----+----------+----------+-----------+-----------+-----------+-----------+------------+------------
 10001 |   1 |        1 |   726.31 |         1 |         8 |         1 |    726.31 |          1 |     996.57
(1 row)

功能

可以找到单边骤变的点。

缺陷

无法输出跳跃点

query 4

输出跳跃点,与左右相邻的任一点差值大于局部落差%?的点

select * from 
(
  select id, 
    val, 
    min(val) over(order by id rows between 10 preceding and current row) as left_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径的左边 , 值越大,可以找到更平滑变化的高潮和低谷
    max(val) over(order by id rows between 10 preceding and current row) as left_max,  -- 左边相邻10个点的最大值(包括当前点), 辐射半径的左边 , 值越大,可以找到更平滑变化的高潮和低谷 
    min(val) over(order by id rows between current row and 10 following) as right_min,  -- 右边相邻10个点的最小值(包括当前点), 辐射半径的右边 , 值越大,可以找到更平滑变化的高潮和低谷 
    max(val) over(order by id rows between current row and 10 following) as right_max,  -- 右边相邻10个点的最大值(包括当前点), 辐射半径的右边 , 值越大,可以找到更平滑变化的高潮和低谷 
    min(val) over(order by id rows between 10 preceding and 10 following) as range_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径两边全部 , 值越大,可以找到更平滑变化的高潮和低谷 
    max(val) over(order by id rows between 10 preceding and 10 following) as range_max,  -- 右边相邻10个点的最大值(包括当前点), 辐射半径两边全部 , 值越大,可以找到更平滑变化的高潮和低谷 
    lag(val) over(order by id) as lag_val, -- 上一个值(第一条为空)
    lead(val) over(order by id) as lead_val, -- 下一个值(最后一条为空)
    min(val) over() as global_min, -- 全局的最小值(包括当前点)
    max(val) over() as global_max  -- 全局的最大值(包括当前点)
    from wind
) t
where 
lag_val is not null 
and 
lead_val is not null 
and 
(
abs(lag_val-val)/(range_max-range_min+0.00000000001) > 0.35   
or
abs(lead_val-val)/(range_max-range_min+0.00000000001) > 0.35   
)

query 5

把以上几个QUERY结合起来

相邻点的个数,建议根据总的个数除以一个百分比,例如1万条记录,去百分之一作为评判标准,那么10000*0.01=100,取值100.

select * from 
(
  select id, 
    val, 
    min(val) over(order by id rows between 10 preceding and current row) as left_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径的左边 , 值越大,可以找到更平滑变化的高潮和低谷
    max(val) over(order by id rows between 10 preceding and current row) as left_max,  -- 左边相邻10个点的最大值(包括当前点), 辐射半径的左边 , 值越大,可以找到更平滑变化的高潮和低谷 
    min(val) over(order by id rows between current row and 10 following) as right_min,  -- 右边相邻10个点的最小值(包括当前点), 辐射半径的右边 , 值越大,可以找到更平滑变化的高潮和低谷 
    max(val) over(order by id rows between current row and 10 following) as right_max,  -- 右边相邻10个点的最大值(包括当前点), 辐射半径的右边 , 值越大,可以找到更平滑变化的高潮和低谷 
    min(val) over(order by id rows between 10 preceding and 10 following) as range_min,  -- 左边相邻10个点的最小值(包括当前点), 辐射半径两边全部 , 值越大,可以找到更平滑变化的高潮和低谷 
    max(val) over(order by id rows between 10 preceding and 10 following) as range_max,  -- 右边相邻10个点的最大值(包括当前点), 辐射半径两边全部 , 值越大,可以找到更平滑变化的高潮和低谷 
    lag(val) over(order by id) as lag_val, -- 上一个值(第一条为空)
    lead(val) over(order by id) as lead_val, -- 下一个值(最后一条为空)
    -- avg(val) over(order by id rows between 10 preceding and current row) as range_left_avg, -- 左局部平均值 , 值越大,可以找到更平滑变化的高潮和低谷
    -- stddev(val) over(order by id rows between 10 preceding and current row) as range_left_stddev, -- 左局部采样标准方差 , 值越大,可以找到更平滑变化的高潮和低谷
    -- avg(val) over(order by id rows between current row and 10 following) as range_right_avg, -- 右局部平均值 , 值越大,可以找到更平滑变化的高潮和低谷
    -- stddev(val) over(order by id rows between current row and 10 following) as range_right_stddev, -- 右局部采样标准方差 , 值越大,可以找到更平滑变化的高潮和低谷
    min(val) over() as global_min, -- 全局的最小值(包括当前点)
    max(val) over() as global_max  -- 全局的最大值(包括当前点)
    from wind
) t
where 
( -- query 1
(  -- 找出全局谷底开始
(  
left_max - val > ((global_max-global_min)*0.5)  -- 左边相邻10个点的最大值 - 当前值 > 全局落差的50% ,值越大,展示落差越大的点
and 
val = left_min  -- 拐点
)

and

(
right_max - val > ((global_max-global_min)*0.5)  -- 右边相邻10个点的最大值 - 当前值 > 全局落差的50% ,值越大,展示落差越大的点
and 
val = right_min  -- 拐点
)
)  -- 找出全局谷底结束

or

(  -- 找出全局高潮开始
(
val - left_min > ((global_max-global_min)*0.5)  -- 当前值 - 左边相邻10个点的最小值 > 全局落差的50% ,值越大,展示落差越大的点
and 
val = left_max  -- 拐点
)

and

(
val - right_min > ((global_max-global_min)*0.5)  -- 当前值 - 右边相邻10个点的最小值  > 全局落差的50% ,值越大,展示落差越大的点
and 
val = right_max  -- 拐点
)
)  -- 找出全局高潮开始
) -- query 1

or 

( -- query 2
(  -- 找越过低水位后的局部低谷开始
 (
 left_max - val > ((range_max-range_min)*0.5)   -- 左边相邻10个点的最大值 - 当前值 > 局部落差的50%
 and 
 val = left_min  -- 拐点 
 )

 and

 (
 right_max - val > ((range_max-range_min)*0.5)   -- 右边相邻10个点的最大值 - 当前值 > 局部落差的50%
 and 
 val = right_min  -- 拐点
 )

 and 

 (val - global_min)::numeric/(global_max-global_min+0.0000001) <= 0.1      -- 当前点必须是低水位线外的点,低水位线为全局低谷上浮10%的值
)  -- 找越过低水位后的局部低谷结束

or

(  -- 找越过高水位后的局部高潮开始
 (
 val - left_min > ((range_max-range_min)*0.5)   -- 当前值 - 左边相邻10个点的最小值 > 局部落差的50%
 and 
 val = left_max  -- 拐点
 )

 and

 (
 val - right_min > ((range_max-range_min)*0.5)   -- 当前值 - 右边相邻10个点的最小值 > 局部落差的50%
 and 
 val = right_max  -- 拐点
 )

 and 

 (global_max - val)::numeric/(global_max-global_min+0.0000001) <= 0.1      -- 当前点必须是高水位线外的点,高水位线为全局高潮下调10%的值
)  -- 找越过高水位后的局部高潮结束
) -- query 2

or

( -- query 3
(  -- 开始 找出单边,左陡右平缓,谷底
(  
left_max - val > ((global_max-global_min)*0.5)  -- 左边相邻10个点的最大值 - 当前值 > 全局落差的50% 
and 
val = left_min  -- 拐点
)

and

(
(right_max-right_min)/(range_max-range_min+0.00000001) < 0.05  -- 要求越平缓,设置0.05的值越低。
)
)  -- 结束 找出单边,左陡右平缓,谷底

or

(  -- 开始 找出单边,右陡左平缓,谷底
(  
right_max - val > ((global_max-global_min)*0.5)  -- 右边相邻10个点的最大值 - 当前值 > 全局落差的50% 
and 
val = right_min  -- 拐点
)

and

(
(left_max-left_min)/(range_max-range_min+0.00000001) < 0.05  -- 要求越平缓,设置0.05的值越低。
)
)  -- 结束 找出单边,右陡左平缓,谷底

or

(  -- 开始 找出单边,左陡右平缓,高潮
(  
val - left_min > ((global_max-global_min)*0.5)  -- 当前值 - 左边相邻10个点的最小值 > 全局落差的50% 
and 
val = left_max  -- 拐点
)

and

(
(right_max-right_min)/(range_max-range_min+0.00000001) < 0.05  -- 要求越平缓,设置0.05的值越低。
)
)  -- 结束 找出单边,左陡右平缓,高潮

or

(  -- 开始 找出单边,右陡左平缓,高潮
(  
val - right_min > ((global_max-global_min)*0.5)  -- 左边相邻10个点的最大值 - 当前值 > 全局落差的50% 
and 
val = right_max  -- 拐点
)

and

(
(left_max-left_min)/(range_max-range_min+0.00000001) < 0.05  -- 要求越平缓,设置0.05的值越低。
)
)  -- 结束 找出单边,右陡左平缓,高潮
) -- query 3

or

( -- query 4
lag_val is not null 
and 
lead_val is not null 
and 
(
abs(lag_val-val)/(range_max-range_min+0.00000000001) > 0.35   
or
abs(lead_val-val)/(range_max-range_min+0.00000000001) > 0.35   
)
) -- query 4
;

结果

  id   |  val   | left_min | left_max | right_min | right_max | range_min | range_max | global_min | global_max 
-------+--------+----------+----------+-----------+-----------+-----------+-----------+------------+------------
     6 |  62.66 |    62.66 |   701.20 |     62.66 |    897.21 |     62.66 |    897.21 |          1 |     996.57
    18 |  62.47 |    62.47 |   897.21 |     62.47 |    963.36 |     62.47 |    963.36 |          1 |     996.57
    21 | 963.36 |    62.47 |   963.36 |     90.01 |    963.36 |     62.47 |    963.36 |          1 |     996.57
    44 |  57.54 |    57.54 |   884.70 |     57.54 |    949.44 |     57.54 |    949.44 |          1 |     996.57
    45 | 949.44 |    57.54 |   949.44 |    291.63 |    949.44 |     57.54 |    949.44 |          1 |     996.57
    62 |  54.09 |    54.09 |   841.67 |     54.09 |    835.75 |     54.09 |    841.67 |          1 |     996.57
    76 | 892.74 |   186.73 |   892.74 |     43.92 |    892.74 |     43.92 |    892.74 |          1 |     996.57
    83 |  43.92 |    43.92 |   892.74 |     43.92 |    828.41 |     43.92 |    892.74 |          1 |     996.57
    98 | 961.65 |   108.82 |   961.65 |    222.69 |    961.65 |    108.82 |    961.65 |          1 |     996.57
   112 |  72.93 |    72.93 |   778.41 |     72.93 |    689.00 |     72.93 |    778.41 |          1 |     996.57
   126 |  44.19 |    44.19 |   829.42 |     44.19 |    900.01 |     44.19 |    900.01 |          1 |     996.57
   141 | 950.97 |    44.80 |   950.97 |     80.02 |    950.97 |     44.80 |    950.97 |          1 |     996.57
...........
  9839 | 972.29 |   109.72 |   972.29 |     92.10 |    972.29 |     92.10 |    972.29 |          1 |     996.57
  9844 |  92.10 |    92.10 |   972.29 |     92.10 |    940.05 |     92.10 |    972.29 |          1 |     996.57
  9866 | 958.95 |   128.55 |   958.95 |     91.19 |    958.95 |     91.19 |    958.95 |          1 |     996.57
  9882 | 867.15 |    91.19 |   867.15 |      5.84 |    867.15 |      5.84 |    867.15 |          1 |     996.57
  9884 |   5.84 |     5.84 |   867.15 |      5.84 |    900.04 |      5.84 |    900.04 |          1 |     996.57
  9894 | 900.04 |     5.84 |   900.04 |    154.28 |    900.04 |      5.84 |    900.04 |          1 |     996.57
  9902 | 154.28 |   154.28 |   900.04 |    154.28 |    871.17 |    154.28 |    900.04 |          1 |     996.57
  9913 |  34.37 |    34.37 |   871.17 |     34.37 |    786.24 |     34.37 |    871.17 |          1 |     996.57
  9924 | 879.23 |    80.56 |   879.23 |     73.87 |    879.23 |     73.87 |    879.23 |          1 |     996.57
  9930 |  73.87 |    73.87 |   879.23 |     73.87 |    858.93 |     73.87 |    879.23 |          1 |     996.57
  9950 | 941.34 |   117.90 |   941.34 |     61.66 |    941.34 |     61.66 |    941.34 |          1 |     996.57
  9953 |  61.66 |    61.66 |   941.34 |     61.66 |    872.60 |     61.66 |    941.34 |          1 |     996.57
  9974 | 916.83 |   278.73 |   916.83 |    147.24 |    916.83 |    147.24 |    916.83 |          1 |     996.57
  9986 | 957.28 |   147.24 |   957.28 |     87.52 |    957.28 |     87.52 |    957.28 |          1 |     996.57
 10001 |      1 |        1 |   726.31 |         1 |         8 |         1 |    726.31 |          1 |     996.57
(948 rows)

截取1-1000进行绘图,看看找出来的是不是关键点

pic

使用以下QUERY可以打印稀疏点,进行比对

select t1.id, case when t2.val is null then t1.val else t2.val end as val from (select generate_series(1,1000) id, 0 val ) t1 left join 
(...) t2
on (t1.id=t2.id) 
order by t1.id
;

另一个例子

造一批波动数据

do language plpgsql $$
declare
  rand int;
  step int;
  range int;
begin
  for i in 1..10 loop 
    rand := 100*random();
    step := 10*random();
    range := 10+1000*random();
    for x in 1..range loop
      insert into wind(val) values( x*step+rand);
    end loop;
    for x in 1..range loop
      insert into wind(val) values( (range+1-x)*step*0.5+rand);
    end loop;
  end loop;
end;
$$;

数据如图

pic

query 5 输出

  id   |  val   | left_min | left_max | right_min | right_max | range_min | range_max | lag_val | lead_val | global_min | global_max 
-------+--------+----------+----------+-----------+-----------+-----------+-----------+---------+----------+------------+------------
 92632 |    273 |      243 |      273 |     133.5 |       273 |     133.5 |       273 |     270 |    147.0 |        9.0 |       4197
 92633 |  147.0 |    147.0 |      273 |     132.0 |     147.0 |     132.0 |       273 |     273 |    145.5 |        9.0 |       4197
 92716 |   22.5 |     22.5 |     37.5 |      22.5 |       165 |      22.5 |       165 |    24.0 |       93 |        9.0 |       4197
 92717 |     93 |     22.5 |       93 |        93 |       173 |      22.5 |       173 |    22.5 |      101 |        9.0 |       4197
 93230 |   4197 |     4117 |     4197 |    2105.0 |      4197 |    2105.0 |      4197 |    4189 |   2141.0 |        9.0 |       4197
 93231 | 2141.0 |   2141.0 |     4197 |    2101.0 |    2141.0 |    2101.0 |      4197 |    4197 |   2137.0 |        9.0 |       4197
 93744 |   89.0 |     89.0 |    129.0 |        36 |      89.0 |        36 |     129.0 |    93.0 |       36 |        9.0 |       4197
 93745 |     36 |       36 |    125.0 |        36 |        76 |        36 |     125.0 |    89.0 |       40 |        9.0 |       4197
 94292 |   2224 |     2184 |     2224 |    1110.0 |      2224 |    1110.0 |      2224 |    2220 |   1128.0 |        9.0 |       4197
 94293 | 1128.0 |   1128.0 |     2224 |    1108.0 |    1128.0 |    1108.0 |      2224 |    2224 |   1126.0 |        9.0 |       4197
 94840 |   34.0 |     34.0 |     54.0 |        10 |      34.0 |        10 |      54.0 |    36.0 |       10 |        9.0 |       4197
 94841 |     10 |       10 |     52.0 |        10 |        30 |        10 |      52.0 |    34.0 |       12 |        9.0 |       4197
 94858 |     44 |       24 |       44 |      17.0 |        44 |      17.0 |        44 |      42 |     26.0 |        9.0 |       4197
 94859 |   26.0 |     26.0 |       44 |      16.0 |      26.0 |      16.0 |        44 |      44 |     25.0 |        9.0 |       4197
 94876 |    9.0 |      9.0 |     19.0 |       9.0 |       128 |       9.0 |       128 |    10.0 |      101 |        9.0 |       4197
 94877 |    101 |      9.0 |      101 |       101 |       131 |       9.0 |       131 |     9.0 |      104 |        9.0 |       4197
 95007 |    491 |      461 |      491 |     281.0 |       491 |     281.0 |       491 |     488 |    294.5 |        9.0 |       4197
 95008 |  294.5 |    294.5 |      491 |     279.5 |     294.5 |     279.5 |       491 |     491 |    293.0 |        9.0 |       4197
 95138 |   99.5 |     99.5 |    114.5 |        67 |       112 |        67 |     114.5 |   101.0 |       67 |        9.0 |       4197
 95139 |     67 |       67 |    113.0 |        67 |       117 |        67 |       117 |    99.5 |       72 |        9.0 |       4197
 95491 |   1827 |     1777 |     1827 |     922.0 |      1827 |     922.0 |      1827 |    1822 |    944.5 |        9.0 |       4197
 95492 |  944.5 |    944.5 |     1827 |     919.5 |     944.5 |     919.5 |      1827 |    1827 |    942.0 |        9.0 |       4197
 95965 |   1013 |      933 |     1013 |     493.0 |      1013 |     493.0 |      1013 |    1005 |    529.0 |        9.0 |       4197
 95966 |  529.0 |    529.0 |     1013 |     489.0 |     529.0 |     489.0 |      1013 |    1013 |    525.0 |        9.0 |       4197
 96086 |   49.0 |     49.0 |     89.0 |      49.0 |       114 |      49.0 |       114 |    53.0 |       60 |        9.0 |       4197
 96302 |   1350 |     1290 |     1350 |     675.0 |      1350 |     675.0 |      1350 |    1344 |    702.0 |        9.0 |       4197
 96303 |  702.0 |    702.0 |     1350 |     672.0 |     702.0 |     672.0 |      1350 |    1350 |    699.0 |        9.0 |       4197
 96518 |   57.0 |     57.0 |     87.0 |      57.0 |        75 |      57.0 |      87.0 |    60.0 |       66 |        9.0 |       4197
 97106 |    653 |      643 |      653 |     354.5 |       653 |     354.5 |       653 |     652 |    359.0 |        9.0 |       4197
 97107 |  359.0 |    359.0 |      653 |     354.0 |     359.0 |     354.0 |       653 |     653 |    358.5 |        9.0 |       4197
 98061 |   2287 |     2227 |     2287 |    1159.0 |      2287 |    1159.0 |      2287 |    2281 |   1186.0 |        9.0 |       4197
 98062 | 1186.0 |   1186.0 |     2287 |    1156.0 |    1186.0 |    1156.0 |      2287 |    2287 |   1183.0 |        9.0 |       4197
(32 rows)

小结

使用本文的方法,一定要注意设置好对应的变量,包括范围记录数,落差比例,抖动比例。。

才能得到想要的关键点 。

参考

1. https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

2. https://www.postgresql.org/docs/9.6/static/functions-aggregate.html

3. 旋转门数据压缩算法在PostgreSQL中的实现

Flag Counter

digoal’s 大量PostgreSQL文章入口