1

Say we have a table setup like this:

+---------+--------------+---------------+
| id text | time integer | value decimal |
+---------+--------------+---------------+
|       1 |            1 |            10 |
|       1 |            2 |            10 |
|       1 |            3 |             7 |
|       1 |            4 |             8 |
|       1 |            5 |             6 |
|       1 |            6 |             5 |
|       1 |            7 |             4 |
|       1 |            8 |             3 |
|       1 |            9 |             7 |
|       1 |           10 |            11 |
|       1 |           11 |             3 |
|       1 |           12 |             8 |
|       1 |           13 |             2 |
|       1 |           14 |             4 |
|       1 |           15 |             1 |
+---------+--------------+---------------+

Would it be possible to select the starting and ending times for when id is under the same condition for a certain amount of time? Say we wanted to look at ID 1 where the value is < 10 for at least seconds. So the resulting query would return:

+------------+----------+
| start_time | end_time |
+------------+----------+
|         11 |       15 |
|          3 |        9 |
+------------+----------+
0
3

A slight variation of Vérace very detailed answer. The idea is to enumerate the rows per id, and per id plus, if the condition holds. I named the difference grp. If grp changes it means that there is a change to whether the condition holds. I.e. min and max grouped by grp will apply to intervals where condition either holds or fails.

I used a having clause for the duration condition.

select id, min(the_time), max(the_time) -- min and max per interval
from (
    select id, the_value, the_time
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time) 
         - row_number() over (partition by id
                                 , case when the_value < 10 
                                        then 1
                                        else 0 
                                   end 
                              order by the_time) as grp
    from tab
) as x
where the_value < 10 -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4; -- duration

The added sample data results in:

1   3   9
1   11  15

It is possible to determine the condition in one place by nesting the query, but I'm not sure whether that is useful or not:

select id, MIN(the_time) AS min, MAX(the_time) AS max -- min and max per interval
from (
    select id, the_value, the_time, condition
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time) 
         - row_number() over (partition by id, condition 
                              order by the_time) as grp
   from (
       select id, the_value, the_time, the_value < 10 as condition
       from tab
   ) as a
) as x
where condition -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4 -- duration
ORDER BY id, min;
1
  • 1
    Lovely piece of SQL - PARTITIONing by CASE - really elegant - didn't know you could do that! Much as I hate to say it, I think the OP should give you the correct answer vote! +1 from me too! Fiddle here shows what's happening. – Vérace Nov 25 '19 at 21:14
1

In order to solve this problem, I did the following (fiddle here - the DDL, DML and final SQL are also at the end of this answer):

EDIT: Simplified and correct (correct answer anyway!) SQL posted to dbfiddle here. The inner loop of the SQL has been modified slightly (and simplified), but the train of thought can still be followed in the original fiddle for those interested in working through it, by swapping the new fiddle's inner loop for the old fiddle's inner loop and then running the old fiddle's queries with the new loop! @Lennart 's solution is more elegant than mine.

Created the table:

CREATE TABLE tab
(
  id TEXT NOT NULL,
  the_time INTEGER NOT NULL,
  the_value INTEGER NOT NULL
);

and populated it:

INSERT INTO tab
VALUES
(1, 1, 10), (1, 2, 10), (1, 3, 7), (1, 4, 8), (1, 5, 6), (1, 6, 5), (1, 7, 4),
(1, 8, 3), (1, 9, 7), (1, 10, 10), (1, 11, 12),
(2, 1, 10), (2, 2, 10), (2, 3, 10), (2, 4, 13), (2, 5, 6), (2, 6, 5), (2, 7, 4),
(2, 8, 3), (2, 9, 7),
(2, 10, 10), (2, 11, 12);

You will notice that I've added another id with slightly different data - I imagine that there would be more than one id anyway, and in any case, it helped with testing. I also added a datum to the original (1, 11, 12) because this helped me out of a tricky situation with NULLs. Since this is time series type data, I imagine there'll be no "end point" as such, so that could be OK. In any case, I'll leave it up to you to deal with the NULL logic if that's an issue, or just flag it for my attention.

