Window Function / Aggregate Function / Interrupting Window

Question

I have a Table looking like this (Cols 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

I try to create col E based on A-D via window functions. I need to calculate the max(C) without interruption of D (if it changes the next window should begin) ordered by A, B and C.

select*,max(c) over (partition by D) from table?.. next row have D='A' again?.. — Vao Tsun
– Vao Tsun, Commented Sep 14, 2017 at 10:14
No, it won't work. Look at 5 row, it has D=A and C=9999-12-31, so E for rows 1-3 would be also 9999-12-31 and OP doesn't want that. — Łukasz Kamiński
– Łukasz Kamiński, Commented Sep 14, 2017 at 10:15
How come 5th row date derived, in which way it is different than first 3 rows of the data — Rams
– Rams, Commented Sep 14, 2017 at 10:41
The table represents a time-related mapping between A and D over time B. Here, the overall period should be recognized until a switch by D occurs. Therefore, the row 5 is different to the lines 1-3. — Cedric
– Cedric, Commented Sep 14, 2017 at 12:38

Gordon Linoff · Accepted Answer · 2017-09-14 11:00:55Z

1

You need to identify adjacent groups. One method uses a difference of window functions to identify the groups:

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;

It is a bit hard to explain how the difference of row numbers works. However, if you run the subquery and stare at the results, you'll probably see how it works.

answered Sep 14, 2017 at 11:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cedric Over a year ago

Works like a charm. Thank you!

Rams · Accepted Answer · 2017-09-14 10:23:36Z

0

Try below query to get the requested result

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

edited Sep 14, 2017 at 10:23

answered Sep 14, 2017 at 10:14

Rams

2,1591 gold badge15 silver badges20 bronze badges

2 Comments

Łukasz Kamiński Over a year ago

Does not work. Rows 1-3 will get 9999-12-31 instead of 2013-06-30

Rams Over a year ago

@ŁukaszKamiński, it will fail but how come 5th row date derived, in which way it is different than first 3 rows of the data

Collectives™ on Stack Overflow

Window Function / Aggregate Function / Interrupting Window

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related