代码之家 › 专栏 › 技术社区 › Ramy

在SQL中查找局部最大值和局部最小值

sql-server-2005

Ramy · 技术社区 · 14 年前

为了找到股票价格与时间关系图的最大提取量,首先必须找到给定一组价格和天数的所有局部最大值(峰值)和局部最小值(谷)。您将如何在SQL Server 2005中做到这一点?

编辑: 有一种暴力的方法可以使用光标: 将第一天的高点与第二天的高点进行比较。如果第一天的最高值高于第二天的最高值,则第一天的最高值是局部的最高值。

实际上,我需要找到价格图趋势改变方向的每一个点。

edit2:我应该注意,要从中工作的数据库表有以下列:

股票ID利息
日期
嗨,int——这是一分硬币
低利息——也以便士为单位
因此,对于给定的日期范围,您将看到该日期范围内每天相同的stockID。

2 回复 | 直到 14 年前

akaphenom 14 年前

令人信服的是,还没有经过彻底的测试——但是如何使用CTE和rowNumber()分两步进行测试呢?

1)确定每行的所有下一个选项 2)下一行的后续高值小于当前行的任何行-则当前行必须是本地最大值。

或者类似的:

begin 
    DECLARE  @highTable as table (high bigint, day date)

    declare @securityid int,
    @start datetime,
    @end datetime

    set @start = '1-1-2010'
    set @end = '2-1-2010'   
    select @securityid = id from security where riccode = 'MSFT.OQ' ;

    with highsandlows_cte as (
        SELECT 
            ROW_NUMBER() over (order by day) i
            , high
            , day
            , (select top 1 day from quotes nextHi where nextHi.high > today.high and nextHi.day >= today.day and nextHi.securityId = today.securityId order by day asc) nextHighestDay

        FROM 
            quotes today
        WHERE 
            today.securityid = @securityid )

    select 
        * 
        , (Coalesce((select 1 from highsandlows_cte t2 where t1.i + 1  = t2.i and t1.nextHighestDay > t2.nextHighestDay),0))  as isHigh
    from 
        highsandlows_cte t1

    order by 
        day
end

好的,以上是错误的-这似乎更符合轨道:

begin 
      DECLARE  @highTable as table (high bigint, day date)

      declare @securityid int,
    @start datetime,
    @end datetime

      set @start = '1-1-2010'
      set @end = '2-1-2010'   
      select @securityid = id from security where riccode = 'MSFT.OQ' ;



      with highsandlows_cte as (
            SELECT 
                  ROW_NUMBER() over (order by day) i
                  , high
                  , day
                  , low
            FROM 
                  quote today
            WHERE 
                  today.securityid = @securityid and today.day > convert(varchar(10), @start, 111) and convert(varchar(10), @end, 111) >today.day)



 select
             cur.day
            , cur.high
            , cur.low
        , case when ((cur.high > prv.high or prv.high IS null)and(cur.high > nxt.high or nxt.high is null)) then 1 else 0 end as isLocalMax
        , case when ((cur.low < prv.low or prv.low IS null)and(cur.low < nxt.low or nxt.low is null)) then 1 else 0 end as isLocalMin
  from 
        highsandlows_cte cur left outer join highsandlows_cte nxt
                on  cur.i + 1  = nxt.i
            left outer join highsandlows_cte prv
                on  cur.i - 1  = prv.i
  order by 
        cur.day
end

但获取重复项(高/低)的问题…

JNK 14 年前

好的,我想一步一步来:

1-第二天找出所有的“峰值”,即最大值和较低的最大值:

DECLARE @HiTable (hi int, day date)

INSERT INTO @HiTable
SELECT hi, day
FROM table t1
WHERE EXISTS (
 SELECT t2.hi
 FROM Table t2
 WHERE t1.hi > t2.hi AND t1.day < t2.day and StockID = X)

2-找到你所有的“山谷”,这是第二天最小值较高的最小值:

DECLARE @LowTable (low int, day date)

INSERT INTO @LowTable
SELECT low, day
FROM table t1
WHERE EXISTS (
 SELECT t2.low
 FROM Table t2
 WHERE t1.low < t2.low AND t1.day < t2.day and StockID = X)

3-将这些数据与标识值组合成按日期排序的表,以使我们保持有序。

DECLARE @TableVar (low int, hi int, day date, autoid int IDENTITY)
INSERT INTO @TableVar
(SELECT low, hi, day
FROM (
 SELECT Low, NULL as 'hi', date FROM @LowTable
 UNION ALL
 SELECT NULL as 'Low', hi, date FROM @HiTable
 )
ORDER BY DATE)

4-删除异常值

DELETE FROM @TableVar WHERE AutoID > (SELECT MAX(AutoID) FROM @Table WHERE low IS NULL)
DELETE FROM @TableVar WHERE AutoID < (SELECT MIN(AutoID) FROM @Table WHERE hi IS NULL)