By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table T (ts timestamp, val decimal(4, 2));
INSERT INTO T (ts, val)
VALUES
('2022-01-01 00:00:00', 0.89),
('2022-01-01 10:01:00', 6.89),
('2022-01-02 10:01:21', 10.99),
('2022-01-02 10:07:00', 11.89),
('2022-01-02 12:01:00', 0.89),
('2022-01-02 13:07:00', 6.39),
('2022-01-02 14:00:00', 0.69),
('2022-01-03 14:02:00', 5.39),
('2022-01-03 15:04:00', 6.89),
('2022-01-03 15:06:00', 7.3),
('2022-01-03 15:10:00', 1.89),
('2022-01-03 15:50:00', 0.8);
CREATE TABLE
INSERT 0 12
with flagged as (
select *,
case when
lag(val, 1, 0.0) over (order by ts) <= 5
and val > 5 then 1 end as flag
from T
), grouped as (
select *,
count(flag) over (order by ts) as grp
from flagged
)
select
min(ts),
max(case when val > 5 then ts end)
from grouped
group by grp
having max(val) > 5
order by grp;
| min | max |
|---|---|
| 2022-01-01 10:01:00 | 2022-01-02 10:07:00 |
| 2022-01-02 13:07:00 | 2022-01-02 13:07:00 |
| 2022-01-03 14:02:00 | 2022-01-03 15:06:00 |
SELECT 3