Как эффективно получить «самую последнюю соответствующую строку»?

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

У меня есть таблица, inventory say, которая представляет инвентарь, который я держу в определенный день.

 date       | good | quantity
------------------------------
2013-08-09 | egg  | 5
2013-08-09 | pear | 7
2013-08-02 | egg  | 1
2013-08-02 | pear | 2
 

и таблица, «цена», скажем, которая держит цену товара в определенный день

 date       | good | price
--------------------------
2013-08-07 | egg  | 120
2013-08-06 | pear | 200
2013-08-01 | egg  | 110
2013-07-30 | pear | 220
 

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

 date       | pricing date | good | quantity | price
----------------------------------------------------
2013-08-09 | 2013-08-07   | egg  | 5        | 120
2013-08-09 | 2013-08-06   | pear | 7        | 200
2013-08-02 | 2013-08-01   | egg  | 1        | 110
2013-08-02 | 2013-07-30   | pear | 2        | 220
 

Я знаю один способ сделать это:

 select inventory.date, max(price.date) as pricing_date, good
from inventory, price
where inventory.date >= price.date
and inventory.good = price.good
group by inventory.date, good
 

, а затем снова присоедините этот запрос к инвентарю. Для больших таблиц даже выполнение первого запроса (без добавления снова в инвентарь) медленнее very . Однако эта же проблема быстро решена, если я просто использую свой язык программирования для выпуска одного max(price.date) ... where price.date <= date_of_interest ... order by price.date desc limit 1 для каждого date_of_interest из таблицы инвентаризации, поэтому я знаю, что нет никаких вычислительных помех. Однако я бы предпочел решить всю проблему с помощью одного SQL-запроса, поскольку это позволило бы мне выполнить дальнейшую обработку SQL в результате запроса.

Есть ли стандартный способ сделать это эффективно? Похоже, что он должен часто появляться и что должен быть способ написать быстрый запрос для него.

Я использую Postgres, но ответ на SQL-общий признак будет оценен.

43 голоса | спросил Tom Ellis 9 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 09 Sep 2013 18:52:25 +0400 2013, 18:52:25

6 ответов


34

Он очень сильно зависит от обстоятельств и точных требований. Рассмотрим мой комментарий к вопросу .

Простое решение

