SQLShack (日本語)

admin

Max、Min、AVGなどのさまざまな集計関数を使用してデータの計算を行います。 これらの関数を使用して単一の出力行を取得します。 SQL Severには、分類に従って個々のフィールドのランクを指定するSQLランク関数が用意されています。 これは、参加する行ごとに集計値を返します。 SQLランク関数もウィンドウ関数として認識されます。

  • 注:この中のWindows用語は、Microsoft Windowsオペレーティングシステムに関連していません。, これらはSQLランク関数です。

以下のランク関数を持っています。

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • NTILE()

SQLのRANK関数では、OVER()句を使用して結果セット内の行のセットを定義します。 また、SQL PARTITION BY句を使用して、区画内のデータのサブセットを定義することもできます。 Order by句を使用して、結果を降順または昇順でソートすることもできます。

これらのSQLランク関数を調べる前に、サンプルデータを準備しましょう。, このサンプルデータでは、数学、科学、英語の科目の三人の学生の試験結果を持っています。

ExamResultテーブルには次のサンプルデータがあります。

今後の例では、各SQLランク関数を使用してみましょう。

ROW_Number()SQLランク関数

ROW_Number()SQLランク関数を使用して、指定されたデータの各行に対して一意の連番を取得します。 これは、最初の行のランクを与え、各行の値を一つずつインクリメントします。 同様の値を持つ行についても、異なるランクが得られます。,

次のクエリを実行して、マークごとに学生のランクを取得します。,

1
2
3
4
5
SELECT Studentname,
Subject,
Marks,
ROW_NUMBER() OVER(ORDER BY Marks) RowNumber
FROM ExamResult;

By default, it sorts the data in ascending order and starts assigning ranks for each row., 上記のスクリーンショットでは、マーク1の行番号50を取得します。

Order By句で降順を指定することができ、それに応じてランクが変更されます。,ae”>

1
2
3
4
5
SELECT Studentname,
Subject,
Marks,
ROW_NUMBER() OVER(ORDER BY Marks desc) RowNumber
FROM ExamResult;

RANK() SQL RANK Function

We use RANK() SQL Rank function to specify rank for each row in the result set., 私たちは、三つの科目の学生の結果を持っていま 私たちは、被験者のマークに従って学生の結果をランク付けしたいと考えています。 たとえば、次のスクリーンショットでは、学生Isabellaは英語の科目で最高のマークを取得し、数学の科目で最低のマークを取得しました。 マークに従って、イザベラは英語で最初のランクと数学の科目で3位を取得します。

この結果セットを取得するには、次のクエリを実行します。,v id=”c5a1f1b829″>

1
2
3
4
5
6
7
SELECT Studentname,
Subject,
Marks,
RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Studentname,
Rank;

Let’s execute the following query of SQL Rank function and look at the result set., このクエリでは、データをより小さなサブセットに分割するためにSQL PARTITION By句を指定しませんでした。 それぞれの行のランクを取得するには、over句on Marks句（降順）を使用してSQL Rank関数を使用します。,div id=”7d9d5b54ae”>

