Combining multiple rows based on recent values in PostgreSQL

user_7836937 0
Viewed 69

My first question on here, so i will try to explain it good.

I have a specific need which i tried to come up with a query but din't succeed to. Also googled it, and did not find it, but probably my input was not good, as it does not seem to me it should be that hard.

So some example of table and data i have (dates are in format here dd/MM/yyyy):

----------------------------------------------------------------------------
|   id    |   asset_id   |    value    |    start_date    |    end_date    |
----------------------------------------------------------------------------
|    1    |       1      |    value1   |    20-10-2020    |   31-10-2020   |
----------------------------------------------------------------------------
|    1    |       1      |    value1   |    01-11-2020    |   05-11-2020   |
----------------------------------------------------------------------------
|    1    |       2      |    value2   |    05-10-2020    |   10-10-2020   |
----------------------------------------------------------------------------
|    1    |       2      |    value3   |    10-10-2020    |   15-10-2020   |
----------------------------------------------------------------------------
|    1    |       3      |    value3   |    15-08-2020    |   31-08-2020   |
----------------------------------------------------------------------------
|    1    |       3      |    value1   |    01-09-2020    |   05-09-2020   |
----------------------------------------------------------------------------
|    1    |       3      |    value1   |    05-09-2020    |   10-09-2020   |
----------------------------------------------------------------------------

So the specific need i have is to look at the two most recent rows grouped by id and asset_id. If the value of these two rows is the same, then combine the rows into one, with the start_date from the first row and end_date of the second one. If the values do not match, then nothing should be done.

For the specific input (previous table), some desired output should be:

----------------------------------------------------------------------------
|   id    |   asset_id   |    value    |    start_date    |    end_date    |
----------------------------------------------------------------------------
|    1    |       1      |    value1   |    20-10-2020    |   05-11-2020   |
----------------------------------------------------------------------------
|    1    |       2      |    value2   |    05-10-2020    |   10-10-2020   |
----------------------------------------------------------------------------
|    1    |       2      |    value3   |    10-10-2020    |   15-10-2020   |
----------------------------------------------------------------------------
|    1    |       3      |    value3   |    15-08-2020    |   31-08-2020   |
----------------------------------------------------------------------------
|    1    |       3      |    value3   |    01-09-2020    |   10-09-2020   |
----------------------------------------------------------------------------

So for the group (id, asset_id) where the values are (1,1), two rows form the input table should be combined as i described as their value is the same. So the 1st and 2nd row should combine to the 1st row from the output. For the (1,2) group, the values are different, so no combining should be done. For the (1,3) group, the two most recent rows (the 6th and 7th from the input) should combine in the 5th in the output table.

It seems not hard, but i have trouble to come with something specific. I made an sqlfiddle where anyone can try.

Any help really appreciated.

aggregate-functions greatest-n-per-group datetime postgresql sql
1 Answers

You can filter the top two rows per group with row_number(). Then, aggregate by value: if both rows in the group have the same value, they are grouped together, else then end up in two different groups.

So:

select id, asset_id, value, min(start_date) start_date, max(end_date) end_date
from (
    select t.*,
        row_number() over(partition by id, asset_id order by start_date desc) rn
    from mytable t
) t
where rn <= 2
group by id, asset_id, value
user_106767160
Related