是否基于规则对数据集进行分组？

Question

我有下面的数据集：

Zone    Car      FiscalYear Rating  FinalRating     Year   Bool
----------------------------------------------------------------
Zone1   Mercedes    2020    green     green          2020   1
Zone1   Mercedes    2020    green     green          2021   0
Zone1   Mercedes    2021    green     green          2020   0
Zone1   Mercedes    2021    amber     amber          2021   1
Zone2   BMW         2020    amber     amber          2020   1
Zone2   BMW         2021    green     green          2020   0
Zone2   Skoda       2020    amber     amber          2020   1
Zone2   Skoda       2021    green     green          2020   0

我试图实现的是按区域、汽车和FiscalYear对数据进行分组。如果我们复制了要分组的会计年度，我想选择Bool = 1的行，基本上fiscalYear等于Year，但仅在需要分组的情况下。这就是我想要达到的目标：

Zone    Car      FiscalYear Rating  FinalRating     Year   Bool
----------------------------------------------------------------
Zone1   Mercedes    2020    green     green          2020   1
Zone1   Mercedes    2021    amber     amber          2021   1
Zone2   BMW         2020    amber     amber          2020   1
Zone2   BMW         2021    green     green          2020   0
Zone2   Skoda       2020    amber     amber          2020   1
Zone2   Skoda       2021    green     green          2020   0

我使用的是SQL Server 2014。我尝试使用分区和不同的分组规则，但没有成功。任何帮助都将不胜感激。

Answer 1

如果我没理解错的话，你不需要分组。您只需要为每个组选择一行，因此可以选择将ROW_NUMBER()与适当的PARTITION BY和ORDER BY子句一起使用：

SELECT Zone, Car, FiscalYear, Rating, FinalRating, Year, Bool
FROM (
   SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY Zone, Car, FiscalYear ORDER BY Bool DESC) AS Rn
   FROM (VALUES
      ('Zone1', 'Mercedes', '2020', 'green', 'green', 2020, 1),
      ('Zone1', 'Mercedes', '2020', 'green', 'green', 2021, 0),
      ('Zone1', 'Mercedes', '2021', 'green', 'green', 2020, 0),
      ('Zone1', 'Mercedes', '2021', 'amber', 'amber', 2021, 1),
      ('Zone2', 'BMW',      '2020', 'amber', 'amber', 2020, 1),
      ('Zone2', 'BMW',      '2021', 'green', 'green', 2020, 0),
      ('Zone2', 'Skoda',    '2020', 'amber', 'amber', 2020, 1),
      ('Zone2', 'Skoda',    '2021', 'green', 'green', 2020, 0)
   ) v (Zone, Car, FiscalYear, Rating, FinalRating, Year, Bool)   
) t
WHERE Rn = 1

结果：

Zone  Car      FiscalYear RatingFinalRating Year Bool
Zone1 Mercedes 2020       green  green      2020 1
Zone1 Mercedes 2021       amber  amber      2021 1
Zone2 BMW      2020       amber  amber      2020 1
Zone2 BMW      2021       green  green      2020 0
Zone2 Skoda    2020       amber  amber      2020 1
Zone2 Skoda    2021       green  green      2020 0

Answer 2

编写此代码的最快方法可能是：

select top 1 with ties Zone, Car, FiscalYear, Rating, FinalRating, Year, Bool
from yourtable t
order by row_number() over (PARTITION BY Zone, Car, FiscalYear ORDER BY Bool DESC) desc