С DISTINCT ON в Postgres:

 SELECT DISTINCT ON (i.good, i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good, i.the_date, p.the_date DESC;
 

Упорядоченный результат.

Или с помощью NOT EXISTS в стандартном SQL (работает со всеми СУБД, которые я знаю):

 SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM   inventory  i
LEFT   JOIN price p ON p.good = i.good AND p.the_date <= i.the_date
WHERE  NOT EXISTS (
   SELECT 1 FROM price p1
   WHERE  p1.good = p.good
   AND p1.the_date <= i.the_date
   AND p1.the_date >  p.the_date
   );
 

Тот же результат, но с произвольным порядком сортировки - если вы не добавите ORDER BY.
В зависимости от распределения данных, точных требований и индексов, один из них может быть быстрее.
Как правило, DISTINCT ON является победителем, и вы получаете отсортированный результат поверх него. Но в некоторых случаях другие методы запросов (намного) быстрее. См. Ниже.

Решения с подзапросами для вычисления значений max /min обычно медленнее. Варианты с CTE, как правило, медленнее.

Обычные представления (например, предложенные другим ответом) не помогают производительности в Postgres.

SQL Fiddle.


Правильное решение

Строки и сортировка

Прежде всего, вы страдаете от субоптимального макета таблицы. Это может показаться тривиальным, но нормализация вашей схемы может пройти долгий путь.

Сортировка по символьным типам (text, varchar, ...) необходимо выполнить в соответствии с локалью - COLLATION в частности. Скорее всего, ваша БД использует некоторый локальный набор правил (например, в моем случае: de_AT.UTF-8). Узнайте с помощью:

 SHOW lc_collate;
 

Это делает сортировку и просмотр индексов медленнее . Чем дольше ваши строки (названия товаров) хуже. Если вам действительно не нравятся правила сортировки в вашем выпуске (или порядок сортировки вообще), это может быть быстрее, если вы добавите COLLATE "C":

 SELECT DISTINCT ON (i.good COLLATE "C", i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good COLLATE "C", i.the_date, p.the_date DESC; 

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

Индекс

Если ваш запрос должен использовать индекс, столбцы с символьными данными должны использовать соответствующую сортировку (good в примере):

 CREATE INDEX inventory_good_date_desc_collate_c_idx
ON price(good COLLATE "C", the_date DESC); 

Обязательно прочитайте последние две главы этого связанного ответа на SO:

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

Normalize

Резервные строки (имя товара) также накидывают ваши таблицы и индексы, что делает все еще медленнее. При правильном расположении таблиц вы могли бы избежать большей части проблемы для начала. Может выглядеть так:

 CREATE TABLE good (
  good_id serial PRIMARY KEY
, good    text   NOT NULL
);

CREATE TABLE inventory (
  good_id  int  REFERENCES good (good_id)
, the_date date NOT NULL
, quantity int  NOT NULL
, PRIMARY KEY(good_id, the_date)
);

CREATE TABLE price (
  good_id  int     REFERENCES good (good_id)
, the_date date    NOT NULL
, price    numeric NOT NULL
, PRIMARY KEY(good_id, the_date));
 

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

 CREATE INDEX price_good_date_desc_idx ON price(good, the_date DESC);
 

Опять же, сопоставление должно соответствовать вашему запросу (см. выше).

В Postgres 9.2 или более поздней версии «покрытие индексов» для сканирования только по индексу может помочь еще несколько - особенно если ваши таблицы содержат дополнительные столбцы, что делает таблицу существенно больше, чем индекс покрытия.

Эти результирующие запросы выполняются намного быстрее:

НЕ СУЩЕСТВУЕТ

 SELECT i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM   inventory  i
JOIN   good       g USING (good_id)
LEFT   JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
AND    NOT EXISTS (
   SELECT 1 FROM price p1
   WHERE  p1.good_id = p.good_id
   AND    p1.the_date <= i.the_date
   AND    p1.the_date >  p.the_date
   );
 

DISTINCT ON

 SELECT DISTINCT ON (i.the_date)
       i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM   inventory  i
JOIN   good       g USING (good_id)
LEFT   JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
ORDER  BY i.the_date, p.the_date DESC;
 

SQL Fiddle.


Более быстрые решения

Если это еще не достаточно быстро, могут быть более быстрые решения.

Рекурсивный CTE /JOIN LATERAL /коррелированный подзапрос

Специально для распределений данных с многими ценами на хорошее :

Материализованный вид

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

Postgres 9.3+ имеет автоматическую поддержку материализованных представлений. Вы можете легко реализовать базовую версию в более старых версиях.

ответил Erwin Brandstetter 10 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowTue, 10 Sep 2013 02:32:11 +0400 2013, 02:32:11
6

FYI, я использовал mssql 2008, поэтому Postgres не будет иметь индекс «include». Однако использование базовой индексации, показанной ниже, изменится с хэш-соединений, чтобы объединить объединения в Postgres: http://explain.depesz.com/s/eF6 (без индекса) http://explain.depesz.com/s/j9x (с индексом по критериям присоединения)

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

 create view mostrecent_pricing_dates_per_good as
select i.good,i.date i_date,max(p.date)p_date
  from inventory i
  join price p on i.good = p.good and i.date >= p.date
 group by i.good,i.date;
 

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

 select i.good
       ,i.date inventory_date
       ,i.quantity
       ,p.date pricing_date
       ,p.price       
  from inventory i
  join price p on i.good = p.good
  join mostrecent_pricing_dates_per_good x 
    on i.good = x.good 
   and p.date = x.p_date
   and i.date = x.i_date
 

Это дает следующий план выполнения: http://sqlfiddle.com/#!3/24f23/1 no indexing

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

Теперь добавьте базовые индексы, чтобы помочь критериям, используемым в вашем соединении (я не утверждаю, что это оптимальные индексы, но они иллюстрируют точку): http://sqlfiddle.com/#!3/5ec75/1 с базовым индексированием

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

Последняя итерация использует «включить» в индексы, чтобы упростить план для перехода и получить дополнительно запрошенные данные прямо из самого индекса. Таким образом, поиски исчезли: http://sqlfiddle.com/#!3/5f143/1 введите описание изображения здесь>> </p>

<p> Теперь у нас есть план запросов, в котором общая стоимость запроса распределяется равномерно между очень быстрыми операциями поиска индекса. Это будет близко к тому, что хорошо. Разумеется, другие эксперты могут улучшить это дальше, но решение устраняет ряд серьезных проблем: </p>

<ol>
<li> Он создает понятные структуры данных в вашей базе данных, которые легче создавать и повторно использовать в других областях приложения. </li>
<li> Все наиболее дорогостоящие операторы запросов были учтены из плана запроса, используя некоторую базовую индексацию. </li>
</ol></body></html>

ответил cocogorilla 9 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 09 Sep 2013 21:17:50 +0400 2013, 21:17:50
5

Если у вас есть PostgreSQL 9.3 (выпущенный сегодня), вы можете использовать LATERAL JOIN.

У меня нет возможности проверить это и никогда не использовал его раньше, но из того, что я могу сказать из документации синтаксис будет примерно таким:

 SELECT  Inventory.Date,
        Inventory.Good,
        Inventory.Quantity,
        Price.Date,
        Price.Price
FROM    Inventory
        LATERAL
        (   SELECT  Date, Price
            FROM    Price
            WHERE   Price.Good = Inventory.Good
            AND     Price.Date <= Inventory.Date
            ORDER BY Price.Date DESC
            LIMIT 1
        ) p;
 

Это в основном эквивалентно APPLY SQL-сервера , и для демонстрационных целей есть рабочий пример этого в SQL-Fiddle .

ответил GarethD 9 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 09 Sep 2013 19:46:19 +0400 2013, 19:46:19
5

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

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

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

 CREATE VIEW most_recent_rows AS
SELECT good, max(date) as max_date
FROM inventory
GROUP BY good;
 

Это должно хорошо работать, когда вы делаете что-то вроде:

 SELECT price 
  FROM inventory i
  JOIN goods g ON i.goods = g.description
  JOIN most_recent_rows r ON i.goods = r.goods
 WHERE g.id = 123;
 

Тогда вы можете присоединиться к этому. Запрос завершит присоединение к представлению против базовой таблицы, но при условии, что у вас есть уникальный индекс (дата, хороший в этом порядке ), вам должно быть хорошо идти (так как это будет простой поиск кеша). Это будет очень хорошо работать с несколькими рядами, поднятыми вверх, но будет очень неэффективно, если вы попытаетесь переварить миллионы цен на товары.

Вторая вещь, которую вы могли бы сделать, - добавить в таблицу инвентаря столбец b__support и

 create unique index on inventory (good) where most_recent;
 

Затем вы захотите использовать триггеры, чтобы установить для параметра most_recent значение false, когда была добавлена ​​новая строка для хорошего. Это добавляет больше сложности и больших шансов на ошибки, но это полезно.

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

Обновить . По словам Первина ниже, похоже, я неправильно понял это. Перечитывая вопрос, я совсем не уверен, что вас спрашивают. Я хочу упомянуть в обновлении, какова потенциальная проблема, которую я вижу, и почему это оставляет это непонятным.

Предлагаемый дизайн базы данных не имеет реального использования IME с системами ERP и учета. Он будет работать в гипотетической идеальной модели ценообразования, где все, проданное в определенный день данного продукта, имеет одинаковую цену. Тем не менее, это не всегда так. Это даже не касается таких вещей, как обмен валюты (хотя некоторые модели притворяются, что это так). Если это надуманный пример, это неясно. Если это реальный пример, возникают проблемы с дизайном на уровне данных. Я собираюсь предположить здесь, что это реальный пример.

Вы не можете предположить, что только одна дата указывает цену за данный товар. Цены в любом бизнесе могут обсуждаться на счет контрагента и даже иногда на транзакцию. По этой причине вы действительно должны хранить цену в таблице, которая фактически обрабатывает инвентарь в или из (таблица инвентаря). В таком случае ваша таблица даты /товара /цены просто указывает базовую цену, которая может быть изменена на основе переговоров. В таком случае эта проблема возникает из-за проблемы с отчетами, которая является транзакционной и работает в одной строке из каждой таблицы за раз. Например, вы можете посмотреть цену по умолчанию для данного продукта в данный день как:

  SELECT price 
   FROM prices p
   JOIN goods g ON p.good = g.good
  WHERE g.id = 123 AND p."date" >= '2013-03-01'
  ORDER BY p."date" ASC LIMIT 1;
 

С индексом цен (good, date) это будет хорошо работать.

Я это надуманный пример, возможно, что-то ближе к тому, над чем вы работаете.

ответил Chris Travers 10 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowTue, 10 Sep 2013 05:39:51 +0400 2013, 05:39:51
3

Другим способом было бы использовать функцию окна lead(), чтобы получить диапазон дат для каждой строки в таблице, а затем использовать between при присоединении к инвентарю. Я действительно использовал это в реальной жизни, но главным образом потому, что это была моя первая идея, как решить эту проблему.

 with cte as (
  select
    good,
    price,
    date,
    coalesce(lead(date) over(partition by good order by date) - 1
            ,Now()::date) as ndate
  from
    price
)

select * from inventory i join cte on
  (i.good = cte.good and i.date between cte.date and cte.ndate)
 

SqlFiddle

ответил Tomas Greif 9 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 09 Sep 2013 20:51:08 +0400 2013, 20:51:08
1

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

Итак, для вашей цены на товар:

  Select i.date, p.Date pricingDate,
    i.good, quantity, price        
 from inventory I join price p 
    on p.good = i.good
        And p.Date = 
           (Select Max(Date from price
            where good = i.good
               and date <= i.Date)
 

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

ответил 9 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 09 Sep 2013 19:04:43 +0400 2013, 19:04:43

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132