CODEWITHSUNDEEP
sql
Raskolnikov
Raskolnikov

Reputation: 3999

SQL Group By within some time frame

I need to group by id, status within some timestamp.

for example I have this table:

id    | status | time   | value | deviceId
1     |  true  | 10:31  |   1   |      5 
2     |  true  | 10:32  |   2   |     5 
3     |  true  | 10:33  |   3   |     5
4     |  false | 10:34  |   3   |    5 
5     |  false | 10:35  |   4   |   5 
6     |  false | 10:36  |   5   |    5 
7     |  true  | 10:37  |   4   |    5
8     |  true  | 10:38  |   5   |    5
9     |  true  | 10:39  |   6   |  5

Table is ordered by time.

Every group should be for same id, if status is true, within time were status is true.

For same groupId I will need new results when status become true, but this is in another time frame so should be another group.

Based on above example result should be:

deviceId  | avg(value)
5 | 2 (average value for rows 1,2,3 for deviceid = 5)
5 | 5       (average value of rows 7,8,9 for deviceid = 5. Same group, but another time frame)

I can group by deviceId and group by status.

SELECT  deviceid ,status, AVG(value)
FROM mytable
WHERE status = true
GROUP BY deviceid,status;

But I don't know how to do all that within time frame? Thank you for your help.

Edit:

I tried to explain what is time frame. Maybe my English is not good enough so I will try again. You can see that time is in order. Table is ordered by time.

Rows 1,2,3 are within time frame while status i true.

In row 4 status is false so this is time when second time frame starts. Second time frame are rows 4,5,6.

In row 7 status is again true, so here third time frame starts. Third time frame are rows 7,8,9

I just need results of groups where status is true, so only first and third time frame are relevant to me.

Upvotes: 1

Views: 577

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can determine the groups of "true"s by doing a cumulative count of "false"s. This count will be constant for a group of consecutive "true"s.

The rest is then just filtering and aggregation:

select deviceid, grp, avg(value)
from (select t.*,
             sum(case when status = 'false' then 1 else 0 end) over (partition by deviceid order by time) as grp
      from t
     ) t
where status = 'true'
group by deviceid, grp;

Upvotes: 2

Related Questions