Сложный SQL-запрос для соответствия между двумя таблицами

Я все еще работаю над тем же проектом, что и когда задал свой предыдущий вопрос о стеке. Переполнение. Мои таблицы SQL полностью описаны в этом вопросе, и я прошу вас прочитать это, чтобы понять мою новую проблему.

Разница теперь в том, что две таблицы trades и events больше не синхронизированы: теперь время в этих двух таблицах не совсем совпадает. . Однако я знаю, что между двумя таблицами по-прежнему существует однозначное соответствие, а это означает, что каждая сделка имеет соответствующее событие, но некоторые события em> не соответствует сделке.

Торги :

  id |   time    |  price  | volume |   foo
-----+-----------+---------+--------+-------
 201 | 32400.524 |      53 |   2085 |   xxx
 202 | 32400.530 |      53 |   1162 |   xxx
 203 | 32400.531 |   52.99 |     50 |   xxx
 204 | 32401.532 |   52.91 |   3119 |   xxx
 205 | 32402.437 |   52.91 |   3119 |   xxx
 206 | 32402.832 |   52.91 |   3119 |   xxx
 207 | 32403.255 |   52.91 |   3119 |   xxx
 208 | 32404.242 |   52.92 |   3220 |   xxx
 209 | 32405.823 |   52.92 |   3220 |   xxx
 210 | 32406.839 |   52.92 |   3220 |   xxx

События :

   id |   time    |  price  | volume |  bar 
-----+-----------+---------+--------+------
 328 | 32399.345 |   52.91 |   3119 |  yyy
 329 | 32400.964 |   52.91 |   3119 |  yyy
 330 | 32401.194 |   52.91 |   3119 |  yyy
 331 | 32401.746 |   52.91 |   3119 |  yyy
 332 | 32401.823 |   52.91 |   3119 |  yyy
 333 | 32402.534 |   52.91 |   3119 |  yyy
 334 | 32402.876 |   52.92 |   3220 |  yyy
 335 | 32403.839 |   52.92 |   3220 |  yyy
 336 | 32404.634 |   52.92 |   3220 |  yyy
 337 | 32405.234 |   52.91 |   2501 |  yyy

Я хочу установить соответствие между двумя таблицами, минимизировав разницу во времени между сделкой и событием. Это имеет смысл: если есть несколько событий, соответствующих сделке по объему и цене, мы должны взять событие, которое наименее "далеко во времени" от сделки.

Я попытался сделать следующее:

SELECT 
    t.*,
   (SELECT e.id
        FROM events o
        WHERE e.price = t.price
        AND e.volume = t.volume
        ORDER BY ABS(o.time - t.time)
        LIMIT 1
    ) as most_probable_corresponding_event_id
FROM trades t
ORDER BY t.time;

Но проблема в том, что этот запрос не дает однозначного соответствия: одно и то же событие e можно выбрать для разных сделок t1 и t2, если это событие является ближайшим из обеих сделок t1 и t2. Чего я хочу, так это вести эксклюзивную переписку.

Спасибо за помощь.

РЕДАКТИРОВАТЬ :

Результат, который я ожидаю для примера данных, будет:

   trade_id | order_id |  price  | volume |  bar |   foo 
 -----------+----------+---------+--------+------+-------
      204   |   331    |   52.91 |   3119 |  xxx |   yyy
      205   |   333    |   52.91 |   3119 |  xxx |   yyy
      206   |   334    |   52.91 |   3119 |  xxx |   yyy
      207   |   335    |   52.92 |   3220 |  xxx |   yyy
      208   |   336    |   52.92 |   3220 |  xxx |   yyy
      209   |   337    |   52.92 |   3220 |  xxx |   yyy

sql postgresql-9.4
person Edouard Berthe    schedule 15.12.2015    source источник
comment
не могли бы вы добавить ожидаемый результат?   -  person davejal    schedule 15.12.2015
comment
Я добавил ожидаемый результат для примера данных. Цель состоит в том, чтобы просто установить соответствие, чтобы данные xxx и yyy были вместе только в одной таблице.   -  person Edouard Berthe    schedule 15.12.2015
comment
Каким будет правильное соответствие, если есть два соответствия вверху и внизу? например, ваше время: 32399.345 а есть 32400.345 и 32398.345 ?   -  person Jorge Campos    schedule 15.12.2015
comment
Выбираем один из двух, допустим, что мы выбираем первый. Цель моего предыдущего вопроса заключалась в том, чтобы разделить по времени, цене и объему, а затем установить соответствие между обоими первыми, затем обоими вторыми и т. д.   -  person Edouard Berthe    schedule 15.12.2015
comment
Ваш образец неверен. Между trade_id 206 и event_id 304 нет соответствия, так как нет совпадений по цене и объему   -  person Jorge Campos    schedule 15.12.2015
comment
Помимо того, что ваши образцы данных неверны, лучшее, что я мог сделать, это ранжировать группы. Когда я выполняю подзапрос, назначая порядок ранжирования 1, это приведет к дублированию результатов. Если вы посмотрите на результаты, которые у меня есть, вы увидите, что для получения того, что вы хотите, вам нужно запросить trade_id и event_id, которые ранее не были выбраны. Это даже трудно объяснить. Взгляните, я думаю, вы поймете лучше: sqlfiddle.com/#!15/14ac3/1   -  person Jorge Campos    schedule 15.12.2015

Ответы (2)


arrow_upward
0
arrow_downward

Я пытался сделать многое, но не могу получить ваш результат. Хотя у меня есть кое-что, что может помочь.

Используя следующий запрос, вы получите все записи с одинаковой ценой и объемом, а также разницей во времени между событием и сделкой.

select *  from
(SELECT t.id as trade_id, e.id as event_id, e.price as price, e.volume as volume,e.bar as bar, t.foo as foo, abs(e.time-t.time) as diff 
FROM events e
inner JOIN trades t on t.price = e.price AND t.volume = e.volume order by trade_id,diff asc ) a

Используя ваши данные, невозможно получить некоторые ожидаемые результаты. т.е. 206 не может иметь order_id 334 с использованием совпадения цены и объема.

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

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

select *  from
(SELECT t.id as trade_id, e.id as event_id, e.price as price, e.volume as volume,e.bar as bar, t.foo as foo, abs(e.time-t.time) as diff FROM events e 
inner JOIN trades t on t.price = e.price AND t.volume = e.volume order by trade_id,diff asc ) a group by trade_id
person davejal    schedule 15.12.2015

arrow_upward
0
arrow_downward

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

SELECT 
t.*,
(SELECT e.id
    FROM events o
    WHERE e.price = t.price
    AND e.volume = t.volume
    AND o.time > t.time
    ORDER BY ABS(o.time - t.time)
    LIMIT 1
) as most_probable_corresponding_event_id
FROM trades t
ORDER BY t.time;
person Sam Andrews    schedule 15.12.2015