SQL для связывания строк по значению поля

У меня есть таблица:

create table transaction_log (
  id serial, 
  operation_type character varying(36),
  date timestamp with time zone,
  sum   double precision,
  user_id integer,
  PRIMARY KEY(id)
);

Он поддерживает два типа операций: block и unblock для нескольких пользователей (Contragent_id) и лоты (lot_id) и поле даты и времени operation.

  • Один пользователь может иметь несколько блокированных и разблокированных операций
  • Записи с блочной операцией могут иметь одну операцию разблокировки после.
  • Блокировка и разблокировка последовательны. Для пользователя должна быть блокировка, и следующая блокировка может быть только после того, как произошла операция разблокировки.
  • Дата и время разблокировки могут быть такими же, как и для блокировки. Это значит заблокирован и мгновенно разблокирован.
  • Один пользователь может иметь несколько последовательностей операций блокировки и разблокировки на смешанной временной шкале.
  • id уникален

Например:

id, sum, operation_type, date, user_id
1, 5900, blocked, 2018-01-05 11:00, 1
2, 3500, blocked, 2018-01-08 12:00, 2
3, 5900, unblock, 2018-02-11 09:00, 1
4, 1000, blocked, 2018-01-09 05:00, 3
5, 3500, unblock, 2018-01-24 19:00, 2 

Поэтому мне нужно, чтобы SQL извлекал все операции блока с датой соответствующей операции разблокировки, если она существует. Например: block_ID, sum, blocked_date, unblock_date. Итак, из данных примера мне нужно получить: Например:

block_ID, sum, blocked_date, unblock_date
1, 5900, 2018-01-05 11:00, 2018-02-11 09:00
2, 3500, 2018-01-08 12:00, 2018-01-24 19:00
4, 1000, blocked, 2018-01-09 05:00, null

Я полагаю, что для этого мне нужен оператор WITH, но я не могу понять, как правильно сопоставить записи.

Любая помощь приветствуется.

Кстати, Postgres 9.4

выборочные данные и ожидаемый результат более плодотворны

Zaynul Abadin Tuhin 06.09.2018 18:24

Пример данных с SQL Fiddle sqlfiddle.com/#!15/68b3c/9

Zhlobopotam 06.09.2018 19:48

Извините, я только что узнал, что у нас работает версия 9.4.

Zhlobopotam 07.09.2018 09:26

Таким образом, для пользователя не может быть двух заблокированных записей, следующих друг за другом; между ними должна быть запись о разблокировке. То же и наоборот: не может быть двух записей разблокировки, следующих друг за другом; между ними должна быть заблокированная запись. Вы говорите, что два события могут произойти в одно и то же время. Всегда ли это означает «заблокирован и мгновенно разблокирован» или может также означать «разблокирован и мгновенно снова заблокирован»?

Thorsten Kettner 07.09.2018 09:45

да. Обновлено описание. два события могут происходить с одной и той же меткой времени, что означает «заблокировано и мгновенно разблокировано»

Zhlobopotam 07.09.2018 10:06
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
5
57
2

Ответы 2

Вы можете попробовать способ ниже

with block as
     (
    select * from transactions
    where operation='blocked'
    ),
    unblock as 
    (
    select * from transactions
    where operation='unblock'
    )
    select block.id as block_ID, block.sum,
    block.date, unblock.date from block
    left join unblock on block.user_id=unblock.user_id

Это не сработает. Идентификатор уникален для каждой записи в таблице, а не для пары блок-разблокировка. Я добавил примеры к вопросу.

Zhlobopotam 06.09.2018 19:46

@Zhlobopotam помогает?

Zaynul Abadin Tuhin 06.09.2018 22:36

Пример работает, но с более реальной датой - nope sqlfiddle.com/#!17/99da1/7 (здесь операция блока с тем же lot_id и contragent_id должна соответствовать следующей записи разблокировки с тем же lot_id и contragent_id

Zhlobopotam 07.09.2018 09:18

@Zhlobopotam, не могли бы вы дать мне ожидаемый результат в соответствии с вашими реальными данными?

Zaynul Abadin Tuhin 07.09.2018 09:47

Для примера операции блока данных с идентификатором 34788321 должна быть установлена ​​дата разблокировки записи с идентификатором 34788322. Запись 34795864 будет иметь дату от 34795866 и так далее.

Zhlobopotam 07.09.2018 10:08

Если ваши данные согласованы, вы просто ищете минимальную дату разблокировки, следующую за датой блокировки. Вы можете получить это в подзапросе в предложении SELECT:

select user_id, sum, date as block,
(
  select min(ub.date)
  from blocktable ub
  where ub.operation_type = 'unblock'
  and ub.user_id = b.user_id
  and ub.date >= b.date
) as unblock
from blocktable b
where operation_type = 'blocked';

Или в предложении FROM с боковым соединением:

select b.user_id, b.sum, b.date as block, ub.unblock
from blocktable b
left join lateral
(
  select min(ub.date) as unblock
  from blocktable ub
  where ub.operation_type = 'unblock'
  and ub.user_id = b.user_id
  and ub.date >= b.date
) b
where operation_type = 'blocked';

При боковом соединении можно получить даже целый ряд:

select *
from blocktable b
left join lateral
(
  select *
  from blocktable ub
  where ub.operation_type = 'unblock'
  and ub.user_id = b.user_id
  and ub.date >= b.date
  order by ub.date
  fetch first 1 row only
) as unblock
where operation_type = 'blocked';

Другой вариант получения единственной даты - LEAD:

select user_id, sum, block, unblock
from
(
  select
    user_id,
    sum,
    date as block,
    lead(date) over (partition by user_id order by date, operation_type) as unblock,
    operation_type
  from mytable
) block_and_unblock
where operation_type = 'blocked';

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

Другие вопросы по теме