Oracle-分組求和

• 普通使用

with
    tmp_table
as(
    select '1001' group1, 'a' group2, 'Y' group3, 1 num from dual
    union all
    select '1001' group1, 'a' group2, 'N' group3, 2 num from dual
    union all
    select '1001' group1, 'a' group2, 'Y' group3, 3 num from dual
    union all
    select '1001' group1, 'b' group2, 'N' group3, 4 num from dual
    union all
    select '1001' group1, 'b' group2, 'Y' group3, 5 num from dual
    union all
    select '1002' group1, 'a' group2, 'N' group3, 6 num from dual
    union all
    select '1002' group1, 'a' group2, 'Y' group3, 7 num from dual
    union all
    select '1002' group1, 'b' group2, 'N' group3, 8 num from dual
    union all
    select '1002' group1, 'b' group2, 'Y' group3, 9 num from dual
)
select group1, group2, group3, sum(num) num
    from tmp_table
        group by group1, group2, group3
            order by group1, group2, group3

• 進階使用

with
    tmp_table
as(
    select '1001' group1, 1 num from dual
    union all
    select '1001' group1, 2 num from dual
    union all
    select '1001' group1, 3 num from dual
    union all
    select '1001' group1, 4 num from dual
    union all
    select '1001' group1, 5 num from dual
    union all
    select '1002' group1, 6 num from dual
    union all
    select '1002' group1, 7 num from dual
    union all
    select '1002' group1, 8 num from dual
    union all
    select '1002' group1, 9 num from dual
)
select group1,  num, sum(num) over(partition by group1 order by group1) ttl_num
    from tmp_table
        order by group1

• 進階使用
  累加

with
    tmp_table
as(
    select '1001' group1, 1 num from dual
    union all
    select '1001' group1, 2 num from dual
    union all
    select '1001' group1, 3 num from dual
    union all
    select '1001' group1, 4 num from dual
    union all
    select '1001' group1, 5 num from dual
    union all
    select '1002' group1, 6 num from dual
    union all
    select '1002' group1, 7 num from dual
    union all
    select '1002' group1, 8 num from dual
    union all
    select '1002' group1, 9 num from dual
)
select group1,  num, sum(num) over(partition by group1 order by group1 asc rows between unbounded preceding and current row) num
    from tmp_table
        order by group1

• 進階使用 grouping sets
  每一層都會sum一下

with
    tmp_table
as(
    select '1001' group1, 'a' group2, 'Y' group3, 1 num from dual
    union all
    select '1001' group1, 'a' group2, 'N' group3, 2 num from dual
    union all
    select '1001' group1, 'a' group2, 'Y' group3, 3 num from dual
    union all
    select '1001' group1, 'b' group2, 'N' group3, 4 num from dual
    union all
    select '1001' group1, 'b' group2, 'Y' group3, 5 num from dual
    union all
    select '1002' group1, 'a' group2, 'N' group3, 6 num from dual
    union all
    select '1002' group1, 'a' group2, 'Y' group3, 7 num from dual
    union all
    select '1002' group1, 'b' group2, 'N' group3, 8 num from dual
    union all
    select '1002' group1, 'b' group2, 'Y' group3, 9 num from dual
)
select group1, group2, group3, sum(num) num
    from tmp_table
        group by grouping sets((group1, group2, group3), (group1, group2), (group1))
            order by group1, group2, group3

• 進階使用 rollup
  在grouping sets的基礎上，增加一個總的合計

with
    tmp_table
as(
    select '1001' group1, 'a' group2, 'Y' group3, 1 num from dual
    union all
    select '1001' group1, 'a' group2, 'N' group3, 2 num from dual
    union all
    select '1001' group1, 'a' group2, 'Y' group3, 3 num from dual
    union all
    select '1001' group1, 'b' group2, 'N' group3, 4 num from dual
    union all
    select '1001' group1, 'b' group2, 'Y' group3, 5 num from dual
    union all
    select '1002' group1, 'a' group2, 'N' group3, 6 num from dual
    union all
    select '1002' group1, 'a' group2, 'Y' group3, 7 num from dual
    union all
    select '1002' group1, 'b' group2, 'N' group3, 8 num from dual
    union all
    select '1002' group1, 'b' group2, 'Y' group3, 9 num from dual
)
select group1, group2, group3, sum(num) num
    from tmp_table
        group by rollup(group1, group2, group3)
            order by group1, group2, group3

• 進階使用 cube
  所有層都會有一個合計

with
    tmp_table
as(
    select '1001' group1, 'a' group2, 'Y' group3, 1 num from dual
    union all
    select '1001' group1, 'a' group2, 'N' group3, 2 num from dual
    union all
    select '1001' group1, 'a' group2, 'Y' group3, 3 num from dual
    union all
    select '1001' group1, 'b' group2, 'N' group3, 4 num from dual
    union all
    select '1001' group1, 'b' group2, 'Y' group3, 5 num from dual
    union all
    select '1002' group1, 'a' group2, 'N' group3, 6 num from dual
    union all
    select '1002' group1, 'a' group2, 'Y' group3, 7 num from dual
    union all
    select '1002' group1, 'b' group2, 'N' group3, 8 num from dual
    union all
    select '1002' group1, 'b' group2, 'Y' group3, 9 num from dual
)
select group1, group2, group3, sum(num) num
    from tmp_table
        group by cube(group1, group2, group3)
            order by group1, group2, group3

• grouping

with
    tmp_table
as(
    select '1001' group1, 'a' group2, 'Y' group3, 1 num from dual
    union all
    select '1001' group1, 'a' group2, 'N' group3, 2 num from dual
    union all
    select '1001' group1, 'a' group2, 'Y' group3, 3 num from dual
    union all
    select '1001' group1, 'b' group2, 'N' group3, 4 num from dual
    union all
    select '1001' group1, 'b' group2, 'Y' group3, 5 num from dual
    union all
    select '1002' group1, 'a' group2, 'N' group3, 6 num from dual
    union all
    select '1002' group1, 'a' group2, 'Y' group3, 7 num from dual
    union all
    select '1002' group1, 'b' group2, 'N' group3, 8 num from dual
    union all
    select '1002' group1, 'b' group2, 'Y' group3, 9 num from dual
)
select group1, group2, group3, sum(num) num, grouping(group1),  grouping(group2), grouping(group3)
    from tmp_table
        group by grouping sets((group1, group2, group3), (group1, group2), (group1))
            order by group1, group2, group3