如何有效地获取“最近对应的行”？

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

#1 楼

这在很大程度上取决于情况和确切的要求。考虑我的评论。
简单解决方案
在Postgres中使用DISTINCT ON：

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;

返回的行是有序的。请参阅：

在每个GROUP BY组中选择第一行？

还是在标准SQL中与NOT EXISTS配合使用（适用于我知道的每个RDBMS）：

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 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？

每件商品只有几行，DISTINCT ON通常会更快，并且得到排序结果顶部。但是对于某些情况，其他查询技术却要快得多。参见下文。
带有子查询以计算最大值/最小值的解决方案通常较慢。但是，带有CTE的变体通常较慢。 （使用Postgres 12改进了CTE。）
纯视图（如另一个答案所建议的那样）根本无法提高Postgres的性能。
db <> fiddle hereOld sqlfiddle
正确的解决方案
字符串和排序规则
首先，您的表布局不太理想。这看似微不足道，但规范化架构可能会走很长一段路。
根据当前的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;

请注意在两个位置添加的排序规则。
速度快了两倍在我的测试中，每行有2万行，并且具有非常基本的名称（'good123'）。
索引
如果您的查询应该使用索引，则包含字符数据的列必须使用匹配的排序规则（在示例中为good）：

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

阅读上面链接的相关答案的后两章。 br />您甚至可以在同一列上使用具有不同排序规则的多个索引-如果您还需要在其他查询中根据其他（或默认）排序规则对商品进行排序。
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));

主键自动提供（几乎）我们需要的所有索引。
根据丢失的详细信息，price上多列索引的第二列按降序排列可能会有所改善性能：

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

，排序规则也必须与您的查询匹配（请参见上文）。
由于Postgres 9.2“仅索引”扫描的“覆盖索引”可以提供更多帮助-特别是在表包含其他索引的情况下列，使表大大大于索引。
这些结果查询快得多：
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;

NOT EXISTS

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
   );

小提琴此处OLD sqliddle
更快的解决方案
如果仍然不够快，可能会有更快的解决方案。
递归CTE / JOIN LATERAL /相关子查询
特别适用于每商品价格昂贵的数据分发：

优化GROUP BY查询以检索每个用户的最新记录

材料化视图
如果需要运行通常，我建议您创建一个实例化视图。我认为可以肯定地说，过去日期的价格和库存很少变化。一次计算结果并将快照存储为实例化视图。
Postgres 9.3+具有对实例化视图的自动支持。您可以轻松地在旧版本中实现基本版本。

#2 楼

正如Erwin和其他人所指出的那样，有效的查询取决于很多变量，PostgreSQL非常努力地基于这些变量优化查询执行。通常，您首先要写清楚一些，然后在发现瓶颈后再进行性能修改。

此外，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;

然后您可以加入。该查询最终将针对基础表将视图加入视图，但是假设您有一个唯一的索引（日期，该顺序正确），那么您应该行得通（因为这将是简单的缓存查找）。这在查找几行后会很好用，但是如果您要消化数百万的商品价格，效率将非常低。

您可以做的第二件事是将most_recent添加到库存表中bool列和

create unique index on inventory (good) where most_recent;

然后，当插入商品的新行时，您想使用触发器将most_recent设置为false。这样会增加更多的复杂性和错误的机会，但是很有帮助。

同样，很多情况取决于适当的索引。对于最近的日期查询，您可能应该有一个日期索引，并且可能是一个以日期开头并包括您的加入条件的多列索引。

更新下面的Per Erwin评论，看来我误会了这个。重新阅读这个问题，我根本不确定所要问的是什么。我想在更新中提及我看到的潜在问题，以及为什么这让我不清楚。

提供的数据库设计没有将IME与ERP和会计系统一起实际使用。它可以在假设的完美定价模型中工作，在该模型中，给定产品在给定日期销售的所有商品都具有相同的价格。然而，这并非总是如此。诸如货币兑换之类的东西甚至都不是这种情况（尽管某些模型假装确实如此）。如果这是人为的例子，目前尚不清楚。如果这是一个真实的例子，则在数据级别的设计存在更大的问题。我要在这里假设这是一个真实的例子。

您不能假设仅日期指定了给定商品的价格。任何业务的价格都可以按交易对手甚至有时按交易进行协商。因此，您确实应该将价格存储在实际处理库存的表中（库存表）。在这种情况下，您的日期/商品/价格表仅指定了基础价格，该基础价格可能会根据协商而更改。在这种情况下，此问题已从报告问题变为事务性问题，并且一次处理每个表的一行。例如，您可以在给定的日期查找给定产品的默认价格，如下所示：

表现很好。

我这是一个人为的示例，也许更接近您正在研究的内容会有所帮助。

#3 楼

仅供参考，我使用的是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;

这将产生以下结果执行计划：
http://sqlfiddle.com/#!3/24f23/1


...所有扫描均经过完整排序。请注意，哈希匹配的性能成本占据了总成本的大部分...并且我们知道表扫描和排序速度很慢（与目标：索引查找相比）。

现在，将基本索引添加到帮助您的联接中使用的标准（我不认为这些是最佳索引，但它们说明了这一点）：
http://sqlfiddle.com/#!3/5ec75/1


这表明有所改善。嵌套循环（内部联接）操作不再占用查询的任何相关总成本。其余成本现在分散在索引查找中（扫描库存，因为我们提取了每个库存行）。但是我们仍然可以做得更好，因为查询可以提取数量和价格。要获取该数据，请在评估联接条件之后，必须执行查找。

最后一次迭代在索引上使用“ include”以使计划更容易滑倒并获取额外请求的数据直接脱离索引本身。因此查找消失了：
http://sqlfiddle.com/#!3/5f143/1


现在，我们有了一个查询计划，其中查询的总成本在非常快的索引查找操作之间平均分配。这将接近所获得的一切。当然其他专家可以进一步改善这一点，但是该解决方案消除了两个主要问题：


它在您的数据库中创建了可理解的数据结构，更易于在其他数据库中进行组合和重用。应用程序领域。
使用一些基本索引已将所有最昂贵的查询运算符从查询计划中排除。

#4 楼

如果您碰巧拥有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;

这基本上等同于SQL Server的APPLY，并且在SQL-Fiddle上有一个有效的示例用于演示。

#5 楼

另一种方法是使用窗口函数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

#6 楼

使用从库存到价格的联接，其联接条件将价格表中的记录限制为仅在库存日期或之前的记录，然后提取最大日期，并且该日期是该子集中的最高日期

所以对于您的库存价格：

 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)

如果任何指定商品的价格在同一天多次变化，这些列中的日期和时间，您可能需要对联接施加更多限制以仅选择价格更改记录之一。