在子查询中使用大小写内部的外部引用进行聚合.收到错误

Question

这只是我完整查询的一小部分

我有一些数据正在使用聚合和子查询计算今年和去年(许多其他事情)值。

以下是我的查询的工作和最简单的版本：

SELECT distinct 
t1.country,
t1.manufacturer,
t1.category,
t1.month, 
t1.year,
(SELECT SUM(value) FROM data  ----- value is the column name which i want to aggregate 
WHERE mar_short_desc = t1.mar_short_desc
AND category = t1.category
AND manufacturer = t1.manufacturer
AND month = t1.month
AND year = t1.year ----- This Is the only difference Filter
) AS Company_TY, 
(SELECT SUM(value) FROM data  
WHERE mar_short_desc = t1.mar_short_desc
AND category = t1.category
AND manufacturer = t1.manufacturer
AND month = t1.month
AND year = t1.year -1  ----- This Is the only difference Filter
) AS Company_LY,
FROM data  t1
----- Other filters are here ignored for simplicity 
) a

我想优化它。因此，尝试将两个子查询合并为一个并使用"case"语句来计算相同的值。

这是修改后的版本：

SELECT distinct
t1.country,
t1.manufacturer,
t1.category,
t1.month, 
t1.year,
(SELECT SUM(CASE WHEN year = t1.year THEN value END) AS Company_TY,
SUM(CASE WHEN year = t1.year -1 THEN value END) AS company_LY 
FROM data
WHERE mar_short_desc = t1.mar_short_desc
AND category = t1.category
AND manufacturer = t1.manufacturer
AND month = t1.month
)
FROM data  t1
) a 

这给了我错误："在 包含外部引用的聚合表达式。如果表达式 聚合包含外部引用，然后是外部引用 引用必须是表达式中引用的唯一列。

我想做的是制作所有常见过滤器的框架，然后使用唯一的过滤器聚合该结果(原因：所以我不必一次又一次地过滤同样的东西。 这是一个大代码和大量数据，所以，想要优化)

Answer 1

为了优化，请考虑避免在SELECT中使用相关的子查询，而只需将主表JOIN派生表。这应该更有效，因为聚合只运行一次，而不是针对外部查询中的每一行。

由于年份匹配不同，下面使用两个派生表。虽然添加GROUP BY子句更详细，但它应该比第一个 SQL 代码块中的相关子查询运行得更好。甚至可以向联接字段添加索引以提高性能。

SELECT distinct
t1.country,
t1.manufacturer,
t1.category,
t1.month, 
t1.year,
sub1.Company_TY,
sub2.Company_LY
FROM data  t1
INNER JOIN
(SELECT mar_short_desc, category,  manufacturer, month, year,
SUM(value) AS Company_TY
FROM data 
GROUP BY mar_short_desc, category, manufacturer, month, year
) sub1
ON sub1.mar_short_desc = t1.mar_short_desc
AND sub1.category = t1.category
AND sub1.manufacturer = t1.manufacturer
AND sub1.month = t1.month
AND sub1.year = t1.year
INNER JOIN
(SELECT mar_short_desc, category,  manufacturer, month, year,
SUM(value) AS Company_LY
FROM data 
GROUP BY mar_short_desc, category, manufacturer, month, year
) sub2
ON sub2.mar_short_desc = t1.mar_short_desc
AND sub2.category = t1.category
AND sub2.manufacturer = t1.manufacturer
AND sub2.month = t1.month
AND sub2.year = t1.year - 1

Answer 2

在您的原始示例中，您有两列由内部查询返回给外部查询，如果您弹出一列并将另一列保留在子查询中，它可能会起作用。我不确定您正在使用的独特子句将如何进行。

SELECT distinct
t1.country,
t1.manufacturer,
t1.category,
t1.month, 
t1.year,
SUM(CASE WHEN year = t1.year THEN value END) AS Company_TY,
(SELECT SUM(CASE WHEN year = t1.year -1 THEN value END) AS company_LY 
FROM data
WHERE mar_short_desc = t1.mar_short_desc
AND category = t1.category
AND manufacturer = t1.manufacturer
AND month = t1.month
)
FROM data  t1
) a 

也尝试下面的查询，它使用 GROUP BY 和派生表，它们都更有效。

select Data1.country
, Data1.manufacturer
, Data1.category
, Data1.month
, Data1.year
, Data1.Total
, Data2.Total
from (SELECT t1.country,
t1.manufacturer,
t1.category,
t1.month, 
t1.year,
SUM(value) 'Total'
from data
group by t1.country,
t1.manufacturer,
t1.category,
t1.month, 
t1.year) Data1
inner join (SELECT country,
manufacturer,
category,
month, 
year,
SUM(value) 'Total'
from data
group by country,
manufacturer,
category,
month, 
year) Data2 on Data2.country = Data1.country
and Data2.manufacturer = Data1.manufacturer
and Data2.category = Data1.category
and Data2.month = Data1.month
and Data2.year = Data1.year -1