У меня есть таблица:
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, 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
Пример данных с SQL Fiddle sqlfiddle.com/#!15/68b3c/9
Извините, я только что узнал, что у нас работает версия 9.4.
Таким образом, для пользователя не может быть двух заблокированных записей, следующих друг за другом; между ними должна быть запись о разблокировке. То же и наоборот: не может быть двух записей разблокировки, следующих друг за другом; между ними должна быть заблокированная запись. Вы говорите, что два события могут произойти в одно и то же время. Всегда ли это означает «заблокирован и мгновенно разблокирован» или может также означать «разблокирован и мгновенно снова заблокирован»?
да. Обновлено описание. два события могут происходить с одной и той же меткой времени, что означает «заблокировано и мгновенно разблокировано»
Вы можете попробовать способ ниже
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 помогает?
Пример работает, но с более реальной датой - nope sqlfiddle.com/#!17/99da1/7 (здесь операция блока с тем же lot_id и contragent_id должна соответствовать следующей записи разблокировки с тем же lot_id и contragent_id
@Zhlobopotam, не могли бы вы дать мне ожидаемый результат в соответствии с вашими реальными данными?
Для примера операции блока данных с идентификатором 34788321 должна быть установлена дата разблокировки записи с идентификатором 34788322. Запись 34795864 будет иметь дату от 34795866 и так далее.
Если ваши данные согласованы, вы просто ищете минимальную дату разблокировки, следующую за датой блокировки. Вы можете получить это в подзапросе в предложении 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';
Если вам нужен запрос, который отлично работает даже в случае несоответствий (особенно двух заблокированных или двух разблокированных записей, следующих друг за другом), вам, возможно, придется выбирать данные последовательно, то есть в рекурсивном запросе или в приложении.
выборочные данные и ожидаемый результат более плодотворны