consider the below table:
| id | quantity | timestamp | value | flag |
|---|---|---|---|---|
| 1 | temperature | some TS | 30 | 0 |
| 2 | pressure | some TS | 10 | 0 |
| 3 | temperature | some TS | 30 | 3 |
| 4 | pressure | some TS | 10 | 1 |
| 5 | temperature | some TS | 30 | 0 |
| 6 | pressure | some TS | 10 | 2 |
| 7 | temperature | some TS | 30 | 3 |
| 8 | pressure | some TS | 10 | 0 |
| 9 | temperature | some TS | 30 | 3 |
| 10 | pressure | some TS | 10 | 0 |
I need to create a select statement to get above data in an interval of 5 minutes and grouped by timestamp & quantity.
The grouping needs to follow the below conditions:
- if in an interval, all the rows have a flag of either 0/1/2 then the value will be average of all values and the flag will be the highest occurance of flag in the specific interval. If there are same flag counts then the maximum flag will be added. Ex: if in an interval of 6 rows, which have 3 each of flag 1 and 2, then the resultant flag will be 2
- if in an interval, some rows contain flag of 3 and majority of rows contain 0/1/2, then consider only rows with 0/1/2 rows for averaging the value and consider above condition 1 for flag calculation in valid rows only.
- if in an interval, the majority of rows are of flag 3, then average the value to all rows and then assign the flag to 3.
I have tried to solve this issue, but i couldn't.
