SQL to check for Re-Occurring Event. — oracle-tech

    Forum Stats

  • 3,714,818 Users
  • 2,242,634 Discussions
  • 7,845,078 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

SQL to check for Re-Occurring Event.

Mr.Jondee
Mr.Jondee Member Posts: 125 Blue Ribbon
edited December 2020 in SQL & PL/SQL

Hello All ,

Please will need help in solving below business requirement, excerpts below...

Thanks very much


Oracle version: 12.2.0.1.0

Problem:

EVENTS_FACT_TAB.NEW_EVENT_FLAG  - Should tell us if the viewing event was the first view event for the given device

EVENTS_FACT_TAB.REJUV_EVENT_FLAG - Should tell us if the viewing event was the first view event after a specified absence for the given device

                  for the past 364 Days

 

Notes for implementation :

First check the Lookup_dim table to see if the device_id is present or not

We should be able to handle multiple view events on a given day for a given device. Only the FIRST view event of the day should be tagged as "NEW", 

not the rest of the view events from the same day

This applies to REJUV_EVENT_FLAG as well. 

DDL and DML:

Expected Result:


Current Attempt: To resolve_New_event_flag

  --load below value into temp table                      

SELECT FACT_ID,

    DEVICE_ID,

    VIEW_START,

    VIEW_END,

    NEW_EVENT_FLAG,

    REJUV_EVENT_FLAG , 

    ROW_NUMBER () OVER (PARTITION BY device_id ORDER BY VIEW_END DESC)  RANK_DEVICE

 FROM EVENTS_FACT_TAB;

  

 --update fact table 

UPDATE EVENTS_FACT_TAB a

set a.NEW_EVENT_FLAG = 1

where a.device_id in (select b.device_id from Temp_table where rank_device = 1)

Tagged:

