SQL中具有平均值和计数的子查询

Question

Dears，我的数据包括以下信息；客户ID、交易金额和交易对手地区。对于每个地区，我想计算交易量的平均值。我的想法是使用子查询，但我搞砸了。这是我目前的想法，不起作用。

select avg(count_for_DK) from data where
(select count(amount) as count_for_DK, customer_id from data 
where country = 'DK'
group by customer_id, country 
order by customer_id asc)

有人能帮帮我吗？

屏幕上有我的表格示例：https://ibb.co/BZXDL4Q

Answer 1

根据我的理解，您希望在country='DK'的情况下获得客户的平均金额若这个逻辑就是你们想要的，那个么下面的查询就可以了：

select customer_id ,avg(amount) as average_for_DK from data 
where country = 'DK'
group by customer_id
order by customer_id 

如果您想获得国家/地区"DK"客户完成的交易数量的平均值

select avg(count_for_DK) from (
select customer_id ,count(amount) as count_for_DK from data 
where country = 'DK'
group by customer_id
order by customer_id) t 

如果你想得到每个国家的客户完成的交易数量的平均值：

select country,avg(count_for_country) from (
select country,customer_id ,count(amount) as count_for_country from data 

group by country,customer_id
order by country,customer_id) t
group by country