我正在尝试将一个新列添加到我的表中,该列将是通过两个现有列的除法计算得出的平均值。因此,平均值=总销售额/订单数量。
我的数据如下:click to view picture
我不明白为什么示例代码A不起作用而示例代码B起作用。有人能解释一下吗?
代码A示例
%%sql
SELECT
c.country,
count(distinct c.customer_id) customer_num,
count(i.invoice_id) order_num,
ROUND(SUM(i.total),2) total_sales,
order_num / total_sales avg_order_value
FROM customer c
LEFT JOIN invoice i ON c.customer_id = i.customer_id
GROUP BY 1
ORDER BY 4 DESC;
代码B示例
%%sql
WITH
customer_sales AS
(
SELECT
c.country,
count(distinct c.customer_id) customer_num,
count(i.invoice_id) order_num,
ROUND(SUM(i.total),2) total_sales
FROM customer c
LEFT JOIN invoice i ON c.customer_id = i.customer_id
GROUP BY 1
ORDER BY 4 DESC
)
SELECT
country,
customer_num,
order_num,
total_sales,
total_sales / order_num avg_order_value
FROM customer_sales;
谢谢!
1条答案
按热度按时间bnl4lu3b1#
根据DBMS的不同,有些允许您在计算中引用别名(在同一个SELECT中),而另一些则要求您在外部查询中将别名带到外部,或者声明您以前的聚合/函数,如COUNT或SUM。