Более эффективный SQL, чем использование “ОБЪЕДИНЕНИЯ (B в A)”?
Редактирование 1 (разъяснение): Спасибо за ответы до сих пор! Ответ приятен.
Я хочу разъяснить вопрос немного, потому что на основе ответов я думаю, что не описал один аспект проблемы правильно (и я уверен, что это - мой отказ, поскольку мне было тяжело определять его даже для меня).
Вот протирание: набор результатов должен содержать ТОЛЬКО записи с tstamp МЕЖДУ '2010-01-03' И '2010-01-09' И одну запись, где tstamp ЯВЛЯЕТСЯ ПУСТЫМ для каждого order_num в первом наборе (всегда будет один с пустым указателем tstamp для каждого order_num).
Ответы, данные до сих пор, кажется, включают все записи для определенного order_num, если существует кто-либо с tstamp МЕЖДУ '2010-01-03' И '2010-01-09'. Например, если бы была другая запись с order_num = 2 и tstamp = 12.01.2010 0:00:00, то это не должно быть включено в результат.
Исходный вопрос:
Рассмотрите таблицу заказов, содержащую идентификатор (уникальный), order_num, tstamp (метка времени), и item_id (единственный объект включенный в порядок). tstamp является пустым, если порядок не был изменен, в этом случае существует другая запись с идентичным order_num, и tstamp затем содержит метку времени того, когда изменение произошло.
Пример...
id order_num tstamp item_id __ _________ ___________________ _______ 0 1 100 1 2 101 2 2 2010-01-05 12:34:56 102 3 3 113 4 4 124 5 5 135 6 5 2010-01-07 01:23:45 136 7 5 2010-01-07 02:46:00 137 8 6 100 9 6 2010-01-13 08:33:55 105
Что самый эффективный SQL-оператор состоит в том, чтобы получить все заказы (на основе order_num), которые были изменены один или несколько раз во время определенного диапазона дат? Другими словами, для каждого порядка нам нужны все записи с тем же order_num (включая тот с ПУСТЫМ УКАЗАТЕЛЕМ tstamp) для каждого order_num, ГДЕ по крайней мере один из order_num имеет tstamp NOT NULL И tstamp МЕЖДУ '2010-01-03' И '2010-01-09'. Это, "ГДЕ по крайней мере один из order_num имеет tstamp NOT NULL", с которым я испытываю трудности.
Набор результатов должен быть похожим на это:
id order_num tstamp item_id __ _________ ___________________ _______ 1 2 101 2 2 2010-01-05 12:34:56 102 5 5 135 6 5 2010-01-07 01:23:45 136 7 5 2010-01-07 02:46:00 137
SQL, который я придумал, является этим, которое является по существу "ОБЪЕДИНЕНИЕМ (B в A)", но это медленно выполняется, и я надеюсь, что существует более эффективное решение:
SELECT history_orders.order_id, history_orders.tstamp, history_orders.item_id
FROM
(SELECT orders.order_id, orders.tstamp, orders.item_id
FROM orders
WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09')
AS history_orders
UNION
SELECT current_orders.order_id, current_orders.tstamp, current_orders.item_id
FROM
(SELECT orders.order_id, orders.tstamp, orders.item_id
FROM orders
WHERE orders.tstamp IS NULL)
AS current_orders
WHERE current_orders.order_id IN
(SELECT orders.order_id
FROM orders
WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09');
6 ответов
снова Спасибо за все предложения. Я нашел три решения, которые работают, включая мой оригинал. В конце я добавил некоторые результаты производительности, которые не так велики, как я надеялся. Если кто-то может улучшить это, я бы был взволнован!
1) Лучшее решение, найденное до сих пор:
SELECT history_orders.order_id, history_orders.tstamp, history_orders.item_id
FROM
(SELECT orders.order_id, orders.tstamp, orders.item_id
FROM orders
WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09'
OR orders.tstamp IS NULL)
AS history_orders
WHERE history_orders.order_id IN
(SELECT orders.order_id
FROM orders
WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09');
2) Я также пытался использовать существующие вместо IN, что требует дополнительного предложения, где в последнем выборе:
SELECT history_orders.order_id, history_orders.tstamp, history_orders.item_id
FROM
(SELECT orders.order_id, orders.tstamp, orders.item_id
FROM orders
WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09'
OR orders.tstamp IS NULL)
AS history_orders
WHERE EXISTS
(SELECT orders.order_id
FROM orders
WHERE history_orders.order_id = orders.order_id
AND orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09');
3) и, наконец, есть Мое оригинальное решение, использующее объединение.
Комментарии:
Чтобы прокомментировать размер таблицы, моя фактическая проблема «реальной мир» включает 4 столы (связанные с внутренними присоединениями), содержащие 98, 2189, 43897, 785656 записей соответственно.
Производительность - я провел каждое решение три раза, а вот мои результаты реального мира:
1: 52, 51, 51 секунды
2: 54, 54, 53 с
3: 56, 56, 56 с
Возможно, подзапрос:
select * from order o where o.order_num in (select distinct
order_num from order where tstamp between '2010-01-03' and '2010-01-09')
Если я не понимаю, что-то вроде этого должен сделать трюк:
SELECT o1.id, o1.order_num, o.tstamp, o.item_id
FROM orders o1
WHERE EXISTS(
SELECT * FROM orders o2
WHERE o1.order_num = o2.order_num
AND o2.tstamp BETWEEN '2010-01-03' AND '2010-01-09')
Преимущество использования существует, заключается в том, что он останавливается как можно скорее.
Надеюсь, я получил ваш вопрос правильно. Это должно вернуть все заказы, которые имеют порядок, который был изменен в пределах предоставляемого времени.
SELECT o.order_id, o.tstamp, o.item_id
FROM orders o
JOIN ( SELECT DISTINCT o2.order_num
FROM orders o2
WHERE o2.tstamp BETWEEN '2010-01-03' AND '2010-01-09' ) o3
ON ( o3.order_num = o.order_num )
Вы можете самостоятельно присоединиться к столу. Упрощенный, это будет выглядеть так:
select order_id
from orders all_orders
inner join orders not_null_orders
on all_orders.order_id = not_null_orders.order_id
where
not_null_orders.tstamp is not null
and all_orders.tstamp between '2010-01-03' AND '2010-01-09'
Я знаю, что уже очень поздно отвечать, но я только что увидел эту публикацию и подумал, может, мне стоит попробовать это один раз, как насчет этого запроса, он действительно очень мал по сравнению со всеми вышеперечисленными решениями и решает цель.
select * from orders_gc where order_num in
(select order_num
from orders_gc
group by order_num
having count(id) > 1 and
MAX(tstamp) between '03-jan-2010' and '09-jan-2010')