SQL获取每月和所有月份最高值的行

Question

我需要一些帮助来编写满足以下要求的 SQL 语句(最终结果)。

注意:我正在使用 SQL 语法在 SAP HANA 系统(数据库)上编写此 SQL。 SQL 语法通常被普遍使用。

用于列名的一些缩写:

cust = customer
ctry = country
mth = month
HCostPP = Highest cost per period
HCtryPP = highest country per period
HCostAP = Highest cost over all periods
HCtryAP = highest country over all periods

我的源表中有粒度数据。通过像下面这样编写 SQL，我得到了聚合数据:

SELECT distinct cust,ctry,mth,sum(cost)
FROM mytable
GROUP BY cust,ctry,mth

I get aggregated data like which i used further to get my required results:  
cust  ctry  mth     cost
c001   US  201506  -100
c001   DK  201506  -100
c001   DE  201506  -50
c001   FR  201507  -200
c001   UK  201507   -50

最终要求的结果 我想实现应该如下所示:

cust   ctry  mth   cost  HCostPP HCtryPP HCostAP HCtryAP
c001   US  201506  -100  -100    DK      -200    FR
c001   DK  201506  -100  -100    DK      -200    FR
c001   DE  201506  -50   -100    DK      -200    FR
c001   FR  201507  -200  -200    FR      -200    FR
c001   UK  201507   -50  -200    FR      -200    FR

所需结果说明

based on data group (cust,ctry,mth) need to get for which 
country COST were hightest 'within each month' (HCostPP , HCtryPP)
and then again 'over all months'(HCostAP , HCtryAP).

捕捉

for month 201506, -100 cost is same for both US and DK. 
In this case take either one e.g. DK or US (i am showing above to take DK)

我试过的:
我知道需要两个左连接。第一个左连接应该如下所示以获得 HCostPP ， HCtryPP :

LEFT SIDE                            RIGHT SIDE
cust   ctry  mth   cost          cust   ctry  mth   cost
c001   US  201506  -100          c001   DK  201506  -100
c001   DK  201506  -100          c001   FR  201507  -200
c001   DE  201506  -50
c001   FR  201507  -200
c001   UK  201507   -50

为了获得右侧表，当我编写 SQL 时:

SELECT cust,ctry,mth, MIN(cost)
FROM
(
SELECT distinct cust,ctry,mth,sum(cost)
FROM mytable
GROUP BY cust,ctry,mth
)
GROUP BY cust,ctry,mth

i don't get the required result, i get:
cust   ctry  mth   cost
c001   US  201506  -100
c001   DK  201506  -100
c001   DE  201506  -50
c001   FR  201507  -200
c001   UK  201507   -50

if i do like: 
SELECT cust,mth, MIN(cost)
FROM
(
SELECT distinct cust,ctry,mth,sum(cost)
FROM mytable
GROUP BY cust,ctry,mth
)
GROUP BY cust,mth

then i get below and i lose 'cntry' column:
cust     mth   cost
c001     201506  -100
c001     201507  -200

此外，如果我使用 INNER JOIN 来获取“cntry”列:

SELECT cust,mth,ctry,cost FROM mytable AS 'main'
INNER JOIN (
SELECT cust,mth, MIN(cost) as cost1
FROM
(
SELECT distinct cust,ctry,mth,sum(cost)
FROM mytable
GROUP BY cust,ctry,mth
)
GROUP BY cust,mth ) AS 'sub'
ON main.cust=sub.cust, main.mnth=sub.mnth, main.cost=sub.cost1

then this gives me what is also not desired as 
it is giving me both rows i.e. for US and DK and i need only one here: 
cust   ctry  mth   cost
c001   US  201506  -100
c001   DK  201506  -100
c001   FR  201507  -200

我非常感谢在编写 SQL 以实现上述所需结果(部分最终要求的结果)方面的任何帮助。

感谢您的帮助。/问候/诺曼

Answer 1

这是第一部分，对于 PerPeriod 结果，您应该使用 over (partion by) 构造(这是很好的描述 here 。

select 
 t1.cust,
 t1.ctry,
 t1.mth,
 t1.cost,
 (select t3.ctry from mytable t3 order by t3.cost asc limit 1)  HCtryAP,
 min(t2.cost) HCostAP
from mytable t1, mytable t2
group by 1,2,3
order by 3, 4;