我想通過為特定產品匹配樣本來減少樣本數量。
條件：總重量不超過 200 磅并且盡可能減少最輕和最重的樣品
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');
桌子
| 產品 | id_sample | 重量樣本 |
|---|---|---|
| 一個 | 1001 | 160 |
| 一個 | 1002 | 40 |
| 一個 | 1003 | 5 |
| 乙 | 1004 | 195 |
| 乙 | 1005 | 190 |
| 乙 | 1006 | 180 |
| 乙 | 1007 | 160 |
| 乙 | 1008 | 26 |
| 乙 | 1009 | 12 |
輸出 ：
| 產品 | id_sample1 | weight_sample1 | id_sample2 | weight_sample2 | 總重量 |
|---|---|---|---|---|---|
| 一個 | 1001 | 160 | 1003 | 5 | 165 |
| 乙 | 1006 | 180 | 1009 | 12 | 192 |
| 乙 | 1007 | 160 | 1008 | 26 | 186 |
[輸出影像][1]
問候
編輯 ：
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
到目前為止，我的代碼在這里，但感覺太復雜且無法正常作業（！）[1]：https ://i.stack.imgur.com/9jZ8r.png
uj5u.com熱心網友回復：
你可以這樣做：
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)
)
給出正確的結果，但它想將 A 1001 與 A 1002 組合得到 200。
首先我們加入 product 和 a.id < b.id 因為我們不想要 (a, a) 或同時擁有 (a, b) 和 (b, a)，一個就足夠了。
然后應用總權重條件，然后我們檢查不存在是否存在樣本，該樣本可以與選擇的樣本組合以提供更高的權重仍然 <= 200。因此每個樣本在輸出。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/496605.html