时间戳

首页 > 解决方案 > GROUP BY 子句中不允许使用有序分析函数

mysql - GROUP BY 子句中不允许使用有序分析函数

问题描述

我有一个查询连接 2 个表，其中包含两个表中字段的 SELECT 语句，以及一个有序分析函数来计算特定客户的总量。当我尝试对字段进行分组时，出现错误“GROUP BY 子句中不允许使用有序分析函数”。我需要 GROUP BY 因为还有其他字段需要分组，但我还需要 SUM() OVER (PARTITION BY()) 进行其他计算。如何创建子查询以消除错误？

查询是这样的：

a.cust_no,
b.cust_name,
a.location,
c.product,
SUM(a.volume),
SUM(a.weight),
SUM(volume) OVER (PARTITION BY cust_no ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fixed_volume, 
SUM(CASE WHEN a_flag = 'Y' THEN volume ELSE 0 END)  OVER (PARTITION BY cust_no ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bias_volume
FROM test_table a
JOIN test_table2 b ON a.cust_no = b.cust_no
JOIN test_table3 c ON a.cust_no = c.cust_no
GROUP BY 1,2,3,4

标签： mysqlsqlteradata

解决方案

您的 GROUP BY 子句中没有 OLAP 函数。

我希望3504 Selected non-aggregate values must be part of the associated group，这应该解决它：

select
    a.cust_no,
    b.cust_name,
    a.location,
    c.product,
    SUM(a.volume),
    SUM(a.weight),
    --volume is a detail row -> doesn't exist after aggregation
    --SUM(a.volume) OVER (PARTITION BY cust_no) AS fixed_volume, 
    --aggregated volume can be used
    SUM(SUM(a.volume)) OVER (PARTITION BY cust_no) AS fixed_volume, 
    --same logic
    SUM(SUM(CASE WHEN a_flag = 'Y' THEN a.volume ELSE 0 END))  OVER (PARTITION BY cust_no ) AS bias_volume
FROM test_table a
JOIN test_table2 b ON a.cust_no = b.cust_no
JOIN test_table3 c ON a.cust_no = c.cust_no
GROUP BY 1,2,3,4

推荐阅读