Stack Overflow Asked by Radagast on November 5, 2020
I’m working with PostgreSQL 12, but the question is standard SQL.
I have a table like this:
| timestamp | raw_value |
| ------------------------ | --------- |
| 2015-06-27T03:52:50.000Z | 0 |
| 2015-06-27T03:53:00.000Z | 0 |
| 2015-06-27T03:53:10.000Z | 1 |
| 2015-06-27T03:53:20.000Z | 1 |
| 2015-06-27T04:22:20.000Z | 1 |
| 2015-06-27T04:22:30.000Z | 0 |
| 2015-06-27T05:33:40.000Z | 1 |
| 2015-06-27T05:33:50.000Z | 1 |
I need to get the first and last timestamp of each group with raw_value = 1, i.e. needed result :
| start_time | end_time |
| ------------------------ | ------------------------ |
| 2015-06-27T03:53:10.000Z | 2015-06-27T04:22:20.000Z |
| 2015-06-27T05:33:40.000Z | 2015-06-27T05:33:50.000Z |
My best effort so far looks like this:
SELECT timestamp, raw_value, row_number() over w as rn, first_value(obt) OVER w AS start_time, last_value(obt) OVER w AS end_time
FROM mytable
WINDOW w AS (PARTITION BY raw_value ORDER BY timestamp GROUPS CURRENT ROW )
ORDER BY timestamp;
Google doesn’t have much info about it, but according to the docs the "GROUPS" clause is exactly what I need, but the end result is wrong, because window functions simply copy value from the timestamp column:
| timestamp | raw_value | rn | start_time | end_time |
| ------------------------ | --------- | --- | ------------------------ | ------------------------ |
| 2015-06-27T03:52:50.000Z | 0 | 1 | 2015-06-27T03:52:50.000Z | 2015-06-27T03:52:50.000Z |
| 2015-06-27T03:53:00.000Z | 0 | 2 | 2015-06-27T03:53:00.000Z | 2015-06-27T03:53:00.000Z |
| 2015-06-27T03:53:10.000Z | 1 | 1 | 2015-06-27T03:53:10.000Z | 2015-06-27T03:53:10.000Z |
| 2015-06-27T03:53:20.000Z | 1 | 2 | 2015-06-27T03:53:20.000Z | 2015-06-27T03:53:20.000Z |
| 2015-06-27T04:22:20.000Z | 1 | 3 | 2015-06-27T04:22:20.000Z | 2015-06-27T04:22:20.000Z |
| 2015-06-27T04:22:30.000Z | 0 | 3 | 2015-06-27T04:22:30.000Z | 2015-06-27T04:22:30.000Z |
| 2015-06-27T05:33:40.000Z | 1 | 4 | 2015-06-27T05:33:40.000Z | 2015-06-27T05:33:40.000Z |
| 2015-06-27T05:33:50.000Z | 1 | 5 | 2015-06-27T05:33:50.000Z | 2015-06-27T05:33:50.000Z |
At line#6 I’d expect the row number to reset to 1, but it doesn’t! I tried using BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
as well without luck.
I have created a DB Fiddle link for your convenience as well.
If there is any other way to achieve the same result in SQL (ok to be PG-specific) without window functions, I’d like to know.
Identify groups using row_number() - sum()
trick, then choose min and max time for each identified group.
with grp as (
select obt, raw_value
, row_number() over w - sum(raw_value) over w as g
from tm_series
window w as (order by obt)
)
select min(obt), max(obt)
from grp
where raw_value = 1
group by g;
DB fiddle here.
(The GROUPS
clause depends on window ordering and seems to have nothing common with your problem.)
Correct answer by Tomáš Záluský on November 5, 2020
Your updated fiddle here.
For an gaps and islands approach, first mark your transitions from raw_value = 0
to raw_value = 1
with mark_changes as (
select obt, raw_value,
case
when raw_value = 0 then 0
when raw_value = lag(raw_value) over (order by obt) then 0
else 1
end as transition
from tm_series
),
Keep only the raw_value = 1
rows, and sum()
the preceding transition
markers to place each row into a group.
id_groups as (
select obt, raw_value,
sum(transition) over (order by obt) as grp_num
from mark_changes
where raw_value = 1
)
Use group by
on these grp_num
values to get your desired result.
select min(obt) as start_time,
max(obt) as end_time
from id_groups
group by grp_num
order by min(obt);
Answered by Mike Organek on November 5, 2020
1 Asked on February 15, 2021 by christoph1197
1 Asked on February 14, 2021 by andyroo
2 Asked on February 14, 2021 by hank
2 Asked on February 14, 2021 by lars-holdaas
1 Asked on February 14, 2021 by grango
c exception memory management new operator segmentation fault
1 Asked on February 14, 2021 by ajay-kumar
1 Asked on February 14, 2021 by andrew-vo
15 Asked on February 14, 2021 by david-essien
1 Asked on February 14, 2021 by nikhil-shrivastava
0 Asked on February 13, 2021 by elighne
1 Asked on February 13, 2021 by usef-juan
1 Asked on February 13, 2021 by janpeterka
1 Asked on February 13, 2021 by mahedi-hasan-durjoy
1 Asked on February 13, 2021 by bmalbusca
2 Asked on February 13, 2021 by yeroduk
0 Asked on February 13, 2021 by alexw-h-b
5 Asked on February 13, 2021 by alvira
2 Asked on February 13, 2021 by evgeniy-golovin
2 Asked on February 13, 2021 by lv98
Get help from others!
Recent Questions
Recent Answers
© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP