sql - 按递增顺序选择 SQL

Question

table :

  id | year |     score 
-----+------+-----------
  12 | 2011 |     0.929
  12 | 2014 |     0.933
  12 | 2010 |     0.937
  12 | 2013 |     0.938
  12 | 2009 |      0.97
  13 | 2010 |     0.851
  13 | 2014 |     0.881
  13 | 2011 |     0.885
  13 | 2013 |     0.895
  13 | 2009 |     0.955
  16 | 2009 |     0.867
  16 | 2011 |     0.881
  16 | 2012 |     0.886
  16 | 2013 |     0.897
  16 | 2014 |     0.953

期望输出:

  id | year |     score 
-----+------+-----------
  16 | 2009 |     0.867
  16 | 2011 |     0.881
  16 | 2012 |     0.886
  16 | 2013 |     0.897
  16 | 2014 |     0.953

我在尝试输出与年份相关的分数时遇到困难。
任何帮助将不胜感激。

Answer 1

所以你要选择id = 16 因为它是唯一一个值稳定增加的值。

多版本SQL支持lag() ，这可以帮助解决这个问题。对于给定的 id，您可以通过执行以下操作来确定所有值是增加还是减少:

select id,
       (case when min(score - prev_score) < 0 then 'nonincreasing' else 'increasoing' end) as grp
from (select t.*, lag(score) over (partition by id order by year) as prev_score
      from table t
     ) t
group by id;

然后，您可以使用连接选择所有“增加”的 ID:

select t.*
from table t join
     (select id
      from (select t.*, lag(score) over (partition by id order by year) as prev_score
            from table t
           ) t
      group by id
      having min(score - prev_score) > 0
     ) inc
     on t.id = inc.id;