在红移中为同一行元素创建具有不同组名的自定义列

Question

我有一张红移的桌子

我正在寻找动态创建一个自定义列，称为group1等组将包含id（1,2,4），group2将包含（1,2,5），预期输出类似。预期输出将是

表样本

我用case语句尝试了下面的查询，但group2没有添加元素1和2，并且只在group1中添加

在（1,2,4）中选择case，然后在（1,2,5）中选择'group1，然后在（1,2,5）中选择'group2'，否则id结束，样本组中的和（值）增加1

Answer 1

这有点棘手，因为这些组是重叠的。我建议：

with group_mapping as (
      select 1 as id, 'group1' as grp union all
      select 2 as id, 'group1' as grp union all
      select 4 as id, 'group1' as grp union all
      select 1 as id, 'group2' as grp union all
      select 2 as id, 'group2' as grp union all
      select 5 as id, 'group3' as grp
     )
select coalesce(gm.grp, t.id::text) as grp, sum(value)
from t left join
     group_mapping gm
     on gm.id = t.id
group by coalesce(gm.grp, t.id::text);

注意：如果您有一个包含组映射信息的表，那么为此目的不需要CTE。

如果两组没有重叠，那么另一种方法是通过一个表达式进行聚合。

Answer 2

WITH cte AS (
SELECT
case 
when id in (1,2,4) then 'group1' 
when id in (1,2,5) then 'group2' 
else 'other' end as mygroup,
value
FROM yourTable
)
SELECT mygroup,
sum(value) over (partition by position mygroup order by mygroup) as totalValue
FROM cte
group by mygroup

Answer 3

--drop table element_data;
create table element_data(id number(10),name varchar2(10),value number(10));

insert into element_data values(1,'e1',100);
insert into element_data values(2,'e2',200);
insert into element_data values(3,'e3',100);
insert into element_data values(4,'e4',50);
insert into element_data values(1,'e1',100);
insert into element_data values(2,'e2',200);
insert into element_data values(5,'e5',250);
select * from element_data;


select sum(output) from(
select  unique ID,(case when id=1 or id =5 or id=2 then
              sum(value) over(partition by name ) end) output from element_data)
              UNION all
              select sum(output) from(
select  unique ID,(case when id=1 or id =2 or id=4 then
              sum(value) over(partition by name ) end) output from element_data)
              union all
              select sum(output) from(
select  unique ID,(case when id=3 then
              sum(value) over(partition by name ) end) output from element_data)
              ;