时间戳

首页 > 解决方案 > 分组时Oracle SQL对分区求和

sql - 分组时Oracle SQL对分区求和

问题描述

我有下表：

OP VT VK 全科医生 是 非盟
OP1 VT1 VK1 GP1 1 8
OP2 VT1 VK1 GP1 2
OP3 VT2 VK1 GP1 3 8
OP4 VT2 VK1 GP1 4
OP5 VT3 VK2 GP1 5 8
OP6 VT3 VK2 GP1 6
OP7 VT4 VK2 GP1 7 8
OP8 VT4 VK2 GP1 8
OP9 VT5 VK3 GP2 9 8
OP10 VT5 VK3 GP2 10
OP11 VT6 VK3 GP2 11 8
OP12 VT6 VK3 GP2 12
OP13 VT7 VK4 GP2 13 8
OP14 VT7 VK4 GP2 14
OP15 VT8 VK4 GP2 15 8
OP16 VT8 VK4 GP2 16

我想获得以下输出

VT VK 全科医生 VT_AU VT_AU8 VK_AU VK_AU8 GP_AU GP_AU8
VT1 VK1 GP1 3 1 10 4 36 16
VT2 VK1 GP1 7 3 10 4 36 16
VT3 VK2 GP1 11 5 26 12 36 16
VT4 VK2 GP1 15 7 26 12 36 16
VT5 VK3 GP2 19 9 42 20 100 48
VT6 VK3 GP2 23 11 42 20 100 48
VT7 VK4 GP2 27 13 58 28 100 48
VT8 VK4 GP2 31 15 58 28 100 48

到目前为止，我已经尝试过了，但是过度分区不能与 group by 一起使用。

-- Logic to obtain the output:
--VT_AU -> grouping by VT, sum of BE
--VT_AU8 -> grouping by VT, sum of BE when AU=8
--VK_AU -> grouping by VK, sum of BE
--VK_AU8 -> grouping by VK, sum of BE when AU=8
--GP_AU -> grouping by GP, sum of BE
--GP_AU8 -> grouping by GP, sum of BE when AU=8

with
  aux (op,vt, vk, gp, be, au) as (
    select 'OP1', 'VT1', 'VK1', 'GP1' , 1, 8 from dual union all
    select 'OP2', 'VT1', 'VK1', 'GP1', 2, null from dual union all
    select 'OP3', 'VT2', 'VK1' , 'GP1', 3, 8 from dual union all
    select 'OP4', 'VT2', 'VK1', 'GP1' , 4, null from dual union all
    select 'OP5', 'VT3', 'VK2' , 'GP1', 5, 8 from dual union all
    select 'OP6', 'VT3', 'VK2', 'GP1', 6, null from dual union all
    select 'OP7', 'VT4', 'VK2', 'GP1' , 7, 8 from dual union all
    select 'OP8', 'VT4', 'VK2', 'GP1', 8, null from dual union all
    select 'OP9', 'VT5', 'VK3' , 'GP2', 9, 8 from dual union all
    select 'OP10', 'VT5', 'VK3', 'GP2' , 10, null from dual union all
    select 'OP11', 'VT6', 'VK3' , 'GP2', 11, 8 from dual union all
    select 'OP12', 'VT6', 'VK3', 'GP2', 12, null from dual union all
    select 'OP13', 'VT7', 'VK4', 'GP2' , 13, 8 from dual union all
    select 'OP14', 'VT7', 'VK4', 'GP2', 14, null from dual union all
    select 'OP15', 'VT8', 'VK4' , 'GP2', 15, 8 from dual union all
    select 'OP16', 'VT8', 'VK4', 'GP2' , 16, null from dual
  )

SELECT
    vt,
    vk,
    gp,
    SUM(be) vt_au,
    sum(case when au=8 then be end) vt_au8,
    sum(be) over(partition by vk) vk_au,
    sum(case when au=8 then be end) over(partition by vk) vt_au8,
    sum(be) over(partition by gp) gp_au,
    sum(case when au=8 then be end) over(partition by gp) gp_au8
FROM
    aux
GROUP BY
    vt,
    vk,
    gp
ORDER BY
    vt,
    vk,
    gp;

标签： sqloracle

解决方案

如果我理解正确，您需要使用窗口函数进行条件聚合：

select VT, VK, GP,
       sum(be),
       sum(sum(case when au = 8 then be end)) over (partition by vt),
       sum(sum(be)) over (partition by vk),
       sum(sum(case when au = 8 then be end)) over (partition by vk),
       sum(sum(be)) over (partition by gp),
       sum(sum(case when au = 8 then be end)) over (partition by gp)
from aux
group by vt, vk, gp;

这是一个 db<>fiddle。

推荐阅读