Oraclesql拆分行數以填充最大數量

假設我有一個文章串列，我想把它分割成最大的值：

id | name | quantity | maxquantity
1 | name_a| 3 | 5
2 | name_a| 1 | 5
3 | name_a|  3 |  5
4 | name_a| 5 | 5
5 | name_b| 7 | 4
6 | name_b| 2 | 4

我想創建按名稱分組的包，填充到最大值，得到以下結果：

我想創建按名稱分組的包，填充到最大值。

id | name |數量 | 最大數量 | 標簽 | 有效數量 
1 | name_a|  3 | 5 | name_a_part1 | 3
2 | name_a| 1 | 5 | name_a_part1 | 1
3 | name_a| 3 | 5 | name_a_part1 | 1
                                                                        ^- sum() = maxquantity

3 | name_a| 3 | 5 | name_a_part2 | 2
4 | name_a| 5 | 5 | name_a_part2 | 3
                                                                        ^- sum() = maxquantity

4 | name_a| 5 | 5 | name_a_part3 | 2
                                                                        ^- sum() =最大數量 或其余 的 name_a

5 | name_b| 7 | 4 | name_b_part1 | 4
                                                                        ^- sum() = Maxquantity

5 | name_b| 7 | 4 | name_b_part2 | 3
6 | name_b| 2 | 4 | name_b_part2 | 1
                                                                        ^- sum() = maxquantity

6 | name_b|  2 | 4 | name_b_part3 | 1
                                                                        ^- sum（） =最大數量 或其余 的 name_b
         

uj5u.com熱心網友回復：

一個相當簡單的方法是將資料分解成每個專案的獨立行，計算出該級別的bin，然后再重新匯總：

with cte (id, name, quantity, maxquantity, n) as (
      select id, name, quantity, maxquantity, 1 as n
      from t
      unionall
      select id, name, quantity, maxquantity, n    1
      from cte
      where n </span> quantity
     ) 
select id, name, quantity, maxquantity,
       count(*) as number_in_bin,
       ceil(bin_counter / maxquantity) as bin_number
from (select cte.*,
             row_number() over（partition by name order by id, n) as bin_counter
      from cte
     ) cte
group by id, name, quantity, maxquantity, ceil(bin_counter / maxquantity) 
order by id, bin_number;

這里是一個db<>fiddle。

uj5u.com熱心網友回復：

你可以通過使用分析函式的單一遞回查詢來實作：

WITH split_bins (id, name, quantity, maxquantity, tag, effective_quantity, prev_quantity) as ( SELECT id, 名稱。 數量。 最大數量。 FLOOR( COALESCE( SUM(數量) OVER( PARTITION BY name ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ), 0 ) / maxquantity ) 1, 最小值( 最大數量 - MOD( COALESCE( SUM(數量) OVER( PARTITION BY name ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ), 0 ), 最大數量 ), 數量 ), 0 FROM文章 UNION ALL SELECT id, 名稱。 數量。 最大數量。 標簽 1, 最小值( 數量 - prev_quantity - effective_quantity, 最大數量 ), prev_quantity effective_quantity FROM split_bins WHERE prev_quantity effective_quantity </span> quantity ) SEARCH DEPTH FIRST BY ID SET id_order SELECT id, 名稱。 數量。 最大數量。 name || '_part' || tag as tag, 有效數量 FROM split_bins。

其中，對于樣本資料：

CREATE TABLE articles ( id, name, quantity, maxquantity ) AS
SELECT 1, 'name_a', 3, 5 FROM DUAL UNION ALLSELECT 2, 'name_a', 1, 5 FROM DUAL UNION ALLSELECT 3, 'name_a', 3, 5 FROM DUAL UNION ALLSELECT 4, 'name_a', 5, 5 FROM DUAL UNION ALL
SELECT 5, 'name_b', 7, 4 FROM DUAL UNION ALLSELECT 6, 'name_b', 2。4 FROM DUAL UNION ALLSELECT 7, 'name_c', 6, 2 FROM DUAL UNION ALLSELECT 8, 'name_c', 2, 2 FROM DUAL;

輸出：

數量MAXQUANTITYTAG

ID	NAME	MAXQUANTITY	EFFECTIVE_QUANTITY
1	name_a	name_a_part1	3

name_a ? 5 name_a_part1 1





3 name_a_part1 name_a 5 name_a_part1 1





3 name_a_part1 name_a 5 name_a_part2 2





4 name_a_part2 name_a 5 5 name_a_part2 name_a_part2 3





name_a 5 5 name_a_part3 name_a_part3 2





5 name_b name_b name_b_part1? 4





5 name_b name_b 7 name_b_part2? 3





name_b 2 name_b_part2 1





6 name_b name_b 2 name_b_part3? 1





7 name_c name_c name_c_part1 2





7 name_c_part1 name_c name_c_part2 2





name_c_part2 name_c_part2 name_c_part3 name_c_part3 2





name_c name_c_part4 name_c_part4 2

db<>fiddle here