犬の登録データを分析する際、犬種ごとの頭数ランキングを作成して上位を抽出することはよくある要件です。SQLのウィンドウ関数であるRANK関数やDENSE_RANK関数を活用すると、簡潔にランキングを生成できます。本記事では、ランキング作成の手法と具体的なSQL例を紹介します。
RANK関数とDENSE_RANK関数の違い
RANK関数は、同順位が存在する場合に次の順位を飛ばして計算します。一方、DENSE_RANK関数は、同順位があっても連続した順位を割り当てます。
例えば、頭数が同じ犬種が2つある場合、RANKでは1位、1位、3位となり、DENSE_RANKでは1位、1位、2位となります。
犬種ごとの頭数集計
まず、犬種ごとに頭数を集計します。GROUP BY句を使って集計することが基本です。
SELECT breed, COUNT(*) AS count FROM dogs GROUP BY breed
この結果を基に、ランキング関数で順位を付与します。
RANK関数を使ったランキング例
次に、RANK関数を使って上位3犬種を抽出する例です。
SELECT breed, count, RANK() OVER (ORDER BY count DESC) AS rank FROM (SELECT breed, COUNT(*) AS count FROM dogs GROUP BY breed) AS subquery WHERE rank <= 3;
このクエリでは、サブクエリで集計した頭数に対してRANK関数で順位を付与し、上位3犬種を取得しています。
DENSE_RANK関数を使った場合
DENSE_RANKを使うと、同順位の扱いが変わります。例えば同じ頭数の犬種があった場合でも、連続した順位として上位3を取得できます。
SELECT breed, count, DENSE_RANK() OVER (ORDER BY count DESC) AS dense_rank FROM (SELECT breed, COUNT(*) AS count FROM dogs GROUP BY breed) AS subquery WHERE dense_rank <= 3;
この方法で、同数の犬種が存在しても公平に上位を抽出可能です。
実務での注意点
大量データでは、集計とランキングを効率化するためにインデックスを活用することが推奨されます。また、集計対象や条件を絞ることで、クエリのパフォーマンスを向上させることができます。
まとめ
犬種ごとの頭数ランキングを作成する際、RANK関数やDENSE_RANK関数を活用すると簡潔に順位付けが可能です。サブクエリで頭数を集計し、ウィンドウ関数で順位を付与することで、上位3犬種のみを抽出できます。
用途に応じてRANKとDENSE_RANKを使い分け、正確かつ効率的にランキングデータを取得しましょう。
コメント