postgresql – 如何有效地获取“最新的相应行”?
我有一个必须非常常见的查询模式,但我不知道如何为它编写有效的查询.我想查找一个表的行,这些行对应于“另一个表的行之后的最近日期”.
库存说,我有一张桌子,它代表我在某一天持有的库存. 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 然后再次将此查询加入库存.对于大型表,即使进行第一次查询(不再加入库存)也非常慢.但是,如果我只使用我的编程语言发出一个max(price.date)…其中price.date< = date_of_interest ... order by price.date desc limit 1查询每个date_of_interest,同样的问题很快得到解决从库存表,所以我知道没有计算障碍.但是,我宁愿用单个SQL查询解决整个问题,因为它允许我对查询结果进行进一步的SQL处理. 是否有一种标准的方法来有效地做到这一点?感觉它必须经常出现,并且应该有一种方法来为它编写快速查询. 我正在使用Postgres,但是SQL-generic的答案将不胜感激.
这在很大程度上取决于具体情况和确切要求.考虑
my comment to the question.
简单解决方案 随着 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; 订购结果. 或者使用标准SQL中的 SELECT i.the_date,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. 使用子查询来计算最大/最小值的解决方案通常较慢.具有CTE的变体通常较慢. 简单的观点(如另一个答案所提出的)在Postgres中根本无助于表现. SQL Fiddle. 适当的解决方案 字符串和整理 首先,您会遇到次优的表格布局.这可能看起来微不足道,但规范化您的架构可能会有很长的路要走. 必须根据区域设置按character types ( SHOW lc_collate; 这使得排序和索引查找更慢.你的字符串(商品名称)越长越差.如果您实际上并不关心输出中的排序规则(或排序顺序),如果添加COLLATE“C”,这可能会更快: SELECT DISTINCT ON (i.good COLLATE "C",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",p.the_date DESC; 请注意我是如何在两个地方添加排序规则的. 指数 如果您的查询应该使用索引,则具有字符数据的列必须使用匹配的排序规则(在示例中很好): CREATE INDEX inventory_good_date_desc_collate_c_idx ON price(good COLLATE "C",the_date DESC); 请务必阅读有关SO的相关答案的最后两章: > Select first row in each GROUP BY group? 您甚至可以在同一列上具有多个具有不同排序规则的索引 – 如果您还需要根据其他查询中的另一个(或默认)排序规则对货物进行排序. 规范化 冗余字符串(良好的名称)也会使表和索引膨胀,这使得一切变得更慢.使用正确的表格布局,您可以避免大部分问题.看起来像这样: 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,the_date)); 主键自动提供(几乎)我们需要的所有索引. CREATE INDEX price_good_date_desc_idx ON price(good,the_date DESC); 同样,排序规则必须与您的查询匹配(参见上文). 在Postgres 9.2或更高版本中,“covering indices” for index-only scans可以提供更多帮助 – 尤其是如果您的表格中包含额外的列,使得表格远远大于覆盖索引. 这些结果查询要快得多: 不存在 SELECT i.the_date,g.good,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.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 /相关子查询 特别是对于每种商品的价格很高的数据分布: > Optimize GROUP BY query to retrieve latest record per user 物化视图 如果你需要经常快速地运行它,我建议你创建一个物化视图.我认为可以安全地假设过去几天的价格和库存很少发生变化.计算结果一次并将快照存储为物化视图. Postgres 9.3+ has automated support for materialized views.您可以在旧版本中轻松实现基本版本. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |