SQL - 按国家/地区按月划分的总收入上限

Question

我试图找到每个国家/地区创纪录的收入月份。下面的查询为每个国家/地区按月提供收入。

select d.calendar_year_month as 'Record_month',
c.country_name as 'country'
,sum(Net_qty*(unit_charge+unit_shipping_charge)) as 'Revenue'
from sensu_reporting.commercial_analysts.customer_science_transactions CST (nolock)
join Sensu.dbo.Country_D C (nolock) on cst.country_code = c.Country_Code
join sensu.dbo.Date_D d (nolock) on cst.Order_Date_Key = d.Date_Key

where cst.site_key in ('95')
and cst.order_date_key >= 20180101
group by d.calendar_year_month, c.country_name

我尝试使用：

select a.country,
a.record_month,
max(a.revenue) as 'Record_Revenue'
from(
select d.calendar_year_month as 'Record_month',
c.country_name as 'country'
,sum(Net_qty*(unit_charge+unit_shipping_charge)) as 'Revenue'
from sensu_reporting.commercial_analysts.customer_science_transactions CST (nolock)
join Sensu.dbo.Country_D C (nolock) on cst.country_code = c.Country_Code
join sensu.dbo.Date_D d (nolock) on cst.Order_Date_Key = d.Date_Key

where cst.site_key in ('95')
and cst.order_date_key >= 20180101
group by d.calendar_year_month, c.country_name)
a 
group by country, record_month

但是，这为我提供了与初始查询相同的数据。我做错了什么，如何修改我的查询，使其只提供每个国家/地区收入最高的月份？

Answer 1

由于我不知道您的表结构，这是一个只有一个表(id，calendar_year_month，国家/地区代码，收入(的简化示例。我使用子查询来确定最大收入。

select calendar_year_month, country, revenue
from reporting r
where revenue = (select max(revenue) from reporting r2 where r.country = r2.country )
group by calendar_year_month, country

我得到的结果是这样的

201802 NO 1500

201802 SE 3000 201803 DE 7000 201803 NO 1500

请注意，NO有两行。 我希望这可以转换为您的表结构。

Answer 2

这是你想要的吗？

with r as (
select d.calendar_year_month as Record_month,
c.country_name as country,
sum(Net_qty*(unit_charge+unit_shipping_charge)) as Revenue
from sensu_reporting.commercial_analysts.customer_science_transactions CST join
Sensu.dbo.Country_D C
on cst.country_code = c.Country_Code join
sensu.dbo.Date_D d
on cst.Order_Date_Key = d.Date_Key
where cst.site_key in ('95') and cst.order_date_key >= 20180101
group by d.calendar_year_month, c.country_name
)
select r.*
from (select r.*,
row_number() over (partition by record_month order by revenue desc) as seqnum
from r
) r
where seqnum = 1;