表中每组数据随机抽取20%的SQL语句怎么写？

[sxwjl]

对表中每组数据随机抽取20%（如以code分组）的记录，即要求某一组只有1-5条记录的抽1条，6-10条的抽2条，11-15条的抽3条...，请问SQL语句怎么写？

[z123zjf]

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

[z123zjf]

按 code分组，分析函数垓下 ntile(5)over(partition by code order by .......)

[ghx287524027]

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

这有一个写法，但不是最优的~

[js14982]

select * from 
(select 
ntile(5) over(partition by  code order by rownum) ntile
from tt)
where ntile=1

ntile(5) 随机分5组

[sxwjl]

自己解决：
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)