postgresql, как написать такой запрос?

select 
  log.created_at as on, 
  (select 
      case when type='off' then created_at end from log as l2    
      where l2.created_at>log.created_at 
      order by created_at limit 1
  ) as off from log where type='on';

Только оно будет работать если время уникально. Если возможно более одного события ровно в одно время то нужно еще подумать.

И еще я думаю эта конструкция с case и limit скорее всего не понравится оптимизатору. Скорее всего можно придумать решение на агрегатах которое будет быстрее.

с событиями в одно и то же время легко решается

where l2.created_at >= log.created_at and log.id != l2.id

но это все равно до фига сложно, реальная таблица сложнее и запрос такой будет на километр, походу проще просто достать все данные и посчитать все на сервере, или хранимку написать.

А если три события в одно время? Тут возможны варианты.

Имхо лучше всего если события вставляются в один поток, тогда можно считать что id выделены в правильном порядке и ориентироваться на них.

даже два взаимоисключающих события в одно время это уже неопределенность получается

off 2016-10-14 10:10:02
on 2016-10-14 10:10:02

Имхо лучше всего если события вставляются в один поток, тогда можно считать что id выделены в правильном порядке и ориентироваться на них.

так а чем это отличается от простого инсерта в базу? сиквенсы постгреса не могут на два запроса вернуть один и тот же id. Любая другая реализация потока событий будет ни чем не лучше. Только это не решит проблему точности системы и как следствие актуальности данных.

Лучше не мучать базу подобным. В приложении же поток событий тривиально схлопывается в такое представление.

1) Придётся добавить граничные записи, будут несколько мешаться в результате, но зато вопрос решается;

2) исходим из того, что одновременных 'on' и 'off' быть не может, если это не так — это решаемо, но запросы придётся дорабатывать.

Собственно:

-- одноразово, подкорректировать, если timestamp'ы выходят за пределы 1900-01-02..2099-12-30
INSERT INTO log (type, created_at)
VALUES
    ('on',  '1900-01-01 00:00:00'),
    ('off', '1900-01-01 00:00:01'),
    ('on',  '2099-12-31 23:59:58'),
    ('off', '2099-12-31 23:59:59');

-- собствено, запрос
SELECT
    t.created_atb AS turn_on,
    k.created_at AS turn_off
FROM (
    SELECT
        b.id AS idb,
        b.type AS typeb,
        b.created_at AS created_atb,
        a.id AS ida,
        a.type AS typea,
        a.created_at AS created_ata
    FROM
        log a,
        log b
    WHERE
        a.type = 'on'
        AND b.type = 'on'
        AND b.created_at IN (
            SELECT MAX(c.created_at)
            FROM log c
            WHERE c.type = 'on'
                AND c.created_at <= a.created_at
                AND c.id != a.id
        )
    ) AS t
    LEFT JOIN log k
        ON k.type = 'off'
        AND k.created_at >= t.created_atb
        AND k.created_at <= t.created_ata
ORDER BY t.created_atb;

Результат у меня:

       turn_on       |      turn_off       
---------------------+---------------------
 1900-01-01 00:00:00 | 1900-01-01 00:00:01
 2016-10-14 10:10:01 | 2016-10-14 10:10:02
 2016-10-14 10:10:03 | 
 2016-10-14 10:10:04 | 2016-10-14 10:10:05
(4 rows)

При желании, первую строку можно отфильтровать.

Ну да, b.id, b.type, a.id, a.type можно не выбирать, забыл убрать из запроса.

Мб coaleasce?

Уточню по пункту 2 — не должно быть такого:

...
 347 | on   | 2016-10-14 10:10:01
...
 563 | on   | 2016-10-14 10:10:01
...

...
 211 | on   | 2016-09-23 17:14:22
...
 429 | on   | 2016-09-23 17:14:22
...

В противном случае, надо заводить дополнительный отличительный признак и обрабатывать его.

Таки налажал. Финальная версия, с фильтрацией:

SELECT
    t.created_atb AS turn_on,
    k.created_at AS turn_off
FROM (
    SELECT
        b.created_at AS created_atb,
        a.created_at AS created_ata
    FROM
        log a,
        log b
    WHERE
        a.type = 'on'
        AND b.type = 'on'
        AND b.created_at IN (
            SELECT MAX(c.created_at)
            FROM log c
            WHERE c.type = 'on'
                AND c.created_at <= a.created_at
                AND c.id != a.id
        )
    ) AS t LEFT JOIN log k
        ON k.type = 'off'
        AND k.created_at >= t.created_atb
        -- должно быть именно '<' - у нас список событий
        AND k.created_at < t.created_ata
WHERE
    t.created_atb > '1900-01-02 00:00:00'
ORDER BY
    t.created_atb;

А ты не хочешь сразу держать в базе записи вида «начало конец» и править их или апдейтить при вводе? Просто тоже приходилось работать с состояниями и большим количеством переключений, и как-то проще способа не нашел.

Я подозреваю (возможно, беспочвенно), что у топикстартера то, что кидает записи в базу, является неким stateless устройством, и база поэтому является списком событий, а не состояний — таких случаев просто море, например, у нас АТС кидает CDR именно так - «начало звонка», «звонок идёт»,.. «окончание звонка», причём, теоретически, станция может помереть во время звонка, и записи «окончание звонка» может просто не быть. Мой запрос, собственно, и производит конвертацию из списка событий в список состояний.

Так а почему бы не делать это при добавлении записей?

Ну например пришло начало звонка, создали запись вида

start--+--end
время  |  Null

Если пришел off — добавляем время вместо Null для последней записи, начало которой случилось до времени off, нет?

Что значит «добавляем»? Вы слишком абстрагируетесь. То, что Вы предлагаете, подразумевает умение регистратором (назовём его так) запоминать параметр «начало звонка» и умение выдавать на, например, сигнал «on» команду в базу «INSERT ...», на сигнал «off» — команду «UPDATE ... WHERE ...», и тому подобный высокий интеллект, причём, если первый INSERT по какой-то причине потеряется, то и обратную смену состояния мы потеряем, разве что регистратор будет убеждаться в наличии такой записи, ну и т. д. А так мы имеет простенький агент, который INSERT'ом кидает в базу дату/время события и тип события, и всё, остальное мы уже можем получить сами.

Да я в результате пришел к выводу что делать такое на sql нету смысла, нужен тривиальный мап-редьюс, + можно будет кидать в него события в реальном времени параллельно с базой.

сложность увеличивается, надежность уменьшается, скорость работы уменьшается, еще и надо заранее определиться с тем какую стату собирать и в каком виде, а профита по сути 0.