CODEWITHSUNDEEP
sqlsql-serverpostgresql
Andrey
Andrey

Reputation: 45

propagate hierarchical values from a single column

I have table with structure like this, which represents status changes.

+-----------+----------+----------------+-----------------+-------+
| record_id | group_id | attribute type |  change date    | value |
+-----------+----------+----------------+-----------------+-------+
|         1 |        1 | status         | 4/16/2008 18:59 | s1    |
|         2 |        1 | details        | 4/16/2008 18:59 | d5    |
|         3 |        1 | details        | 8/7/2008 18:31  | d2    |
|         4 |        1 | details        | 2/5/2009 22:15  | d1    |
|         5 |        1 | status         | 4/3/2009 21:27  | s2    |
|         6 |        1 | details        | 4/3/2009 21:27  | d7    |
|         7 |        2 | status         | 4/3/2009 21:46  | s1    |
|         8 |        2 | details        | 4/3/2009 21:46  | d1    |
+-----------+----------+----------------+-----------------+-------+

I'd like to query status changes and status details changes in two columns, grouped by time stamp (actually any status change makes change to details, so only details change timestamp could be used for easier grouping) and status propagated to related details, like this:

+-----------+-----------------+--------+---------+
| object id |  change date    | status | details |
+-----------+-----------------+--------+---------+
|         1 | 4/16/2008 18:59 | s1     | d5      |
|         1 | 8/7/2008 18:31  | s1     | d2      |
|         1 | 2/5/2009 22:15  | s1     | d1      |
|         1 | 4/3/2009 21:27  | s2     | d3      |
|         2 | 4/3/2009 21:46  | s1     | d1      |
+-----------+-----------------+--------+---------+

this is what I've started with, but it leaves me with NULLs 

        SELECT history.record_id,
                history.group_id,
                history.changedate,
                status_chages.value AS status,
                history.value AS details
        FROM history
        LEFT JOIN (SELECT
             history.group_id,
             history.changedate,
             history.value
             FROM history
             WHERE history.attribute_type = 'status') status_chages
        ON status_chages.group_id = history.group_id AND
           status_chages.changedate = history.changedate
        WHERE history.attribute = 'details'

First thing which came to my mind is to fill NULLs with previous row data.

But is there a better approach for querying the result listed above?

Upvotes: 0

Views: 72

Answers (1)

klin
klin

Reputation: 121774

The query gives desired layout:

select 
    group_id, 
    change_date, 
    max(case attr_type when 'status' then value else null end) as status,
    max(case attr_type when 'status' then null else value end) as detail
from history
group by 1, 2
order by 1, 2;

 group_id |     change_date     | status | detail 
----------+---------------------+--------+--------
        1 | 2008-04-16 18:59:00 | s1     | d5
        1 | 2008-08-07 18:31:00 |        | d2
        1 | 2009-02-05 22:15:00 |        | d1
        1 | 2009-04-03 21:27:00 | s2     | d7
        2 | 2009-04-03 21:46:00 | s1     | d1
(5 rows)

You can fill nulls with previous values in such a way:

select
    group_id, 
    change_date, 
    max(status) over (partition by group_id, part) status,
    detail
from (
    select *, count(status) over (partition by group_id order by change_date) part
    from (
        select 
            group_id, 
            change_date, 
            max(case attr_type when 'status' then value else null end) as status,
            max(case attr_type when 'status' then null else value end) as detail
        from history
        group by 1, 2
        ) s
    ) s
order by 1, 2;

 group_id |     change_date     | status | detail 
----------+---------------------+--------+--------
        1 | 2008-04-16 18:59:00 | s1     | d5
        1 | 2008-08-07 18:31:00 | s1     | d2
        1 | 2009-02-05 22:15:00 | s1     | d1
        1 | 2009-04-03 21:27:00 | s2     | d7
        2 | 2009-04-03 21:46:00 | s1     | d1
(5 rows)

Test it in rextester.

Upvotes: 2

Related Questions