是否有可能对运行总数编制索引以加快窗口函数的运行速度?
我要执行:
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'
);
编辑:
实现所需内容的唯一方法是在数据中显式包含一个累计和列。这将需要重新组织查询(进行您想要的减法),并使用触发器来维护值。如果历史数据永远不变,这可能是一种合理的方法。但是,更新或插入较早的行可能会变得非常昂贵。
相关文章