Question 167866 in PostgreSQL Question posted in
The official documentation can be found here.

How to classify groups based on changes from the previous period in PostgreSQL?

Good day to everyone!
I have a table with some teams and their rating for the period. Data looks like this:

team date rating
team_1 2022-09 7.5
team_1 2022-12 8
team_1 2023-03 8.5
team_2 2022-09 8
team_2 2022-12 7
team_2 2023-03 8
team_3 2022-09 8
team_3 2022-12 8
team_3 2023-03 7
team_4 2022-09 9
team_4 2022-12 8
team_4 2023-03 7

As we can see, rating of the team may increase or decrease comparing to the last period, or it can change for two consecutive periods. Thus, team may be classified to one of following groups:

  1. Increasing for two consecutive periods
  2. Increased compared to the last period
  3. Decreased compared to the last period
  4. Decreasing for two consecutive periods

In this sample, the result should be like this:

team status
team_1 Increasing for two consecutive periods
team_2 Increased compared to the last period
team_3 Decreased compared to the last period
team_4 Decreasing for two consecutive periods

My question is how can I get such result? I suspect there should be some window function, but I’m not sure how exactly to use it.

Tags:

2

Answers


  1. 0

    Yes, window functions, like here:

    select team, case sgn when  1 then 'increasing ' 
                      when -1 then 'decreasing '
                      else 'stable '
             end ||'for '||cnt||' period(s)' status
from (
  select team, dt, sgn, rn, 
         count(1) over (partition by team, sgn order by dt) cnt
  from ( 
    select team, dt, rating, 
           sign(rating - lag(rating) over (partition by team order by dt)) sgn, 
           row_number() over (partition by team order by dt desc) rn 
      from teams ) t1 ) t2
where rn = 1

    dbfiddle demo

    Compare ratings for each row using lag(), assign sign to get values -1, 0 or 1. Then count these consecutive values for each team and take last row, found with row_number(). Use case ... when to present values as you wanted.

    Edit:

    In the above query I assumed, that there can be maximum three enntries for each team, because these were possible team statuses. In case there are more entries gap-n-islands problem need to be solved at first, so query has to be modified like:

    select team, case sgn when  1 then 'increasing ' 
                      when -1 then 'decreasing '
                      else 'stable '
             end ||'for '||cnt||' period(s)' status
from (
  select team, dt, sgn, rn, count(1) over (partition by team, grp, sgn order by dt) cnt
  from (
    select team, dt, sgn, rn, sum(grp) over (partition by team order by dt) grp
    from (
      select team, dt, sgn, rn,  
             case when sgn <> lag(sgn) over (partition by team order by dt) 
             then 1 else 0 end grp
      from ( 
        select team, dt, rating, 
               sign(rating - lag(rating) over (partition by team order by dt)) sgn, 
               row_number() over (partition by team order by dt desc) rn 
        from teams ) t1) t2 ) t3 ) t4 
  where rn = 1

    so it counts only last consecutive series.

    dbfiddle demo

    Login or Signup to reply.
  2. 0

    I ended answering a slightly more general case by not limiting comparisons to 3 periods.

    It creates a difficult situation where having a rating e.g. increase, then pause (or decrease), then increase again requires us to ignore the first increase and only keep the last.

    WITH T(Team, Date, Sign) AS (
    SELECT Team, Date, SIGN(Rating - LAG(Rating) OVER (PARTITION BY Team ORDER BY date))
    FROM MyTable
), LastChange(Team, Date) AS(
    SELECT Team, LEAST(COALESCE(MAX(Date) FILTER (WHERE Sign >= 0),''), COALESCE(MAX(Date) FILTER (WHERE Sign <= 0),''))
    FROM T
    GROUP BY Team
)
SELECT T1.Team,
       CASE T1.Sign WHEN 1 THEN 'Increasing' ELSE 'Decreasing' END AS Direction,
       COUNT(T2.*) AS ForLastConsecutive
FROM T T1
JOIN T T2 
    ON  T1.Team = T2.Team
    AND T1.Sign * T2.Sign > 0
JOIN LastChange ON T2.Team = LastChange.Team AND T2.Date >= LastChange.Date
WHERE T1.Date = '2023-03'
GROUP BY T1.Team, T1.Sign
ORDER BY Team

    I let you combine the last 2 columns into a status column.

    Explanation:

    1. The sign of the difference of 2 consecutive ratings gives you the direction of the change. In the main query, when multiplying 2 signs from joined tables, we want to keep the 1 * 1 and the -1 * -1, both being > 0.
      This product simplifies the JOIN criteria quite a bit.
    2. The second trick is theCTE LastChange, which will find the date of the last increase and of the last decrease and, out of both, will keep the earlier date.
      Note that if the rating of a team only keeps increasing, then the date returned by COALESCE(MAX(Date) FILTER (WHERE Sign <= 0) will be NULL (and symmetrically if it keeps decreasing). In order for the LEAST() to work well, we need a COALESCE.
      Because your column date is in fact a text (the day is missing), I used ''. If it was an actual date, you would need to use -infinity.

    You can try what difference it makes with the below sample, in which I added 1 value for team_3:

    WITH MyTable(Team,date,rating) AS (
    VALUES
    ('team_1','2022-09',7.5),
    ('team_1','2022-12',8),
    ('team_1','2023-03',8.5),
    ('team_2','2022-09',8),
    ('team_2','2022-12',7),
    ('team_2','2023-03',8),
    ('team_3','2022-06',9),
    ('team_3','2022-09',8),
    ('team_3','2022-12',8),
    ('team_3','2023-03',7),
    ('team_4','2022-09',9),
    ('team_4','2022-12',8),
    ('team_4','2023-03',7)
), ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search