-
1. Re: Exclude the row depending on the last date
John Thorton Dec 10, 2018 1:39 PM (in response to 3848360)65c71872-51fa-46fa-9db7-78bcc95aeab9 wrote:
Hi together,
I need your help in a SQL query Problem.
My Problem is it, I want exclude all timelines which have in the last date the conditions JIRA_PROGRESS= closed and
JIRA_DECISION = false
The filter should only affect the last date.
Thanks for your help
Please click on URL below & respond accordingly
-
2. Re: Exclude the row depending on the last date
Frank Kulash Dec 10, 2018 1:58 PM (in response to 3848360)Hi,
This does what you requested:
WITH got_max_dt AS
(
SELECT time_series_id, jira_ticket_no, time_series_dt
, time_series_id, kpi_name, ts_status
, jira_progress, jira_decision
, MAX (time_series_dt) OVER () AS max_dt
FROM vw_bqi_tableau_data
)
SELECT time_series_id, jira_ticket_no, time_series_dt
, time_series_id, kpi_name, ts_status
, jira_progress, jira_decision
FROM got_max_dt
WHERE jira_decision = 'false'
AND jira_progress = 'closed'
AND time_series_dt = max_dt
;
assuming "last date" means the latest time_series_dt. If "last date" is based on some other column, then use that column for computing and comparing max_dt.
Analytic functions are computed after the WHERE clause has been applied. To use the results of an analytic function (like MAX, above) in a WHERE clause, you can compute the analytic function in a sub-query (like got_max_dt above).
I hope this answers your question.
If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using (e.g., 12.1.0.1.0).
See the forum FAQ: Re: 2. How do I ask a question on the forums?
-
3. Re: Exclude the row depending on the last date
BEDE Dec 10, 2018 1:54 PM (in response to 3848360)Now, I'm guessing that vw_bqi_tableau_data is a view. So, you should post the view's DDL and the DDL for the tables included in that view. Maybe it would be better not to use the view itself, but rather one or more of the tables included in that view.
About: "should affect last date" - am I to assume you mean what is found in time_series_dt?
If so, I thing something like below:
with tb(
select time_series_id, time_series_dt, kpi_name, ts_status, Jira_progress, Jira_decision
,max(time_series_dt) over (partition by 1) max_dt --- should it be partition by some group? which?
from vw_bki_tableau_data
)
select *
from tb
where
case
when time_series_dt=max_dt and Jira_decision='false' and Jira_progress='closed' then 1
when time_series_dt!=max_dt then 1
else 0
end=1
;
Although, I fear the max(time_series_dt) involving a window sort my cause a terrible performance if vw_bki_tableau_data has lot of rows...
-
4. Re: Exclude the row depending on the last date
Stew Ashton Dec 10, 2018 4:06 PM (in response to 3848360)Hello,
What is a "timeline" exactly? Is it every row that has the same jira_ticket_no?
The impression I get is :
- a timeline is a group of rows
- You want to check the last row (in date order) of each timeline to see if it contains 'false' and 'closed'
- If the last row has those values, you want to exclude the entire timeline from the result
- Otherwise you want to see the entire timeline.
Did I understand you correctly?
Best regards,
Stew Ashton
-
5. Re: Exclude the row depending on the last date
Stew Ashton Dec 10, 2018 4:32 PM (in response to Stew Ashton)Assuming my understanding is correct:
CREATE TABLE T ( TIME_SERIES_ID NUMBER , JIRA_TICKET_NO NUMBER , TIME_SERIES_DT DATE , JIRA_PROGRESS VARCHAR2 ( 6 BYTE ), JIRA_DECISION VARCHAR2 ( 5 BYTE ) ); Insert into T values (1,1,to_date('2018-12-08 00:00','yyyy-mm-dd hh24:mi'),null,'open'); Insert into T values (2,2,to_date('2018-12-08 00:00','yyyy-mm-dd hh24:mi'),null,'open'); Insert into T values (3,3,to_date('2018-12-08 00:00','yyyy-mm-dd hh24:mi'),null,'open'); Insert into T values (4,4,to_date('2018-12-08 00:00','yyyy-mm-dd hh24:mi'),null,'open'); Insert into T values (5,1,to_date('2018-12-09 00:00','yyyy-mm-dd hh24:mi'),null,'open'); Insert into T values (6,2,to_date('2018-12-09 00:00','yyyy-mm-dd hh24:mi'),null,'open'); Insert into T values (7,3,to_date('2018-12-09 00:00','yyyy-mm-dd hh24:mi'),null,'open'); Insert into T values (8,4,to_date('2018-12-09 00:00','yyyy-mm-dd hh24:mi'),null,'open'); Insert into T values (9,1,to_date('2018-12-10 00:00','yyyy-mm-dd hh24:mi'),'closed','false'); Insert into T values (10,2,to_date('2018-12-10 00:00','yyyy-mm-dd hh24:mi'),'closed','true'); Insert into T values (11,3,to_date('2018-12-10 00:00','yyyy-mm-dd hh24:mi'),'open','false'); Insert into T values (12,4,to_date('2018-12-10 00:00','yyyy-mm-dd hh24:mi'),'open','true'); select * from t where jira_ticket_no not in ( select jira_ticket_no from t group by jira_ticket_no having max(jira_progress) keep (dense_rank last order by time_series_dt) = 'closed' and max(jira_decision) keep (dense_rank last order by time_series_dt) = 'false' ) order by 2,1;
TIME_SERIES_ID JIRA_TICKET_NO TIME_SERIES_DT JIRA_PROGRESS JIRA_DECISION 2 2 2018-12-08 00:00 open 6 2 2018-12-09 00:00 open 10 2 2018-12-10 00:00 closed true 3 3 2018-12-08 00:00 open 7 3 2018-12-09 00:00 open 11 3 2018-12-10 00:00 open false 4 4 2018-12-08 00:00 open 8 4 2018-12-09 00:00 open 12 4 2018-12-10 00:00 open true Regards,
Stew
-
6. Re: Exclude the row depending on the last date
Frank Kulash Dec 10, 2018 6:12 PM (in response to 3848360)Hi,
If Stew's assumptions in reply #4 are correct, and you're using Oracle 12.1 (or higher), you can also do it this way:
SELECT time_series_id, jira_ticket_no, time_series_dt
, jira_progress, jira_decision
FROM t
MATCH_RECOGNIZE
(
PARTITION BY jira_ticket_no
ORDER BY time_series_dt DESC
MEASURES FIRST (jira_decision) AS last_jira_decision
, FIRST (jira_progress) AS last_jira_progress
ALL ROWS PER MATCH
PATTERN (any_row+)
DEFINE any_row AS 1 = 1
)
WHERE LNNVL (last_jira_decision = 'false')
OR LNNVL (last_jira_progress = 'closed')
ORDER BY jira_ticket_no, time_series_dt DESC
;
Stew Ashton, you're good at MATCH_RECOGNIZE. Can you suggest improvements? Would you use a NOT IN sub-query in version 12.1 or up?
-
7. Re: Exclude the row depending on the last date
Stew Ashton Dec 10, 2018 6:40 PM (in response to Frank Kulash)Hi Frank,
My browser is having trouble with the ODC site. I was hoping the NOT IN would use an index if available, but my tests show that is not the case. I have a MATCH_RECOGNIZE solution but it is probably not quite as good as the equivalent analytic solution.
Regards, Stew
-
8. Re: Exclude the row depending on the last date
Stew Ashton Dec 10, 2018 8:22 PM (in response to Frank Kulash)1 person found this helpfulHi Frank,
Using MATCH_RECOGNIZE, I would say "order by date descending and match if the most recent row does not have 'false' and 'closed'". That way the question "is there a match?" is answered immediately.
select * from t match_recognize( partition by jira_ticket_no order by time_series_dt desc all rows per match pattern (^a b*) define a as 1 = any (decode(jira_progress,'closed',0,1), decode(JIRA_DECISION,'false',0,1)) );
Something similar could be done using analytic functions:
select TIME_SERIES_ID, JIRA_TICKET_NO, TIME_SERIES_DT, JIRA_PROGRESS, JIRA_DECISION from ( select t.*, first_value(jira_progress) over(partition by jira_ticket_no order by time_series_dt desc) jp, first_value(JIRA_DECISION) over(partition by jira_ticket_no order by time_series_dt desc) jd from t ) where 1 = any (decode(jp,'closed',0,1), decode(jd,'false',0,1));
I don't find any significant performance difference between these two solutions, though with larger volumes MATCH_RECOGNIZE starts to run a bit faster.
Regards,
Stew
-
9. Re: Exclude the row depending on the last date
3848360 Dec 11, 2018 2:00 PM (in response to Stew Ashton)Hi Stew,
to 1. A timeline is group by time_series_id, the status refers to the timeline
2. I want check the last date from every timeline (time_series_id) that have a status "false" and "closed"
3 and 4 did you descripe perfectly
Best regards
Lea
-
10. Re: Exclude the row depending on the last date
Frank Kulash Dec 11, 2018 2:10 PM (in response to 3848360)Hi, Lea,
3848360 wrote:
Hi Stew,
to 1. A timeline is group by time_series_id, the status refers to the timeline
...
Replies 4-8 assumed that jira_ticket_no identified the different timelines. If timeline is based on time_series_id instead, then use time_series_id instead of jira_ticket_no in the solutions above.
-
11. Re: Exclude the row depending on the last date
3848360 Dec 12, 2018 1:25 PM (in response to 3848360)Thanks for your help, I could solved the problem
-
12. Re: Exclude the row depending on the last date
Frank Kulash Dec 12, 2018 1:55 PM (in response to 3848360)Don't forget to mark the thread as "Answered".