Examples of Oracle continuous data processing

The following section explains the functionality of a table in the Oracle database, which stores successive time records, and also stores a tag bit.

Now you want to get a result set: When the mark bit is 0 o'clock, take the previous 1 time data, and if the mark bit is 1 o'clock, take the time data of the current record.

=================

Get the dry goods first. Explain again

1. Build a table

CREATE TABLE test_date (   t_time varchar),  --time T_isom number   default 0  --tag)

2. Initialize data

DELETE from Test_date;insert to Test_date (T_time,t_isom) VALUES (' 20140101 ', 1); insert into Test_date (T_TIME,T_ISOM) VALUES (' 20140102 ', 0), insert into test_date (T_TIME,T_ISOM) VALUES (' 20140103 ', 0); insert into Test_date (T_TIME,T_ISOM) VALUES (' 20140104 ', 0), insert into test_date (T_TIME,T_ISOM) VALUES (' 20140105 ', 0); insert into Test_date (T_TIME,T_ISOM) VALUES (' 20140106 ', 0), insert into test_date (T_TIME,T_ISOM) VALUES (' 20140107 ', 0); insert into Test_date (T_TIME,T_ISOM) VALUES (' 20140108 ', 1), insert into test_date (T_TIME,T_ISOM) VALUES (' 20140109 ', 1); insert into Test_date (T_TIME,T_ISOM) VALUES (' 20140110 ', 0), insert into test_date (T_TIME,T_ISOM) VALUES (' 20140111 ', 1); insert into Test_date (T_TIME,T_ISOM) VALUES (' 20140112 ', 0), insert into test_date (T_TIME,T_ISOM) VALUES (' 20140113 ', 0); insert into Test_date (T_TIME,T_ISOM) VALUES (' 20140114 ', 1);

3. Get the result data

Select Case when    t_isom=1 then t_time   ELSE      N   END result_time  -the result to be, comment by Danielinbiti   , C.*from (SELECT b.*, (m-b. T_time) as M_d, (B.t_time-n) as N_d from (  SELECT A.*,max (Dnext) through (PARTITION by X) as M,min (Dpre) over (PARTITION by x) as N from   (    SELECT t_time,t_isom,row_number () over (ORDER by T_time)-row_number () over (PARTITION by T_isom Orde R by T_time) x    , leads (t_time) over  (order by T_time) as Dnext,lag (T_time) over  (order by T_time) as Dpre          From Test_date  ) (A ORDER by T_time) B) c ORDER by T_time

The above steps can be used to obtain the results, although it is possible that some edge data may have bugs, but this does not affect the interpretation of the main principle. The edge can be completed by increasing the judgment processing.

Here is the main explanation of the third step of the content

1, first inside a layer of SQL

SELECT T_time,t_isom,row_number () over (order by T_time)-row_number () over (PARTITION by T_isom ORDER by T_time) x    , Lea D (t_time) over  (order by T_time) as Dnext,lag (T_time) through  (order by T_time) as Dpre from          test_date

The main row_number () over (order by T_time) and Row_number () over (PARTITION by T_isom ORDER by T_time) are the key to processing continuity.

Row_number () over (order by T_time): Get Row_number () in chronological order to ensure that all records are numbered consecutively

Row_number () over (PARTITION by T_isom Order by T_time): Group by the tag bit, then sort the time to ensure that the records in the group are numbered consecutively.

Since two are consecutive, so subtract, then each group will get a value (here is the case of each grouping, so the record within each grouping is the same value), tentatively X

Lead and lag are statistical functions that take a record of the next line and the previous row, which is not difficult.

2, according to the results of the first layer, the results of processing, according to the X-value group, to obtain the maximum and minimum date of each grouping.

3, the rest can be arbitrarily at the mercy of, all the results have been calculated in the second layer, you can choose any combination to obtain the desired results. For example, the current record most recently marked bit is 1 records and so on.

Examples of Oracle continuous data processing