PostgreSQL嵌套INNER JOIN查询提示列需在GROUP BY子句原因是什么？

原始报错SQL #

SELECT 
    TO_CHAR (o.ShippedDate, 'yyyy.MM') AS Month 
    ,o.OrderID
    ,Total
    ,SUM (Total) OVER PARTITION BY TO_CHAR (ShippedDate, 
    ‘yyyy.MM’) ORDER BY O.OrderID) AS Running_Total 
FROM public.orders O
INNER JOIN (
  SELECT OrderID, SUM(Quantity*UnitPrice) AS Total 
  FROM public.order_details 
  GROUP BY OrderID 
  ORDER BY OrderID
) OD ON O.OrderID = OD.OrderID
WHERE
    TO_CHAR (o.ShippedDate, 'yyyy.MM') IS NOT NULL

报错信息 #

ERROR: column "o.shippeddate" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: TO_CHAR (o.ShippedDate, 'yyyy.MM') AS Month

报错原因分析 #

• 触发本次报错的核心原因是窗口函数OVER子句缺少左侧括号：SUM (Total) OVER PARTITION BY 应为 SUM (Total) OVER (PARTITION BY，缺失左括号后PostgreSQL无法识别这是窗口函数，会将SUM判定为普通聚合函数，要求所有未聚合的查询字段都加入GROUP BY子句，因此抛出对应错误。
• 同时还有一处隐藏语法错误：PARTITION BY子句中格式化日期的单引号使用了中文全角符号‘yyyy.MM’，需要替换为英文半角单引号'yyyy.MM'，否则后续也会触发解析报错。
• 冗余提示：内层子查询的ORDER BY OrderID没有实际作用，聚合后的结果顺序不会被外层查询继承，可直接删除。

修复后的可运行参考语句 #

SELECT 
    TO_CHAR (o.ShippedDate, 'yyyy.MM') AS Month 
    ,o.OrderID
    ,Total
    ,SUM (Total) OVER (PARTITION BY TO_CHAR (ShippedDate, 'yyyy.MM') ORDER BY O.OrderID) AS Running_Total 
FROM public.orders O
INNER JOIN (
  SELECT OrderID, SUM(Quantity*UnitPrice) AS Total 
  FROM public.order_details 
  GROUP BY OrderID
) OD ON O.OrderID = OD.OrderID
WHERE
    TO_CHAR (o.ShippedDate, 'yyyy.MM') IS NOT NULL

内容的提问来源于stack exchange，提问作者Timea Dbs