Best Answer

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,950 Red Diamond
    edited December 2020

    Hi,

    Not everyone who wants to hep you is as familiar with your application as you are, so be sure to explain clearly what you mean and what you want. For example, when you say:

    EVENTS_FACT_TAB.NEW_EVENT_FLAG  - Should tell us if the viewing event was the first view event for the given device

    what, exactly, does that mean? What is a "viewing event"? Does every row in the events_fact_tab table represent a viewing event?

    EVENTS_FACT_TAB.REJUV_EVENT_FLAG - Should tell us if the viewing event was the first view event after a specified absence for the given device

    How do you compute the amount of time between events? E.g., do you compare the veiw_end value of the earlier event to the view_start value of the later event?

    Only the FIRST view event of the day should be tagged as "NEW", 

    How do you determine which row is first? Is it view_start? View_end? Something else?

    First check the Lookup_dim table to see if the device_id is present or not

    What, exactly, does "present" mean? Do you just need to check if a given value occurs in the watch_device_id column? Do the TIMESTAMP columns have anything to do with deciding if the device_id is present? What happens if it is present? What happens differently if it is not present?

    Describe, at least for one device, how you would get the desired flags. (Just to make sure the problem is clear; this may not be the best way to do it in SQL). For example, device '896635'. Why is one of the rows from 2020 considered the new event, and not the row from 2018?

    I'm not sure exactly what you want to do, but, whatever it is, I'll be you can do it without a temporary table. In Oracle, you can perform a sub-query (a WITH clause, for example, of the USING clause of a MERGE statement) and then use its result set as if that were a table.

  • Mr.Jondee
    Mr.Jondee Member Posts: 125 Blue Ribbon


    EVENTS_FACT_TAB.NEW_EVENT_FLAG - Should tell us if the viewing event was the first view event for the given device

     what, exactly, does that mean? What is a "viewing event"?

     Does every row in the events_fact_tab table represent a viewing event?

       --yes every row in the EVENTS_FACT_TAB is a view event , usually will be loaded on a daily basis .

    What, exactly, does "present" mean?

    --"Present" means if the device_id exists in the LOOKUP_DIM table .

      --the table is supposed to have all distinct device_ids to know when first and last they appeared.

       

     Do you just need to check if a given value occurs in the watch_device_id column?

      -- Yes also and the LAST_ACTIVITY_TS to know the Timestamp the device was last seen. when last the act , but the face , for this requirement the watch_device_id in the LOOKUP_DIM 

       Do the TIMESTAMP columns What happens if it is present? 

     What happens differently if it is not present?

    ---when no device is present or available in the LOOKUP_DIM ,

     then its shows it hasn't been inserted before. Then we use the values in the EVENTS_FACT_TAB and then rank accordingly to see 

     if it is a new device ,then if the device is being rejuvunated(coming in after 364 days of absence)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,950 Red Diamond

    Hi,

    Okay; now I know what "viewing event" and "present" mean. However, none of my other questions have been answered. When you post a specific example of how you determine the desired flags, then maybe I'll understand.

  • Mr.Jondee
    Mr.Jondee Member Posts: 125 Blue Ribbon

    @Frank Kulash

    Thanks for your response so far.

    I think we can skip the LOOKUP_DIM  table for now as it is having same information.

    So for every entry into the EVENTS_FACT_TAB  , I am supposed to create a stored proc to update these two coulmns (

    NEW_EVENT_FLAG , REJUV_EVENT_FLAG)

    is the device_id new ? first time entry in the table then set NEW_EVENT_FLAG = 1

    if the device has already been inserted before in the Fact , but hasn't been present in the past 364 days , and then show_up later ,

    then the REJUV_EVENT_FLAG can be set to = 1


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,950 Red Diamond

    Hi,

    I think we can skip the LOOKUP_DIM  table for now as it is having same information.

    Okay.

    You still haven't explained how you compute the number of days absent. Say the only two rows for a device had these dates:

    VIEW_START     VIEW_END

    11/01/2000 8:00 AM 11/02/2000 8:00 AM

    11/10/2000 8:00 AM 11/20/2000 8:00 AM

    How long would you say the absence was

    • 8 days
    • 9 days
    • 18 days
    • 19 days
    • something else

    ?

    I also don't understand the desired results for device '896635'.

    Expected Result:

    Why is the row with view_start = 10/6/2000 given new_event_flag = 1? Why not the row with view_start = 12/5/2018?

  • Mr.Jondee
    Mr.Jondee Member Posts: 125 Blue Ribbon

    when we partition by device_id the minimum (view_start) entry will tell us that ts the first time the device is appearing so we can set the new_event_flag as 1 , if a new event re-occurs for that device after 364 days (when the next event is inserted) , we will check if the prior maximum view_end and if the device_id has been absent for 364 days , then comes back then REJUV_EVENT_FLAG is set = 1


    Why is the row with view_start = 10/6/2000 given new_event_flag = 1? Why not the row with view_start = 12/5/2018?

    --apologies for that ...that was due to bad data. which has then been fixed

  • Mr.Jondee
    Mr.Jondee Member Posts: 125 Blue Ribbon

    You still haven't explained how you compute the number of days absent. Say the only two rows for a device had these dates:

    absence is if any event is beinf re-inserted after 364 days past the prior max View_end of that same device_id

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,950 Red Diamond
    edited December 2020

    HI,

    we will check if the prior view_end and if the device_id has been absent for 364 days , then comes back then REJUV_EVENT_FLAG is set = 1.

    Okay, so you check view_end; then what do you do with it? Do you compare it to view_start on another row?


    I see you changed your original message. Please don't change your messages after you post them. It makes the thread even harder to follow.

    Are these really the results you want now?

    Expected Result:

    For device '896635', why is new_event_flag =1 on the row with view_start = 12/5/2018, and not the row with view_start = 10/6/2018?

  • Mr.Jondee
    Mr.Jondee Member Posts: 125 Blue Ribbon

    Okay, so you check view_end; then what do you do with it? Do you compare it to view_start on another row?

    --when a new_event for that device is re-entered is only when you check the max( View_end ) of the prior event if it has been greater than 364 . no need to compare to view_start in another row.

    Expected result


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,950 Red Diamond
    edited December 2020

    --when a new_event for that device is re-entered is only when you check the max( View_end ) of the prior event if it has been greater than 364 . no need to compare to view_start in another row.

    Expected result


    Are you serious? I thought this was just a typo, but you keep repeating it, even after I pointed out the problem. Explain, step-be-step, for device '896635' how you figure new_event_flag = 1 for the row with view_start=10/6/2018.

    What does "max( View_end ) of the prior event" mean? How can a single event have multiple values for view_end?

  • Mr.Jondee
    Mr.Jondee Member Posts: 125 Blue Ribbon

    My Apologies , seems I was confused at some point too. Thanks for the solution , looks like expected result .

    Thanks very much for the time and effort

Sign In or Register to comment.