Oracle 为日期范围内的每个日期生成随机数的行

我下面有一些工作SQL ，它为每个雇员ID生成一行。

我的目标是通过独立运行良好的函数获取范围内的每个日期，然后为指定范围内的每一个雇员ID获取N （随机数(1-10 ）行)。

一旦SQLworks ，我打算把这个代码放在一个过程中，这样我就可以给它传递一个日期范围。因此，我们可以确保我们运行的是同一个版本的Oracle，我在实时SQL上测试了这个版本。

以下是仅一天的一些示例输出。请注意，雇员ID和位置ID必须存在于其相应的表中。

由于函数调用总是生成时间为00:00:00的日期，所以我计划最终将时间添加到access_date中。

EMPLOYEE_ID    CARD_NUM LOCATION_ID    ACCESS_DATE
          1    F123456           10    07302021 09:47:48
          1    F123456            5    07282021 19:17:42
                  
          2    R33432             4    07282021 02:00:37
                  
          3    C765341            2    07282021 17:33:57
          3    C765341            6    07282021 17:33:57
          3    C765341            1    07282021 18:53:07
                  
          4    D564311            6    07282021 03:06:37

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

CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
RETURN nt_date  PIPELINED   DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
 LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start + INTERVAL '1' DAY;
  END LOOP;
  RETURN;
END   generate_dates_pipelined;
/

Create table employees(
  employee_id NUMBER(6), 
  first_name VARCHAR2(20),
  last_name VARCHAR2(20),
  card_num VARCHAR2(10),
  work_days VARCHAR2(7)
);

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

INSERT INTO employees (
  EMPLOYEE_ID,
  first_name, 
  last_name,
  card_num,
  work_days
)
WITH names AS ( 
  SELECT 1, 'Jane',     'Doe',      'F123456', 'NYYYYYN'   FROM dual UNION ALL 
  SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN' FROM dual UNION ALL 
  SELECT 3, 'Justin',   'Case',     'C765341','NYYYYYN' FROM dual UNION ALL 
  SELECT 4, 'Mike',     'Jones',      'D564311','NYYYYYN' FROM dual  )
SELECT * FROM names;  

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 <= 10;


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

SELECT e.employee_id,
     e.card_num,
     l.location_id,   
     c.access_date,
     e.rn, l.rn, c.rn
FROM   ( 
 SELECT employee_id,
        round ( dbms_random.value ( 1, 10 ) ) rn,
        card_num
 FROM   employees
) e
INNER JOIN
( SELECT location_id, 
        row_number() OVER  (ORDER BY dbms_random.value) AS rn
 FROM locations
) l
ON (e.rn = l.rn)
INNER JOIN
( SELECT COLUMN_VALUE AS access_date, 
        row_number() OVER  (ORDER BY dbms_random.value) AS rn
 FROM   TABLE(generate_dates_pipelined(SYSDATE, ADD_MONTHS(SYSDATE, 1)))
) c
ON (e.rn >= c.rn)
ORDER BY
     employee_id,
     location_id;
复制

你很接近了。

与其为每个员工生成一个随机行号，只需生成一个1-10之间的随机数即可。然后连接所有具有小于或等于此值的（随机）行号的日期。

如果您知道位置编号为1-10 ，没有间隙，则可以使用这种方法在顶部选择中也生成这些ID。

如果不是，您可以使用交叉应用将此表也“连接”到日期，返回第一个日期。

SELECT e.employee_id,
     e.card_num,
     l.location_id,   
     c.access_date
FROM   ( 
 SELECT employee_id,
        round ( dbms_random.value ( 1, 10 ) ) rn,
        card_num
 FROM   employees
) e
INNER JOIN
( SELECT COLUMN_VALUE AS access_date, 
        row_number() OVER  (ORDER BY dbms_random.value) AS rn
 FROM   TABLE(generate_dates_pipelined(SYSDATE, ADD_MONTHS(SYSDATE, 1)))
) c
ON (e.rn >= c.rn)
CROSS APPLY ( 
SELECT * FROM (
    SELECT location_id, 
           row_number() OVER  (ORDER BY dbms_random.value) AS rn
    FROM   locations
) l 
WHERE l.rn <= c.rn
FETCH FIRST 1 ROWS ONLY
) l
ORDER BY
     employee_id,
     location_id;
     
EMPLOYEE_ID CARD_NUM     LOCATION_ID ACCESS_DATE         
          1 F123456                2 07292021 00:00:00    
          1 F123456                2 08122021 00:00:00    
          1 F123456                4 08012021 00:00:00    
          1 F123456                6 08032021 00:00:00    
          1 F123456               10 08102021 00:00:00    
          2 R33432                 2 08012021 00:00:00    
          2 R33432                 5 08102021 00:00:00    
          2 R33432                 8 08032021 00:00:00    
          3 C765341                2 08012021 00:00:00    
          3 C765341                5 08122021 00:00:00    
          3 C765341                7 07292021 00:00:00    
          3 C765341                7 08102021 00:00:00    
          3 C765341                9 08032021 00:00:00    
          4 D564311                1 08122021 00:00:00    
          4 D564311                1 08012021 00:00:00    
          4 D564311                2 08032021 00:00:00    
          4 D564311                3 08102021 00:00:00 
复制