I have a table called products with this schema:

CREATE TABLE products (
  id INT PRIMARY KEY,
  sku TEXT NOT NULL,
  fee REAL
);

And another table with fee change log with this schema:

CREATE TABLE fee_change(
  id SERIAL PRIMARY KEY,
  sku_id INT NOT NULL,
  old_fee REAL NOT NULL,
  new_fee REAL NOT NULL,
  FOREIGN KEY (sku_id) REFERENCES products(id)
);

Is there anyway to get last 2 fee changes for each sku in one sql and not 2 rows for each sku, I want to have 2 new columns with old_fee_1, new _fee_1, old_fee_2, new_fee_2:

Desired result:

   id   |    sku   |   old_fee_1  |  new_fee_1  |  old_fee_2  |  new_fee_2
    1   |    ASC   |      4       |     2.5     |      3      |      4
    2   |    CF2   |      4       |      1      |      3      |      4
    3   |    RTG   |     0.5      |      1      |      2      |     0.5
    4   |    VHN5  |     null     |     null    |     null    |     null

dbfiddle

CodePudding user response：

As starting point I took your query from the fiddle you linked:

SELECT *
FROM products AS p
LEFT JOIN LATERAL (
    SELECT *
    FROM fee_change
    WHERE sku_id = p.id
    ORDER BY id DESC
    LIMIT 2
) AS oo
ON true

demo: db<>fiddle

You can use the FILTER clause (alternatively it works with a CASE WHEN construct as well) to pivot your joined table. To get the pivot value, you can add a row count (using the row_number() window function):

SELECT 
    p.id, p.sku, p.fee,
    MAX(old_fee) FILTER (WHERE row_number = 1) AS old_fee_1,       -- 2
    MAX(new_fee) FILTER (WHERE row_number = 1) AS new_fee_1,
    MAX(old_fee) FILTER (WHERE row_number = 2) AS old_fee_2,
    MAX(new_fee) FILTER (WHERE row_number = 2) AS new_fee_2
    
FROM products AS p
LEFT JOIN LATERAL (
    SELECT 
        *,
        row_number() OVER (PARTITION BY sku_id)                    -- 1
    FROM fee_change
    WHERE sku_id = p.id
    ORDER BY id DESC
    LIMIT 2
) AS oo ON true

GROUP BY p.id, p.sku, p.fee                                        -- 2

1. Create pivot value
2. Do the filtered aggregation to create the pivoted table.

CodePudding user response：

Something like this should do the trick :

SELECT p.id, 
       p.sku,
       old.old_fee_1,
       old.new_fee_1,
       new.old_fee_2,
       new.new_fee_2        
FROM products p
LEFT JOIN (SELECT fee.sku_id id, fee.old_fee old_fee_1, fee.new_fee new_fee_1 
           FROM fee_change ORDER BY fee.id DESC LIMIT 1 OFFSET 1) old ON old.id = p.id

LEFT JOIN (SELECT fee.sku_id id, fee.old_fee old_fee_2, fee.new_fee new_fee_2 
          FROM fee_change ORDER BY fee.id DESC LIMIT 1 OFFSET 0) new ON new.id = p.id