求助，oracle 怎样先分组然后取数据的10%

[qq_36620445]

假如我有一张人员信息表 reginfo 里面有科室字段 deptcode，时间字段 regdate
怎么 取某个时间段内 按照 deptcode 进行分组 ，然后取每个科室10%的人员, 10%随便取，随机也行，前10% 后10% 都可以
求助各位大神

[Wazy_csdn]

可以做一个小小的转换，楼主其实是要获取每个科室的10%的人员信息吧，拿SCOTT用户下的EMP表做示范，写一个test：

declare 
  -- Local variables here
  xempno varchar2(10);
  xename varchar2(10);
  xdeptno varchar2(10);
begin
  --首先創建一個表，用來裝每個科室的10%的人員，我這裡創建的是T1
  for r1 in (select deptno,round(count(empno) * 0.1) qty
          from emp a
         where a.hiredate between to_date('xxxxxxxx', 'yyyy/mm/dd') and
               to_date('zzzzzzzz', 'yyyy/mm/dd')
         group by deptno)
     loop
         select empno,ename,deptno into xempno,xename,xdeptno  from emp where deptno = r1.deptno
         and rownum <=r1.qty;
         
         insert into t1
         values(xempno,xename,xdeptno);
         commit;
     end loop;
end;

接着查询T1表就好了

[nayi_224]

写过程就太麻烦了，直接一条sql搞定。

select *
  from (select t1.*,
               percent_rank() over(partition by deptcode order by ora_hash(t1.rowid || trunc(dbms_random.value * 10000), 10000)) pp
          from reginfo t1
         where t1.regdate between trunc(sysdate, 'y') and sysdate
          ) t1
 where t1.pp < 0.1;

[象话]

说一下思路，首先查询整张表的条目数， 然后 总条目数 * 0.1 = endNum; 然后就是分页，从第1条 取到 endNum条。

[nayi_224]

percent_rank或者row_number+count。 10%要是随机取得话，可以用ora_hash。

[不剪发的Tony老师]

1. 随机取10%：

WITH tmp AS (
  SELECT r.*, percent_rank() OVER (PARTITION BY deptcode ORDER BY dbms_random.value()) AS percent
    FROM reginfo r
   WHERE regdate BETWEEN to_date('2017-01-01', 'YYYY-MM-DD') AND to_date('2018-01-01', 'YYYY-MM-DD')
)
SELECT *
  FROM tmp
 WHERE tmp.percent <= 0.1;

2.取各个科室最早注册的 10%：

WITH tmp AS (
  SELECT r.*, percent_rank() OVER (PARTITION BY deptcode ORDER BY regdate) AS percent
    FROM reginfo r
   WHERE regdate BETWEEN to_date('2017-01-01', 'YYYY-MM-DD') AND to_date('2018-01-01', 'YYYY-MM-DD')
)
SELECT *
  FROM tmp
 WHERE tmp.percent <= 0.1;

3. 取各个科室最晚注册的 10%：

WITH tmp AS (
  SELECT r.*, percent_rank() OVER (PARTITION BY deptcode ORDER BY regdate DESC) AS percent
    FROM reginfo r
   WHERE regdate BETWEEN to_date('2017-01-01', 'YYYY-MM-DD') AND to_date('2018-01-01', 'YYYY-MM-DD')
)
SELECT *
  FROM tmp
 WHERE tmp.percent <= 0.1;

备注：把 r.* 改成表 reginfo 中需要显示的各个字段名称；percent_rank() 函数的文档：https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/PERCENT_RANK.html#GUID-66A868F5-9EBA-482A-BF8C-09300B9EE165

[大道无痕~]

simple

[alike001]

建立临时表，然后从里面select