Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

timeseries/eventstream processing

User_2SRRNOct 15 2021

Hello,
I’m having sort of a struggle querying a table which contains events in the form of “timestamp” and “start/stop” records.
To better illustrate my problem, I’ve created a small table with some data (please see attachment). The demo data consists of a “customer” (representing any business key) and a timestamp marked as either “start” or “stop” event type. My goal is to get the actual duration between a “start” and a “stop” event/record.
The date calculation itself is not a problem and it’s easy to enumerate the events. However, I don’t know how to “reset” the group id, if there are more than one “roundtrips” (pairs of start and stop)
Customers 1000 and 2000 show the most simple case, there’s one, resp. two pairs of a start and a stop event. These work well for me.
My base idea was to just assign row numbers, so “start 1” goes to “stop 1” and so on. However to simulate some “noise” I’ve also included some examples which contain multiple start or stop events. In this situation, I want to pick the first “start” and the last “stop” of a group.
If there’s only one “start”, I’d like to assume the current date (sysdate) as the “stop” event. Invalid constellations (see comments in script) would be ignored/filtered out; eg. If there’s no “start” in a group or “stop” before “start”
Anyone knows how to do this? I was thinking of CTEs each dealing with one situation, but I’m not sure if that’s a performance killer.
Thanks in advance for any help :-)

eventstream.txt (3.34 KB)

Comments

Frank Kulash

Hi, User_4G3I3
Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. For PL/SQL questions, include a complete working block that does the parts you already know how to do. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).
So how can I detect every new ID 
Create a variable to hold the value of the previous id. Initialize it to something that can not be a value of id. Inside the beginning of the loop say

IF  id = prev_id THEN ...

At the end of the loop, say

prev_id := id;

Alternatively, you could add a column in the query that tells if a row is the first row for the id, using an analytic function such as LAG or ROW_NUMBER.

1 person found this helpful
Solomon Yakobson

First of all there is no row order in relational table. Order is provided vi ORDER [SIBLINGS] BY clause. So I will assume rows for same ID should be ordered by PAYMENT. Then there is no need to PL/SQL. All you need is:

SELECT  ID,
        MIN(PAYMENT)
  FROM  YOUR_TABLE
  GROUP BY ID
  ORDER BY ID
/

SY.

User_4G3I3

Sorry, I am new to this community and I will follow your guidelines next time :)
Here is the block of code. I am using the database from this textbook (Oracle 11g pl sql programming 2nd edition casteel)
SQL developer Version 21.2.1.204 Build 204.1703
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
example (1).JPG

Solomon Yakobson

Again, post CREATE TABLE statement and INSERT statements to populate it. Nobody wants to waste time on typing.
SY.

User_4G3I3

database_file.rar (1.94 KB)

User_4G3I3

Ignore the drop table part at the top, I forgot to remove it.

Solomon Yakobson

Most people will not open files from unknown sources. Post it, not attach it.
SY.

User_4G3I3

@Frank Kulash
Thank you, ROW_NUMBER solved my problem.

1 - 8

Post Details

Added on Oct 15 2021
16 comments
207 views
2 mentions