时间戳

首页 > 解决方案 > Oracle sql 拆分行以填充 maxquantity

sql - Oracle sql 拆分行以填充 maxquantity

问题描述

假设我有一个文章列表，我想将其拆分以填充最大值：

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  | quantity | maxquantity | tag          | effective_quantity 
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() = maxquantity or the rest of 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() = maxquantity or the rest of name_b

标签： sqloracle

解决方案

一种非常简单的方法是将数据分解为每个项目的单独行，计算该级别的 bin，然后重新聚合：

with cte (id, name, quantity, maxquantity, n) as (
      select id, name, quantity, maxquantity, 1 as n
      from t
      union all
      select id, name, quantity, maxquantity, n + 1
      from cte
      where n < 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。

推荐阅读