oracle动态求和分组

问题描述:

这里有很多条记录,其中需要按照数量字段,进行合并
合并规则:
按照合并数量和>=6的合并为一条记录
合并之后的记录,有新的ID

思路:先根据数量排序,再根据和为6进行分组标记,然后根据标记求和,最后筛选记录;

这里的难点就是: 分组标记,也就是指定n行为一组,这里就需要使用trunc(rownum - 1) / n)+1这种来标记行;
如果是指定行数的循环标记,则使用mod结合row_number()来实现;

下面的方法不算完美,如果要更简洁的做法:可以使用model实现;

-- 创建表
drop table yusydemo;
create table yusydemo(
       id number(18) primary key,
       prodID varchar2(4),
       money number(8),
       prodcount int
);

-- 创建序列
create sequence seq_demo_id 
minvalue 1
start with 1
increment by 1
nomaxvalue
nocache
nocycle;

-- 创建触发器
create or replace trigger trigger_demo_id
before insert on yusydemo for each row when (new.id is null)
begin 
  select seq_demo_id.nextval into :new.id from dual;
end;



-- 初始化数据
insert into yusydemo (prodID, money, prodcount)
values ('aaa', '2000', 1);

insert into yusydemo (prodID, money, prodcount)
values ('aaa', '1111', 2);

insert into yusydemo (prodID, money, prodcount)
values ('aaa', '222', 3);
insert into yusydemo (prodID, money, prodcount)
values ('aaa', '33', 4);
insert into yusydemo (prodID, money, prodcount)
values ('aaa', '4444', 5);

select * from yusydemo;

--基本分组的数据已经完成 
with dd as
 (select countflag || '生成newID' newid,
         sum(cc.prodcount) over(partition by countflag) weekSum,
         cc.*
    from (select case
                   when bb.prodcount = 1 then
                    trunc((bb.rn - 1) / 6) + 1 || '#' || bb.prodcount
                   when bb.prodcount = 2 then
                    trunc((bb.rn - 1) / 3) + 1 || '#' || bb.prodcount
                   when bb.prodcount in (3, 4, 5) then
                    trunc((bb.rn - 1) / 2) + 1 || '#' || bb.prodcount
                 end countflag, --这个就是分组标记
                 bb.*
            from (select row_number() over(partition by aa.prodcount order by prodcount) rn,
                         aa.*
                    from yusydemo aa
                   where aa.prodcount < 6) bb) cc
   order by cc.prodcount, cc.countflag)
--如果想要筛选每组的一条记录出来,也就是 合并之后的那一条数据  
select *
  from dd
 where (dd.countflag, dd.rn) in
       (select dd.countflag, min(dd.rn)
          from dd
         where dd.weekSum > 6
         group by dd.countflag);
 
 
 
--2.分组合并之后的结果集小于6的单独处理
--基本分组的数据已经完成.这里每组可能会剩余一下未合并的数据,这里手动处理一下就可以了
with dd as
 (select countflag || '生成newID' newid,
         sum(cc.prodcount) over(partition by countflag) weekSum,
         cc.*
    from (select case
                   when bb.prodcount = 1 then
                    trunc((bb.rn - 1) / 6) + 1 || '#' || bb.prodcount
                   when bb.prodcount = 2 then
                    trunc((bb.rn - 1) / 3) + 1 || '#' || bb.prodcount
                   when bb.prodcount in (3, 4, 5) then
                    trunc((bb.rn - 1) / 2) + 1 || '#' || bb.prodcount
                 end countflag, --这个就是分组标记
                 bb.*
            from (select row_number() over(partition by aa.prodcount order by prodcount) rn,
                         aa.*
                    from yusydemo aa
                   where aa.prodcount < 6) bb) cc
   order by cc.prodcount, cc.countflag)
   select * from dd where dd.weekSum<6;