找出sql中不断增加的子序列 [英] Finding out the increasing subsequence in sql

问题描述

假设我有x和y值的表，其中y被排序为DESC，例如

Suppose I have table of x and y values where y is ordered DESC, for example

  x  |  y  
-----+-----
  94 | 985
  73 | 940
 469 | 865
 115 | 864
 366 | 862
 525 | 842
 448 | 837
 318 | 832
 507 | 826
 244 | 758
 217 | 741
 207 | 732
  54 | 688
 426 | 605
 108 | 604
 610 | 587
 142 | 581
 765 | 579
 102 | 572
 512 | 552
 836 | 540

现在我想从第一个值
开始查找x的递增子序列我希望获得以下输出，其中x遵循递增顺序。

now I want to find increasing subsequence of x starting from the first value i.e.the I wish to obtain the following output where x is following an increasing order.

  x  |  y  
-----+-----
  94 | 985
 469 | 865
 525 | 842
 610 | 587
 765 | 579
 836 | 540

是否可以通过sql查询还是我需要使用plpgsql函数并在更新最大值时循环？

Is this possible through sql query or do I need to use plpgsql function and loop while updating the maximum?

推荐答案

您可以使用 max 窗口函数跟踪运行中的max 。

You can keep track of the running max with max window function. Essentially your output asks for that.

select distinct max(val) over(order by id) --replace id with your ordering column
from t

编辑：在OP对问题进行编辑之后

After OP's edit of the question

select x,y 
from (select distinct y,x,max(x) over(order by y desc) running_x_max
      from t
     ) t
where running_x_max=x
order by y desc

这篇关于找出sql中不断增加的子序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！