SQL PARTITION BYで効率よくデータを取得する

SQL OVER句の分析関数で効率よくデータを集計するで分析関数を使って効率よくデータを集計する方法を紹介しましたが、PARTITION BYをうまく使用すれば、効率よく簡単にデータを集計だけでなく、取得することができます。

例えば、以下のようなデータがあるとします。(実際にはこのようなテーブル構成は考えにくいですが、わかりやすくするためにこのような例にしています。副問い合わせの結果や、VIEWなどで以下のテーブルを取得したと考えてください)

売上結果
部署コード 部署名 社員コード 社員名 年月 売上金額
1 営業部 1 テスト社員1 201501 500
1 営業部 1 テスト社員1 201502 1500
2 開発部 2 テスト社員2 201501 100
2 開発部 2 テスト社員2 201502 1000
2 開発部 3 テスト社員3 201501 200
2 開発部 3 テスト社員3 201502 100
2 開発部 4 テスト社員4 201501 2000
2 開発部 4 テスト社員4 201502 1000
1 営業部 5 テスト社員5 201501 1500
1 営業部 5 テスト社員5 201502 2000

部署ごとに、売上金額の合計が一番多い社員を表示したい。

SQLは下記のようになります。

SELECT
    URI2.部署名
    ,URI2.社員名
    ,URI2.売上累計
FROM (
  SELECT
        URI1.部署名
        ,URI1.社員名
        ,URI1.売上累計
        ,ROW_NUMBER() OVER(PATITION BY URI1.部署コード ORDER BY URI1.売上累計 DESC, URI1.社員コード) 売上ランク
    FROM (
        --部署、社員、ごとに売上金額の合計を取得します
        SELECT
            T1.部署コード
            ,T1.部署名
            ,T1.社員コード
            ,T1.社員名
            ,SUM(T1.売上金額) AS 売上累計
        FROM
            売上結果 T1
        GROUP BY
            T1.部署コード
            ,T1.部署名
            ,T1.社員コード
            ,T1.社員名
    ) URI1
) URI2
WHERE
    URI2.売上ランク = 1
実行結果
部署名 社員名 売上累計
営業部 テスト社員5 3500
開発部 テスト社員4 3000

URI1を取得する副問い合わせは、年月ごとにあるデータを、グループ化して、社員ごとに全期間の売上の合計を取得しています。

URI1
部署コード 部署名 社員コード 社員名 売上金額
1 営業部 1 テスト社員1 2000
2 開発部 2 テスト社員2 1100
2 開発部 3 テスト社員3 300
2 開発部 4 テスト社員4 3000
1 営業部 5 テスト社員5 3500

営業部で一番売上が多いのはテスト社員5、開発部ではテスト社員4なので、この2社員だけ取得する必要があります。

URI2を取得する副問い合わせの結果は以下のようになります。

URI2
部署名 社員名 売上金額 売上ランク
営業部 テスト社員1 2000 2
開発部 テスト社員2 1100 2
開発部 テスト社員3 300 3
開発部 テスト社員4 3000 1
営業部 テスト社員5 3500 1

ROW_NUMBER() OVER(PATITION BY URI1.部署コード DESC ORDER BY URI1.売上累計, URI1.社員コード)で、部署コードごとに売上累計の降順で行番号を取得しています(売上累計が同じ場合は社員コードの昇順に取得しています)。

PATITION BY URI1.部署コードで部署コード単位で集計することができるようになります。

売上累計の降順で並び替えたときの1行目が売上累計が最大のデータということになります。

よって、URI2テーブルをWHERE句で売上ランク=1で絞り込むと、部署ごとに売上累計が最大の社員のデータのみ取得することができます。


関連記事

  • SQL WITH句でVIEWを作成し重複するSQL文をまとめる

    WITH句でVIEWを作成し重複するSQL文をまとめる方法を説明します。WITH句を使えば1つの副問い合わせ(SQL)を複数の箇所で使いまわすことができます。


  • SQL OVER句の分析関数で効率よくデータを集計する

    分析関数を使用すれば、効率よく簡単にデータを集計することができます。集計関数COUNTの後にOVERを指定して、分析関数として集計します。OVERの中のPARTITION BYとORDER BYで分析...


  • SQL ORDER BY句で取得したデータを並び替える

    ORDER BY句で取得したデータを並び替える(ソート)方法について説明します。取得するデータを並び替えるには、ORDER BY句でソートしたいカラムを指定します。


  • SQL oracleでnullを比較する際の注意点

    oracleでnullを扱うときに注意する点をまとめました。値がnullのデータを取得したいときは、イコール(=)ではなくIS NULLで取得する必要があります。nullも含めて取得する場合は、NVL...


  • SQL MERGE文でINSERTとUPDATEを一回で行う

    テーブルにデータがすでに存在している場合は更新(UPDATE)、存在していない場合は登録(INSERT)をしたい場合があると思います。MERGE文を使えば一回のSQLで、INSERTとUPDATEの処...


  • SQL PARTITION BYで効率よくデータを取得する

    PARTITION BYをうまく使用すれば、効率よく簡単にデータを集計だけでなく、取得することができます。PATITION BYで部署コード単位で集計することができるようになります。売上累計の降順で並...


  • SQL INSERT文でデータを登録する

    INSERT文でデータベースに新しいデータを登録する方法を紹介します。テーブルに新しいデータを登録したい場合、以下のようなSQLを実行します。


  • SQL インデックスが効かない場合の原因と対処法

    テーブルにインデックスを張っていても実行計画を見るとなぜかテーブルがフルスキャンされていて「なんで?」となった経験があるかと思います。せっかく張ったインデックスが効かないSQLの実装例と対処法を紹介し...


  • SQL IN句をEXISTS句に変換する方法

    IN句をEXISTS句に変換する方法を紹介します。IN句よりもEXISTS句のほうがパフォーマンスが良くなる場合が多いので、主にレスポンス対策としてこの書き換えを行うことが多いと思います。


  • SQL IN句に1000件以上要素を指定したときのエラーの対処法

    SQLのIN句に1000件以上指定するとORA-01795: リストに指定できる式の最大数は1000です。のエラーが発生してしまいます。EXISTS句に書き換えられる場合は書き換えてしまうのが一番簡単...