sql - SQL 的第二大运行值(value)

Question

我有一个table如:

<表类=“s-表”><标题>n值 <正文>112434465468

我可以轻松找到该值的运行最大值:

SELECT *, max(value) over (order by n rows unbounded preceding) as mx
FROM table

<表类=“s-表”><标题>n值mx <正文>111244344466546688

如何获得第二大的滑动数？所以输出是这样的:

<表类=“s-表”><标题>n值第二个_mx <正文>11241344464544686

附:

SELECT *,nth_value(value,2) over (order by n rows unbounded preceding) as second_mx
FROM table

不起作用，因为order by n描述如何对第 n 个进行排序。

Answer 1

我提供一个单独的答案，因为这个答案非常不同(我可能会删除另一个答案)。我确信这只能通过窗口函数来处理。我认为这提供了一个解决方案。

这从一堆解释开始。您可以跳到查询并链接到 db<>fiddle。

有两种情况，第二个最大值非常简单:

• 如果当前值为最大值并且之前出现过，则为第二个最大值。
• 如果当前值为最大值且从未出现过，则前一个最大值为第二个最大值。

另一个简单的案例:

• 如果该值小于或等于前一个第二个最大值，则第二个最大值不会更改。

最后，第二个最大值的一个重要属性:

• 第二个最大值正在增加。

因此，我们的想法是执行以下操作:

1. 计算“简单”的情况。
2. 在“琐碎”的情况下分配第二个，因为它不会根据简单的情况而改变。
3. 在其余部分中分配当前值。
4. 计算第二个总和的累积最大值。

这会导致:

select t.*, max(imputed_second_max) over (order by n) as second_max
from (select t.*,
             (case when sometimes_mx_2 is not null then sometimes_mx_2
                   when value <= max(sometimes_mx_2) over (order by n) then max(sometimes_mx_2) over (order by n)
                   else value
              end) as imputed_second_max
      from (select t.*,
                   (case when value = mx and nth_value > 1 then value
                         when value = mx and nth_value = 1 then lag(mx) over (order by n)
                    end) as sometimes_mx_2
            from (select t.*, max(value) over (order by n) as mx,
                         row_number() over (partition by value order by n) as nth_value
                  from t
                 ) t
           ) t
      ) t
order by n;

我发现我需要增加测试用例以获得更好的覆盖范围。我发现递减序列特别棘手。

Here是一个数据库<> fiddle 。