how to resolve this sql question without using window function

user_6952650 0
Viewed 67

DDL:

create table domain_lifecycle (
domain varchar,
timestamp timestamp,
lifecycle varchar);

DML:

insert into domain_lifecycle values
('example_1.com', '2020-01-01 01:01:00', 'start'),
('example_1.com', '2020-01-01 01:01:05', 'end'),
('example_2.com', '2020-01-01 01:01:07', 'start'),
('example_2.com', '2020-01-01 01:01:17', 'end'),
('example_3.com', '2020-01-01 01:01:20', 'start'),
('example_1.com', '2020-01-01 01:01:25', 'start'),
('example_3.com', '2020-01-01 01:01:23', 'end'),
('example_1.com', '2020-01-01 01:01:38', 'end'),
('example_3.com', '2020-01-01 01:01:40', 'start'),
('example_3.com', '2020-01-01 01:01:42', 'end');

Question: find the domains whose any of the lifecycles (the time elapsed between lifecycle start and end) is at least 5 seconds.

I know how to use window function (lag()) to resolve this:

select distinct domain from
(select *, 
       lag(timestamp) over(partition by domain order by timestamp) as pre_timestamp 
from domain_lifecycle) t
where lifecycle = 'end'
  and extract(epoch from (timestamp - pre_timestamp)) >= 5;

expected result:

enter image description here

How can we resolve this question without using window function? Thanks.

Update: I updated the above DML by adding one more lifecycle(the last two rows) for domain example_3.com to make it clear that the 5 seconds criterion only applies to the same lifecycle that has adjacent start and end for the same domain (it could be separated by other domains' lifecycle though).

postgresql sql
3 Answers

One alternative method is to use a lateral join to select the next end for each start.

SELECT DISTINCT
       domain
       FROM domain_lifecycle c1
            CROSS JOIN LATERAL (SELECT min(timestamp) "timestamp"
                                       FROM domain_lifecycle c2
                                       WHERE c2.domain = c1.domain
                                             AND c2.lifecycle = 'end'
                                             AND c2.timestamp >= c1.timestamp) c3
       WHERE c1.lifecycle = 'start'
             AND c3.timestamp - c1.timestamp >= '5 seconds'::interval;

db<>fiddle

user_96614240

You can also use EXISTS :

SELECT DISTINCT domain
FROM domain_lifecycle dlc
WHERE dlc.lifecycle = 'start'
AND EXISTS
    (SELECT 1
     FROM
       (SELECT MIN(TIMESTAMP) "end_time"
        FROM domain_lifecycle dlc2
        WHERE dlc.domain = dlc2.domain
          AND dlc2.timestamp > dlc.timestamp
          AND dlc2.lifecycle = 'end'
        ) t
     WHERE extract(epoch from t.end_time - dlc.timestamp) >= 5
    );

Demo db<>fiddle

user_13865510

Your lag() is getting domains where the "end" lifecycle is 5 or more seconds after the previous lifecycle.

The simplest method to replace your specific query is probably exists:

select distinct dl.domain
from domain_lifecycle dl
where dl.lifecycle = 'end' and
      exists (select 1
              from domain_lifecycle dl2
              where dl2.domain = dl.domain and
                    dl2.timestamp < dl.timestamp and
                    extract(epoch from (dl.timestamp - dl2.timestamp)) >= 5
             );

However, your data and explanation suggest a simpler approach -- aggregation and having:

select dl.domain
from domain_lifecycle dl
group by dl.domain
having extract(epoch from (max(dl.timestamp) - min(dl.timestamp))) >= 5;

Or as I prefer to write it:

select dl.domain
from domain_lifecycle dl
group by dl.domain
having max(dl.timestamp) >= min(dl.timestamp) + interval '5 second';

If you can have lifecycles values other than 'start' and `'end', then you can add a filter to these queries to focus on these two values (this is true of the query in your question as well).

Here is a db<>fiddle.

EDIT:

Your actual question can be rephrased as: Does a domain have any "end"s with no "start" in the preceding five seconds. This can be solved using:

select distinct dl.domain
from domain_lifecycle dl
where dl.lifecycle = 'end' and
      not exists (select 1
                  from domain_lifecycle dl2
                  where dl2.domain = dl.domain and
                        dl2.lifecycle = 'start' and
                        dl2.timestamp < dl.timestamp and
                        dl2.timestamp >= dl.timestamp - interval '5 second
                 );

This can use an index on (domain, lifecycle, timestamp). Because there is no aggregation and the time range is limited, this might have the best performance profile. That said, window functions are better suited to this problem.

user_11440350
Related