时间戳

首页 > 解决方案 > 自窗口内最后一个最大值以来的计数

sql - 自窗口内最后一个最大值以来的计数

问题描述

我大半夜都在处理这个查询，只是无法让它工作。这是这个问题的附录。查询应该找到最后 10 条记录中最后一个最大值的“Seqnum”。我无法将最后一个最大值限制为窗口。

尽管我尝试了许多其他查询均无济于事，但以下是我为到达那里所做的最大努力：

SELECT [id], high, running_max, seqnum,  
  MAX(CASE WHEN ([high]) = running_max THEN seqnum END) OVER (ORDER BY [id]) AS [lastmax]     
  FROM (  
  SELECT [id], [high],  
      MAX([high]) OVER (ORDER BY [id] ROWS BETWEEN  9 PRECEDING AND CURRENT ROW) AS running_max,   
      ROW_NUMBER() OVER (ORDER BY [id]) as seqnum   
  FROM PY t 
  ) x

运行上述查询时，结果如下。

 id |  high  | running_max | seqnum | lastmax |
+----+--------+-------------+--------+---------+
|  1 |  28.12 |       28.12 |      1 |       1 |
|  2 |  27.45 |       28.12 |      2 |       1 |
|  3 |  27.68 |       28.12 |      3 |       1 |
|  4 |   27.4 |       28.12 |      4 |       1 |
|  5 |  28.09 |       28.12 |      5 |       1 |
|  6 |  28.07 |       28.12 |      6 |       1 |
|  7 |   28.2 |        28.2 |      7 |       7 |
|  8 |   28.7 |        28.7 |      8 |       8 |
|  9 |  28.05 |        28.7 |      9 |       8 |
| 10 | 28.195 |        28.7 |     10 |       8 |
| 11 |  27.77 |        28.7 |     11 |       8 |
| 12 |  28.27 |        28.7 |     12 |       8 |
| 13 | 28.185 |        28.7 |     13 |       8 |
| 14 |  28.51 |        28.7 |     14 |       8 |
| 15 |   28.5 |        28.7 |     15 |       8 |
| 16 |  28.23 |        28.7 |     16 |       8 |
| 17 |  27.59 |        28.7 |     17 |       8 |
| 18 |   27.6 |       28.51 |     18 |       8 |
| 19 |  27.31 |       28.51 |     19 |       8 |
| 20 |  27.11 |       28.51 |     20 |       8 |
| 21 |  26.87 |       28.51 |     21 |       8 |
| 22 |  27.12 |       28.51 |     22 |       8 |
| 23 |  27.22 |       28.51 |     23 |       8 |
| 24 |   27.3 |        28.5 |     24 |       8 |
| 25 |  27.66 |       28.23 |     25 |       8 |
| 26 | 27.405 |       27.66 |     26 |       8 |
| 27 |  27.54 |       27.66 |     27 |       8 |
| 28 |  27.65 |       27.66 |     28 |       8 |
+----+--------+-------------+--------+---------+

不幸的是，lastmax 列采用了所有先前记录的最后一个最大值，而不是仅最后 10 条记录的最大值。结果的方式如下：

结果 - 正确

重要的是要注意它们可以在“高”列中重复，因此需要考虑到这一点。

任何帮助将不胜感激。

标签： sqlsql-serverselect

解决方案

这不是错误。问题是，high并且lastmax必须来自同一行。在使用窗口函数时，这是一个令人困惑的方面。

您在外部查询中的逻辑是查找该行上的lastmax与该行上的匹配的high行。最后一次发生在第 8 行。随后的最大值是“局部的”，因为该特定行的值更高。

例如，在第 25 行，值为 26.660。从第 26 行开始，这是您想要的最大值。但在第 25 行本身，最大值为 28.230。那显然不等于high在那一行。因此，它在外部查询中不匹配。

我认为您不能使用窗口函数轻松地做您想做的事情。可能有一些棘手的方法。

使用cross apply作品的版本。我用过idlastmax。我不确定你是否真的需要seqnum：

select py.[id], py.high, t.high as running_max, t.id as lastmax  
from py cross apply
     (select top (1) t.*
      from (SELECT top (10) t.*
            from PY t 
            where t.id <= py.id
            order by t.id desc
           ) t
      order by t.high desc
     ) t;

这是一个 db<>fiddle。

推荐阅读