主页 / dba / 问题 / 322527
Accepted
Camila326
Asked: 2023-01-25 17:28:29 +0800 CST

如何查询每个唯一数据对的最新对？

  • 772

有一张表，其中有货币汇率。

fx_rates
---
id
buy_curr_code
sell_curr_code
rate
inserted_at

表格以未知频率更新：有时每天一次，有时每天几次，有时几天一次。货币也是如此：这次有些可能会更新，有些可能会更新。

并且数据永远不会从表中删除，而只会被插入或更新。

我如何从中查询最新、最新鲜的汇率？意思是，每个独特的货币buy - sell currency_code对都有一个单一的汇率。

我应该使用distinct吗？如果使用，如何使用？

postgresql

3 个回答

  1. Best Answer

    Erwin 的回答是正确的，但是随着表的增长，查询会越来越慢。所以让我建议一个不同的数据模型：

    添加一个active类型boolean为 TRUE 的列，用于每个 的最近条目(buy_curr_code, sell_curr_code)。

    然后你会添加一个索引：

    CREATE UNIQUE INDEX ON fx_rates (buy_curr_code, sell_curr_code) WHERE active;

    要插入新行，您将运行此事务：

    BEGIN;

UPDATE fx_rates
SET active = FALSE
WHERE buy_curr_code = 42 AND sell_curr_code = 101
RETURNING inserted_at;

/* here I would add a test in my code if the "inserted_at" is more
   recent than "current_timestamp".  If yes, I would have the transaction
   fail, because there is already a more recent entry.
   This should take care of race conditions. */

INSERT INTO fy_rates (buy_curr_code, sell_curr_code, rate, inserted_at, active)
VALUES (42, 101, 2.71828, current_timestamp, TRUE);

COMMIT;

    这个解决方案的好处是现在可以很容易地查询活动条目，而且该查询的性能将与表的大小无关。

    要清理数据，您可以按列表对表进行分区，active并按时间范围对 FALSE 分区进行子分区。

    • 4

  2. DISTINCT ON是最简单的方法之一：

    SELECT DISTINCT ON (buy_curr_code, sell_curr_code) *
FROM   fx_rates
ORDER  BY buy_curr_code, sell_curr_code, inserted_at DESC;

    有一个索引(buy_curr_code, sell_curr_code, inserted_at DESC)可以使它更快。看：

    如果每行有很多行(buy_curr_code, sell_curr_code)——这似乎很可能——其他查询技术会更快。具体取决于未公开的细节。看：

    更好的设计

    如果更改数据库设计是一种选择，我会考虑使用每次转换一个条目的附加表。喜欢：

    CREATE TABLE current_rate (
  exchange_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, buy_curr_code   int NOT NULL  -- or whatever type 
, sell_curr_code  int NOT NULL
, rate            numeric NOT NULL
, inserted_at     timestamptz NOT NULL DEFAULT now()
, UNIQUE (buy_curr_code, sell_curr_code)
);

    并触发ON INSERT并ON UPDATE在表中插入一个新的“日志”条目fx_rates。所有新条目都是对current_rate. 只有触发器写入表fx_rates。（触发器可能会运行额外的检查。）

    INSERT扳机：

    -- function
CREATE OR REPLACE FUNCTION trg_current_rate_insbef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO fx_rate (exchange_id, rate, inserted_at)
   VALUES (NEW.exchange_id, NEW.rate, NEW.inserted_at);
        
   RETURN NEW;
END
$func$;

-- trigger
CREATE TRIGGER current_rate_insbef
BEFORE INSERT ON current_rate
FOR EACH ROW EXECUTE FUNCTION trg_current_rate_insbef();

    在这个小提琴中完成演示。

    然后的内容current_rate始终是您尝试生成的现成结果。

    为什么？

    每种方法都有利有弊。唯一声明的要求是当前费率列表。我的解决方案提供了SELECT * FROM current_rate- 尽可能简单和快速。添加一个新的费率是一个单一的UPDATE。存储：fx_rates比.大几个数量级current_rate。在那个大表上我们根本不需要任何索引。它是有效的INSERT-only，所以没有表和索引膨胀。IDENTITY我们可以通过添加一个整数列作为代理 PK 来使大表更小current_rate，并且只将这个 4 字节的 ID 写入fx_rates。事实上，你的问题中没有任何内容甚至说fx_rates一旦我们拥有了我们仍然需要current_rates。（但我希望会有其他目的。）

    唯一适度复杂的是触发器，它也非常简单。因此，除非您有其他要求，否则建议的设计更简单、更快、更小、更可靠。

    • 3

  3. 第三种在性能方面做得很好并且不需要您更改表架构的替代方法是使用窗口函数，例如ROW_NUMBER(). 这允许您枚举每组buy_curr_code, sell_curr_code对中的行，然后您可以只选择每组的最新行，如下所示：

    WITH ExchangeRatesSorted AS
(
    SELECT 
        id,
        buy_curr_code,
        sell_curr_code,
        rate,
        inserted_at,
        ROW_NUMBER() OVER (PARTITION BY buy_curr_code, sell_curr_code ORDER BY inserted_at DESC, id DESC) AS InsertedSortId
    FROM fx_rates
)

SELECT 
    id,
    buy_curr_code,
    sell_curr_code,
    rate,
    inserted_at
FROM ExchangeRatesSorted
WHERE InsertedSortId = 1

    您可能需要一个索引(buy_curr_code, sell_curr_code, inserted_at, id)以使其最有效。

    原因id添加到表达式中ORDER BY子句的末尾ROW_NUMBER()是因为两行完全同时插入的机会很少inserted_at，所以最新的id（理想情况下真正排在最后的行）是决胜局，以确保ORDER BY子句是确定性的。

    窗口函数是进行计算和处理数据时需要注意的非常有用的工具。

    • 1

相关问题