时间戳

首页 > 解决方案 > Oracle为每位员工创建随机行数

oracle - Oracle为每位员工创建随机行数

问题描述

有人可以提供一个解决方案，用随机的员工数量、随机的位置列表和随机日期填充 access_history 表。

我提供了一个详细的测试用例来生成随机的员工和位置记录。

我得到了 INSERT 工作，但它总是为每个员工生成一个固定数量（20）的记录，我希望每个员工有一个随机数量的记录，即 1、12、7、19、20、4、....

ALTER SESSION SET.  NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

-- create and populate an employee 
-- table with 50 rows

CREATE TABLE employees AS
SELECT level AS employee_id,

CASE round(dbms_random.value(1,20)) 
        WHEN 1 THEN 'Albert' 
        WHEN 2 THEN 'Tom' 
        WHEN 3 THEN 'Anna'
        WHEN 4 THEN 'Ty' 
        WHEN 5 THEN 'Andy' 
        WHEN 6 THEN 'Thomas' 
        WHEN 7 THEN 'Alan'
        WHEN 8 THEN 'Tara' 
        WHEN 9 THEN 'Cheryl' 
        WHEN 10 THEN 'Ed' 
        WHEN 11 THEN 'Steve'
        WHEN 12 THEN 'Mel' 
        WHEN 13 THEN 'Micheal' 
        WHEN 14 THEN 'Ron' 
        WHEN 15 THEN 'Donald'
        WHEN 16 THEN 'Donny' 
        WHEN 17 THEN 'Racheal' 
        WHEN 18 THEN 'Debbie' 
        WHEN 19 THEN 'Madison'
        WHEN  20 THEN 'Danny' 
     END AS first_name,

CASE  round(dbms_random.value(1,20)) 
        WHEN 1 THEN 'Andrews' 
        WHEN 2 THEN 'Thorton' 
        WHEN 3 THEN 'Smith'
        WHEN 4 THEN 'Jones' 
        WHEN 5 THEN 'Ott' 
        WHEN 6 THEN 'Stevens' 
        WHEN 7 THEN 'Feldman'
        WHEN 8 THEN 'Stein' 
        WHEN 9 THEN 'Ross' 
        WHEN 10 THEN 'Eden' 
        WHEN 11 THEN 'Saltzman'
        WHEN 12 THEN 'Kramer'
        WHEN 13 THEN 'Monroe' 
        WHEN 14 THEN 'Hanks' 
        WHEN 15 THEN 'Dunn'
        WHEN 16 THEN 'Dunbar' 
        WHEN 17 THEN 'Rucker' 
        WHEN 18 THEN 'Silverberg' 
        WHEN 19 THEN 'Daniels'
        WHEN  20 THEN 'Kahn' 
     END AS last_name, 
    
dbms_random.string('X',        dbms_random.value(5, 10))  AS card_num

FROM   dual
CONNECT BY level <= 50;


ALTER TABLE employees
     ADD ( CONSTRAINT employee_id_pk
   PRIMARY KEY (employee_id));

-- create and populate a location 
-- table with 10 rows. Randomly
-- make some types 'A' for access
-- 'T' for time and attendance,
-- 'G' for guard tour.

CREATE TABLE locations AS
SELECT level AS location_id,
   'Door ' || level AS location_name,

CASE round(dbms_random.value(1,3)) 
        WHEN 1 THEN 'A' 
        WHEN 2 THEN 'T' 
        WHEN 3 THEN 'G' 
     END AS location_type

FROM   dual
CONNECT BY level <= 25;


 ALTER TABLE locations 
     ADD ( CONSTRAINT location_id_pk
   PRIMARY KEY (location_id));


create table access_history(
   employee_id NUMBER(6), 
   card_num varchar2(10),
   location_id number(4),
   access_date date,
   processed NUMBER(1) default 0
);



INSERT into access_history
 (employee_id,
   card_num,
  location_id,
   access_date)
   with all_combos as
      ( select e.*, l.*
      from   employees e, locations l
     )
    select *
     from (
       select employee_id, card_num,
               location_id, 
             trunc(sysdate) +     dbms_random.value (0, 2) + dbms_random.value (0, .75)
        from   all_combos
        order by dbms_random.value
  );

标签： oraclesqliterandomcartesian-product

解决方案

您可以使用dbms_random. 举个简单的例子：

Select * from
(Select e.*,
       Row_number() over (partition by e.emp_id order by dbms_random.value(0,1)) as rn,
       Count(1) over (partition by e.emp_id) as cnt
  From employees e)
Where dbms_random.value(0,1) * cnt >= rn 
   Or rn = 1 -- to ensure that one record per employee is selcted.

推荐阅读