PG中文社区 / 用PostgreSQL描绘人生的高潮、尿点、低谷 - 窗口
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
用PostgreSQL描绘人生的高潮、尿点、低谷 - 窗口/帧 or 斜率/导数/曲率/微积分

原作者：digoal/德哥　　创作时间：2016-12-03 00:04:54+08　　
doudou586 发布于2016-12-09 00:04:54　　         评论: 2　　 浏览: 5391　　 顶: 748　 踩: 751　

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

作者：digoal 于 2016-12-03

链接：https://github.com/digoal/blog/blob/master/201612/20161203_01.md

标签：PostgreSQL , 曲线拐点 , 窗口查询 , 帧 , 窗口 , 导数 , 曲率 , 微积分 , 斜率

背景

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

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

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

如果你是刚毕业不久的学生，可能会往学校里学到的知识去思考，怎么找曲线的拐点？导数、微积分、斜率、曲率、面积？

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

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

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

高潮、尿点、低谷

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

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

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

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

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

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

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

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

曲线分析

曲线的分辨率

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

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

曲线的长度

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

所以说我们要分析曲线的拐点，和曲线的长度也是有莫大的关系的，你是要看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
......

Query1 

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

Query3

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进行绘图，看看找出来的是不是关键点

使用以下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;
$$;

数据如图

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中的实现

pg_bot_banner.jpg

评论:2　　 浏览: 5391                   顶: 748　 踩: 751　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-22 09:34:09+08
工作证明:https://www.nanss.com/gongzuo/2667.html 平凡的世界读后感:https://www.nanss.com/xuexi/2787.html 解除劳动合同通知书:https://www.nanss.com/gongzuo/2928.html 充满阳光的句子:https://www.nanss.com/wenan/2730.html 公司年终总结:https://www.nanss.com/gongzuo/2570.html 法人授权委托书:https://www.nanss.com/gongzuo/2750.html 入党转正申请书:https://www.nanss.com/shenghuo/2684.html 考察报告:https://www.nanss.com/gongzuo/2714.html 传统文化作文:https://www.nanss.com/xuexi/2859.html 执行异议申请书:https://www.nanss.com/shenghuo/2751.html 班主任工作计划:https://www.nanss.com/gongzuo/2687.html 教师工作总结:https://www.nanss.com/gongzuo/2554.html 年度思想工作总结:https://www.nanss.com/gongzuo/2564.html 读后感600字:https://www.nanss.com/xuexi/2944.html 公务员工作总结:https://www.nanss.com/gongzuo/2555.html 童年读后感:https://www.nanss.com/xuexi/2955.html 销售月工作总结:https://www.nanss.com/gongzuo/2544.html 请假申请书:https://www.nanss.com/gongzuo/2734.html 毕业生个人总结:https://www.nanss.com/xuexi/2571.html 三傻大闹宝莱坞观后感:https://www.nanss.com/shenghuo/2818.html 房屋租赁合同模板:https://www.nanss.com/shenghuo/2674.html 房地产销售工作总结:https://www.nanss.com/gongzuo/2587.html 鲁滨逊漂流记读后感:https://www.nanss.com/xuexi/2764.html 大学毕业自我鉴定:https://www.nanss.com/xuexi/2753.html 实习心得:https://www.nanss.com/gongzuo/2791.html 培训学习总结:https://www.nanss.com/gongzuo/2565.html 毕业实习总结:https://www.nanss.com/xuexi/2638.html 月度工作总结:https://www.nanss.com/gongzuo/3000.html 春节作文:https://www.nanss.com/xuexi/2896.html 中国的世界文化遗产作文:https://www.nanss.com/xuexi/2780.html

2# __ GUEST 回答于 2016-12-09 09:25:57+08
德哥文风有变啊．．．



发表评论：
加入我们
QQ群1：5276420
QQ群2：3336901
QQ群3：254622631
文档群：150657323
文档翻译平台：按此访问
社区邮件列表：按此订阅
商业支持
扫码关注
加入我们
QQ群1：5276420
QQ群2：3336901
QQ群3：254622631
文档群：150657323
文档翻译平台：按此访问
社区邮件列表：按此订阅
商业支持
扫码关注
© PostgreSQL中文社区 ... (自2010年起）