1
2
3
4
5
6
SELECT Studentname,
Subject,
Marks,
RANK() OVER(ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Rank;

In the output, we can see each student get rank as per their marks irrespective of the specific subject., たとえば、完全な結果セットの最高マークと最低マークは、それぞれ90マークと50マークです。 結果セットでは、最高のマークはランク1になり、最低のマークはランク9になります。

二人の学生が同じマークを取得した場合（この例では、行番号4と5）、そのランクも同じです。

DENSE_RANK()SQLランク関数

DENSE_RANK()関数を使用して、指定された列の値に従ってパーティション内の一意のランク番号を指定します。 これは、小さな差を持つランク関数に似ています。,

SQLランク関数DENSE_RANK()では、値が重複している場合、SQLはそれらの行にも異なるランクを割り当てます。 理想的には、重複または類似の値に対して同じランクを取得する必要があります。

次のクエリをDENSE_RANK()関数で実行しましょう。,

1
2
3
4
5
6
select studentname,
subject,
marks
dense_rank()over(order by marks desc)rank
from examresult
order by rank;

出力では、70マークを獲得したlilyとisabellaの両方に同じランクがあることがわかります。,

DENSE_RANK関数をSQL PARTITION BY句と組み合わせて使用しましょう。,v id=”2d0aafc924″>

1
2
3
4
5
6
7
select studentname,
subject,
marks,
dense_rank()over(partition by subject order by marks desc)rank
from examresult
order by studentname,
rank;

divしたがって、この場合のランク関数に似た結果セットです。,

次のクエリで学生マークを更新し、クエリを再実行しましょう。

1
update examresult set marks=70where studentname=’isabella’and subject=’maths’

学生グループでは、イザベラが数学と科学の科目で同様のマークを取得していることがわかります。, この場合、ランクも両方の被験者で同じです。

次のクエリでRANK（）とDENSE_RANK（）SQLランク関数の違いを見てみましょう。,b829″>

1
2
3
4
5
6
7
select studentname,
subject,
marks
dense_rank()over(partition by studentname order by marks)rank
from examresult
order by studentname,
rank;

出力では、パーティション内のランク関数出力のギャップを確認できます。, DENSE_RANK関数にはギャップはありません。

次のスクリーンショットでは、イザベラは二つの科目で同様の番号を持っていることがわかります。 ただし、rank関数は同様の値に対してrank1を割り当てますが、内部的にはrank twoを無視し、次の行はrank threeを取得します。

Dense_Rank関数では、ランクを維持し、値にギャップを与えません。

NTILE(N)SQLランク関数

NTILE(N)関数を使用して、指定された(N)数のグループ内の行数を分散します。, 各行グループは、指定された条件に従ってランクを取得します。 希望する数のグループの値を指定する必要があります。

私の例では、ExamResultテーブルに九つのレコードがあります。 NTILE(2)は、結果に二つのレコードのグループが必要であることを示しています。,

1
2
3
4
5
SELECT *,
NTILE(2) OVER(
ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY rank;

In the output, we can see two groups. Group 1 contains five rows, and Group 2 contains four rows.,

同様に、NTILE(3)は、各グループに三つのレコードを持つ三つのグループの行数を分割します。,

1
2
3
4
5
SELECT *,
NTILE(3) OVER(
ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY rank;

We can use SQL PARTITION BY clause to have more than one partition., 次のクエリでは、被験者の各パーティションは二つのグループに分割されます。,

1
2
3
4
SELECT *,
NTILE(2) OVER(PARTITION BY subject ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY subject, rank;

Practical usage of SQL RANK functions

We can use SQL RANK function to fetch specific rows from the data., ランク1から3までの学生のデータを取得したいとします。 次のクエリでは、共通テーブル式（CTE）を使用してROW_NUMBER（）関数を使用してデータを取得し、後で条件を満たすためにCTEからの結果をフィルタリングします。,d>

StudentRanksを
(
SELECT*,ROW_NUMBER()OVER(ORDER BY Marks)As Ranksum
From ExamResult
)
SELECT StudentName,Marks
FROM StudentRanks
WHERE Ranksum>=1とランク<=3
ランク順

sqlから始まるオフセットフェッチコマンドを使用できますレコードの特定の数を取得するためのサーバー2012。,v id=”0fa57edc44″>

With StudentRanks AS
(
SELECT*,ROW_NUMBER()OVER(ORDER BY Marks)AS Ranksum
FROM ExamResult
)
SELECT StudentName,Marks
FROM StudentRanks
ORDER BY Ranksofset1ROWS FETCH NEXT3ROWS ONLY;

sqlランク関数の簡単な要約

行番号

各ユニークなレコードへの連続ランク番号。,

RANK

パーティション内の各行にランク番号を割り当てます。 これは、同様の値の数値をスキップします。

Dense_RANK

これは、パーティション内の各行にランク番号を割り当てます。 同様の値の数値はスキップされません。

NTILE(N)

これは、指定されたパーティションごとに行数を分割し、パーティション内に一意の値を割り当てます。,

結論

この記事では、SQLランク関数とこれらの関数の違いについて説明しました。 Sql開発者がこれらの関数に精通していると、データをうまく探索して管理するのに役立ちます。 あなたが任意のコメントや質問がある場合は、以下のコメントにそれらを残すこと自由に感じる。,

  • 著者
  • 最近の投稿
インドのグルガオンでMCSA認定およびマイクロソフト認定トレーナーとして13年経験のうち、rajendraは、パフォーマンスの最適化、監視、高可用性、および災害復旧戦略と実装に焦点を当てたさまざまな大企業のために働いています。, 彼は、sql Server、Azure、MySQL、Linux、Power BI、パフォーマンスチューニング、AWS/Amazon RDS、Git、および関連するテクノロジに関する何百もの権威ある記事の著者であり、これまでに10m以上の読者によって閲覧されています。
彼は、単一のトピックに関する記事の最大の無料のオンラインコレクションの一つの作成者であり、Sql Server Always On Availability Groupsに関する50部のシリーズです。 SQL Serverコミュニティへの貢献に基づいて、彼はSQLShackで2020年と2021年に連続して権威ある”Best author of the year”を含むさまざまな賞で認識されています。,
Rajは常に新しい挑戦に興味があるので、彼の文章でカバーされているテーマに関するコンサルティングの助けが必要な場合は、rajendraで連絡することがでgupta16@gmail.,com
Rajendra Guptaによるすべての投稿を表示
Rajendra Guptaによる最新の投稿(すべて参照)
  • AWS RDS SQL Serverのマイナーおよびメジャーバージョンアップグレードの実行-January29,2021
  • AWS RDS PostgreSQLインスタンスのデプロイ-January27,2021
  • aws dmsを使用してオンプレミスsqlデータベースをAws Rds Sql Serverに移行する-january25,2021

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です