postgresqlinventorypostgresql-15online-store

Postgresql query to get inventory from different locations


I have this table:

item_id quantity location_id stock
1 3 11 2
1 3 22 9

i.e the item_id 1, has a total of 3 items, I need to take those 3 items from the stock of the two location (11, 12), it means that the result should be looks like this:

item_id quantity location_id stock committed
1 3 11 0 2
1 3 22 8 1

I've been trying to use window functions like: over and partition by but I'm not sure how to accumulate values, if anyone have an idea how I can solve this I really appreciate thanks!


Solution

  • I've created the following tables and populated them to be consistent with the sample input presented in the original post:

    CREATE TABLE items (
      id integer PRIMARY KEY
    );
    
    CREATE TABLE locations (
      id integer PRIMARY KEY
    );
    
    CREATE TABLE items_locations (
      item_id integer REFERENCES items(id),
      location_id integer REFERENCES locations(id),
      stock integer,
      CONSTRAINT items_locations_pk PRIMARY KEY (item_id, location_id)
    );
    
    INSERT INTO items (id)
    VALUES (1);
    
    INSERT INTO locations (id)
    VALUES (11), (22);
    
    INSERT INTO items_locations (item_id, location_id, stock)
    VALUES (1, 11, 2), (1, 22, 9);
    

    The following SQL performs the operations described in the original post:

    WITH
      parms(item_id, quantity) AS (
        VALUES (1, 3)),
      commitments AS (
        SELECT il.item_id,
               il.location_id,
               GREATEST(0,
                        LEAST(il.stock, parms.quantity - SUM(il.stock)
                              OVER (PARTITION BY il.item_id
                                    ORDER BY il.location_id
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING))) AS committed
          FROM parms
          JOIN items_locations il
            ON il.item_id = parms.item_id),
      updated_rows AS (
        UPDATE items_locations il
          SET stock = il.stock - c.committed
          FROM commitments c
          WHERE c.committed > 0
            AND il.item_id = c.item_id
            AND il.location_id = c.location_id
         RETURNING il.*, c.committed)
      SELECT ur.item_id, parms.quantity, ur.location_id, ur.stock, ur.committed
        FROM parms
        JOIN updated_rows ur
          ON ur.item_id = parms.item_id
      ORDER BY ur.item_id, ur.location_id;
    

    The first CTE, parms, defines the items and desired quantities. The amount of stock to be taken from each location for each item is calculated in commitments. The arguments to LEAST ensure that no more than the available stock is taken. GREATEST is used to ensure that no stock is taken once the desired quantity has been committed. The stock for each item in each location is adjusted by the committed amount and the updated rows along with the associated committed amounts are returned as updated_rows. The final SELECT combines parms with updated_rows to return the output as shown in the original post.

    This query does not include logic to ensure that adequate stock is available to fulfill the requested quantity.