Comment utiliser RANK () dans SQL Server
J'ai un problème en utilisant RANK()
dans SQL Server.
Voici mon code:
SELECT contendernum,
totals,
RANK() OVER (PARTITION BY ContenderNum ORDER BY totals ASC) AS xRank
FROM (
SELECT ContenderNum,
SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
FROM Cat1GroupImpersonation
GROUP BY ContenderNum
) AS a
Les résultats de cette requête sont:
contendernum totals xRank
1 196 1
2 181 1
3 192 1
4 181 1
5 179 1
Quel est le résultat souhaité:
contendernum totals xRank
1 196 1
2 181 3
3 192 2
4 181 3
5 179 4
Je veux classer le résultat en fonction de totals
. S'il y a la même valeur comme 181
, alors deux nombres auront le même xRank
.
8 réponses
Changement:
RANK() OVER (PARTITION BY ContenderNum ORDER BY totals ASC) AS xRank
À:
RANK() OVER (ORDER BY totals DESC) AS xRank
Jetez un oeil à cet exemple:
Vous avez besoin d'aide?]}
Vous pouvez également regarder la différence entre RANK (Transact-SQL) et DENSE_RANK (Transact-SQL):
Rang (Transact-SQL)
Si deux lignes ou plus sont à égalité pour un rang, chaque ligne liée reçoit la même
rang. Par exemple, si les deux meilleurs vendeurs ont le même SalesYTD
valeur, ils sont tous les deux en tête du classement. Le vendeur avec le prochain plus haut
SalesYTD est classé numéro trois, car il y a deux lignes qui sont
de rang supérieur. Par conséquent, la fonction RANK ne revient pas toujours
nombres entiers consécutifs.
DENSE_RANK (Transact-SQL)
Renvoie le rang des lignes dans la partition d'un jeu de résultats, sans
toute lacune dans le classement. Le rang d'une ligne est un plus le nombre de
rangs distincts qui viennent avant la rangée dans question.
Rang (Transact-SQL)
Si deux lignes ou plus sont à égalité pour un rang, chaque ligne liée reçoit la même rang. Par exemple, si les deux meilleurs vendeurs ont le même SalesYTD valeur, ils sont tous les deux en tête du classement. Le vendeur avec le prochain plus haut SalesYTD est classé numéro trois, car il y a deux lignes qui sont de rang supérieur. Par conséquent, la fonction RANK ne revient pas toujours nombres entiers consécutifs.
DENSE_RANK (Transact-SQL)
Renvoie le rang des lignes dans la partition d'un jeu de résultats, sans toute lacune dans le classement. Le rang d'une ligne est un plus le nombre de rangs distincts qui viennent avant la rangée dans question.
Pour répondre au titre de votre question, "comment utiliser Rank () dans SQL Server", voici comment cela fonctionne:
Je vais utiliser cet ensemble de données comme exemple:
create table #tmp
(
column1 varchar(3),
column2 varchar(5),
column3 datetime,
column4 int
)
insert into #tmp values ('AAA', 'SKA', '2013-02-01 00:00:00', 10)
insert into #tmp values ('AAA', 'SKA', '2013-01-31 00:00:00', 15)
insert into #tmp values ('AAA', 'SKB', '2013-01-31 00:00:00', 20)
insert into #tmp values ('AAA', 'SKB', '2013-01-15 00:00:00', 5)
insert into #tmp values ('AAA', 'SKC', '2013-02-01 00:00:00', 25)
Vous avez une partition qui spécifie essentiellement le regroupement.
Dans cet exemple, si vous partitionnez par column2, la fonction rank créera des rangs pour les groupes de valeurs column2. Il y aura différents rangs pour les lignes où column2 = ' SKA 'que les lignes où column2 =' SKB ' et ainsi de suite.
Les rangs sont décidés comme ceci: Le rang pour chaque enregistrement est un plus le nombre de rangs qui viennent avant elle dans sa partition. Le rang ne s'incrémente que lorsque l'un des champs que vous avez sélectionnés (autre que le ou les champs partitionnés) est différent de ceux qui le précèdent. Si tous les champs sélectionnés sont les mêmes, que les rangs seront liés et les deux seront affectés à la valeur, un.
Sachant cela, si nous voulions seulement sélectionner une valeur de chaque groupe dans la colonne Deux, nous pourrions utiliser ceci requête:
with cte as
(
select *,
rank() over (partition by column2
order by column3) rnk
from t
) select * from cte where rnk = 1 order by column3;
Résultat:
COLUMN1 | COLUMN2 | COLUMN3 |COLUMN4 | RNK
------------------------------------------------------------------------------
AAA | SKB | January, 15 2013 00:00:00+0000 |5 | 1
AAA | SKA | January, 31 2013 00:00:00+0000 |15 | 1
AAA | SKC | February, 01 2013 00:00:00+0000 |25 | 1
Vous devez utiliser DENSE_RANK plutôt que RANK. La seule différence est qu'il ne laisse pas de lacunes. Vous ne devriez pas non plus partitionner par contenter_num, sinon vous classez chaque concurrent dans un groupe distinct, donc chacun est classé au 1er rang dans leurs groupes distincts!
SELECT contendernum,totals, DENSE_RANK() OVER (ORDER BY totals desc) AS xRank FROM
(
SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
FROM dbo.Cat1GroupImpersonation
GROUP BY ContenderNum
) AS a
order by contendernum
Un indice pour utiliser StackOverflow, veuillez publier des données DDL et des exemples afin que les gens puissent vous aider à utiliser moins de leur temps!
create table Cat1GroupImpersonation (
contendernum int,
criteria1 int,
criteria2 int,
criteria3 int,
criteria4 int);
insert Cat1GroupImpersonation select
1,196,0,0,0 union all select
2,181,0,0,0 union all select
3,192,0,0,0 union all select
4,181,0,0,0 union all select
5,179,0,0,0;
DENSE_RANK () est un rang sans lacunes, c'est-à-dire "dense".
select Name,EmailId,salary,DENSE_RANK() over(order by salary asc) from [dbo].[Employees]
RANK () - il contient l'écart entre le rang.
select Name,EmailId,salary,RANK() over(order by salary asc) from [dbo].[Employees]
Vous avez déjà groupé par Contenternum, pas besoin de partitionner à nouveau par elle. Utilisez Dense_rank () et l'ordre par totaux desc. En bref,
SELECT contendernum,totals, **DENSE_RANK()**
OVER (ORDER BY totals **DESC**)
AS xRank
FROM
(
SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
FROM dbo.Cat1GroupImpersonation
GROUP BY ContenderNum
) AS a
SELECT contendernum,totals, RANK() OVER (ORDER BY totals ASC) AS xRank FROM
(
SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
FROM dbo.Cat1GroupImpersonation
GROUP BY ContenderNum
) AS a
RANK()
est bon, mais il attribue le même rang pour des valeurs égales ou similaires. Et si vous avez besoin d'un rang unique, alors ROW_NUMBER () résout ce problème
ROW_NUMBER() OVER (ORDER BY totals DESC) AS xRank
Sélectionnez T. Tamil, T. English, T. Maths, T. Total, Dense_Rank()Over(Order by T. total Desc) comme Std_Rank De (sélectionnez tamoul, anglais, mathématiques, (tamoul + anglais + mathématiques) comme Total de L'étudiant) comme T entrez la description de l'image ici