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!