PSQL: How to compare record to previous record (2 tables)?-CodePudding

I have two tables:

01_System_Log

request_date   request_id
2022-01-01     1
2022-01-10     2
2022-01-20     3

Records

firm    name    city  request_id

firm_a  John    NY              1
firm_b  Will    LA              1
firm_c  Andy    SF              1
firm_c  Marga   CH              1

firm_a  John    NY              2
firm_b  Will    LA              2
firm_b  Nancy   LA              2
firm_c  Andy    SF              2
firm_c  Marga   CH              2

firm_a  John    NY              3
firm_b  Will    CH              3
firm_b  Nancy   LA              3
firm_c  Andy    SF              3
firm_c  Marga   SF              3 
firm_c  Joe     SF              3

I need to create a view, where the records of the last request id (3) are compared with the previous (2). So far I made a view, where the most actual records are shown.

SELECT DISTINCT main.firm,
    main.name,
    main.city,
    sys_log.request_id,
FROM "Records" main
INNER JOIN ( 
    SELECT sys.request_date,
           sys.request_id
    FROM "01_System_Log" sys
    ORDER BY sys.request_id DESC
    LIMIT 1 ) sys_log ON sys_log.request_id = main.request_id
WHERE  main.request_id IS NOT NULL
ORDER BY main.firm;

I am not sure how to proceed, while in all compare examples, which I have found, only one table was used.

My ideal result is:

firm   name_    city  request_id  firm_n name_n  city_n  request_id_n

firm_a  John     NY    2          firm_a  John     NY    3
firm_b  Will     LA    2          firm_b  Will     CH    3
firm_b  Nancy    LA    2          firm_b  Nancy    LA    3
firm_c  Andy     SF    2          firm_c  Andy     SF    3
firm_c  Marga    CH    2          firm_c  Marga    SF    3 
                                  firm_c  Joe      SF    3

CodePudding user response：

The information is all coming from one table "Records" so you just need to include that table twice in your query, using appropriate aliases, once to get the records with the highest request_id and once for the request_id which is lower than the highest. Finally you need to find what the highest request_id is, which you can do with a CTE on the "01_System_Log" table.

The CTE is straightforward:

WITH highest AS (
    SELECT request_id AS id
    FROM "01_System_Log"
    ORDER BY request_date DESC LIMIT 1
)

The records that use the highest request_id are also easily found:

SELECT firm, name, city, request_id
FROM "Records"
JOIN highest ON "Records".request_id = highest.id

To get the records with the second highest request_id for each combination of firm and city you need to use a window function:

SELECT DISTINCT firm, name, 
       first_value(city) OVER w AS city, 
       first_value(request_id) OVER w AS request_id
FROM "Records"
JOIN highest ON "Records".request_id < highest.id -- exclude the highest request_id
WINDOW w AS (PARTITION BY (firm, name) ORDER BY request_id DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

And then you tie it all together with sub-queries (and removing columns with duplicate information):

WITH highest AS (
    SELECT request_id AS id
    FROM "01_System_Log"
    ORDER BY request_date DESC LIMIT 1
)
SELECT curr.firm, curr.name, 
       prev.city AS previous_city, prev.request_id AS previous_request_id,
       curr.city AS current_city, curr.request_id AS current_request_id
FROM (
    SELECT DISTINCT firm, name, 
           first_value(city) OVER w AS city, 
           first_value(request_id) OVER w AS request_id
    FROM "Records"
    JOIN highest ON "Records".request_id < highest.id
    WINDOW w AS (PARTITION BY (firm, name) ORDER BY request_id DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ) prev
RIGHT JOIN (
    SELECT firm, name, city, request_id
    FROM "Records"
    JOIN highest ON "Records".request_id = highest.id ) curr USING (firm, name)
ORDER BY curr.firm;