查看: 6879|回复: 11

[PL/SQL] 【讨论】随机分组

[复制链接]
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
发表于 2015-1-15 10:48 | 显示全部楼层 |阅读模式
比如:每4个人一组,把14人随机分组,得到分组结果
思路一:利用集合实现
所有考生为一个集合1,随机排序后取4条数据为一组(集合2),从集合1中减掉集合2中的数据,那么集合1中剩下的就是还没有分配的人,再重复之前过程
但是遇到multiset except只能进行标量SQL类型比较的错误
思路二:分析函数+connect by 实现
先将所有人员随机排序,结果存入临时表中,再对结果进行处理
BEGIN
  --插入临时中间数据(随机排序)
  INSERT INTO TMP_EMP
    SELECT EMPNO, ENAME, ROWNUM RN
      FROM (SELECT * FROM T ORDER BY DBMS_RANDOM.VALUE);
  --插入分组结果
  INSERT INTO TEST
    SELECT TMP_EMP.*,
           LAST_VALUE(LV IGNORE NULLS) OVER(ORDER BY TMP_EMP.RN ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) LV_1
      FROM (SELECT TMP_EMP.*, LEVEL LV
              FROM TMP_EMP
             START WITH RN = 1
            CONNECT BY PRIOR RN + 4 = RN) T1
     RIGHT JOIN TMP_EMP
        ON (T1.RN = TMP_EMP.RN)
     ORDER BY TMP_EMP.RN;
END;


实际中可能有几万数据参与分配,可能每组25~30条数据,求助更好的实现方法~~~~

测试数据:
create table T
(
  EMPNO NUMBER(4) not null,
  ENAME VARCHAR2(10)
)


