Oracle -查找下一个最大数字

gg58donl  于 2023-05-28  发布在  Oracle
关注(0)|答案(2)|浏览(270)

假设我有一个日期和价格列表:

20170322    109.89
20170321    107.02
20170320    109.25
20170317    108.44
20170316    108.53
20170315    107.94
20170314    106.83
20170313    110.02
20170310    107.31
20170309    107.54
20170308    107.67
20170307    108.98

我需要的是,从最近的日期:20170322(109.89),第一个日期/价格值高于原始值,即20170313(110.02)。请注意,这些是按日期的DESC顺序排列的
一整天都在忙这个。

ioekq8ef

ioekq8ef1#

假设这两个列分别称为DT和PRICE,并且假设只有一个“东西”的价格是您监视的(否则您需要一个GROUP BY子句):

select min(dt) as dt, min(price) keep (dense_rank first order by dt) as price
from   your_table
where  price > ( select min(price) keep (dense_rank first order by dt)
                 from   your_table
               )

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm

chhqkbe1

chhqkbe12#

使用MATCH_RECOGNIZE子句的解决方案(需要Oracle 12及更高版本)。
我在WITH子句中创建了测试数据。这不是解决方案的一部分; SQL查询在WITH子句之后的SELECT TICKER, ....处开始
PATTERN中的不情愿匹配中的问号会触发JDBC驱动程序,因此无法从SQL Developer运行此查询;它需要在SQL*Plus或类似的前端中运行。(解决方法是将b*?更改为b*,并在DEFINE子句中添加:b as b.price <= a.price .)
为了更好地说明MATCH_RECOGNIZE的灵活性,我假设可能有几个“ticker”,每个都有其起始日期(最早的价格日期),查询将查找每个ticker第一次出现的价格高于原始价格的情况。

with
     test_data ( ticker, dt, price ) as (
       select 'XYZ', to_date('20170322', 'yyyymmdd'), 109.89 from dual union all
       select 'XYZ', to_date('20170321', 'yyyymmdd'), 107.02 from dual union all
       select 'XYZ', to_date('20170320', 'yyyymmdd'), 109.25 from dual union all
       select 'XYZ', to_date('20170317', 'yyyymmdd'), 108.44 from dual union all
       select 'XYZ', to_date('20170316', 'yyyymmdd'), 108.53 from dual union all
       select 'XYZ', to_date('20170315', 'yyyymmdd'), 107.94 from dual union all
       select 'XYZ', to_date('20170314', 'yyyymmdd'), 106.83 from dual union all
       select 'XYZ', to_date('20170313', 'yyyymmdd'), 110.02 from dual union all
       select 'XYZ', to_date('20170310', 'yyyymmdd'), 107.31 from dual union all
       select 'XYZ', to_date('20170309', 'yyyymmdd'), 107.54 from dual union all
       select 'XYZ', to_date('20170308', 'yyyymmdd'), 107.67 from dual union all
       select 'XYZ', to_date('20170307', 'yyyymmdd'), 108.98 from dual
     )
select ticker, dt, price
from   test_data
match_recognize (
  partition by ticker
  order     by dt
  measures  c.dt as dt, c.price as price
  one row per match
  pattern   ( ^ a b*? c )
  define    c as c.price > a.price
)
;

TICKER  DT            PRICE
------  ----------  -------
XYZ     2017-03-13   110.02

1 row selected.

相关问题