How do I build an events table from three separate tables showing incremental change over time?

user_3549316 0
Viewed 36

I'm trying to build a dataset that shows incremental change over time for some product attributes. The data is in AWS Athena in three separate tables that each store different attributes and they can be updated independently at different times. tbl1 can be joined to tbl2 and tbl2 can be joined to tbl3. There is always a one-to-one relationship between the tables so tbl1.id=1 will only ever relate to tbl2.id=2 and tbl2.id=2 will only relate to tbl3.id=3 in this example:

tbl1
| id | updated_at       | bool  |
| 1  | 2019-09-10 06:00 | True  |
| 1  | 2020-08-05 10:00 | False |
| 1  | 2020-09-03 15:00 | True  |

tbl2
| id | tbl1_id | updated_at       | desc    |
| 2  | 1       | 2019-09-10 06:00 | thing 1 |

tbl3
| id | tbl2_id | updated_at       | value |
| 3  | 2       | 2019-09-10 06:00 | 100   |
| 3  | 2       | 2019-09-19 09:00 | 50    |
| 3  | 2       | 2019-12-02 11:00 | 20    |

I'm trying to write a query that joins this data into a single table and has a row for each incremental update. From the above tables there was the initial insert on 2019-09-10 then four other changes made across tbl1 and tbl3 so it should end up as five rows that look like:

| tbl1_id | tbl1_updated_at  | bool  | tbl2_id | tbl2_updated_at  | desc   | tbl3_id | tbl3_updated_at  | value |
| 1       | 2019-09-10 06:00 | True  | 2       | 2019-09-10 06:00 | thing1 | 3       | 2019-09-10 06:00 | 100   |
| 1       | 2019-09-10 06:00 | True  | 2       | 2019-09-10 06:00 | thing1 | 3       | 2019-09-19 09:00 | 50    |
| 1       | 2019-09-10 06:00 | True  | 2       | 2019-09-10 06:00 | thing1 | 3       | 2019-12-02 11:00 | 20    |
| 1       | 2020-08-05 10:00 | False | 2       | 2019-09-10 06:00 | thing1 | 3       | 2019-12-02 11:00 | 20    |
| 1       | 2020-09-03 15:00 | True  | 2       | 2019-09-10 06:00 | thing1 | 3       | 2019-12-02 11:00 | 20    |

I started with the idea of joining everything together and using some WHERE clauses like:

select
*
from
tbl1
left join tbl2 on tbl1.id = tbl2.tbl1_id
left join tbl3 on tbl2.id = tbl3.tbl2_id
where
???

But couldn't get it working and not sure if this would even work. Perhaps there's some sort of window functions that would do it? It feels like it should be possible to do this in SQL but after two days of trying I'm completely at a loss as to how!

presto amazon-athena sql
1 Answers

This is quite complicated. It would be simpler if you had the tbl1 id in all the tables.

In any case, the idea is to union all the columns together along with the tbl1 id and updated_at. Then aggregate, so there is one row per id and date.

Finally, use last_value() with the ignore nulls option to get the most recent value that is populated:

with t as (
      select id, updated_at, max(bool) as bool, max(descr) as descr, max(value) as value
      from (select tbl1.id, tbl1.updated_at, tbl1.bool, null as descr, null as value
            from tbl1 
            union all
            select tbl2.tbl1_id, tbl2.updated_at, null, tbl2.descr, null
            from tbl2
            union all
            select tbl2.tbl1_id, tbl2.updated_at, null, null, tbl3.value
            from tbl2 join
                 tbl3
                 on tbl2.id = tbl3.tbl2_id
           ) t
     group by id, updated_at
    )
select id, updated_at,
       last_value(bool ignore nulls) over (partition by id order by updated_at) as bool,
       last_value(descr ignore nulls) over (partition by id order by updated_at) as descr,
       last_value(value ignore nulls) over (partition by id order by updated_at) as value
from t;
user_11440350
Related