sql

sql ランク関数の使い方 SQLSERVER

たとえば、クラスで一番背の高い男女の体重や名前を調べたいとき、MAX関数を使おうとしてしまいがちですが、その場合身長をキーにレコードを取得する必要があって、件数が膨大なデータに対して行おうとした場合にパフォーマンスの懸念があります。(データベースはSQL SERVERを使用)

そこで、ランク関数を使うと便利なんですが、注意点もあるので整理してみようと思います。

事前準備

まずは、テーブルを作成します。構成は以下の通り。

カラム名意味型備考
ID行を一意に識別するキーint
NAME名前varchar(10)
SEX性別int1：男、2：女
HEIGHT身長int
WEIGHT体重int
CREATE TABLE sample
(
    ID int PRIMARY KEY,
    NAME varchar(10),
  　SEX int,
    HEIGHT int,
    WEIGHT int
)

次にデータを作成します。

INSERT INTO sample VALUES(1,'James',1,182,75),
	(2,'Oliver',1,170,80),
	(3,'Lucas',1,175,55),
	(4,'Liam',1,185,68),
	(5,'Emma',2,162,48),
	(6,'Mia',2,170,53),
	(7,'Luna',2,158,50),
	(8,'Amelia',2,162,40);

データを作成した結果は以下のような感じになります。

データ集計

準備が整ったので、早速集計を行っていきます。

抽出したいデータは、sampleテーブルの中で、背が高いTop2の男の子と女の子のレコード情報全て、とします。

SELECT
	CASE SEX
		WHEN 1 THEN '男子'
		WHEN 2 THEN '女子'
	END SEI,
	RANK, ID, NAME, HEIGHT, WEIGHT
FROM
	(SELECT
		RANK() OVER(PARTITION BY SEX ORDER BY HEIGHT DESC) as RANK, 
		ID, NAME, SEX, HEIGHT, WEIGHT
	 FROM
		sample
	) tmp
WHERE
	RANK in(1, 2)
ORDER BY
	SEX,
	RANK
;

9行目でRANK関数を使用しています。PARTITION BY句は、ランキング集計をグルーピングしたい場合に使います。

この場合、性別毎にランク集計を行いたいので「PARTITION BY SEX」としています。PARTITION BYを指定しなかった場合はレコード全体からランク集計が行われます。

ORDER BYは、順位付けしたい項目を指定します。この場合、背の高い順にランク付けしたいので、降順を意味する「DESC」を指定します。もし「DESC」を指定しなかった場合は、背の低い順の集計になってしまいます。

取得結果は以下の通り。

最後に

注意点は二つあります。

  • 上記の場合、身長が全く同じ人が存在しなかったので順位の重複はありませんでしたが、当然重複する場合もありえます。重複をそのままにしたい場合はRANK関数、順位が重複した場合でも、ある意味強制的に順位を分けたい場合はROW_NUMBER関数を使用します。この場合の順位は、SQL SERVERのデータの取得順に依存するので注意が必要。
  • 順位で条件付けしたい場合(*)は、ランク関数を使用したサブクエリを作成して、そのサブクエリを参照している側からWEHRE句で条件付けしてください。RANK関数はWHERE句内で条件指定できない為です。これはROW_NUMBER関数を使用した場合も同じです。
    (*) 上記の例では1位と2位の人を条件に指定しています(15行目)

-sql
-