Oracle continuous data processing example

Oracle continuous data processing example

The function described in the following section is to have a table in the Oracle database, which stores continuous time records and a tag bit. Now you need to obtain a result set: when the flag bit is 0, the time data of the previous one is obtained. If the flag bit is 1, the time data of the current record is obtained.

First. Further explanation

1. Create a table

Create table test_date (
T_TIME varchar (20), -- Time
T_ISOM number default 0 -- Mark
)

2. initialize data

Delete from test_date;
Insert into test_date (t_TIME, t_ISOM) values ('20140901', 1 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('20140901', 1 );
Insert into test_date (t_TIME, t_ISOM) values ('20140901', 1 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('20140901', 1 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('201312', 0 );
Insert into test_date (t_TIME, t_ISOM) values ('20140901', 1 );

3. Obtain result data

Select
Case when T_ISOM = 1 THEN T_TIME
ELSE
N
END RESULT_TIME -- result, 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) OVER (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 order by t_TIME) x
, Lead (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 obtain results. Of course, some edge data may have bugs, but this does not affect the interpretation of the main principles. Edge can be processed by adding judgment.

Here we will mainly explain the content of step 3.

1. First, an 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
, Lead (t_TIME) over (order by t_TIME) as dnext, lag (t_TIME) over (order by t_TIME) as dpre
FROM test_date

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): obtains ROW_NUMBER () in chronological ORDER to ensure that all records are consecutively numbered.

ROW_NUMBER () OVER (partition by t_ISOM order by t_TIME): groups by tag bit and sorts the time to ensure that records in the group are consecutively numbered.

Because the two are continuous, so subtract, then each group will get a value (Here we talk about each group, so the records in each group are also the same value), tentatively set to X

Lead and lag are statistical functions. It is not difficult to obtain the records of the next and previous rows.

2. process the results based on the results at the first layer and group the X values to obtain the maximum and minimum dates of each group.

3. The rest can be arbitrary. All the results have been calculated in the second layer. You can obtain the desired results based on any combination you want. For example, the most recent record with a 1 mark.