我的表结构是这样的
itemcode 销售数量 次数 级别
00001 15 2
00002 15 2
00003 10 1
00004 10 2
00005 10 1
00006 8 2
00007 8 1
00008 7 1
00009 6 2
00010 5 2
00011 3 2
00012 1 1
00013 1 1
00014 1 1
我的要求是根据销售总数量进行汇总,然后根据销售数量的排序(desc)一条一条往下加,当达到销售总数量40%时,次数>1的级别为A1,等于1的级别为A2,然后重新从下条开始累加,达到销售总数量36%时, 次数>1的级别为B1,等于1的级别为B2,达到销售总数量18%时, 次数>1的级别为C1,等于1的级别为C2,达到销售总数量6%时, 次数>1的级别为D1,等于1的级别为D2,
具体结果为:
itemcode 销售数量 次数 级别
00001 15 2 A1
00002 15 2 A1
00003 10 1 A2
00004 10 2 B1
00005 10 1 B2
00006 8 2 B1
00007 8 1 B2
00008 7 1 C2
00009 6 2 C1
00010 5 2 C1
00011 3 2 D1
00012 1 1 D2
00013 1 1 D2
00014 1 1 D2
itemcode 销售数量 次数 级别
00001 15 2
00002 15 2
00003 10 1
00004 10 2
00005 10 1
00006 8 2
00007 8 1
00008 7 1
00009 6 2
00010 5 2
00011 3 2
00012 1 1
00013 1 1
00014 1 1
我的要求是根据销售总数量进行汇总,然后根据销售数量的排序(desc)一条一条往下加,当达到销售总数量40%时,次数>1的级别为A1,等于1的级别为A2,然后重新从下条开始累加,达到销售总数量36%时, 次数>1的级别为B1,等于1的级别为B2,达到销售总数量18%时, 次数>1的级别为C1,等于1的级别为C2,达到销售总数量6%时, 次数>1的级别为D1,等于1的级别为D2,
具体结果为:
itemcode 销售数量 次数 级别
00001 15 2 A1
00002 15 2 A1
00003 10 1 A2
00004 10 2 B1
00005 10 1 B2
00006 8 2 B1
00007 8 1 B2
00008 7 1 C2
00009 6 2 C1
00010 5 2 C1
00011 3 2 D1
00012 1 1 D2
00013 1 1 D2
00014 1 1 D2
---------- ---------- ---------- ----
00001 15 2
00002 15 2
00003 10 1
00004 10 2
00005 10 1
00006 8 2
00007 8 1
00008 7 1
00009 6 2
00010 5 2
00011 3 2
00012 1 1
00013 1 1
00014 1 1 14 rows selectedSQL> with x as (select itemcode,sals,cs,sum(sals) over() sals_total,
2 sum(sals) over(order by rownum)/sum(sals) over() sals_bfb
3 from test
4 order by sals desc,itemcode)
5 select itemcode,sals,cs,
6 case when sals_bfb<=0.4 and cs=1 then 'A2'
7 when sals_bfb>0.4 and sals_bfb<=(0.4+0.36) and cs=1 then 'B2'
8 when sals_bfb>(0.4+0.36) and sals_bfb<=(0.4+0.36+0.18) and cs=1 then 'C2'
9 when sals_bfb>(0.4+0.36+0.18) and sals_bfb<=(0.4+0.36+0.18+0.06) and cs=1 then 'D2'
10 else (case when sals_bfb <= 0.4 then 'A1'
11 when sals_bfb > 0.4 and sals_bfb <= (0.4+0.36) then 'B1'
12 when sals_bfb>(0.4+0.36) and sals_bfb<=(0.4+0.36+0.18) then 'C1'
13 when sals_bfb>(0.4+0.36+0.18) and sals_bfb<=(0.4+0.36+0.18+0.06) then 'D1'
14 else 'E0'
15 end)
16 end JB
17 from x
18 /ITEMCODE SALS CS JB
---------- ---------- ---------- --
00001 15 2 A1
00002 15 2 A1
00003 10 1 A2
00004 10 2 B1
00005 10 1 B2
00006 8 2 B1
00007 8 1 B2
00008 7 1 C2
00009 6 2 C1
00010 5 2 C1
00011 3 2 D1
00012 1 1 D2
00013 1 1 D2
00014 1 1 D214 rows selectedSQL>
--楼主根据实际条件再修改一下:
SQL> select * from t_1123;ITEMCODE ITEMSUM ITEMCOUNT FLAG
-------- ------- --------- ----
00001 15 2
00002 15 2
00003 10 1
00004 10 2
00005 10 1
00006 8 2
00007 8 1
00008 7 1
00009 6 2
00010 5 2
00011 3 2
00012 1 1
00013 1 1
00014 1 1 14 rows selectedSQL>
SQL> create or replace procedure p_item_order as
2 itemcode t_1123.itemcode%type;
3 itemsum t_1123.itemsum%type;
4 itemcount t_1123.itemcount%type;
5 flag t_1123.flag%type;
6
7 cursor c1 is
8 select count(*) from t_1123;
9
10 cursor c2 is
11 select itemcode, itemsum, itemcount
12 from t_1123
13 where itemcode = (select min(itemcode)
14 from t_1123
15 where flag is null
16 or flag = '');
17
18 cursor c3 is
19 select sum(itemsum) from t_1123 where flag is not null;
20
21 v_t1123 number(4) := 0;
22 v_sum number(3) := 0;
23 v_count number(3) := 0;
24 v_itemsum number(3) := 0;
25 v_itemcode varchar2(5);
26
27 begin
28 open c1;
29 fetch c1
30 into v_t1123;
31 for i in 1 .. 3 loop
32 open c2;
33 fetch c2
34 into v_itemcode, v_sum, v_count;
35 open c3;
36 fetch c3
37 into v_itemsum;
38 if v_itemsum is null then
39 update t_1123 set flag = 'A1' where itemcode = v_itemcode;
40 end if;
41 if v_itemsum is not null then
42 update t_1123
43 set flag = 'A1'
44 where v_sum / v_itemsum >= 0.4
45 and v_count > 1
46 and itemcode = v_itemcode;
47 end if;
48 update t_1123
49 set flag = 'A2'
50 where v_sum / v_itemsum >= 0.2
51 and v_count = 1
52 and itemcode = v_itemcode;
53 commit;
54 close c2;
55 close c3;
56 end loop;
57 for i in 4 .. v_t1123 loop
58 open c2;
59 fetch c2
60 into v_itemcode, v_sum, v_count;
61 open c3;
62 fetch c3
63 into v_itemsum;
64 update t_1123
65 set flag = 'B1'
66 where v_sum / v_itemsum >= 0.36
67 and v_sum / v_itemsum < 0.4
68 and v_count > 1
69 and itemcode = v_itemcode;
70 update t_1123
71 set flag = 'B2'
72 where v_sum / v_itemsum >= 0.36
73 and v_sum / v_itemsum < 0.4
74 and v_count = 1
75 and itemcode = v_itemcode;
76 update t_1123
77 set flag = 'C1'
78 where v_sum / v_itemsum >= 0.18
79 and v_sum / v_itemsum < 0.36
80 and v_count > 1
81 and itemcode = v_itemcode;
82 update t_1123
83 set flag = 'C2'
84 where v_sum / v_itemsum >= 0.18
85 and v_sum / v_itemsum < 0.36
86 and v_count = 1
87 and itemcode = v_itemcode;
88 update t_1123
89 set flag = 'D1'
90 where v_sum / v_itemsum >= 0.06
91 and v_sum / v_itemsum < 0.18
92 and v_count > 1
93 and itemcode = v_itemcode;
94 update t_1123
95 set flag = 'D2'
96 where v_sum / v_itemsum >= 0.06
97 and v_sum / v_itemsum < 0.18
98 and v_count = 1
99 and itemcode = v_itemcode;
100
101 commit;
102
103 close c2;
104 close c3;
105 end loop;
106 close c1;
107 end;
108 /Procedure createdSQL> exec p_item_order;PL/SQL procedure successfully completedSQL> select * from t_1123;ITEMCODE ITEMSUM ITEMCOUNT FLAG
-------- ------- --------- ----
00001 15 2 A1
00002 15 2 A1
00003 10 1 A2
00004 10 2 C1
00005 10 1 C2
00006 8 2 D1
00007 8 1 D2
00008 7 1 D2
00009 6 2 D1
00010 5 2
00011 3 2
00012 1 1
00013 1 1
00014 1 1 14 rows selectedSQL>
我这种实现方式是把>40%的归入到下一档(36%)去了.
当达到销售总数量40%时,次数>1的级别为A1,等于1的级别为A2
非常感谢!
非常感谢!
2 set
3 jb = (select jb
4 from (
5 with x as (select itemcode,sals,cs,sum(sals) over() sals_total,
6 sum(sals) over(order by rownum)/sum(sals) over() sals_bfb
7 from test
8 order by sals desc,itemcode)
9 select itemcode,sals,cs,
10 case when sals_bfb<=0.4 and cs=1 then 'A2'
11 when sals_bfb>0.4 and sals_bfb<=(0.4+0.36) and cs=1 then 'B2'
12 when sals_bfb>(0.4+0.36) and sals_bfb<=(0.4+0.36+0.18) and cs=1 then 'C2'
13 when sals_bfb>(0.4+0.36+0.18) and sals_bfb<=(0.4+0.36+0.18+0.06) and cs=1 then 'D2'
14 else (case when sals_bfb <= 0.4 then 'A1'
15 when sals_bfb > 0.4 and sals_bfb <= (0.4+0.36) then 'B1'
16 when sals_bfb>(0.4+0.36) and sals_bfb<=(0.4+0.36+0.18) then 'C1'
17 when sals_bfb>(0.4+0.36+0.18) and sals_bfb<=(0.4+0.36+0.18+0.06) then 'D1'
18 else 'E0'
19 end)
20 end JB
21 from x)t where t.itemcode = t1.itemcode)
22 /14 rows updated
SQL> SQL>
1 with x as (select itemcode,sals,cs,sum(sals) over() sals_total,
2 sum(sals) over(order by rownum)/sum(sals) over() sals_bfb
3 from test
4 order by sals desc,itemcode)
from (select itemcode,sals,cs,sum(sals) over() sals_total,
sum(sals) over(order by rownum)/sum(sals) over() sals_bfb
from test
order by sals desc,itemcode) x只不过放在前面先生成,后面的语句看的清楚点
--给楼主整出来了,如果原来有数据的话,可以删除flag列中的内容(字段比照一下即可):
delete from t_1123 where flag=null;
commit;SQL> select * from t_1123;ITEMCODE ITEMSUM ITEMCOUNT FLAG
-------- ------- --------- ----
00001 15 2
00002 15 2
00003 10 1
00004 10 2
00005 10 1
00006 8 2
00007 8 1
00008 7 1
00009 6 2
00010 5 2
00011 3 2
00012 1 1
00013 1 1
00014 1 1 14 rows selected
SQL>
SQL> create or replace procedure p_item_order as
2 itemcode t_1123.itemcode%type;
3 itemsum t_1123.itemsum%type;
4 itemcount t_1123.itemcount%type;
5 flag t_1123.flag%type;
6
7 cursor c1 is
8 select count(*) from t_1123;
9
10 cursor c2 is
11 select itemcode, itemsum, itemcount
12 from t_1123
13 where itemcode = (select min(itemcode)
14 from t_1123
15 where flag is null
16 or flag = '');
17
18 --cursor c3 is
19 -- select sum(itemsum) from t_1123 where flag is not null;
20
21 cursor c4 is
22 select sum(itemsum) from t_1123;
23
24 v_t1123 number(4) := 0;
25 v_sum number(3) := 0;
26 v_count number(3) := 0;
27 v_itemsum number(3) := 0;
28 v_itemcode varchar2(5);
29 v_temp number(4) := 0;
30 v_item_temp number(4) := 0;
31
32 begin
33 open c1;
34 fetch c1
35 into v_t1123;
36 open c4;
37 fetch c4
38 into v_item_temp;
39
40 for i in 1 .. v_t1123 loop
41 open c2;
42 fetch c2
43 into v_itemcode, v_sum, v_count;
44 v_temp := v_temp + v_sum;
45 while v_temp / v_item_temp <= 0.4 loop
46 update t_1123
47 set flag = 'A1'
48 where v_count > 1
49 and itemcode = v_itemcode
50 and flag is null;
51 update t_1123
52 set flag = 'A2'
53 where v_count = 1
54 and itemcode = v_itemcode
55 and flag is null;
56 close c2;
57 open c2;
58 fetch c2
59 into v_itemcode, v_sum, v_count;
60 v_temp := v_temp + v_sum;
61 end loop;
62 commit;
63
64 while v_temp / v_item_temp > 0.4 and v_temp / v_item_temp <= 0.76 loop
65 update t_1123
66 set flag = 'B1'
67 where v_count > 1
68 and itemcode = v_itemcode
69 and flag is null;
70 update t_1123
71 set flag = 'B2'
72 where v_count = 1
73 and itemcode = v_itemcode
74 and flag is null;
75 close c2;
76 open c2;
77 fetch c2
78 into v_itemcode, v_sum, v_count;
79 v_temp := v_temp + v_sum;
80 end loop;
81 commit;
82
83 while v_temp / v_item_temp > 0.76 and v_temp / v_item_temp <= 0.94 loop
84 update t_1123
85 set flag = 'C1'
86 where v_count > 1
87 and itemcode = v_itemcode
88 and flag is null;
89 update t_1123
90 set flag = 'C2'
91 where v_count = 1
92 and itemcode = v_itemcode
93 and flag is null;
94 close c2;
95 open c2;
96 fetch c2
97 into v_itemcode, v_sum, v_count;
98 v_temp := v_temp + v_sum;
99 end loop;
100 commit;
101
102 while v_temp / v_item_temp > 0.96 and v_temp / v_item_temp <= 1 loop
103 update t_1123
104 set flag = 'D1'
105 where v_count > 1
106 and itemcode = v_itemcode
107 and flag is null;
108 update t_1123
109 set flag = 'D2'
110 where v_count = 1
111 and itemcode = v_itemcode
112 and flag is null;
113 close c2;
114 open c2;
115 fetch c2
116 into v_itemcode, v_sum, v_count;
117 v_temp := v_temp + v_sum;
118 end loop;
119 commit;
120 close c2;
121 -- close c3;
122 end loop;
123 close c1;
124 close c4;
125 end;
126 /Procedure createdSQL> exec p_item_order;PL/SQL procedure successfully completed
SQL> select * from t_1123;ITEMCODE ITEMSUM ITEMCOUNT FLAG
-------- ------- --------- ----
00001 15 2 A1
00002 15 2 A1
00003 10 1 A2
00004 10 2 B1
00005 10 1 B2
00006 8 2 B1
00007 8 1 B2
00008 7 1 C2
00009 6 2 C1
00010 5 2 C1
00011 3 2 D1
00012 1 1 D2
00013 1 1 D2
00014 1 1 D214 rows selectedSQL>
2.对不起,第一行的delete写错了不用。FLAG列有数据时,使用update t_1123 set flag=null; 以使此列数据初始化;
3.感谢高手使此贴加精,本人同样感动中......
SALS,
CS,
CASE
WHEN SALS_BFB <= 0.4 THEN
DECODE(CS, 1, 'A2', 'A1')
WHEN SALS_BFB > 0.4 AND SALS_BFB <= (0.4 + 0.36) THEN
DECODE(CS, 1, 'B2', 'B1')
WHEN SALS_BFB > (0.4 + 0.36) AND SALS_BFB <= (0.4 + 0.36 + 0.18) THEN
DECODE(CS, 1, 'C2', 'C1')
ELSE
DECODE(CS, 1, 'D2', 'D1')
END JB
FROM (SELECT ITEMCODE,
SALS,
CS,
SUM(SALS) OVER() SALS_TOTAL,
SUM(SALS) OVER(ORDER BY ROWNUM) / SUM(SALS) OVER() SALS_BFB
FROM TEST
ORDER BY SALS DESC, ITEMCODE);
2000写的
SELECT t1.id, Table_1_1.SALS,
CASE WHEN t1.c1 <= 0.4 THEN 'A' WHEN t1.c1 <= 0.4 + 0.36 THEN 'B' WHEN t1.c1 <= 0.4 + 0.36 + 0.18 THEN 'c' ELSE 'D' END + CASE Table_1_1.csjb WHEN
1 THEN '2' ELSE '1' END AS Expr2
FROM (SELECT CAST(a_1.Expr1 AS float) / b_1.Expr1 AS c1, a_1.id
FROM (SELECT SUM(b.SALS) AS Expr1, a.id
FROM Table_1 AS a LEFT OUTER JOIN
Table_1 AS b ON a.id >= b.id
GROUP BY a.id) AS a_1 CROSS JOIN
(SELECT SUM(SALS) AS Expr1
FROM Table_1) AS b_1) AS t1 INNER JOIN
Table_1 AS Table_1_1 ON Table_1_1.id = t1.id
PS:
我的目标是 ----> ^_^
SELECT
Y.*,
level_prefix || to_char(DENSE_RANK() over(PARTITION BY table_name,level_prefix ORDER BY AVG_COL_LEN))
FROM
(
SELECT x.*,
(CASE WHEN accumulative_percent<=0.4 THEN 'A'
WHEN accumulative_percent>0.4 AND accumulative_percent<=(0.4+0.36) THEN 'B'
WHEN accumulative_percent>(0.4+0.36) AND accumulative_percent<=(0.4+0.36 +0.18) THEN 'C'
WHEN accumulative_percent>(0.4+0.36+0.18) THEN 'D'
END) level_prefix
FROM
(
SELECT
table_name,column_name,num_distinct,AVG_COL_LEN,
row_number() over(PARTITION BY table_name ORDER BY num_distinct DESC),
SUM(num_distinct) over(PARTITION BY table_name ORDER BY num_distinct DESC ROWS UNBOUNDED PRECEDING),
SUM(num_distinct) over(PARTITION BY table_name),
SUM(num_distinct) over(PARTITION BY table_name ORDER BY num_distinct DESC ROWS UNBOUNDED PRECEDING)/
SUM(num_distinct) over(PARTITION BY table_name) accumulative_percent
FROM user_tab_col_statistics
WHERE table_name='CIRCUIT'
) x
) Y
对应实现你的需求为如下语句:SELECT
Y.*,
level_prefix || to_char(DENSE_RANK() over(PARTITION BY level_prefix ORDER BY 次数))
FROM
(
SELECT x.*,
(CASE WHEN accumulative_percent<=0.4 THEN 'A'
WHEN accumulative_percent>0.4 AND accumulative_percent<=(0.4+0.36) THEN 'B'
WHEN accumulative_percent>(0.4+0.36) AND accumulative_percent<=(0.4+0.36 +0.18) THEN 'C'
WHEN accumulative_percent>(0.4+0.36+0.18) THEN 'D'
END) level_prefix
FROM
(
SELECT
itemcode,销售数量,次数,
row_number() over(ORDER BY 销售数量 DESC),
SUM(销售数量) over(ORDER BY 销售数量 DESC ROWS UNBOUNDED PRECEDING),
SUM(销售数量) over(),
SUM(销售数量) over(ORDER BY 销售数量 DESC ROWS UNBOUNDED PRECEDING)/SUM(销售数量) over() accumulative_percent
FROM your_table
) x
) Y
什么意思啊
......
end loop;
工作经验5年以上,年令30以上
select '00001' itemcode, 15 qty, 2 times from dual union all
select '00002' itemcode, 15 qty, 2 times from dual union all
select '00003' itemcode, 10 qty, 1 times from dual union all
select '00004' itemcode, 10 qty, 2 times from dual union all
select '00005' itemcode, 10 qty, 1 times from dual union all
select '00006' itemcode, 8 qty, 2 times from dual union all
select '00007' itemcode, 8 qty, 1 times from dual union all
select '00008' itemcode, 7 qty, 1 times from dual union all
select '00009' itemcode, 6 qty, 2 times from dual union all
select '00010' itemcode, 5 qty, 2 times from dual union all
select '00011' itemcode, 3 qty, 2 times from dual union all
select '00012' itemcode, 1 qty, 1 times from dual union all
select '00013' itemcode, 1 qty, 1 times from dual union all
select '00014' itemcode, 1 qty, 1 times from dual)
--以上为测试数据
,t1 as(select tt.*,sum(qty)over(order by qty desc,itemcode)/sum(qty)over()s from tt)
select itemcode,qty,times,
case when s<=0.4 then 'A'
when s<=0.76 then 'B'
when s<=0.94 then 'C'
else 'D' end||decode(times,1,'2','1') "LEVEL"
from t1;
下面用sql方式实现:
1、建表
create table giti_test
(
itemcode varchar2(10),
sale_qty number,
sale_time number,
grade varchar2(10)
);
2、插入数据,自己随意
3、编写sql程序
SELECT T3.ITEMCODE,
T3.SALE_QTY,
T3.SALE_TIME,
CASE
WHEN T3.GRADE_FLAG=1 THEN DECODE(T3.SALE_TIME,1,'A2','A1')
WHEN T3.GRADE_FLAG=2 THEN DECODE(T3.SALE_TIME,1,'B2','B1')
WHEN T3.GRADE_FLAG=3 THEN DECODE(T3.SALE_TIME,1,'C2','C1')
WHEN T3.GRADE_FLAG=4 THEN DECODE(T3.SALE_TIME,1,'D2','D1')
END AS GRADE_NEW,
T3.GRADE
FROM (
SELECT T2.ITEMCODE,
T2.SALE_QTY,
T2.SALE_TIME,
T2.SUM_QTY,
T2.SSS_QTY,
T2.P40,
T2.P36,
T2.P18,
T2.P6,
CASE WHEN T2.SUM_QTY<=T2.P40 OR T2.SSS_QTY-T2.SALE_QTY<T2.P40 THEN 1
WHEN T2.SUM_QTY>T2.P40 AND (T2.SUM_QTY<=T2.P36 OR T2.SSS_QTY-T2.SALE_QTY<T2.P36) THEN 2
WHEN T2.SUM_QTY>T2.P36 AND (T2.SUM_QTY<=T2.P18 OR T2.SSS_QTY-T2.SALE_QTY<T2.P18) THEN 3
WHEN T2.SUM_QTY>T2.P18 AND (T2.SUM_QTY<=T2.P6 OR T2.SSS_QTY-T2.SALE_QTY<T2.P6) THEN 4
ELSE 0
END AS GRADE_FLAG,
T2.GRADE
FROM (
SELECT T1.ITEMCODE,
T1.SALE_QTY,
T1.SALE_TIME,
T1.SUM_QTY,
SUM(T1.SUM_QTY-T1.SALE_QTY) OVER(PARTITION BY 1 ORDER BY T1.SUM_QTY) SSS_QTY,
T1.TOTAL_QTY*40/100 P40,
T1.TOTAL_QTY*(40+36)/100 P36,
T1.TOTAL_QTY*(40+36+18)/100 P18,
T1.TOTAL_QTY*(40+36+18+6)/100 P6,
T1.GRADE
FROM (SELECT T.ITEMCODE,
T.SALE_QTY,
T.SALE_TIME,
SUM(T.SALE_QTY) OVER(PARTITION BY 1 ORDER BY T.SALE_QTY DESC, T.ITEMCODE) SUM_QTY,
SUM(T.SALE_QTY) OVER(PARTITION BY 1) TOTAL_QTY,
T.GRADE
FROM GITI_TEST T
ORDER BY T.SALE_QTY DESC, T.ITEMCODE) T1) T2) T3
update SALE s1
set leve=(
select (CASE
WHEN PreCent <= 40 THEN
(CASE
WHEN times > 1 THEN
'A1'
WHEN times = 1 THEN
'A2'
END) WHEN PreCent <= 76 THEN(CASE
WHEN times > 1 THEN
'B1'
WHEN times = 1 THEN
'B2'
END) WHEN PreCent <= 94 THEN(CASE
WHEN times > 1 THEN
'C1'
WHEN times = 1 THEN
'C2'
END) WHEN PreCent <= 100 THEN(CASE
WHEN times > 1 THEN
'D1'
WHEN times = 1 THEN
'D2'
END) END) LEVE
from (select ItemCode,
SalNum,
times,
sum(SalNum) over(order by SalNum desc, ItemCode) LineSum, --连续总销售和,
sum(SalNum) over() AllSum, --销售总和,
100 * ROUND(sum(SalNum) over(order by SalNum desc, ItemCode) /
sum(SalNum) over(),
4) PreCent --所占总份额%
from sale) s2
where s1.itemcode=s2.itemCode)