如何避免嵌套查询

Question

我读到嵌套查询可能会减慢整个查询的速度，并且想知道在我的代码中避免这种情况的方法。我基本上想总结每个客户的所有订单的总价值(这里的客户是由CUSTOMER_EMAIL表示的)。我需要选择一些将在CASE语句中使用的字段(D365_ID和STORE_NAME)，这些字段将用于插入新列并确定货币，但是当我对结果进行分组时，我不想再显示这些字段(D365_ID和STORE_NAME)。

我原来的代码是:

select CUSTOMER_EMAIL, D365_ID, STORE_NAME,
sum(line_price) VALUE_Total,
count(distinct order_id) AS Orders_Total, 
CASE 
WHEN D365_ID = 'NORD' THEN 'EURO'
WHEN D365_ID = 'US' THEN 'USD'
WHEN D365_ID = 'UK' THEN 'GBP'
WHEN D365_ID = 'LMG' THEN 'EURO'
WHEN D365_ID = 'SWE' THEN 'SEK'     
WHEN D365_ID = 'Unknown' THEN
CASE 
WHEN STORE_NAME LIKE 'DE%' THEN 'EURO'
WHEN STORE_NAME LIKE 'SE EUR%' THEN 'EURO'
WHEN STORE_NAME LIKE 'SE SEK%' THEN 'SEK'
ELSE 'Unknown'
END
ELSE 'Unknown'
END CURRENCY
from "CUSTOMER_TRANSACTIONS"
group by customer_email, D365_ID, CURRENCY, STORE_NAME

然而，我无法删除D365_ID和STORE_NAME，并被迫将其放在组by子句中。

我必须更改代码并创建一个嵌套查询，以便在最终结果中不包含这些字段。

嵌套查询:

select CUSTOMER_EMAIL, CURRENCY,
sum(line_price) VALUE_Total,
count(distinct order_id) AS Orders_Total,
from 
(SELECT CUSTOMER_EMAIL, D365_ID, STORE_NAME, line_price, order_date, order_id,
CASE 
WHEN D365_ID = 'NORD' THEN 'EURO'
WHEN D365_ID = 'US' THEN 'USD'
WHEN D365_ID = 'UK' THEN 'GBP'
WHEN D365_ID = 'LMG' THEN 'EURO'
WHEN D365_ID = 'SWE' THEN 'SEK'     
WHEN D365_ID = 'Unknown' THEN
CASE 
WHEN STORE_NAME LIKE 'DE%' THEN 'EURO'
WHEN STORE_NAME LIKE 'SE EUR%' THEN 'EURO'
WHEN STORE_NAME LIKE 'SE SEK%' THEN 'SEK'
ELSE 'Unknown'
END
ELSE 'Unknown'
END CURRENCY
from "CUSTOMER_TRANSACTIONS")
group by customer_email, CURRENCY

有更好的方法吗?

Answer 1

可以通过别名或列顺序引用组中的列

select CUSTOMER_EMAIL, 
CASE 
WHEN D365_ID = 'NORD' THEN 'EURO'
WHEN D365_ID = 'US' THEN 'USD'
WHEN D365_ID = 'UK' THEN 'GBP'
WHEN D365_ID = 'LMG' THEN 'EURO'
WHEN D365_ID = 'SWE' THEN 'SEK'     
WHEN D365_ID = 'Unknown' THEN
CASE 
WHEN STORE_NAME LIKE 'DE%' THEN 'EURO'
WHEN STORE_NAME LIKE 'SE EUR%' THEN 'EURO'
WHEN STORE_NAME LIKE 'SE SEK%' THEN 'SEK'
ELSE 'Unknown'
END
ELSE 'Unknown'
END CURRENCY
sum(line_price) VALUE_Total,
count(distinct order_id) AS Orders_Total, 

from "CUSTOMER_TRANSACTIONS"
group by CUSTOMER_EMAIL, CURRENCY
--OR group by CUSTOMER_EMAIL, 2