insert into t (EMPNO, ENAME) values (7369, 'SMITH');
insert into t (EMPNO, ENAME) values (7499, 'ALLEN');
insert into t (EMPNO, ENAME) values (7521, 'WARD');
insert into t (EMPNO, ENAME) values (7566, 'JONES');
insert into t (EMPNO, ENAME) values (7654, 'MARTIN');
insert into t (EMPNO, ENAME) values (7698, 'BLAKE');
insert into t (EMPNO, ENAME  values (7782, 'CLARK');
insert into t (EMPNO, ENAME) values (7788, 'SCOTT');
insert into t (EMPNO, ENAME) values (7839, 'KING');
insert into t (EMPNO, ENAME) values (7844, 'TURNER');
insert into t (EMPNO, ENAME) values (7876, 'ADAMS');
insert into t (EMPNO, ENAME) values (7900, 'JAMES');
insert into t (EMPNO, ENAME) values (7902, 'FORD');
insert into t (EMPNO, ENAME) values (7934, 'MILLER');




认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-1-15 10:53 | 显示全部楼层
加一个分组结果列,列的内容为 ceil(rownum/每组的人数)。拥有相同值的记录,即为同一个分组,不同的值,表示不同的组号。

使用道具 举报

回复
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2015-1-15 11:05 | 显示全部楼层
bfc99 发表于 2015-1-15 10:53
加一个分组结果列,列的内容为 ceil(rownum/每组的人数)。拥有相同值的记录,即为同一个分组,不同的值,表 ...

能避免 ORDER BY DBMS_RANDOM.VALUE 吗?

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-1-15 11:13 | 显示全部楼层
四十五度向上 发表于 2015-1-15 11:05
能避免 ORDER BY DBMS_RANDOM.VALUE 吗?

我的意思是说你可以将记录按你期望的方法进行排序,对排序结果,用ceil(rownum/每组的人数)来分组。

使用道具 举报

回复
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2015-1-15 11:16 | 显示全部楼层
bfc99 发表于 2015-1-15 11:13
我的意思是说你可以将记录按你期望的方法进行排序,对排序结果,用ceil(rownum/每组的人数)来分组。

恩,我了解你的意思
我想问的是,这个需求能不能用其他方式达到随机的效果,不用 order by dbms_random.value

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-1-15 11:23 | 显示全部楼层
四十五度向上 发表于 2015-1-15 11:16
恩,我了解你的意思
我想问的是,这个需求能不能用其他方式达到随机的效果,不用 order by dbms_rando ...

我觉得用dbms_random可以确保每次的“随机”,用其它一些办法来保证每次“随机”的效果可能更复杂,或者说得不偿失。

所以,不知道你想不用dbms_random的原因是什么?

使用道具 举报

回复
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2015-1-15 11:26 | 显示全部楼层
bfc99 发表于 2015-1-15 11:23
我觉得用dbms_random可以确保每次的“随机”,用其它一些办法来保证每次“随机”的效果可能更复杂,或者说 ...

因为前辈给我说过,order by dbms_random.value 会很慢,特别是数据量比较大的情况下
不知道几万数据这么排序会不会影响效率,还没有在正式环境中试验过

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-1-15 11:32 | 显示全部楼层
四十五度向上 发表于 2015-1-15 11:26
因为前辈给我说过,order by dbms_random.value 会很慢,特别是数据量比较大的情况下
不知道几万数据这么 ...

SQL>create table demo0115 as select * from dba_objects;

Table created.

Elapsed: 00:00:01.26
SQL>set autotrace off
SQL>select count(*) from demo0115;

  COUNT(*)
----------
    130849

Elapsed: 00:00:00.09
SQL>set autotrace traceonly
SQL>select * from demo0115 order by dbms_random.value;

130849 rows selected.

Elapsed: 00:00:02.19

Execution Plan
----------------------------------------------------------
Plan hash value: 140725247

---------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |   117K|    23M|       |  5844        (1)| 00:01:11 |
|   1 |  SORT ORDER BY           |              |   117K|    23M|    27M|  5844        (1)| 00:01:11 |
|   2 |   TABLE ACCESS FULL| DEMO0115 |   117K|    23M|       |   559        (1)| 00:00:07 |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        371  recursive calls
          0  db block gets
       2383  consistent gets
         17  physical reads
          0  redo size
   13884331  bytes sent via SQL*Net to client
      96477  bytes received via SQL*Net from client
       8725  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
     130849  rows processed



SQL>select * from demo0115 order by object_id;

130849 rows selected.

Elapsed: 00:00:01.29

Execution Plan
----------------------------------------------------------
Plan hash value: 140725247

---------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |   117K|    23M|       |  5844        (1)| 00:01:11 |
|   1 |  SORT ORDER BY           |              |   117K|    23M|    27M|  5844        (1)| 00:01:11 |
|   2 |   TABLE ACCESS FULL| DEMO0115 |   117K|    23M|       |   559        (1)| 00:00:07 |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       2077  consistent gets
          0  physical reads
          0  redo size
    6535475  bytes sent via SQL*Net to client
      96477  bytes received via SQL*Net from client
       8725  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     130849  rows processed

是要慢一些,但要看需求是否可以接受。比如我上面的实验,13万数据,2秒多出结果。如果不用dbms_random,用表上的其它列排序,是1秒多。

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
11
ITPUB社区千里马徽章
日期:2013-06-09 10:15:34懒羊羊
日期:2015-03-04 14:52:11懒羊羊
日期:2015-02-10 13:36:05马上有对象
日期:2015-02-02 12:29:02红宝石
日期:2015-01-19 09:44:10马上有车
日期:2014-11-11 14:16:07马上有车
日期:2014-03-27 15:59:39优秀写手
日期:2014-03-12 06:00:13马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09
发表于 2015-1-15 11:56 | 显示全部楼层
除了DBMS_RANDOM.VALUE,我想不出。

使用道具 举报

回复
论坛徽章:
310
生肖徽章:鼠
日期:2014-08-07 19:59:00生肖徽章:牛
日期:2015-01-14 17:40:00生肖徽章:虎
日期:2014-11-05 19:45:02生肖徽章:兔
日期:2015-03-05 07:52:09生肖徽章:龙
日期:2015-03-05 07:53:59生肖徽章:蛇
日期:2014-08-28 11:56:51生肖徽章:马
日期:2014-11-04 20:19:57生肖徽章:羊
日期:2015-01-09 15:48:14生肖徽章:猴
日期:2015-02-04 16:57:45生肖徽章:鸡
日期:2014-09-05 20:35:13
 楼主| 发表于 2015-1-15 17:23 | 显示全部楼层
bfc99 发表于 2015-1-15 11:32
SQL>create table demo0115 as select * from dba_objects;

Table created.

了解了,谢谢

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表