CODEWITHSUNDEEP
mysql
Ravi
Ravi

Reputation: 667

How to find value of a row based on values between start and ending condition?

I have been working on a table to get the end timestamp of a play (nowPlaying) event based on the following conditions:

  1. If there is a browseSearch between nowPlaying and browseFind, then the case is ignored.
  2. browseFind would be the starting point for each of the nowPlaying event.
  3. There could be any number of other events between a browseFind and nowPlaying.

What I have done is tried to partition the query and add another column with Start, Break, Skip and end as shown in table below:

The events are ordered by timestamp event_ts

+------+--------------+-------+---------------------+
| id   | page_type    | p_t   | event_ts            |
+------+--------------+-------+---------------------+
|    1 | browseFind   | Start | 2021-01-01 06:00:00 |
|    1 | browseSearch | break | 2021-01-01 06:00:10 |
|    1 | browseFind   | Start | 2021-01-01 06:01:00 |
|    1 | x-ray        | skip  | 2021-01-01 06:01:30 |
|    1 | browseSearch | break | 2021-01-01 06:02:00 |
|    1 | nowPlaying   | end   | 2021-01-01 06:03:00 |
|    1 | browseFind   | Start | 2021-01-01 06:10:00 |
|    1 | abc          | skip  | 2021-01-01 06:11:00 |
|    1 | abc          | skip  | 2021-01-01 06:12:00 |
|    1 | nowPlaying   | end   | 2021-01-01 06:12:00 |
+------+--------------+-------+---------------------+

The only valid timestamp would be the last one 2021-01-01 06:12:00 because rest have break in between their Start and End. Also if there is no end and a start follows, then the loop will reset.

I saw a bunch of post on SO, but it was not done in SQL, but rather programmatically. How can I use the p_t column to write a SQL that will look at events between the start and end if it exists and get the valid timestamp of end

My Query:

select id,
page_type,
case when page_type in ('browseFind') then 'Start' 
     when page_type in ('browseSearch') then 'break'
     when page_type not in ('browseFind','nowPlaying') then 'skip'
     when page_type in ('nowPlaying') then 'end'
end as p_t,
event_ts
from steps
order by event_ts;

Queries for DDL:

create table steps (id int, page_type varchar(100), event_ts timestamp);

insert into steps values (1,'browseFind','2021-01-01 6:00:00');
insert into steps values (1,'browseSearch','2021-01-01 6:00:10');
insert into steps values (1,'browseFind','2021-01-01 6:01:00');
insert into steps values (1,'x-ray','2021-01-01 6:01:30');
insert into steps values (1,'browseSearch','2021-01-01 6:02:00');
insert into steps values (1,'nowPlaying','2021-01-01 6:03:00');
insert into steps values (1,'browseFind','2021-01-01 6:10:00');
insert into steps values (1,'abc','2021-01-01 6:11:00');
insert into steps values (1,'abc','2021-01-01 6:12:00');
insert into steps values (1,'nowPlaying','2021-01-01 6:12:00');

Upvotes: 0

Views: 26

Answers (1)

Akina
Akina

Reputation: 42728

Do you need in this:

WITH
cte AS ( SELECT id, page_type, event_ts, 
                LAG(page_type) OVER (PARTITION BY id ORDER BY event_ts) lag_page_type
         FROM steps
         WHERE page_type IN ('browseFind', 'browseSearch', 'nowPlaying') )
SELECT id, page_type, event_ts
FROM cte
WHERE page_type = 'nowPlaying'
  AND lag_page_type = 'browseFind';

fiddle

Upvotes: 1

Related Questions