■ やりたいこと
管理番号が主キーのテーブルがあります。これを管理テーブルとします。
もうひとつ、管理番号、枝番、支店の3項目が主キーのテーブルがあります。これを、明細テーブルとします。
管理テーブルと明細テーブルは、管理番号で結合出来ます。この時の結合条件は、1（管理テーブル）対多（明細テーブル）になります。
これを前提として、管理テーブルを親として、明細テーブルを枝番と支店で集計した一覧（枝番＆支店）と、明細テーブルを支店で集計した一覧（支店）を”それぞれソートして”結合したデータを作りたいのです。
枝番＆支店のソート条件は、枝番の昇順、支店の昇順。支店のソート条件は支店です。
上記のサンプルデータを使うと、欲しい結果はこうなります。
さて、これをSQL1回で抽出しましょうか。
■ 考え方
今あるものだけで何とかしようとすると難しいですが、無ければ作れば良いだけの話です。
それぞれ内部テーブルを作って、その中で並び順で連番を作成。その連番で結合すれば良いので。
■ 内部テーブル「枝番＆支店」
まず、管理番号、枝番、支店で金額を集計。この結果を内部テーブルにして、さらに
分析関数のROW_NUMBERを使って、管理番号の単位（PARTITION）に、枝番、支店の昇順に連番を採番します。
SELECT
管理番号,枝番,支店,金額 ,
ROW_NUMBER ()
OVER (
PARTITION BY 管理番号
ORDER BY 枝番,支店
) AS 連番
FROM
(
SELECT
管理番号,枝番,支店,SUM(金額) AS 金額
FROM 明細
GROUP BY
管理番号,枝番,支店
)
この実行結果は、こうなります。
■ 内部テーブル「支店」
内部テーブル「支店」の考え方も同じですね。
管理番号、支店で金額を集計。この結果を内部テーブルにして、さらに
分析関数のROW_NUMBERを使って、管理番号の単位（PARTITION）に、支店の昇順に連番を採番します。
SELECT
管理番号,支店,金額 ,
ROW_NUMBER ()
OVER (
PARTITION BY 管理番号
ORDER BY 支店
) AS 連番
FROM
(
SELECT
管理番号,支店,SUM(金額) AS 金額
FROM 明細
GROUP BY
管理番号,枝番,支店
)
この実行結果は、こうなります。
■ まとめる
さて、ここまでの結果をまとめて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のどっかにエラーがあるかもしれませんが、ま、大体こんなもんでしょう。
■ 後書き
今回のような処理。本当は高級言語の方でやった方が良いのかも知れません。
まず管理番号の一覧を取得して、これでループ。ループの中で、枝番＆支店、支店をそれぞれ抽出して、ループ処理って感じかな？
ただ、私の設計ポリシーは可能な限り処理をDB側に寄せること。何回もDBに処理を投げるよりも、多少複雑なSQLになっても1回で終わらせた方が総合的な負荷は少ないし、処理速度も速くなるだろうって判断です。
それに、後で処理を追う時に、SQLを読み解くだけで処理の全体像が分かるから（私には）楽だし。
ま、この辺りは環境（ハード、ソフト、人員のスキルなど）によって答えは変わるところでしょうね。
次回：
元が1つ集計単位が違うデータの1対多結合の1回のSQLで実行！（ORACLE）の内情最新の記事：
http://harikofu.blog.fc2.com/投稿記事の一覧：
http://harikofu.web.fc2.com/--- blog end —
スポンサードリンク
生活
0
0
