sql - 使用 SQL 将数量分配给不同的桶

Question

我正在尝试将一些 SQL 代码放在一起，这些代码能够以特定顺序但在单独的事务中在容量有限的桶中分配一定数量的桶。例如，请参见下文。

我有 2 个桶，每个桶都有一定的容量(整数):

bucket       capacity
1            100
2            50

我有一定的数量要分装到多个交易中:

transaction   quantity
1             50
2             60
3             20
4             40

运行 SQL 代码后我想要以下结果，它应该保留交易编号并告诉我每个桶能够容纳多少该数量。必须按照桶号的顺序和交易号的顺序填充桶:

transaction   quantity_bucketed   bucket_id   overage
1             50                  1           0
2             50                  1           0
2             10                  2           0
3             20                  2           0
4             20                  2           20

如果没有更多的桶，并且还有一个数量要被桶，它应该像上面的例子一样转到“超额”列。

Answer 1

DROP TABLE IF EXISTS buckets;
CREATE TABLE buckets (
    bucket_id bigserial primary key,
    capacity integer);

-- something to put in the buckets
DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions (
    transaction_id bigserial primary key,
    quantity integer);

-- create 2 buckets with different capacities
INSERT INTO buckets (capacity)
VALUES (100),(50);

-- create some traffic to put in the buckets
INSERT INTO transactions (quantity)
VALUES (50),(60),(20),(40);


WITH buckets AS (
    -- expand buckets (create a row per bucket capacity)
    SELECT row_number() OVER () bucket_row_id, *
    FROM (
        -- slot = a unit of capacity
        SELECT *, generate_series(1,b.capacity) slot 
        FROM buckets b
    ) useless_alias
), xact AS (
    -- expand transactions, creating an id per unit of quantity
    SELECT row_number() OVER () unit_row_id, *
    FROM (
        -- an item per transaction quantity
        SELECT *, generate_series(1,t.quantity) unit 
        FROM transactions t
    ) useless_alias
), filled AS (
    -- join buckets to transactions on slots=units
    --   slots with no units = wasted bucket capacity
    --   units with no slots = overage
    SELECT b.*, x.*
    FROM xact x
    FULL JOIN buckets b
    ON b.bucket_row_id = x.unit_row_id
)
-- finally, do the do
SELECT transaction_id, CASE WHEN bucket_id IS NULL THEN 'overage' ELSE bucket_id::text END bucket_id , count(unit_row_id) quantity_bucketed
FROM filled
GROUP BY 1,2
ORDER BY 1,2

警告:我没有尝试从“超龄”中创建一个额外的专栏。填充桶时，超量没有放入哪个桶有点无关紧要。在示例中，只有 1 个事务超额。我假设在您的实际用例中，如果有更多交易，您真的希望看到每笔交易的数量没有分桶。