Rather than just dump the solution in your lap, I'm going to go through it step by step - not only will this hopefully help you, but it helps me remember also! I've done this type of thing before, but I've rarely seen it so well expressed in English as here. I haven't used all of the extra fields that I've generated, but I've left them in as "fossils" so that it's easier to follow my train of thought and see how the solution evolves.

First, I ran this SQL:

SELECT 
  id, the_time, 
  LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
  the_value,
  LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
  CASE 
    WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
      AND     the_value < 10) 
    THEN 1
    WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
      AND      the_value >= 10)
    THEN 1
    ELSE 0
  END AS change
FROM tab;

Result (snipped for brevity):

id  the_time    lag the_value   lead    change
 1         1     10        10     10         1
 1         2     10        10      7         0
 1         3     10         7      8         1  << Major change of interest happens here!
 1         4      7         8      6         0
 1         5      8         6      5         0

EDIT in response to OP's question in comments:

The key to this first piece of SQL is:

  CASE 
    WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 -- 1st CASE
      AND     the_value < 10) 
    THEN 1
    WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
      AND      the_value >= 10) -- the 2nd CASE
    THEN 1
    ELSE 0
  END AS change

What this does is set the value of change to 1 whenever the_value transitions from (above or equal to 10) to below 10 - the first CASE and then again when it transitions back from (below 10) to (10 or above) - the second CASE.

Then, on this "inside loop", I ran the following:

SELECT id, the_time, SUM(change) OVER (PARTITION BY id ORDER BY id, the_time) AS the_sum
FROM
(
  SELECT 
    id, the_time, 
    LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
    the_value,
    LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
    CASE 
      WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
        AND     the_value < 10) 
      THEN 1
      WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
        AND      the_value >= 10)
      THEN 1
      ELSE 0
    END AS change
  FROM tab
) AS t1
ORDER BY id, the_time;

Result (again, snipped for brevity):

id  the_time    the_sum
 1         1          1
 1         2          1
 1         3          2   <<-- Again, the change of interest at time = 3!
 1         4          2
 1         5          2

EDIT in response to OP's query:

What this does is increment the SUM as it moves down through the derived table from query 1, incrementing at the boundaries where there is one of the changes mentioned above - either from above or equal to 10 to below 10 or the other way round.

Using this derived table, I ran this SQL:

SELECT id, MIN(the_time) AS the_min, MAX(the_time) AS the_max, the_sum
FROM
(
  SELECT id, the_time, SUM(change) OVER (PARTITION BY id ORDER BY id, the_time) AS the_sum
  FROM
  (
    SELECT 
      id, the_time, 
      LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
      the_value,
      LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
      CASE 
        WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
          AND     the_value < 10) 
        THEN 1
        WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
          AND      the_value >= 10)
        THEN 1
        ELSE 0
      END AS change
    FROM tab
    ) AS t1
    ORDER BY id, the_time
  ) AS t2 
GROUP BY id, the_sum
ORDER BY id, the_sum;

Result (not snipped):

id  the_min the_max the_sum
 1        1       2       1
 1        3       9       2
 1       10      11       3
 2        1       1       1
 2        2       2       2
 2        3       4       3
 2        5       9       4
 2       10      11       5

