我有以下表格定义和 INSERT 语句,它们工作正常。
create table. tour_detail (
location_id NUMBER(4),
time interval day to second
)
insert into tour_detail.
(location_id,time)
values (3, interval '11:30' hour to minute)
是否可以修改以下示例中的 CTE tour_detail表以执行以下操作。
修改它以仅保留 HH24:MI 设置,其余字段 = 0。
时间怎么能像 val = val + rand(6,12( 分钟那样相互基于。
如果 val>= 23:45 是否可以将其重置为某个随机 HH24:MI。
是否可以为每个tour_hdr(tour_id(生成tour_detail行的随机数(15-25(。生成这些行时,仅使用位置 ID 行,其中 location_type = "G">
需要确保相同的location_id不是连续的
我目前正在保持tour_detail(tour_time( MMDDYYYY HH24:MI:SS,我只需要HH:SS。
ALTER SESSION SET. NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
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 tour_hdr AS
SELECT level AS tour_id,
'Tour ' || level AS tour_name
FROM dual
CONNECT BY level <= 15;
ALTER TABLE tour_hdr
ADD ( CONSTRAINT tour_hdr_id_pk
PRIMARY KEY (tour_id));
create table tour_detail
(
tour_id NUMBER(4),
tour_time DATE,
location_id NUMBER(4)
)
declare
v_loc number;
v_prev_loc number := 0;
v_dt date := trunc(sysdate);
v_dt_save date := trunc(sysdate);
begin
for trs in ( select tour_id from tour_hdr)
loop
-- for each tour generate 15 to 25 rows
for i in 1..dbms_random.value(15, 25) loop
-- If date >23:45:00 reset. Should I
-- interval be used instead of date
IF v_dt > (v_dt_save + (1/1440*1425))
THEN
-- reset time
v_dt := v_dt_save;
ELSE
-- increase last used date by random 6 to 12 minutes
v_dt := v_dt + dbms_random.value(6,12)/(24*60);
END IF;
-- get random location
select location_id
into v_loc
from (select location_id from locations where location_type = 'G' order by dbms_random.value)
where rownum = 1;
IF v_prev_loc != v_loc
THEN
insert into tour_detail (tour_id, tour_time, location_id)
values (trs.tour_id, v_dt, v_loc);
v_prev_loc := v_loc;
END IF;
end loop;
end loop;
end;
我的目标是从tour_detail中提取一个记录,找到access_history表的记录,看看警卫是否及时,早,晚,没有到那个位置。简而言之,我需要将MMDDYYYY HH24:MI:SS与时间间隔行进行比较。那会很难实现
吗目前还不清楚你真正想要的结果,以及location_id
应该是固定的还是随机的(如果是这样,如何?(。
如果您希望使用随机间隔值生成给定数量的行,那么一个选项是使用递归 cte 和dbms_random.value
。您需要确定间隔的最大长度和迭代次数。例如,这为您提供了 10 条记录,间隔最长为 12 小时:
with cte(rnd, n) as (
select dbms_random.value, 1 from dual
union all
select dbms_random.value, n+ 1 from cte where n < 10
)
select 3 as location_id, rnd * 12 * interval '1' hour as time
from cte order by n
只需在查询的最开头添加以下行,即可将其转换为insert
语句:
insert into tour_detail (location_id, time)
with ...
select ...
DB小提琴上的演示:
LOCATION_ID |时间 ----------: |:------------------ 3 |+00 07:13:32.262069 3 |+00 03:45:18.965145 3 |+00 07:13:41.394986 3 |+00 02:24:19.516714 3 |+00 11:38:16.000109 3 |+00 11:11:47.947329 3 |+00 08:14:54.641965 3 |+00 02:52:43.090595 3 |+00 03:44:26.418538 3 |+00 09:29:35.390110