SQL Serverで犬テーブルの体重順位と平均体重をウィンドウ関数で求める方法

SQL Serverで犬のデータを扱うとき、各犬の名前・犬種・体重に加えて、同一犬種内での体重順位や犬種ごとの平均体重を求めたいケースがあります。この記事では、ウィンドウ関数を活用してこれを効率的に取得する方法を解説します。

犬テーブルの基本構造

まず、犬テーブルの基本的な構造を確認しておきます。通常、以下のようなカラムが存在します。

このテーブルを元に、ウィンドウ関数で順位や平均値を求めていきます。

ウィンドウ関数とは？

ウィンドウ関数は、行ごとの集計や順位計算を、グループ全体を参照しながら行えるSQL Serverの強力な機能です。

代表的なウィンドウ関数には RANK() や ROW_NUMBER()、AVG() OVER() があります。これらを使うことで、集計と行の詳細情報を同時に取得できます。

犬種ごとの体重順位を求める

犬種ごとの体重順位は RANK() を用いると簡単に求められます。SQLの例を示します。

SELECT 犬名, 犬種, 体重, RANK() OVER(PARTITION BY 犬種 ORDER BY 体重 DESC) AS 体重順位
FROM 犬テーブル;

このクエリでは、PARTITION BY 犬種 により犬種ごとに区切り、ORDER BY 体重 DESC で体重の高い順に順位を付けています。

実行結果は次のようになります。

犬種ごとの平均体重を計算する

平均体重は AVG() OVER() を使用すると、犬種ごとに一括で計算できます。

SELECT 犬名, 犬種, 体重, AVG(体重) OVER(PARTITION BY 犬種) AS 平均体重
FROM 犬テーブル;

これにより、各犬の行にその犬種の平均体重が付与されます。例えば柴犬では 13.25kg、ビーグルでは 10.0kg となります。

順位と平均体重を同時に取得する

先ほどの2つのウィンドウ関数を組み合わせると、犬種ごとの体重順位と平均体重を一度のクエリで取得できます。

SELECT 犬名, 犬種, 体重,
       RANK() OVER(PARTITION BY 犬種 ORDER BY 体重 DESC) AS 体重順位,
       AVG(体重) OVER(PARTITION BY 犬種) AS 平均体重
FROM 犬テーブル;

このクエリを実行すると、各犬の情報に加え、犬種内での順位と平均体重が確認できます。

まとめ

SQL Serverのウィンドウ関数を活用することで、犬テーブルから犬種ごとの体重順位や平均体重を簡単に取得できます。RANK() と AVG() OVER() を組み合わせることで、複雑な集計も効率的に行えます。

これらのテクニックを使うと、ペットデータの分析やランキング表示など、さまざまな応用が可能です。