窗口函数/聚合函数/中断窗口

Question

我有一个如下所示的表(A-D列)：

A        B           C                 D        E
----------------------------------------------------------
1        2011        2011-06-30        A        2013-06-30
1        2012        2012-06-30        A        2013-06-30 
1        2013        2013-06-30        A        2013-06-30 
1        2014        2015-06-30        B        2015-06-30 
1        2015        9999-12-31        A        9999-12-31 
2        2014        9999-12-31        C        9999-12-31 
2        2015        9999-12-31        C        9999-12-31 
2        2016        9999-12-31        C        9999-12-31 

我尝试通过窗口函数创建基于A-D的col。我需要计算max( C )而不中断按A、B和C排序的D(如果它改变，则下一个窗口应开始)。

Answer 1

您需要识别相邻的组。一种方法使用窗口函数的差异来识别组：

select t.*,
       max(c) over (partition by a, seqnum_a - seqnum_ad) as e
from (select t.*,
             row_number() over (partition by a order by b) as seqnum_a,
             row_number() over (partition by a, d order by b) as seqnum_ad
      from t
     ) t;

很难解释行号的不同是如何工作的。但是，如果运行子查询并查看结果，您可能会看到它是如何工作的。

Answer 2

尝试下面的查询以获取请求的结果

select t1.*,t2.C as E from table1 as t1
(select D,max(c) C from table1 group by D) as t2 on t1.D=t2.D