Limit result depending on sum of value-CodePudding

I´ve been trying to automize a list of addresses in SQL. I have multiple addresses and quantities and i need only the addresses that will fulfill the quantity i need

For example:

I have a table with

Item A qty 20
Item B qty 5
Item C qty 23

And a table with addresses and units

Address 1  item A  15units
Address 2  item A  10units
Address 3  item A  10units
Address 4  item A  13units

The result should show only

Address 2  item A  10units
Address 3  item A  10units

CodePudding user response：

Assuming that your first table sales stores the sales made :

Item	qty
A	20
B	5
C	23

and your second table stocks indicates the addresses of the stored items :

Address	item	units
1	A	15
2	A	10
3	A	10
4	A	13

then you can select a subset of addresses so that the sum of the stored units is equal or greater than the quantity sold :

WITH list AS (
SELECT st.item
     , sa.qty AS sales_qty
     , array_agg(st.address) OVER w AS addresses
     , array_agg(st.units) OVER w AS units
     , sum(units) OVER w AS total_stored_units
  FROM sales AS sa
 INNER JOIN stocks AS st
    ON st.item = sa.item
WINDOW w AS (PARTITION BY st.item ORDER BY st.address)
)
SELECT DISTINCT ON (item)
       item, unnest(addresses) AS address, unnest(units) AS units
  FROM list
 WHERE total_stored_units >= sales_qty
 ORDER BY item, total_stored_units ASC

Result

item	address	units
A	1	15
A	2	10

see dbfiddle

CodePudding user response：

Thanks a lot @Edouard! I ended up using something like

WITH r AS 
(
SELECT 
  item,
  address,
  quantity, 
  sum(quantity)
  OVER (
    PARTITION BY item
    ORDER BY quantity desc
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as total_units
FROM st
GROUP BY 1,2,3 
)

SELECT *
FROM r
GROUP BY 1,2,3,4
HAVING total_units <= qty_needed   avg(units)
ORDER BY units desc

I couldnt run your query in the bigquery console, but from it a re read some documentation of windows functions find this solution.

Again, thanks a lot!