社内SEの徒然なる日記

元が1つ集計単位が違うデータの1対多結合の1回のSQLで実行!(ORACLE)

■ やりたいこと

管理番号が主キーのテーブルがあります。これを管理テーブルとします。
集計単位が違うSQLサンプル - 1

もうひとつ、管理番号、枝番、支店の3項目が主キーのテーブルがあります。これを、明細テーブルとします。
集計単位が違うSQLサンプル - 2

管理テーブルと明細テーブルは、管理番号で結合出来ます。この時の結合条件は、1(管理テーブル)対多(明細テーブル)になります。

これを前提として、管理テーブルを親として、明細テーブルを枝番と支店で集計した一覧(枝番&支店)と、明細テーブルを支店で集計した一覧(支店)を”それぞれソートして”結合したデータを作りたいのです。

枝番&支店のソート条件は、枝番の昇順、支店の昇順。支店のソート条件は支店です。

上記のサンプルデータを使うと、欲しい結果はこうなります。
集計単位が違うSQLサンプル - 3

さて、これをSQL1回で抽出しましょうか。

■ 考え方

今あるものだけで何とかしようとすると難しいですが、無ければ作れば良いだけの話です。

それぞれ内部テーブルを作って、その中で並び順で連番を作成。その連番で結合すれば良いので。

■ 内部テーブル「枝番&支店」

まず、管理番号、枝番、支店で金額を集計。この結果を内部テーブルにして、さらに分析関数のROW_NUMBERを使って、管理番号の単位(PARTITION)に、枝番、支店の昇順に連番を採番します。

SELECT 
管理番号,枝番,支店,金額 ,
ROW_NUMBER ()
OVER (
PARTITION BY 管理番号
ORDER BY 枝番,支店
) AS 連番
FROM
(
SELECT
管理番号,枝番,支店,SUM(金額) AS 金額
FROM 明細
GROUP BY
管理番号,枝番,支店
)


この実行結果は、こうなります。
集計単位が違うSQLサンプル - 4

■ 内部テーブル「支店」

内部テーブル「支店」の考え方も同じですね。

管理番号、支店で金額を集計。この結果を内部テーブルにして、さらに分析関数のROW_NUMBERを使って、管理番号の単位(PARTITION)に、支店の昇順に連番を採番します。

SELECT 
管理番号,支店,金額 ,
ROW_NUMBER ()
OVER (
PARTITION BY 管理番号
ORDER BY 支店
) AS 連番
FROM
(
SELECT
管理番号,支店,SUM(金額) AS 金額
FROM 明細
GROUP BY
管理番号,枝番,支店
)


この実行結果は、こうなります。
集計単位が違うSQLサンプル - 5

■ まとめる

さて、ここまでの結果をまとめて1つのSQLにします。
SELECT
管理.管理番号
,枝番支店.連番
,枝番支店.枝番
,枝番支店.支店
,枝番支店.金額
,支店.支店
,支店.金額
FROM
管理
-- 内部テーブル「枝番&支店」
,(
SELECT
管理番号,枝番,支店,金額 ,
ROW_NUMBER ()
OVER (
PARTITION BY 管理番号
ORDER BY 枝番,支店
) AS 連番
FROM
(
SELECT
管理番号,枝番,支店,SUM(金額) AS 金額
FROM 明細
GROUP BY
管理番号,枝番,支店
)
) 枝番支店
-- 内部テーブル「支店」
,(
SELECT
管理番号,支店,金額 ,
ROW_NUMBER ()
OVER (
PARTITION BY 管理番号
ORDER BY 支店
) AS 連番
FROM
(
SELECT
管理番号,支店,SUM(金額) AS 金額
FROM 明細
GROUP BY
管理番号,枝番,支店
)
) 支店
WHERE
管理.管理番号 = 枝番支店.管理番号
AND 枝番支店.管理番号 = 支店.管理番号
AND 枝番支店.連番 = 支店.連番
ORDER BY
管理.管理番号
,枝番支店.連番


最後のソート順の指定は、管理番号と連番のみで OK。この連番は目的のソート順に採番されているので、これ以上の条件は不要です。

最終結果は、こうなります。
集計単位が違うSQLサンプル - 6

机上検証しか出来なかったのでSQLのどっかにエラーがあるかもしれませんが、ま、大体こんなもんでしょう。

■ 後書き

今回のような処理。本当は高級言語の方でやった方が良いのかも知れません。

まず管理番号の一覧を取得して、これでループ。ループの中で、枝番&支店、支店をそれぞれ抽出して、ループ処理って感じかな?

ただ、私の設計ポリシーは可能な限り処理をDB側に寄せること。何回もDBに処理を投げるよりも、多少複雑なSQLになっても1回で終わらせた方が総合的な負荷は少ないし、処理速度も速くなるだろうって判断です。

それに、後で処理を追う時に、SQLを読み解くだけで処理の全体像が分かるから(私には)楽だし。

ま、この辺りは環境(ハード、ソフト、人員のスキルなど)によって答えは変わるところでしょうね。

次回:元が1つ集計単位が違うデータの1対多結合の1回のSQLで実行!(ORACLE)の内情

最新の記事:http://harikofu.blog.fc2.com/
投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する