当前位置: 首页 > 面试题库 >

SQL窗口函数-自上次最大值以来的行数

裴经义
2023-03-14
问题内容

我正在尝试创建一个SQL查询,该查询将拉取自Windows函数内的最后一个最大值起的最后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中的多个变量来接近,但这似乎没有效率或可伸缩性。


问题答案:

您可以计算累积最大值,然后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;


 类似资料:
  • 问题内容: 在问另一个问题时,我发现SQL Server(在2005年和2008年同时发生)在处理窗口函数子句中的语句时似乎有奇怪的不一致行为。以下代码给出了一个错误: 错误是 窗口函数不支持将常量用作ORDER BY子句表达式。 我认为这是因为该语句的计算结果可能为,这是一个常量。也可能像预期的那样,此代码给出了相同的错误: …大概是出于同样的原因。但是,此代码不会给出错误: 与第一个代码块的唯

  • 问题内容: 在SQL Server中,我试图计算自过去5天首次观察到与今天相同的天气(今天假设是2018年8月6日)以来的天数。每个镇。 数据如下: 这需要执行得很好,但到目前为止,我只想针对每个镇进行单个查询(并且将会有数十个镇,而不仅仅是四个镇)。这行得通,但不会扩展。 这是多伦多的那个… …正确返回4,因为今天有雨,而过去5天内第一次下雨是8月3日。 但是我想要返回的是一个像这样的表: 这怎

  • NowCoder 题目描述 给定一个数组和滑动窗口的大小,找出所有滑动窗口里数值的最大值。 例如,如果输入数组 {2, 3, 4, 2, 6, 2, 5, 1} 及滑动窗口的大小 3,那么一共存在 6 个滑动窗口,他们的最大值分别为 {4, 4, 6, 6, 6, 5}。 解题思路 // java public ArrayList maxInWindows(int[] num, int size)

  • 问题内容: 我们有一个多用户系统,用户保存到中央SQL Server 2005数据库。我们遇到了一个问题,一个用户刷新数据库中的更改,而另一个用户保存新数据。当前收集更改的方式是在每个表上都有一个timestamp列,该列在每次插入/更新时都被填充。另一个用户将在客户端上存储一个时间戳,这是他最后一次从数据库中提取数据。 每次保存都是在事务中完成的。我们正在处理的示例如下: User1开始保存,打

  • 问题内容: 我想创建一个数组,其中包含通过给定numpy数组移动的窗口的所有es。很抱歉,这听起来令人困惑。我举一个例子。输入: 我的窗口宽度为5的输出应为: 每个数字应为输入数组宽度5的子数组的最大值: 我没有在numpy中找到一个开箱即用的函数来做到这一点(但是如果有一个,我不会感到惊讶;我并不是一直以numpy开发人员的想法来思考)。我考虑过为输入创建偏移的2D版本: 然后,我可以对此进行申

  • 我在Scala中查看幻灯片函数中的Spark。