零售业里的,20%商品,产生80%的销售,就是要查出是哪些商品的贡献度最大。
如图:
我如何取每类商品中按销售额降序排列后,依次累加直到累计值>=0.8*sum(销售额)的前几种商品的销售额明细。
sql:
-- Create table
create table TAB_1
(
COD VARCHAR2(10),
REC NUMBER(8,4)
);
insert into TAB_1 (COD, REC)
values ('A', 500);
insert into TAB_1 (COD, REC)
values ('B', 600);
insert into TAB_1 (COD, REC)
values ('C', 200);
insert into TAB_1 (COD, REC)
values ('D', 223);
insert into TAB_1 (COD, REC)
values ('D', 30);
insert into TAB_1 (COD, REC)
values ('A', 55);
insert into TAB_1 (COD, REC)
values ('A', 3);
commit;
elect m.R1, m.Cod, m.Rec1, n.Rec1 需要查询的数量, n.Cod 需要查询的代码
From (Select Cod, Rec1, Rownum R1
From (Select Cod, Rec1
From (Select Cod, Sum(Rec) Rec1 From Tab_1 a Group By Cod)
Order By Rec1)) m,
(Select Cod, Rec1, Rownum R2
From (Select Cod, Rec1
From (Select Cod, Sum(Rec) Rec1 From Tab_1 a Group By Cod)
Order By Rec1)) n
Where m.R1 <= n.R2
And m.R1 =
(Select Max(R1)
From (Select m.R1, Sum(n.Rec1)
From (Select Cod, Rec1, Rownum R1
From (Select Cod, Rec1
From (Select Cod, Sum(Rec) Rec1
From Tab_1 a
Group By Cod)
Order By Rec1)) m,
(Select Cod, Rec1, Rownum R2
From (Select Cod, Rec1
From (Select Cod, Sum(Rec) Rec1
From Tab_1 a
Group By Cod)
Order By Rec1)) n
Where m.R1 <= n.R2
Group By m.R1
Having Sum(n.Rec1) > = (Select Sum(Rec) * 0.8 From Tab_1)))
Order By m.Rec1 Desc
如图:
我如何取每类商品中按销售额降序排列后,依次累加直到累计值>=0.8*sum(销售额)的前几种商品的销售额明细。
sql:
-- Create table
create table TAB_1
(
COD VARCHAR2(10),
REC NUMBER(8,4)
);
insert into TAB_1 (COD, REC)
values ('A', 500);
insert into TAB_1 (COD, REC)
values ('B', 600);
insert into TAB_1 (COD, REC)
values ('C', 200);
insert into TAB_1 (COD, REC)
values ('D', 223);
insert into TAB_1 (COD, REC)
values ('D', 30);
insert into TAB_1 (COD, REC)
values ('A', 55);
insert into TAB_1 (COD, REC)
values ('A', 3);
commit;
elect m.R1, m.Cod, m.Rec1, n.Rec1 需要查询的数量, n.Cod 需要查询的代码
From (Select Cod, Rec1, Rownum R1
From (Select Cod, Rec1
From (Select Cod, Sum(Rec) Rec1 From Tab_1 a Group By Cod)
Order By Rec1)) m,
(Select Cod, Rec1, Rownum R2
From (Select Cod, Rec1
From (Select Cod, Sum(Rec) Rec1 From Tab_1 a Group By Cod)
Order By Rec1)) n
Where m.R1 <= n.R2
And m.R1 =
(Select Max(R1)
From (Select m.R1, Sum(n.Rec1)
From (Select Cod, Rec1, Rownum R1
From (Select Cod, Rec1
From (Select Cod, Sum(Rec) Rec1
From Tab_1 a
Group By Cod)
Order By Rec1)) m,
(Select Cod, Rec1, Rownum R2
From (Select Cod, Rec1
From (Select Cod, Sum(Rec) Rec1
From Tab_1 a
Group By Cod)
Order By Rec1)) n
Where m.R1 <= n.R2
Group By m.R1
Having Sum(n.Rec1) > = (Select Sum(Rec) * 0.8 From Tab_1)))
Order By m.Rec1 Desc