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

Question

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

4

我需要帮助编写以下要求（最终结果）的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).

CATCH

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以实现上述所需结果（最终所需结果部分）方面提供的任何帮助。

谢谢您的帮助。/敬礼/NOMAN

- nomann

嗨nomann，你的最终结果表是错误的（我猜）。我认为HCtryPP列在最后两行中的条目应该是FR而不是DK，对吧？ - V. Wolf

cust ctry mth cost HCostPP HCtryPP HCostAP HCtryAP c001 美国 201506 -100 -100 丹麦 -200 法国 c001 丹麦 201506 -100 -100 丹麦 -200 法国 c001 德国 201506 -50 -100 丹麦 -200 法国 c001 法国 201507 -200 -200 法国 -200 法国 c001 英国 201507 -50 -200 法国 -200 法国 - V. Wolf

嗨，感谢指出错误。我已经编辑了“最终结果”。 - nomann

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- V. Wolf · Answer 1

这是第一部分，对于PerPeriod结果，您应该使用over（partion by）构造（在这里非常好地描述了）。

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;