请教一个按百分率提取数据的问题

显示全部楼层 · 2010-10-8 09:32:26

目的：T3,两个字段，分别为类型和名称，类型分1，2，3，现需要随机取表中记录，分别按类型取出50%:50%:50%的数据
SQL> select id,name from t3 group by (id,name);
ID NAME
---------- ----------
1 a
1 d
2 b
2 e
2 f
2 g
3 c
3 h
3 k
3 l
10 rows selected.
SQL> exec dbms_stats.gather_table_stats('HR','t3'); 重新分析表统计信息
select * from t3 sample(50) where id = 1
union
select * from t3 sample(50) where id = 2
union
select * from t3 sample(50) where id = 3;
ID NAME
---------- ----------
1 a
2 f
3 c
3 h
SQL> /
ID NAME
---------- ----------
1 a
1 d
2 b
2 e
2 g
3 k
但这个数据提取语句就是达不到我想要的结果，不知道为什么？或者还有别的好的方式吗？谢谢啦！

千问 · 2010-10-8 09:32:26

这样可以吗？不过也是个近似值，
如果id分组个数是奇数的话，不是50%了
WITH t AS
(SELECT1 ID, 'a' NAME FROM dual
UNION ALL
SELECT 1, 'd' FROM dual
UNION ALL
SELECT 2, 'b' FROM dual
UNION ALL
SELECT 2, 'e' FROM dual
UNION ALL
SELECT 2, 'f' FROM dual
UNION ALL
SELECT 2, 'g' FROM dual
UNION ALL
SELECT 3, 'c' FROM dual
UNION ALL
SELECT 3, 'h' FROM dual
UNION ALL
SELECT 3, 'k' FROM dual
UNION ALL
SELECT 3, 'l' FROM dual)
SELECT *
FROM
(
SELECT ID,NAME,
ntile(2)over(PARTITION BY ID ORDER BY NAME) flag
FROM t
)
WHERE flag = 1

千问 · 2010-10-8 09:32:26

with a as
(select 1 id, 'a' name from dual
union all
select 1, 'd' from dual
union all
select 2, 'b' from dual
union all
select 2, 'e' from dual
union all
select 2, 'f' from dual
union all
select 2, 'g' from dual
union all
select 3, 'c' from dual
union all
select 3, 'h' from dual
union all
select 3, 'k' from dual
union all
select 3, 'l' from dual)
select id,name from (
select id,name,row_number()over(partition by id order by id) rn from a) where mod(rn,2)=1 ; 这个也只能取大约50% 不知道满足不?
[ 本帖最后由 aihuong 于 2011-5-29 15:28 编辑 ]

千问 · 2010-10-8 09:32:26

WITH t AS
(SELECT1 ID, 'a' NAME FROM dual
UNION ALL
SELECT 1, 'd' FROM dual
UNION ALL
SELECT 2, 'b' FROM dual
UNION ALL
SELECT 2, 'e' FROM dual
UNION ALL
SELECT 2, 'f' FROM dual
UNION ALL
SELECT 2, 'g' FROM dual
UNION ALL
SELECT 3, 'c' FROM dual
UNION ALL
SELECT 3, 'h' FROM dual
UNION ALL
SELECT 3, 'k' FROM dual
UNION ALL
SELECT 3, 'l' FROM dual)
SELECT * FROM (
SELECT T1.ID,T1.NAME,T1.SAMP ,CUME_DIST() OVER (PARTITION BY T1.ID ORDER BY T1.ID , T1.SAMP ) PICT
FROM (
SELECT T.ID,T.NAME,DBMS_RANDOM.value SAMP
FROM T )
T1 )
WHERE PICT BETWEEN 0 AND 0.5

千问 · 2010-10-8 09:32:26

搞得太复杂了，怎么就没有人用分析函数呢

千问 · 2010-10-8 09:32:26

我用的就是分析函数

千问 · 2010-10-8 09:32:26

这样行不?
dw@dw>WITH t AS
2(SELECT1 ID, 'a' NAME FROM dual
3UNION ALL
4SELECT 1, 'd' FROM dual
5UNION ALL
6SELECT 2, 'b' FROM dual
7UNION ALL
8SELECT 2, 'e' FROM dual
9UNION ALL
10SELECT 2, 'f' FROM dual
11UNION ALL
12SELECT 2, 'g' FROM dual
13UNION ALL
14SELECT 3, 'c' FROM dual
15UNION ALL
16SELECT 3, 'h' FROM dual
17UNION ALL
18SELECT 3, 'k' FROM dual
19UNION ALL
20SELECT 3, 'l' FROM dual)
21select * from (select t.*,CUME_DIST() over( partition by t.id order by dbms_random.value) cd from t) where cdWITH t AS
2(SELECT1 ID, 'a' NAME FROM dual
3UNION ALL
4SELECT 1, 'd' FROM dual
5UNION ALL
6SELECT 2, 'b' FROM dual
7UNION ALL
8SELECT 2, 'e' FROM dual
9UNION ALL
10SELECT 2, 'f' FROM dual
11UNION ALL
12SELECT 2, 'g' FROM dual
13UNION ALL
14SELECT 3, 'c' FROM dual
15UNION ALL
16SELECT 3, 'h' FROM dual
17UNION ALL
18SELECT 3, 'k' FROM dual
19UNION ALL
20SELECT 3, 'l' FROM dual)
21select * from (select t.*,CUME_DIST() over( partition by t.id order by dbms_random.value) cd from t) where cd

千问 · 2010-10-8 09:32:26

首先要保证数据是能取出50%的，如果就1行，取还是不取

千问 · 2010-10-8 09:32:26

学习，原来还有CUME_DIST() 这个分析函数