增量表维护：新增日数据并删除180天前旧数据方案咨询

问题 #

我需要创建一张表，存储来自另外两张表的近180天事件及聚合数据。原本认为物化视图是理想方案，但遇到了限制（此前在Stack Overflow相关讨论中发现增量物化视图存在无法多次查询同一张表的问题）。由于每次查询180天数据会处理15-16TB数据，我希望删除180天前的旧事件，仅新增前一天的数据。

我的计划是先创建视图：

CREATE VIEW test_view AS
SELECT 
colA,
colB,
date_column,
count(*)
FROM table a
WHERE date_column >= current_date - 180
GROUP BY colA,
colB,
date_column
UNION ALL
SELECT 
colA,
colB,
date_column,
count(*)
FROM table b
WHERE date_column >= current_date - 180
GROUP BY colA,
colB,
date_column

再用该视图维护同结构的test_table，执行以下操作：

DELETE * FROM test_table
WHERE date_column < current_date - 180;

INSERT INTO test_table
SELECT * FROM test_view
WHERE date_column = current_date - 1;

请问该方案是否可行？还是视图会查询全部180天数据导致此方案毫无意义？

回答 #

你的方案不可行，核心问题在于普通视图本身不存储数据，每次查询视图时都会重新执行定义里的完整SQL逻辑。

当你执行SELECT * FROM test_view WHERE date_column = current_date - 1时，数据库依旧会扫描两张源表中全部近180天的数据，再过滤出前一天的结果——这完全没达到你减少数据处理量的目的，反而多了一层视图的额外开销。

正确的做法是直接针对前一天的数据做聚合，跳过不必要的视图：

-- 删除过期数据
DELETE FROM test_table
WHERE date_column < current_date - 180;

-- 直接从源表聚合前一天的数据插入
INSERT INTO test_table
SELECT 
  colA,
  colB,
  date_column,
  count(*)
FROM table a
WHERE date_column = current_date - 1
GROUP BY colA, colB, date_column
UNION ALL
SELECT 
  colA,
  colB,
  date_column,
  count(*)
FROM table b
WHERE date_column = current_date - 1
GROUP BY colA, colB, date_column;

这样每次只处理两张源表中前一天的数据，数据量会大幅降低，完全符合你的需求。

内容的提问来源于stack exchange，提问作者Sammy Merk