时间戳

首页 > 解决方案 > 按年份返回最大值和相应 FK(ID) 组的查询，该查询来自另一个按 FK(ID) 和年份平均分组的选择

sql - 按年份返回最大值和相应 FK(ID) 组的查询，该查询来自另一个按 FK(ID) 和年份平均分组的选择

问题描述

我有一个原始表，其中包含 id（pk AI）、farm_fk（或 HERD）、生日、nm（某些指标）。

我想获得每个现有年份和相应的 farm_fk 的最大 nm 平均值。

什么是 DID？

表中的第一个我得到了每个farm_fk组的nm平均值，它返回类似这样的东西我添加了内部选择查询（这里我添加了order by也是为了便于阅读）

SELECT 
    YEAR(FA_BDATE) AS BYEAR, FARM_FK AS HERD, AVG(NET_MERIT) AS NM
FROM 
    __FARM_ANALYTICS_TBL
GROUP BY 
    FARM_FK, YEAR(FA_BDATE)
ORDER BY
    HERD, BYEAR

输出：

2006    10045   -181.553596496582
2007    10045   -252.123779296875
2008    10045   -97.3785720467567
2009    10045   -46.0490081493671
2010    10045   -2.05634630137476
2011    10045   33.5371181927621
2012    10045   74.3363304953117
2013    10045   124.057859333072
2014    10045   177.423686878542
2015    10045   188.846870697982
2016    10045   241.421725696403
2017    10045   318.593410228532
2018    10045   443.3983160078
2019    10045   483.12452005201
2010    10046   -99.2454333305359
2011    10046   42.3376306125096
2012    10046   71.8295436098769
2013    10046   90.7501822723432
2014    10046   133.500797046962
2015    10046   135.329324710063
2016    10046   223.211583482458
2017    10046   261.208083089193
2018    10046   409.256013000512
2019    10046   468.574509707364
2010    10047   -97.1618871688843
2011    10047   -1.06820678710938
2012    10047   20.5999549464746
2013    10047   5.93872594833374
2014    10047   134.559080774134
2015    10047   221.275759446621
2016    10047   203.30495039622
2017    10047   280.823856965995
2018    10047   304.591577597225
2019    10047   399.748709002112

其次，我尝试提取每年和相应牛群的最大值（avg（nm）），我尝试了这个

SELECT 
    BYEAR, HERD, MAX(NM) AS MAX_NM
FROM
    (SELECT 
         YEAR(FA_BDATE) AS BYEAR, FARM_FK HERD, AVG(NET_MERIT) AS NM
     FROM 
         __FARM_ANALYTICS_TBL
     GROUP BY 
         FARM_FK, YEAR(FA_BDATE)) AS AVGNM
GROUP BY 
    BYEAR
ORDER BY 
    BYEAR

哪个不能正常工作，因为我认为根据我需要按 HERD 分组是错误的，而且每年以前的选择中有很多 HERD。在上面的查询中没有 HERD 列我得到这个是正确的，但没有相应的牧群 ID

2005    -258.71630859375
2006    -150.947634379069
2007    -127.1032270704
2008    -5.74109745025635
2009    -19.5938364664714
2010    -2.05634630137476
2011    64.6482777208895
2012    109.018188629743
2013    260.781127929688
2014    219.82367308171
2015    244.199884947291
2016    296.168976219786
2017    391.202879227419
2018    460.009900628413
2019    493.26334651952

标签： sqlsql-serverdatabase

解决方案

我想你只想使用ROW_NUMBER()：

SELECT hy.*
FROM (SELECT YEAR(FA_BDATE) AS BYEAR, FARM_FK AS HERD, AVG(NET_MERIT) AS NM,
             ROW_NUMBER() OVER (PARTITION BY YEAR(FS_BDATE) ORDER BY AVG(NET_MERIT) DESC) as seqnum
      FROM __FARM_ANALYTICS_TBL
      GROUP BY FARM_FK, YEAR(FA_BDATE)
     ) hy
WHERE seqnum = 1
ORDER BY BYEAR;

请注意，如果出现平局，则仅返回其中一个平局。如果您想要所有这些，请使用RANK()而不是ROW_NUMBER().

推荐阅读