Блог

Я работаю с PostgreSQL 12, но вопрос в стандартном SQL. У меня есть таблица, подобная этой:

 | 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         |
  

Мне нужно получить первую и последнюю временную метку каждой группы с raw_value = 1, т.е. необходимый результат :

 | 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 |
  

Мои лучшие усилия на данный момент выглядят так:

 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 не так много информации об этом, но, согласно документам, предложение «GROUPS» — это именно то, что мне нужно, но конечный результат неверен, потому что оконные функции просто копируют значение из столбца timestamp:

 | 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 |
  

В строке # 6 я ожидал бы, что номер строки будет сброшен на 1, но этого не происходит! Я также пытался использовать BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING , но безуспешно.

Я также создал ссылку на DB Fiddle для вашего удобства.

Я хотел бы знать, есть ли какой-либо другой способ добиться того же результата в SQL (хорошо, чтобы быть специфичным для PG) без оконных функций.

Определите группы с помощью row_number() - sum() трюка, затем выберите минимальное и максимальное время для каждой идентифицированной группы.

 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;
  

Здесь скрипка с БД.

( GROUPS Предложение зависит от порядка окон и, похоже, не имеет ничего общего с вашей проблемой.)

Ваша обновленная скрипка здесь.

Для подхода с пробелами и островами сначала отметьте переходы с raw_value = 0 на 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
), 
  

Сохраните только raw_value = 1 строки и sum() предыдущие transition маркеры, чтобы поместить каждую строку в группу.

 id_groups as (
  select obt, raw_value, 
         sum(transition) over (order by obt) as grp_num
    from mark_changes
   where raw_value = 1
)
  

Используйте group by эти grp_num значения, чтобы получить желаемый результат.

 select min(obt) as start_time, 
       max(obt) as end_time
  from id_groups
 group by grp_num
 order by min(obt);