select * from (select * from CUST_INFO order by dbms_random.value()) where rownum<=100
这个那就只能先用 select ceil(count(*)*0.1) from CUST_INFO where prov='xxxx' 获取某一个省份10%数据的条数,然后利用 select * from (select * from CUST_INFO where prov='xxxx' order by dbms_random.value()) where rownum<=100(假定的条数) 来一个一个取省份数据
搞定了,with ta as (select * from cust_info order by prov), tb as (select rownum 行编号, ta.* from ta), tc as (select prov, count(1) 客户数, min(行编号) 开始行 from tb group by area_name)select rownum, tb.*, tc.* from tb, tc where tb.prov = tc.prov and 行编号 < 开始行 + 客户数 * 0.1
搞定了,with ta as (select * from cust_info order by prov), tb as (select rownum 行编号, ta.* from ta), tc as (select prov, count(1) 客户数, min(行编号) 开始行 from tb group by prov)select rownum, tb.*, tc.* from tb, tc where tb.prov = tc.prov and 行编号 < 开始行 + 客户数 * 0.1
可以修改ta,其它不变, with ta as (select * from cust_info order by prov,dbms_random.value() ),
with mm as (select prov,count(*) mnt from CUST_INFO group by prov) select b.* from ( select a.*,row_number()over(partition by prov order by dbms_random.value()) rn from CUST_INFO a ) b,mm where b.prov=mm.prov and b.rn<mm.mnt
这样更简单了select * from (select S.*, row_number() over(partition by prov order by dbms_random.value()) c_no, count(1) over(partition by prov) c_count from cust_info S) where c_no < c_count * 0.1
select * from (select S.*, PERCENT_RANK() OVER(partition by prov order by dbms_random.value()) p_rank from cust_info S) where c_no <= c_count * 0.1还有更简单的
select * from (select S.*, PERCENT_RANK() OVER(partition by prov order by dbms_random.value()) p_rank from cust_info S) where p_rank<=0.1
select * from (select * from CUST_INFO order by dbms_random.value()) where rownum<=100
select ceil(count(*)*0.1) from CUST_INFO where prov='xxxx'
获取某一个省份10%数据的条数,然后利用
select * from (select * from CUST_INFO where prov='xxxx' order by dbms_random.value()) where rownum<=100(假定的条数)
来一个一个取省份数据
(select * from cust_info order by prov),
tb as
(select rownum 行编号, ta.* from ta),
tc as
(select prov, count(1) 客户数, min(行编号) 开始行 from tb group by area_name)select rownum, tb.*, tc.*
from tb, tc
where tb.prov = tc.prov
and 行编号 < 开始行 + 客户数 * 0.1
搞定了,with ta as
(select * from cust_info order by prov),
tb as
(select rownum 行编号, ta.* from ta),
tc as
(select prov, count(1) 客户数, min(行编号) 开始行 from tb group by prov)select rownum, tb.*, tc.*
from tb, tc
where tb.prov = tc.prov
and 行编号 < 开始行 + 客户数 * 0.1
可以修改ta,其它不变,
with ta as
(select * from cust_info order by prov,dbms_random.value() ),
select b.* from (
select a.*,row_number()over(partition by prov order by dbms_random.value()) rn from CUST_INFO a
) b,mm where b.prov=mm.prov and b.rn<mm.mnt
取前10%,需要count(1)*0.1 mmt
基本正解,非常感谢。
这个函数row_number()我用的不熟。
取前10%,需要count(1)*0.1 mmt
基本正解,非常感谢。
这个函数row_number()我用的不熟。嗯,我忘了乘10%了
from (select S.*,
row_number() over(partition by prov order by dbms_random.value()) c_no,
count(1) over(partition by prov) c_count
from cust_info S)
where c_no < c_count * 0.1
from (select S.*,
PERCENT_RANK() OVER(partition by prov order by dbms_random.value()) p_rank
from cust_info S)
where c_no <= c_count * 0.1还有更简单的
from (select S.*,
PERCENT_RANK() OVER(partition by prov order by dbms_random.value()) p_rank
from cust_info S)
where p_rank<=0.1