Discussions
Categories
- 196K All Categories
- 2.1K Data
- 209 Big Data Appliance
- 1.9K Data Science
- 447.4K Databases
- 220.8K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 23 Multilingual Engine
- 516 MySQL Community Space
- 463 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 469 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.9K SQL & PL/SQL
- 20.9K SQL Developer
- 292.7K Development
- 7 Developer Projects
- 124 Programming Languages
- 289.4K Development Tools
- 95 DevOps
- 3K QA/Testing
- 645.5K Java
- 23 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.8K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 16 Java Essentials
- 143 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 197 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 242 LiveLabs
- 35 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Oracle how to classify time series ?
PAN KEVIN
Member Posts: 184 Red Ribbon
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:
1) if any VALUE in KP_ >3, update LABEL of table cses_test = 2;
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_));
2) 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 table of table cses_test = the corresponding EQ_NUM in table eq_test;
3) if any VALUE in KP_ < =3, AND eighty percent of the value in GEOC_LAT_, GEOC_LONG_ and UT_ are NOT within the affected aree of any earthquake in table eq_test, then update LABEL of table cses_test = 0;
The affected aree of any earthquake are: 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 reslut 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);
Answers
-
I will appreciate you very much if anyone can give me a clue.
-
Is the question unclear ?