postgresql - 我需要使用 PostgreSQL 尽快在日期范围之间添加大量值，最好的方法是什么？

Question

这是我正在尝试做的一个简单示例:

CREATE TABLE daily_factors (
    factor_date date,
    factor_value numeric(3,1));

CREATE TABLE customer_date_ranges (
    customer_id int,
    date_from date,
    date_to date);

INSERT INTO
    daily_factors
SELECT
    t.factor_date,
    (random() * 10 + 30)::numeric(3,1)
FROM
    generate_series(timestamp '20170101', timestamp '20210211', interval '1 day') AS t(factor_date);

WITH customer_id AS (
    SELECT generate_series(1, 100000) AS customer_id),
date_from AS (
    SELECT
        customer_id,
        (timestamp '20170101' + random() * (timestamp '20201231' - timestamp '20170101'))::date AS date_from
    FROM
        customer_id)
INSERT INTO
    customer_date_ranges
SELECT
    d.customer_id,
    d.date_from,
    (d.date_from::timestamp + random() * (timestamp '20210211' - d.date_from::timestamp))::date AS date_to
FROM
    date_from d;

所以我基本上制作了两个表:

每日因素列表，从 2017 年 1 月 1 日到今天的每一天都有一个；

日期范围在 2017 年 1 月 1 日到今天之间的 100,000 个“客户”的列表，有些长，有些短，基本上是随机的。

然后我想将每个客户在其日期范围内的因素相加，并取平均值。

SELECT 
    cd.customer_id,
    AVG(df.factor_value) AS average_value
FROM 
    customer_date_ranges cd
    INNER JOIN daily_factors df ON df.factor_date BETWEEN cd.date_from AND cd.date_to
GROUP BY
    cd.customer_id;

在日期范围内进行非对等连接永远不会很漂亮，但是有什么方法可以加快速度吗？
我能想到的唯一索引是这个:

CREATE INDEX performance_idx ON daily_factors (factor_date);

它对执行时间的影响很小。当我在本地运行它时，我看到大约 32 秒没有索引，大约 28 秒有索引。
我可以看到这是我正在构建的系统中的一个巨大瓶颈，但我想不出任何方法来加快速度。我的想法是:

而不是使用日常因素，我可以在很大程度上摆脱每月的因素，但现在我增加了“整月和部分月”的复杂性。对于增加的复杂性，似乎不值得，例如“从 2020 年 2 月到 8 月需要整整 7 个月，然后是 2020 年 1 月的 10/31 和 2020 年 9 月的 15/30”；

我可以预先计算我需要的每个平均值，但是有了 1,503 个因子(并且会随着每一天的增加而增加)，这已经是 1,128,753 个要存储的数字(假设我们忽略了零日期范围并且我的数学是正确的)。此外，我的现实世界系统具有额外的复杂性，具有 20 个可能值的第二个标识符，因此这意味着需要预先计算大约 2000 万个数字。此外，每天要存储的值的数量呈指数增长；

我可以将这项工作从数据库中取出，并在代码中(在内存中)进行，因为似乎关系数据库在这里可能不是最佳解决方案？

还有其他建议吗？

Answer 1

解决这个问题的经典方法是存储 factor_value 的运行总和，而不是(或除了)单个值。然后，您只需查找两个端点(实际上是末尾，一个在开始之前)的运行总和，然后取差值。当然，除以计数，把它变成一个平均值。我从来没有在数据库中做过这个，但没有理由不能在那里做。