0

Consider a historical table where a value was changed and then changed back:

value | date
  3   | 1/1/2019
  3   | 1/2/2019
  3   | 1/3/2019
  4   | 1/4/2019
  4   | 1/5/2019
  3   | 1/6/2019
  3   | 1/7/2019
  3   | 1/8/2019
  3   | 1/9/2019
  2   | 1/10/2019
  2   | 1/11/2019
  3   | 1/12/2019
  3   | 1/13/2019
  3   | 1/14/2019

How do I write a query that shows min and max dates that still shows the value change? I'm looking for:

value | min(date) | max(date)  | days_at_this_val
  3   | 1/1/2019  | 1/3/2019   |        3
  4   | 1/4/2019  | 1/5/2019   |        2
  3   | 1/6/2019  | 1/9/2019   |        4
  2   | 1/10/2019 | 1/11/2019  |        2
  3   | 1/12/2019 | 1/14/2019  |        3

GROUP BY obviously lumps all the 3s together, and RANK and ROW_NUMBER resume counting 3s where they left off. What's the best way to do this?

2

A common way of doing this is to compare the total order (regarding date) vs the relative order within each value, let's call the difference grp. If the grp changes, it means that date from another partition of value interfered with the current group. So by picking the min date for each value, grp we can achieve what you want. I'll leave days_at_this_val as an exercise for the reader;-)

select v, min(d) as mind, max(d) as maxd
from (
    select v, d
         , row_number() over (order by d) 
         - row_number() over (partition by v order by d) as grp
    from t
) as tmp
group by v, grp
order by min(d);

DB<>fiddle

For future postings, you will get more (and thereby better) answers if you simplify for those trying to answer, by posting ddl and sql instead of ascii tables:

create table t (v int not null, d date not null);

insert into t (v,d) values ( 3   , '1/1/2019'),
                           (3   , '1/2/2019'),
                           ...
                           (3   , '1/14/2019');

or by creating a fiddle like the one I provided in the link.

  • thank you!!! row_number() over (order by d) - row_number() over (partition by v order by d) was the magic thing I was looking for. I just couldn't figure out how to get them to group like that! – user1601118 Oct 21 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.