Question 208099 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL query to identify continuous events that satisfy a condition group by product id

I have data that looks as follows.

timestamp productid measure
2022-01-01 00:00:00 1 4
2022-01-01 00:01:00 1 5
2022-01-01 00:02:00 1 6
2022-01-01 00:01:00 2 5
2022-01-01 00:00:00 2 7
2022-01-01 00:01:00 3 9

The table records the telemetry of the product at 1-minute intervals.
The table has an incremental id which is not in any way uniquely identifiable. The primary index is a combination of productid and timestamp.

I want a query that can select productid and timestamp of the 1st row where the measure is between 4 and 5 for 10 continuous timestamps when the data is partitioned by productid and ordered by timestamp.

I tried creating a list of all measures that satisfy the condition and then seeing if they are continuous, but it is taking really long.

Tags:

2

Answers


  1. 0

    Not entirely sure, but does this give you what you need?

    SELECT min(timestamp), productid FROM "table"
where measure between '4' and '5'
group by productid
    Login or Signup to reply.
  2. 0

    Another gaps and islands problem. You say "over 10" so I will treat a run of any length as one unit:

    with data as (
    select *,
        case when measure between 4 and 5 then 1 else 0 end as qualified
    from T
), transitions as (
    select *,
        case when lag(qualified, 1, 0) 
            over (partition by productid order by timestamp) = 0 and qualified = 1
            then 1 else 0 end as flag
   from data
), groups as (
    select *,
        sum(flag) over (partition by productid order by timestamp) as grp
    from transitions
)
select productid, min(timestamp) as begintime, sum(qualified) as length
from groups
group by productid, grp;
having sum(qualified) > 10 -- or possibly you want>= 10?
order by productid, begintime;

    The problem doesn’t fundamentally change whether you need to partition by product or not. Just comment out those sections if not. The trick is to mark the spots where there’s a transition and based on that the subsequent run of rows can be associated with a common and unique number (grp).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search