SQL grouping with partitions

Question

I have the following table:

ID  Rating  Rating_from Rating_to
1   2       2010-01-01  2011-01-01
1   2       2011-01-02  2012-02-01
1   3       2012-02-02  2013-03-01
1   2       2013-03-02  2013-04-01
1   2       2013-04-02  9999-12-31

It contains a rating per ID, that is checked on an ad-hoc basis. Each time the rating is checked, the last row is given a Rating_to date, usually the day before the new rating, and a new row is entered with a Rating_from date of the actual day of the rating. The Rating_to is set to 9999-12-31 instead of NULL. ‍♀️ Often, the rating remains the same. At times, the rating changes. An ID may also over time receive a rating it used to have before.

How do I get the earliest Rating_from date and latest Rating_to date, per ID, per rating, without grouping ratings that have the same rating value, but are interspersed with other ratings?

I'm trying to get the following table:

ID  Rating  Rating_from Rating_to
1   2       2010-01-01  2012-02-01
1   3       2012-02-02  2013-03-01
1   2       2013-03-02  NULL

Using the data from above, I tried grouping by ID and Rating (and setting a MIN() and MAX() on the from and to fields), but then I would only get two rows, one for rating 2 and one for rating 3, even though there were two periods of rating 2.

I asked a colleague, he suggested using LAG() and LEAD(), but I'm not sure how that will help here. The data is in SQL Server 2017, and there are around a million IDs. Any suggestions are very welcome.

I have added the below TABLE CREATE script with real table data, hope that helps:

CREATE TABLE tbl(
  id INT,
  rating int,
  rating_from DATE,
  rating_to DATE
);

INSERT INTO tbl VALUES
  (1, 2, '2014-05-23', '2015-04-13'),
  (1, 2, '2015-04-14', '2015-06-02'),
  (1, 2, '2015-06-03', '2016-05-31'),
  (1, 2, '2016-06-01', '2018-03-22'),
  (2, 1, '2016-06-01', '9999-12-31'),
  (3, 3, '2016-06-01', '9999-12-31'),
  (1, 2, '2018-03-23', '2018-08-06'),
  (1, 3, '2018-08-07', '2018-08-21'),
  (1, 2, '2018-08-22', '2018-09-19'),
  (1, 2, '2018-09-20', '9999-12-31');

user_3633108 · Answer

Here's a solution based on Itzik Ben-Gan's Islands approach. It first finds rows where a change has occurred. Generates a running total of these to get a unique ID per change and then groups on the change. It's a fast and elegant approach.

With LagAndLead AS
(
SELECT 
ID,Rating,Rating_from,Rating_to
, CASE WHEN     LAG(Rating) OVER (PARTITION BY ID ORDER BY Rating_from) <> Rating 
    THEN 1 
    ELSE 0 
END AS IsStart
FROM tbl
),
Islands AS 
(
SELECT ID,Rating,Rating_from, rating_to
, SUM(IsStart) OVER (PARTITION BY ID ORDER BY Rating_from ROWS UNBOUNDED PRECEDING) AS IslandID
FROM LagAndLead
)
SELECT S.ID,MIN(S.Rating) AS Rating ,min(S.Rating_from) AS Rating_from, max(S.rating_to) AS rating_to
FROM Islands AS S
GROUP BY S.ID,S.IslandID

Example: dbfiddle.uk

user_1144035 · Answer

I find that a convenient solution is similar to the lag() approach. Instead of lag() it looks for the maximum "to date"

select id, rating, min(rating_from), max(rating_to)
from (select t.*,
             sum(case when dateadd(day, 1, prev_rating_to) >= rating_from then 0 else 1 end) over
                 (partition by id, rating order by rating_from) as grp
      from (select t.*,
                   max(rating_to) over (partition by id, rating
                                        order by rating_from
                                        rows between unbounded preceding and 1 preceding
                                       ) as prev_rating_to
            from tbl t
           ) t
     ) t
group by id, rating, grp
order by id, rating, min(rating_from);

This method takes into account the rating_to dates as well. So it will find gaps even when the rating does not change.

Here is a db<>fiddle.

user_9438107 · Answer

i just try one example and i wish to share with you. if you feel the result is OK then take that.

declare @temp as table 
(
    id int,
    rating int,
    rating_from date,
    rating_to date null
);

insert into @temp (id,rating,rating_from,rating_to)values
(1,2,'2010-01-01','2011-01-01'),
(1,2,'2011-01-02','2012-02-01'),
(1,3,'2012-02-02','2013-03-01'),
(1,2,'2013-03-02','2011-01-01'),
(1,2,'2013-04-02',null);

select id,rating,min(rating_from) rating_from,max(Rating_to) rating_to from @temp
group by id,rating
union 
select id,rating,max(rating_from) rating_from,max(Rating_to) rating_to from @temp
where Rating_to is null
group by id,rating
order by rating_from,rating_to


id  rating  rating_from rating_to
1   2   2010-01-01  2012-02-01
1   3   2012-02-02  2013-03-01
1   2   2013-04-02  NULL

I hope this will help to you...

user_11998619 · Answer

Please try the following query if it gives correct results and better performance:

SELECT * FROM
(
SELECT 
  ID, Rating, 
  MIN(Rating_from) AS Rating_from, 
  MAX(Rating_to) AS Rating_to
FROM (

SELECT 
  ID, Rating, Rating_from, Rating_to
  ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Rating_from, ID) R_NUM 
  ,ROW_NUMBER() OVER(PARTITION BY Rating, ID ORDER BY Rating_from, ID) R_NUM_Rating
FROM TEST
) AS A
WHERE A.R_NUM = A.R_NUM_Rating OR A.R_NUM_Rating = 1
GROUP BY ID, Rating

UNION ALL

SELECT 
  ID, Rating, 
  MIN(Rating_from) AS Rating_from, 
  MAX(Rating_to) AS Rating_to
FROM (

SELECT 
  ID, Rating, Rating_from, Rating_to
  ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Rating_from, ID) R_NUM 
  ,ROW_NUMBER() OVER(PARTITION BY Rating, ID ORDER BY Rating_from, ID) R_NUM_Rating
FROM TEST
) AS A
WHERE A.R_NUM <> A.R_NUM_Rating AND A.R_NUM_Rating <> 1
GROUP BY ID, Rating
) AS FINAL
ORDER BY 3, 1

SQL grouping with partitions

4 Answers