with t as(select rownum n from dual connect by level<=11)select n from (select n, ntile(1 / 0.2) over(order by dbms_random.random) rn from t) where rn = 1
按 code分组,分析函数垓下ntile(5)over(partition by code order by .......)
WITH TEMP AS ( SELECT '移动号卡10元' NAME,'0101' CODE FROM DUAL UNION SELECT '移动号卡20元' NAME,'0101' CODE FROM DUAL UNION SELECT '移动号卡30元' NAME,'0101' CODE FROM DUAL UNION SELECT '移动号卡40元' NAME,'0101' CODE FROM DUAL UNION SELECT '联通号卡10元' NAME,'0102' CODE FROM DUAL UNION SELECT '联通号卡20元' NAME,'0102' CODE FROM DUAL UNION SELECT '联通号卡50元' NAME,'0102' CODE FROM DUAL UNION SELECT '联通号卡100元' NAME,'0102' CODE FROM DUAL UNION SELECT 'QQ游戏点卡10元' NAME,'0103' CODE FROM DUAL UNION SELECT 'QQ游戏点卡20元' NAME,'0103' CODE FROM DUAL UNION SELECT 'QQ游戏点卡30元' NAME,'0103' CODE FROM DUAL UNION SELECT 'QQ游戏点卡40元' NAME,'0103' CODE FROM DUAL UNION SELECT 'QQ游戏点卡50元' NAME,'0103' CODE FROM DUAL UNION SELECT 'QQ游戏点卡60元' NAME,'0103' CODE FROM DUAL )select t1.code,t1.name from (select CODE,NAME,row_number() over(partition by CODE order by CODE) rm from temp group by code,name order by 1,2) t1, (select code,ceil(max(rm)*0.2) cnt from (select CODE,NAME,row_number() over(partition by CODE order by CODE) rm from temp group by code,name order by 1,2) group by code) t2 where t1.code=t2.code and t1.rm<=t2.cnt order by 1,2 这有一个写法,但不是最优的~
select * from (select ntile(5) over(partition by code order by rownum) ntile from tt) where ntile=1ntile(5) 随机分5组
自己解决: Select * from (select CODE,NAME,row_number() over(partition by CODE order by CODE) t1,count(*) over partition by CODE) t2) where t1<=ceil(t2*0.2)
from (select n, ntile(1 / 0.2) over(order by dbms_random.random) rn from t)
where rn = 1
WITH TEMP AS
(
SELECT '移动号卡10元' NAME,'0101' CODE FROM DUAL
UNION
SELECT '移动号卡20元' NAME,'0101' CODE FROM DUAL
UNION
SELECT '移动号卡30元' NAME,'0101' CODE FROM DUAL
UNION
SELECT '移动号卡40元' NAME,'0101' CODE FROM DUAL
UNION
SELECT '联通号卡10元' NAME,'0102' CODE FROM DUAL
UNION
SELECT '联通号卡20元' NAME,'0102' CODE FROM DUAL
UNION
SELECT '联通号卡50元' NAME,'0102' CODE FROM DUAL
UNION
SELECT '联通号卡100元' NAME,'0102' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡10元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡20元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡30元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡40元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡50元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡60元' NAME,'0103' CODE FROM DUAL
)select t1.code,t1.name from
(select CODE,NAME,row_number() over(partition by CODE order by CODE) rm from temp group by code,name order by 1,2) t1,
(select code,ceil(max(rm)*0.2) cnt from
(select CODE,NAME,row_number() over(partition by CODE order by CODE) rm from temp group by code,name order by 1,2)
group by code) t2
where t1.code=t2.code and t1.rm<=t2.cnt
order by 1,2
这有一个写法,但不是最优的~
select * from
(select
ntile(5) over(partition by code order by rownum) ntile
from tt)
where ntile=1ntile(5) 随机分5组
Select * from
(select CODE,NAME,row_number() over(partition by CODE order by CODE) t1,count(*) over partition by CODE) t2) where t1<=ceil(t2*0.2)