How can I merge rows of a table that records history of changes in order to obtain what a row looked like at a specific moment?

Question

I have the following 2 SELECTs:

SELECT * FROM public.app_user WHERE id = 'e31b55bf';
+--------+----+-----------+-----+-----+--------------------------+
|id      |name|email      |role |bio  |created_at                |
+--------+----+-----------+-----+-----+--------------------------+
|e31b55bf|Jon |jon@app.com|admin|Hello|2022-01-01 00:00:00.000000|
+--------+----+-----------+-----+-----+--------------------------+

SELECT * FROM history.app_user WHERE id = 'e31b55bf';
+--------+----+--------------+--------+----+--------------------------+
|id      |name|email         |role    |bio |updated_at                |
+--------+----+--------------+--------+----+--------------------------+
|e31b55bf|ASDF|test          |NULL    |NULL|2022-01-02 00:00:00.000000|
|e31b55bf|Test|test@gmail.com|basic   |NULL|2022-01-03 00:00:00.000000|
|e31b55bf|NULL|NULL          |standard|asdf|2022-01-04 00:00:00.000000|
|e31b55bf|NULL|NULL          |mod     |NULL|2022-01-05 00:00:00.000000|
+--------+----+--------------+--------+----+--------------------------+

public.app_user contains the users of my app and history.app_user contains a record of previous values of the rows of the first. In the example above, the user e31b55bf was a mod instead of an admin before the 5th of January, an standard user with the bio "asdf" before the 4th, a basic user named "Test" with the email "test@gmail.com" before the 3rd...

I want to come up with a SELECT or a FUNCTION that will tell me what the row looked like at a specific point in time. I believe I have already accomplished it, but my solution looks more complex than it should be. It's also tedious to translate it to other tables: say public.project and history.project, for example, where the columns are totally different. I believe a cleaner, easier to read and write solution exists. Can a SQL wizard help me here?

My current solution consists of overriding the values of the current row at public.app_user with the non-null values of this:

SELECT t.uuid,
       t1.name,
       t2.email,
       t3.role,
       t4.bio
FROM (
     SELECT uuid,
            MIN(CASE WHEN name IS NOT NULL THEN updated_at END)       AS name_date,
            MIN(CASE WHEN email IS NOT NULL THEN updated_at END)      AS email_date,
            MIN(CASE WHEN role IS NOT NULL THEN updated_at END)       AS role_date,
            MIN(CASE WHEN bio IS NOT NULL THEN updated_at END)        AS bio_date
     FROM history.app_user
     WHERE updated_at > '2022-01-03 12:00:00.000000' -- Date to check
     GROUP BY uuid
     ) t
     LEFT JOIN history.app_user t1 ON t1.updated_at = t.name_date
     LEFT JOIN history.app_user t2 ON t2.updated_at = t.email_date
     LEFT JOIN history.app_user t3 ON t3.updated_at = t.role_date
     LEFT JOIN history.app_user t4 ON t4.updated_at = t.bio_date

Edouard · Accepted Answer · 2022-01-14T17:27:04.993

Solution 1 : window function

SELECT DISTINCT ON (uuid)
     , uuid
     , (array_agg(name) FILTER (WHERE name IS NOT NULL) OVER w)[1] AS name
     , (array_agg(email) FILTER (WHERE email IS NOT NULL) OVER w)[1] AS email
     , (array_agg(role) FILTER (WHERE role IS NOT NULL) OVER w)[1] AS role
     , (array_agg(bio) FILTER (WHERE bio IS NOT NULL) OVER w)[1] AS bio 
  FROM history.app_user
 WHERE updated_at > '2022-01-03 12:00:00.000000' -- Date to check
WINDOW w AS (PARTITION BY uuid ORDER BY updated_at)

array_agg() is an aggregate function which is used as a window function here.

FILTER (WHERE condition) is used to exclude the NULL values from the selected rows.

The window is a subset of rows associated to the current row as described in the manual, ie all the existing rows with the same uuid as stated in the PARTITION BY clause. The ORDER BY clause allows to put the earliest non-null value in the first position of the resulting array and which is selected by [1].

The main issue of using here window functions is that we get as many rows as the ones filtered by the WHERE clause. The DISTINCT ON () clause excludes the redundant rows from the final result.

The second solution based on the same function used as an aggregate function instead of a window function is more accurate in our case.

Solution 2 : aggregate function

SELECT uuid
     , (array_agg(name ORDER BY updated_at) FILTER (WHERE name IS NOT NULL))[1] AS name
     , (array_agg(email ORDER BY updated_at) FILTER (WHERE email IS NOT NULL))[1] AS email
     , (array_agg(role ORDER BY updated_at) FILTER (WHERE role IS NOT NULL))[1] AS role
     , (array_agg(bio ORDER BY updated_at) FILTER (WHERE bio IS NOT NULL))[1] AS bio 
  FROM history.app_user
 WHERE updated_at > '2022-01-03 12:00:00.000000' -- Date to check
 GROUP BY uuid

This looks much better than my solution. Could you explain why this works? I've never used WINDOW and PARTITION. — BlueSialia, Jan 14 '22 at 17:05
I've updated the answer with some explanations regarding the window functions, and a second solution based on aggregate functions and which is more accurate. — Edouard, Jan 14 '22 at 17:28
From what I can understand, both solutions provide the same result so they are equally accurate. Do you mean performant? Is the second solution usually faster because it has less redundant operations? — BlueSialia, Jan 14 '22 at 17:47
"Accurate" means the solution 2 better fits your need, ie having one resulting row per uuid. Then I would think the second solution is also more performant but the best way to check would be you to execute `EXPLAIN ANALYSE` for both solutions and to share the results. — Edouard, Jan 14 '22 at 20:27

How can I merge rows of a table that records history of changes in order to obtain what a row looked like at a specific moment?

1 Answers1