SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle how to classify time series ?

PAN KEVINNov 9 2020 — edited Nov 9 2020

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:

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);

thatJeffSmith-Oracle

Answer

Should be out soon.

Marked as Answer by jdoornae · Jun 29 2022

1 person found this helpful

jdoornae

It's there now. Thanks!

Mal Baird

Is JDK 8 or 11 included?

thatJeffSmith-Oracle

11, fixing the typo now

stom

Downloaded SQL Developer 22.2 with JDK 11
Copied my preferences over from the previous installation (21.4.3.063).
When I try to connect to a database from the connection tree, I get this error.
Any ideas?

thatJeffSmith-Oracle

What type of connection is it?
I'm running the exact same thing you are, 64-bit Windows w/Java 11 included, and on an upgrade can connect just fine.
Maybe, try extracting the zip again using native Windows file explorer to a net-new directory, and try connecting from that location?

stom

It is a basic connection. I specify the host, port and service name. I right clicked on the connection name -> properties -> Test
I extracted to a new location and I also downloaded the "No Java" version. I get the same error on both attempts.

thatJeffSmith-Oracle

Do you have an oracle client defined in the Advanced Preferences, or are you using a TNS connection type or do you have 'OCI' checked on your advanced connection properties?

stom

I am using a basic connection type. I have a TNSNAMES file but I don't use it.
I have "OCI/Thick driver" checked. We use Radius authentication for our DB logins and I remember it wasn't working without this box checked.

thatJeffSmith-Oracle

That's probably it, get a newer client and try that, a 19c or 21c client.

user-x79xr

Same issue for me - java.lang.NoClassDefFoundError: oracle/jdbc/datasource/impl/OracleDataSource

I'm using 19c Client with "OCI/Thick driver" checked in SQLDeveloper 22.2 but still facing this issue.

1 - 11

Added on Nov 9 2020

#database, #pl-sql, #plsql, #r12, #sql

2 comments

156 views

SQL & PL/SQL

Oracle how to classify time series ?

Comments

Post Details