I have a table which is consists of time series fields (Collection), I intend to classify time series based on certain conditions.
Table cses_test is
GEOC_LAT_ GEOC_LONG_ KP_ UT_ LABEL EQ_NUM
<Collection> <Collection> <Collection> <Collection> -1 0
<Collection> <Collection> <Collection> <Collection> -1 0
<Collection> <Collection> <Collection> <Collection> -1 0
Table eq_test is
TIME_ LATITUDE LONGITUDE EQ_NUM
2019-04-25 02:27:59:000000 -57.930 -57.2700000 1
2018-08-03 00:21:31:956000 64.900 -139.0000000 2
The certain conditions are:
UPDATE cses_test SET LABEL = (SELECT (CASE WHEN ( COUNT ( CASE WHEN COLUMN_VALUE > 3 THEN 1 END) / COUNT (*)) > 0 THEN 2 ELSE -1 END) FROM TABLE (KP_));
if any VALUE in KP_ < =3, AND eighty percent of the value in GEOC_LAT_, GEOC_LONG_, and UT_ are within the affected area of any earthquake in table eq_test, then update LABEL of table cses_test = 1, and EQ_NUM of the table of table cses_test = the corresponding EQ_NUM in table eq_test;
if any VALUE in KP_ < =3, AND eighty percent of the value in GEOC_LAT_, GEOC_LONG_, and UT_ are NOT within the affected area of any earthquake in table eq_test, then update LABEL of table cses_test = 0;
The affected area of any earthquake is: within 10° of the epicenter, and 2 days before the earthquake (BETWEEN (EQ.TIME_ - (24*20 / 24)) AND EQ.TIME_；BETWEEN EQ.LONGITUDE/EQ.LATITUDE - 10 AND EQ.LONGITUDE/EQ.LATITUDE + 10)
My expected result is
GEOC_LAT_ GEOC_LONG_ KP_ UT_ LABEL EQ_NUM
<Collection> <Collection> <Collection> <Collection> 0 0
<Collection> <Collection> <Collection> <Collection> 1 2
<Collection> <Collection> <Collection> <Collection> 2 0
===========================================
create table cses_test
CREATE OR REPLACE TYPE NUMBER_VARRAY_60 AS VARRAY (60) OF NUMBER (8,2) NOT NULL;
CREATE OR REPLACE TYPE TIMESTAMP_VARRAY_60 AS VARRAY (60) OF TIMESTAMP(6) NOT NULL;
create table CSES_TEST
(
geoc_lat_ NUMBER_VARRAY_60,
geoc_long_ NUMBER_VARRAY_60,
kp_ NUMBER_VARRAY_60,
ut_ TIMESTAMP_VARRAY_60,
label NUMBER,
eq_num NUMBER default 0
);
Insert into CSES_TEST
(GEOC_LAT_, GEOC_LONG_, KP_, UT_, LABEL,
EQ_NUM)
Values
(NUMBER_VARRAY_60(69.456223,69.279823,69.103226,68.926445,68.749413,68.572182,68.394745,68.217133,68.039322,67.86132,67.683167,67.504814,67.326302,67.147621,66.968712,66.789726,66.610565,66.43132,66.251869,66.072212,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), NUMBER_VARRAY_60(134.41,134.61,134.82,135.02,135.21,135.41,135.6,135.79,135.97,-136.16,-136.34,-136.52,-136.69,-136.87,-137.04,-137.21,-137.37,137.54,137.7,137.86,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), NUMBER_VARRAY_60(2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), TIMESTAMP_VARRAY_60(TO_TIMESTAMP('2018/8/1 0:21:31.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:34.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:37.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:40.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:43.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:46.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:49.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:52.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:55.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:21:58.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:01.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:04.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:07.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:10.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:13.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:16.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:19.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:22.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:25.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:28.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), -1,
0);
Insert into CSES_TEST
(GEOC_LAT_, GEOC_LONG_, KP_, UT_, LABEL,
EQ_NUM)
Values
(NUMBER_VARRAY_60(65.892464,65.71257,65.532623,65.352455,65.260422,65.170258,65.080017,64.989799,64.899529,64.80925,64.718933,64.628586,64.538216,64.447815,64.357376,64.266914,64.176422,64.085892,63.99535,63.904766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), NUMBER_VARRAY_60(-138.016998,-138.172913,-138.326782,-138.478775,-138.555664,-138.630463,-138.704849,-138.778732,-138.852142,-138.925125,-138.99765,-139.069733,-139.141388,-139.212616,-139.283417,-139.35379,-139.423737,-139.493271,-139.562408,-139.631134,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), NUMBER_VARRAY_60(2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), TIMESTAMP_VARRAY_60(TO_TIMESTAMP('2018/8/1 0:22:31.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:34.956000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:37.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:40.955000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:42.487000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:43.987000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:45.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:46.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:48.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:49.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:51.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:52.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:54.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:55.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:57.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:22:58.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:00.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:01.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:03.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:04.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), -1,
0);
Insert into CSES_TEST
(GEOC_LAT_, GEOC_LONG_, KP_, UT_, LABEL,
EQ_NUM)
Values
(NUMBER_VARRAY_60(63.814171,63.723534,63.632877,63.542198,63.451481,63.360733,63.269962,63.179298,63.088482,62.997635,62.90678,62.815884,62.724968,62.634022,62.54306,62.452068,62.361053,62.270023,62.17897,62.087875,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), NUMBER_VARRAY_60(-139.699463,-139.76738,-139.834915,-139.902054,-139.968826,-140.035202,-140.101196,-140.166733,-140.231995,-140.296875,-140.361404,-140.425568,-140.48938,-140.552826,-140.615936,-140.678696,-140.741119,-140.803207,-140.864944,-140.926361,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), NUMBER_VARRAY_60(4.3,4.3,4.3,4.3,5.3,5,6,7,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,2.3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), TIMESTAMP_VARRAY_60(TO_TIMESTAMP('2018/8/1 0:23:06.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:07.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:09.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:10.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:12.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:13.988000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:15.488000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:16.986000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:18.486000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:19.986000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:21.486000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:22.986000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:24.486000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:25.986000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:27.486000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:28.986000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:30.486000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:31.986000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:33.486000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),TO_TIMESTAMP('2018/8/1 0:23:34.986000','YYYY/fmMMfm/fmDDfm fmHH24fm:MI:SS.FF'),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), -1,
0);
COMMIT;
create table eq_tescreate table EQ_TEST
create table EQ_TEST
(
time_ TIMESTAMP(6),
latitude NUMBER(7,3),
longitude NUMBER(12,7),
eq_num NUMBER
);
insert into EQ_TEST (TIME_, LATITUDE, LONGITUDE, EQ_NUM)
values ('2019-04-25 02:27:59:000000', -57.930, -57.2700000, 1);
insert into EQ_TEST (TIME_, LATITUDE, LONGITUDE, EQ_NUM)
values ('2018-08-03 00:21:31:956000', 64.900, -139.0000000, 2);
Hi Please try this piece of code. COMMIT is not added by purpose. You can add it after you are sure that this code will do the job. Not perfect one but...
begin
-- First update KP_ > 3 records in CSES_TEST table
update cses_test c set c.label = 2 where c.rowid in (select c1.rowid from cses_test c1, table(c1.kp_) k where k.column_value > 3);
-- Then try to update records with KP_ < = 3
for res in (
select r_id, perc, eq_num
from
(
select r_id, perc, eq_num, count(1) over(partition by r_id order by perc desc) ord
from (
select distinct r_id, perc,eq_num
from (
select r_id, (count(1) over (partition by r_id, e.eq_num) / rslt.cnt) * 100 as perc , e.eq_num
from (
select c1.rowid as r_id, lat.res as latitude, lon.res as longitude, ut.res as u_time, count(1) over (partition by c1.rowid) as cnt
from
cses_test c1
join
-- Here start to represent GEOC_LAT_, GEOC_LOC_ and UT_ as tables and joining each other by rowid and rownum
(
select r_id, res, row_number() over(partition by r_id order by r_num) r_n
from
(
select c.rowid as r_id,column_value as res,rownum r_num
from cses_test c, table(c.geoc_lat_)
where c.label != 2)
) lat on (lat.r_id = c1.rowid)
join
(
select r_id, res, row_number() over(partition by r_id order by r_num) r_n
from
(
select c.rowid as r_id,column_value as res,rownum r_num
from cses_test c, table(c.geoc_long_)
where c.label != 2)
) lon on (lat.r_id = lon.r_id and lat.r_n = lon.r_n)
join
(
select r_id, res, row_number() over(partition by r_id order by r_num) r_n
from
(
select c.rowid as r_id,column_value as res,rownum r_num
from cses_test c, table(c.ut_)
where c.label != 2)
) ut on (ut.r_id = lat.r_id and lat.r_n = ut.r_n)
where
lat.res is not null and
c1.label != 2 -- Exclude already updated KP_ > 3 recordsfrom cses_test
) rslt
join eq_test e on (rslt.u_time between e.time_ - interval '2' day and e.time_ and rslt.longitude/rslt.latitude between e.longitude/e.latitude -10 and e.longitude/e.latitude +10)
)
)
)
where ord = 1 -- take only first record from result as one cses_test record can fall into two eq_test records. We need EQ_NUM with the biggest Percentage of coverage
) loop
update cses_test t
set
t.label = case when res.perc >= 80 then 1 else 0 end,
t.eq_num = case when res.perc >= 80 then res.eq_num else 0 end
where
t.rowid = res.r_id;
end loop;
end;
Thanks.