From this we have all the "islands" in the dataset. The two of interest to us are 3 - 9 for id = 1 and 5 - 9 for id = 2 (I have chosen >= 5 as the length of the islands of interest to us, since one isn't specified in the question).

EDIT in response to OP's query:

By taking the MIN() and MAX() of the_time and then doing a GROUP BY the_sum (and id, but that's not important here), we get the minimum of time where a given "island transition" started and the maximum of time where that "island transition" finished. Take this record from the derived table here:

 1        3       9       2

This tells us that, for id = 1, that from times 3 - 9, there was a run whose sum was 2. The fact that both are below 10 tells us that it was a run of below 10's - the very data we're looking for.

So, I then surrounded the SQL above with this (full working SQL on fiddle and bottom of question):

SELECT 
  id, 
  the_min AS start_time, 
  the_max AS end_time, 
  (the_max - the_min) + 1 AS the_run_length  -- Optional, not requested
FROM
(
   Inner SQL above
)
WHERE ((the_max - the_min) + 1) >= 5  -- always brackets, operator precedence can be tricky!
AND (the_min < 10)
AND (the_max < 10)  -- don't want runs of greater than or = 10, only less than 10!
ORDER BY id, the_min;

Result (full):

id  start_time  end_time    the_run_length
 1           3         9                 7
 2           5         9                 5

EDIT in response to OP's query:

So, here we eliminate all of the unimportant islands by using the WHERE clause to eliminate all the islands that are either not below 10 OR not >= 5.

And, we can see from inspection of the raw data that there are only two runs of the_value < 10 where the length of the run is >= 5. You may wish to "tidy up" the SQL using Common Table Expressions (aka the WITH clause). They can help to make SQL more readable - YMMV, but I'll leave that to you!

A couple of points. You should never use SQL keywords (i.e. time) for table or column names - you're only asking for trouble down the road if ever you want to port your SQL, plus it makes the SQL difficult to read and bug-prone! Secondly, if you are asking questions in the future, could you please give your table data in the form of DDL and DML - typing stuff in manually is also error-prone and tricky. Help us to help you! +1 for an interesting question!

EDIT in response to OP's query:

If you're having difficulty with this, I suggest that you go through the different queries from the beginning, from the inside out - experiment with removing WHERE conditions - add some more data and see what's happening. Window (or Analytic) functions are very powerful and will repay the effort you put into learning them by orders of magnitude!

=================== DDL, DML and final SQL =====================

DDL:

CREATE TABLE tab
(
  id TEXT NOT NULL,
  the_time INTEGER NOT NULL,
  the_value INTEGER NOT NULL
);

DML:

INSERT INTO tab
VALUES
(1, 1, 10), (1, 2, 10), (1, 3, 7), (1, 4, 8), (1, 5, 6), (1, 6, 5), (1, 7, 4),
(1, 8, 3), (1, 9, 7), (1, 10, 10), (1, 11, 12),
(2, 1, 10), (2, 2, 10), (2, 3, 10), (2, 4, 13), (2, 5, 6), (2, 6, 5), (2, 7, 4),
(2, 8, 3), (2, 9, 7),
(2, 10, 10), (2, 11, 12);

Final SQL:

SELECT 
  id, 
  the_min AS start_time, 
  the_max AS end_time, 
  (the_max - the_min) + 1 AS the_run_length  -- Optional, not requested
FROM
(
  SELECT id, MIN(the_time) AS the_min, MAX(the_time) AS the_max, the_sum
  FROM 
  (
    SELECT id, the_time, SUM(change) OVER (PARTITION BY id ORDER BY id, the_time) AS the_sum
    FROM
    (
      SELECT 
        id, the_time, 
        LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
        the_value,
        LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
        CASE 
          WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
            AND     the_value < 10) 
          THEN 1
          WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
            AND      the_value >= 10)
          THEN 1
          ELSE 0
        END AS change
      FROM tab
      ) AS t1
      ORDER BY id, the_time
    ) AS t2 
    GROUP BY id, the_sum
    ORDER BY id, the_sum
  ) AS t3
WHERE ((the_max - the_min) + 1) >= 5  -- always brackets, operator precedence can be tricky!
AND (the_min < 10)
AND (the_max < 10)  -- don't want runs of `>` than or `=` 10, only less than!
ORDER BY id, the_min;
2
  • Thanks for the quick and detailed response but could you explain the change column a bit, or the entirety of the first query? Am i correct in saying that change is set to 1 when previous row does not match the same condition as the current row (i.e. if the last row failed and so did this one then its 0 but if the last one failed and this one didn't then it 1) – dotconnor Nov 24 '19 at 9:00
  • I put in a few edits which will hopefully clarify the SQL for you - it may take a bit of time, but it's worth sitting down and getting to grips with Window (Analytic) functions! Best... – Vérace Nov 24 '19 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.