SQL Window Function -自上次最大值以来的行数

Question

我正在尝试创建一个SQL查询，它将拉取自窗口函数中最近5行的最大值以来的行数。在下面的示例中，第8行将返回2。最大值为12，即第8行中的2行。

对于第6行，它将返回5，因为7的最大值距离5行。

|ID  | Date       | Amount  
| 1  | 1/1/2019   |  7  
| 2  | 1/2/2019   |  3  
| 3  | 1/3/2019   |  4  
| 4  | 1/4/2019   |  1  
| 5  | 1/5/2019   |  1  
| 6  | 1/6/2019   |  12  
| 7  | 1/7/2019   |  2  
| 8  | 1/8/2019   |  4  

我尝试了以下几种方法：

SELECT ID, date, MAX(amount) 
OVER (ORDER BY date ASC ROWS 5 PRECEDING) mymax
FROM tbl 

这让我得到了最大值，但我无法有效地确定它有多少行。我可以在SELECT中使用多个变量来接近，但这似乎不是有效或可伸缩的。

Answer 1

您可以计算累积最大值，然后对其使用row_number()。

所以：

select t.*,
       row_number() over (partition by running_max order by date) as rows_since_last_max
from (select t.*, 
             max(amount) over (order by date rows between 5 preceding and current row) as running_max
      from tbl t
     ) t;

我认为这适用于您的样本数据。如果你有重复的东西，它可能不会工作。

在这种情况下，您可以使用日期算法：

select t.*,
       datediff(day,
                max(date) over (partition by running_max order by date),
                date
               ) as days_since_most_recent_max5
from (select t.*, 
             max(amount) over (order by date rows between 5 preceding and current row) as running_max
      from tbl t
     ) t;

编辑：

下面是一个使用行号的示例：

select t.*,
       (seqnum - max(case when amount = running_amount then seqnum end) over (partition by running_max order by date)) as rows_since_most_recent_max5
from (select t.*, 
             max(amount) over (order by date rows between 5 preceding and current row) as running_max,
             row_number() over (order by date) as seqnum
      from tbl t
     ) t;

Answer 2

它将是：

select *,ID- 
(
SELECT ID
FROM
  (
    SELECT
      ID,amount,
      Maxamount =q.mymax
    FROM
      Table_4 
  ) AS derived
WHERE
  amount = Maxamount
) as result
from (

    SELECT ID, date,
    MAX(amount) 
    OVER (ORDER BY date ASC ROWS 5 PRECEDING) mymax
    FROM Table_4 
)as q