我的表结构是这样的
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
------解决方案--------------------
使用Oracle的分析函数（over,dense）。
------解决方案--------------------
这可行吗?
如果前面不是正好的40%，36%，比如41%，36%，18%，5%，那5%的部分将永远不可能被分类了
------解决方案--------------------
这种需求用SQL能做,用程序写吧.
------解决方案--------------------
这个要用存储过程去实现了
------解决方案--------------------
直接使用分析函数肯定能做出来，但要累伤人的。
还上赞同使用存贮过程。
------解决方案--------------------
------解决方案--------------------
條件有點多
------解决方案--------------------
- SQL code
-- 好象也不复杂啊,发现规律就好办SQL> select * from test;ITEMCODE SALS CS JB---------- ---------- ---------- ----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 A100002 15 2 A100003 10 1 A200004 10 2 B100005 10 1 B200006 8 2 B100007 8 1 B200008 7 1 C200009 6 2 C100010 5 2 C100011 3 2 D100012 1 1 D200013 1 1 D200014 1 1 D214 rows selectedSQL>
------解决方案--------------------
- SQL code
--楼主根据实际条件再修改一下：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 A100002 15 2 A100003 10 1 A200004 10 2 C100005 10 1 C200006 8 2 D100007 8 1 D200008 7 1 D200009 6 2 D100010 5 2 00011 3 2 00012 1 1 00013 1 1 00014 1 1 14 rows selectedSQL>
------解决方案--------------------
可能今天俺脑子不好使了，总共才100的数量，15也只占总数量的15％，为什么第一条记录是A1？呵呵...
当达到销售总数量40%时,次数>1的级别为A1,等于1的级别为A2
------解决方案--------------------
楼主对４０％的界定不清楚，要看他的需求了。
顺便修正一下我９楼的SQL，把第二行的order by rownum 改为order by itemcode
------解决方案--------------------
- SQL code
--- 更新表，将上面代码套上就行了啊．SQL> update test t1 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 updatedSQL>
------解决方案--------------------
------解决方案--------------------
先顶了再说。。。
------解决方案--------------------
顶
------解决方案--------------------
1.LZ具体操作时，可使用ITEMCODE排好序的数据，如没有，则新建一下；
2.对不起，第一行的delete写错了不用。FLAG列有数据时，使用update t_1123 set flag=null; 以使此列数据初始化；
3.感谢高手使此贴加精，本人同样感动中......
------解决方案--------------------
oracle ,实现，学习下？
------解决方案--------------------
都是强人，佩服。学习中~~~
------解决方案--------------------
可以用程序实现！
------解决方案--------------------
------解决方案--------------------
学习！
------解决方案--------------------
SELECT ITEMCODE,
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);
------解决方案--------------------
如果就数据量少的话,比如只有两条数据的话,按照这种分是不可能统计出级别的,
还有就是销售数量相差太大的话,也可能按照楼主这种分是不可能统计出的!!
------解决方案--------------------
mark
------解决方案--------------------
mark
------解决方案--------------------
我靠，怎么这么多！
------解决方案--------------------
噢，好贴
------解决方案--------------------
写的不好，大家见笑了
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
------解决方案--------------------
高手，学习了！
------解决方案--------------------
受打击了,什么时候有这种水平
------解决方案--------------------
受非常嚴懲的打擊，你們都是強人俺幾時才能達這種水平
------解决方案--------------------
MARK
PS:
我的目标是 ---->
^_^
------解决方案--------------------
MARK
------解决方案--------------------
用移动窗口分析函数写了一下,基于数据字典表进行的测试,如下:就你的需求来看,partition by可以免去
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