是否有可能对运行总数编制索引以加快窗口函数的运行速度？ [英] Is it possible to index a running total to speed up window functions?

问题描述

我要执行：

    SELECT cust_id, SUM(cost)
    FROM purchases
    WHERE purchase_time BETWEEN('2018-01-01', '2019-01-01')
    GROUP BY cust_id

但是对于大量的行，我预计必须加载每条记录才能聚合相应的总和。我想做的是有一个类似于：

的索引

    CREATE INDEX purchase_sum 
    ON purchases(cust_id, purchase_time, 
    SUM(cost) OVER (PARTITION BY cust_id 
    ORDER BY purchase_time) AS rolling_cost)

我想要一个如下所示的索引：

 cust_id    time    rolling_cost
--------   ------  --------------
   1        Jan 1       5
   1        Jan 2       12
   1        Jan 3       14
   1        Jan 4       20
   1        Jan 5       24
   2        Jan 1       1
   2        Jan 2       7
   2        Jan 3       11
   2        Jan 4       14
   2        Jan 5       19
   3        Jan 1       4
   3        Jan 2       5
   3        Jan 3       10
   3        Jan 4       21
   3        Jan 5       45

从这里，我的原始查询可以通过简单地将每个cust_id减去2个已知值来计算，大致为cost_in_window = rolling_cost('2019-01-01') - rolling_cost('2018-01-01')，这将不需要从源表加载任何东西。

这作为索引可行吗？或者，有没有其他方法来实现同样的目标？

推荐答案

您可能会发现这样更快：

select c.cust_id,
       (select sum(p.cost)
        from purchases p
        where p.cust_id = c.cust_id and
              p.purchase_time >= '2018-01-01' and
              p.purchase_time < '2019-01-01' and
       ) as total_cost
from customers c
having total_cost is not null;

然后，可以使用purchases(cust_id, purchase_time, cost)上的索引。仅计算金额时需要该索引。这是一笔省钱。更重要的是，没有整体汇总--这可能是对所有客户进行计算所节省的更大成本。

但是，对于相同的索引，这可能会更好一些：

select c.cust_id,
       (select sum(p.cost)
        from purchases p
        where p.cust_id = c.cust_id and
              p.purchase_time >= '2018-01-01' and
              p.purchase_time < '2019-01-01'
       ) as total_cost
from customers c
where exists (select 1
              from purchases p
              where p.cust_id = c.cust_id and
                    p.purchase_time >= '2018-01-01' and
                    p.purchase_time < '2019-01-01' 
             );

编辑：

实现所需内容的唯一方法是在数据中显式包含一个累计和列。这将需要重新组织查询(进行您想要的减法)，并使用触发器来维护值。

如果历史数据永远不变，这可能是一种合理的方法。但是，更新或插入较早的行可能会变得非常昂贵。

这篇关于是否有可能对运行总数编制索引以加快窗口函数的运行速度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！