SQL两表按datestamp分组关联、计算差值并创建视图的问题排查

首先来说你遇到的报错原因：你直接在原表table_1和table_2上调用聚合函数SUM()进行关联的写法是语法错误的。SUM(items)是聚合运算后的结果，不是原表的固有列，不能用table_1.SUM(items)这种方式引用；而且原表未做分组聚合就直接关联，会导致数据笛卡尔积，完全不符合你的需求。报错提示的"schema 'table_1' does not exist"大概率是数据库解析错误语法时产生的误导性提示，核心问题还是你的SQL写法逻辑不对。

接下来是满足你所有需求的最优实现方案，我们可以先完成两个表的分组求和，再进行关联、计算差值并创建视图，下面提供两种可读性强的写法：

方案1：使用子查询实现 #

CREATE VIEW items_sum_diff AS
SELECT
    COALESCE(t1.datestamp, t2.datestamp) AS datestamp,
    COALESCE(t1.items_1_sum, 0) AS items_1_sum,
    COALESCE(t2.items_2_sum, 0) AS items_2_sum,
    COALESCE(t2.items_2_sum, 0) - COALESCE(t1.items_1_sum, 0) AS difference
FROM
    (SELECT datestamp, SUM(items) AS items_1_sum FROM table_1 GROUP BY datestamp) t1
FULL OUTER JOIN
    (SELECT datestamp, SUM(items_2) AS items_2_sum FROM table_2 GROUP BY datestamp) t2
ON t1.datestamp = t2.datestamp;

方案2：使用CTE（公共表表达式，可读性更强） #

CREATE VIEW items_sum_diff AS
WITH t1_sum AS (
    SELECT datestamp, SUM(items) AS items_1_sum
    FROM table_1
    GROUP BY datestamp
),
t2_sum AS (
    SELECT datestamp, SUM(items_2) AS items_2_sum
    FROM table_2
    GROUP BY datestamp
)
SELECT
    COALESCE(t1_sum.datestamp, t2_sum.datestamp) AS datestamp,
    COALESCE(t1_sum.items_1_sum, 0) AS items_1_sum,
    COALESCE(t2_sum.items_2_sum, 0) AS items_2_sum,
    COALESCE(t2_sum.items_2_sum, 0) - COALESCE(t1_sum.items_1_sum, 0) AS difference
FROM t1_sum
FULL OUTER JOIN t2_sum ON t1_sum.datestamp = t2_sum.datestamp;

关键细节说明： #

• 使用FULL OUTER JOIN可以确保即使某一天只有其中一个表有数据，也能被展示出来（比如某天只有table_1有数据、table_2没有，或者反过来）
• COALESCE函数用来处理NULL值：如果某一天其中一个表没有求和结果，会用0来替代，避免差值计算出现NULL
• 给分组后的子查询/CTE设置别名（比如t1、t2_sum），这样可以正确引用聚合后的列

基于你的示例数据的输出结果： #

内容的提问来源于stack exchange，提问作者Jonas Palačionis