Hi, I am working on a project and I need some help with the query.
I have the following data in my existing table:
-----------------
S_ID S_DATE
-----------------
A 01-FEB-12
A 14-MAR-12
A 28-APR-14
A 28-MAR-15
A 28-AUG-18
I need to first find the min date from this list of S_DATE which is in the last 2 years time frame from the S_DATE. So I used sub select query to give me the data:
For example, the 4th row with the S_DATE of 28-MAR-15, the 2 years time frame for this row is from 28-MAR-13 (S_DATE2) to 28-MAR-15 (S_DATE). I need to find the min date from the list of S_DATE - which is 28-APR-14 (FIRST_DATE) -> in other words: 28-APR-14 is the min date I can find from the list that is between 28-MAR-13 and 28-MAR-15.
--------------------------------------------------
S_ID S_DATE S_DATE2 FIRST_DATE
--------------------------------------------------
A 01-FEB-12 01-FEB-10 01-FEB-12
A 14-MAR-12 14-MAR-10 01-FEB-12
A 28-APR-14 28-APR-12 28-APR-14
A 28-MAR-15 28-MAR-13 28-APR-14
A 28-AUG-18 28-AUG-16 28-AUG-18
Then the next thing I need to do is to find the min date from the list of S_DATE which is in the last 3 years time frame. I have to use another sub query to get the date:
For the same example, the 4th row. The 3 years time frame is from 28-APR-11 (FIRST_DATE3) to 28-APR-14 (FIRST_DATE) and the min date from the list of S_DATE is 01-FEB-12.
--------------------------------------------------------------------------------------
S_ID S_DATE S_DATE2 FIRST_DATE FIRST_DATE3 FIRST_DATE_IN3
--------------------------------------------------------------------------------------
A 01-FEB-12 01-FEB-10 01-FEB-12 01-FEB-09 01-FEB-12
A 14-MAR-12 14-MAR-10 01-FEB-12 01-FEB-09 01-FEB-12
A 28-APR-14 28-APR-12 28-APR-14 28-APR-11 01-FEB-12
A 28-MAR-15 28-MAR-13 28-APR-14 28-APR-11 01-FEB-12
A 28-AUG-18 28-AUG-16 28-AUG-18 28-AUG-15 28-AUG-18
Below is my code for this example:
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=Q.S_ID
AND S1.S_DATE>=ADD_MONTHS(Q.FIRST_DATE,-12*3)
AND S1.S_DATE<=Q.FIRST_DATE
) AS FIRST_DATE_IN3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
ORDER BY Q.S_DATE
This is only one simple example, So I don't have any issues with sub select query. But my real data set contains over 60,000 rows. And for each S_ID, I have thousands of date to do these logic check. The query ran insanely slow with the sub query. I am new to SQL and I didn't find any useful suggestions online. So please help me with this! I would really appreciate your time and effort!
I would like to know is there any other way I can do to get the same results? If I use a view or stored procedure, will it help?
Thank you!
So you want to find the minimum date in your data within a timeframe offset from the current row?
If so, no need for subquery. Analytic functions are the answer!
Add the over clause after min() and sort by date.
Then set the window clause to
range between N preceding and current row
This will pass all rows with a date N before the current to min. Which finds the earliest of these:
create table sample_test (
s_id varchar(10),
s_date date
);
insert into sample_test values ('A', to_date('01-feb-2012','dd-mon-yyyy'));
insert into sample_test values ('A', to_date('14-mar-2012','dd-mon-yyyy'));
insert into sample_test values ('A', to_date('28-mar-2015','dd-mon-yyyy'));
insert into sample_test values ('A', to_date('28-aug-2018','dd-mon-yyyy'));
commit;
select s_date,
min ( s_date ) over (
order by s_date
range between ( s_date - add_months ( s_date, -24 ) ) preceding
and current row
) min_2_year ,
min ( s_date ) over (
order by s_date
range between ( s_date - add_months ( s_date, -36 ) ) preceding
and current row
) min_3_year
from sample_test;
S_DATE MIN_2_YEAR MIN_3_YEAR
01-FEB-2012 00:00:00 01-FEB-2012 00:00:00 01-FEB-2012 00:00:00
14-MAR-2012 00:00:00 01-FEB-2012 00:00:00 01-FEB-2012 00:00:00
28-MAR-2015 00:00:00 28-MAR-2015 00:00:00 28-MAR-2015 00:00:00
28-AUG-2018 00:00:00 28-AUG-2018 00:00:00 28-AUG-2018 00:00:00