使用 SQL Rank() 获得整体排名和组内排名 [英] Using SQL Rank() for overall rank and rank within a group

问题描述

我正在尝试编写一个查询来返回总体排名和每组排名.我能够获得总体排名"，但领土排名不正确.

I'm trying to write a query to return the overall rank and a per group rank. I'm able to get the "Overall Rank", but the Territory rank isn't correct.

SELECT        Territory, [Agent Number], [Agent Name], [Commission Level], [Profile End Date], [Prior Year Sales], [Current Year Sales YTD], [Total Sales], Rank() 
                             OVER (PARTITION BY Territory
    ORDER BY [Total Sales]) AS [Territory Rank], Rank() OVER (ORDER BY [Total Sales] DESC) AS [Overall Rank]
    FROM            (SELECT        TerritoryBase.Name AS Territory, ContactExtensionBase.ipl_AgentNumber AS [Agent Number], ContactBase.FullName AS [Agent Name], 
                                                        ContactExtensionBase.ath_activecommissionlevel AS [Commission Level], ContactExtensionBase.ath_profileenddate AS [Profile End Date], 
                                                        CASE WHEN ath_PriorYearSales IS NULL THEN '0' ELSE ath_PriorYearSales END AS [Prior Year Sales], CASE WHEN ath_SalesYTD IS NULL 
                                                        THEN '0' ELSE ath_SalesYTD END AS [Current Year Sales YTD], CASE WHEN ath_PriorYearSales IS NULL 
                                                        THEN '0' ELSE ath_PriorYearSales END + CASE WHEN ath_SalesYTD IS NULL THEN '0' ELSE ath_SalesYTD END AS [Total Sales]
                              FROM            ContactBase INNER JOIN
                                                        ContactExtensionBase ON ContactBase.ContactId = ContactExtensionBase.ContactId INNER JOIN
                                                        TerritoryBase ON ContactExtensionBase.ipl_TerritoryId = TerritoryBase.TerritoryId
                              WHERE        (NOT (ContactExtensionBase.ath_PriorYearSales IS NULL)) AND (ContactExtensionBase.ath_PriorYearSales <> 0) OR
                                                        (NOT (ContactExtensionBase.ath_PriorYearSales IS NULL)) AND (ContactExtensionBase.ath_SalesYTD <> 0) OR
                                                        (ContactExtensionBase.ath_PriorYearSales <> 0) AND (NOT (ContactExtensionBase.ath_SalesYTD IS NULL)) OR
                                                        (NOT (ContactExtensionBase.ath_SalesYTD IS NULL)) AND (ContactExtensionBase.ath_SalesYTD <> 0)) AS DerivedTable1
    ORDER BY [Overall Rank]

以下是上述查询的示例结果:

Here is a sample result from the above query:

我知道我不明白如何使用Rank.有人可以帮我解决这个问题吗?

I know that I do not understand how to use Rank. Can someone help me figure this out?

我试图包括马克的建议，所以我的排名陈述现在看起来像这样:

I tried to include what Mark suggested so my rank statements now look like this:

SELECT        Territory, [Agent Number], [Agent Name], [Commission Level], [Profile End Date], [Prior Year Sales], [Current Year Sales YTD], [Total Sales], Rank() 
                         OVER (PARTITION BY [Territory]
ORDER BY [Total Sales] Desc) AS [Territory Rank], Rank() OVER (ORDER BY [Total Sales] Desc) AS [Overall Rank]

产生这个:

这已经很接近了，但领土排名似乎仍然没有按预期工作.我不确定 8 号和 9 号或 12 到 14 号在领土排名中的位置.我是否遗漏了 Rank 的工作原理?

This is close, but the territory rank still doesn't seem to work as expected. I'm not sure where number 8 and 9 or the 12 through 14 went inside the territory rank. Am I missing something with how Rank works?

推荐答案

看起来您的 Territory Rank 正在按 Total Sales 排序，而没有 DESCENDING> 所以它可能是倒退的.

Looks like your Territory Rank is ordering by Total Sales without the DESCENDING so it is probably backwards.

看看杰克和杰瑞.它们是领土排名"352 和 353，而不是 1 和 2.

Look at Jack and Jerry. They are 'territory ranked' 352 and 353, instead of 1 and 2.

注意:根据你在做什么，考虑你想如何处理平局，并注意还有 DENSE_RANK，如果有平局，它不会跳过数字.

NB: depending on what you're doing, consider how you want to handle ties and note there is also DENSE_RANK, which won't skip numbers if there is a tie.

这篇关于使用 SQL Rank() 获得整体排名和组内排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！