Home > database >  In PostgreSQL, how can I optimize a query with which I obtain the differences between the current co
In PostgreSQL, how can I optimize a query with which I obtain the differences between the current co

Time:01-19

I have this audit table

User date text text 2
u1 2023-01-01 hi yes
u1 2022-12-20 hi no
u1 2022-12-01 hello maybe

And I need as a result, something like this:

User date text text 2
u1 2023-01-01 null x
u1 2022-12-20 x x
u1 2022-12-01 null null

So I can know which column changed from the last time.

Something like this is working, but I think may be a way to optimize it? or at least generate a "more easy to look" query? (i need the information for almost 20 columns, not only 3)

SELECT 
    ta.audit_date,
    ta.audit_user,
    CASE
        WHEN ta.audit_operation = 'I' THEN 'Insert'
        WHEN ta.audit_operation = 'U' THEN 'Update'
    END AS action,
    CASE WHEN ta.column1 <> (SELECT column1
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column1,
    CASE WHEN ta.column2 <> (SELECT column2
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column2,
    CASE WHEN ta.column3 <> (SELECT column3
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column3
FROM
    audit_table ta
WHERE
    ta.id = 9207
ORDER BY
    audit_date DESC

Thank you!

CodePudding user response:

I think you can just use the LAG() analytic function here. If I understand correctly:

SELECT *, CASE WHEN text != LAG(text) OVER (ORDER BY date) THEN 'x' END AS text_label,
          CASE WHEN text2 != LAG(text) OVER (ORDER BY date) THEN 'x' END AS text2_label
FROM yourTable
ORDER BY date; 
  • Related