I would like to reduce the number of samples by matching them for a specific product.

Conditions : The total weight does not exceed 200 pounds AND Reduce lightest and heavier samples wherever it’s possible

CREATE TABLE 'sample' ('product' TEXT,'id_sample' INTEGER,'weight_sample' INTEGER);
INSERT INTO 'sample' ('product','id_sample','weight_sample') VALUES 
 ('A','1001','160'), 
 ('A','1002','40'), 
 ('A','1003','5'), 
 ('B','1004','195'), 
 ('B','1005','190'), 
 ('B','1006','180'), 
 ('B','1007','160'), 
 ('B','1008','26'), 
 ('B','1009','12');
    

Table

Output :

[OUTPUT IMG][1]

Greetings

EDIT :

WITH 
     M (product, N_ref, MIN_weight) as (
        SELECT product, count(id_sample), min(weight_sample) FROM sample GROUP BY product)
    ,X as (SELECT sample.*, M."MIN_weight", row_number() over(partition by sample.product order by weight_sample DESC, id_sample) as RN 
           FROM sample, M WHERE sample.product = M.product and M."MIN_weight" sample.weight_sample <= 200)
    ,Z (product, id_sample, weight_sample, RN, Id2_compl, MIN_weight) as (
        SELECT X.product, X.id_sample, X.weight_sample, X.RN, (M.N_ref-X.RN 1), M."MIN_weight"
        FROM X, M
        WHERE X.product = M.product)
    ,T (id_sample2, id_2, weight_sample2, product) as (
        SELECT Z2.id_sample, Z2.id2_compl, Z2.weight_sample, Z2.product
        FROM Z, Z Z2 
        WHERE Z2.RN = Z.id2_compl and Z.product = Z2.product)
SELECT 
    Z.product, Z.id_sample as "id_sample1", Z.weight_sample as "weight_sample1",
    T.id_sample2, T.weight_sample2, (Z.weight_sample T.weight_sample2) as "weight_total"
FROM Z, T
WHERE Z.product = T.product and Z.RN = T.id_2 and "id_sample1" <> "id_sample2" 
order by Z.product, weight_total

Here my code so far, but feel way too complicate and not working (!) [1]: https://i.stack.imgur.com/9jZ8r.png

CodePudding user response：

You could do something like this:

select
    a.product, a.id, a.weight, b.product, b.id, b.weight, a.weight   b.weight as total
from
    sample a inner join sample b on a.product = b.product and a.id < b.id
where
    a.weight   b.weight <= 200 and not exists(
        select 1 from sample x
        where x.product = a.product and x.id <> a.id and x.id <> b.id and
        (x.weight   a.weight <= 200 and x.weight   a.weight > a.weight   b.weight or
         x.weight   b.weight <= 200 and x.weight   b.weight > a.weight   b.weight)
    )

Gives the correct result except it wants to combine A 1001 with A 1002 to get a total of 200.

First we join on product and a.id < b.id because we don't want (a, a) or having both (a, b) and (b, a), one is enough.

Then the total weight conditions are applied and then we check with the not exists if a sample exists which can be combined with the ones selected to give a higher weight still <= 200. So each sample is only used once (or never) in the output.