（元）社内SEの徒然なる日記

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

2016-09-09（Fri）

コンピュータ

■　やりたいこと

管理番号が主キーのテーブルがあります。これを管理テーブルとします。

もうひとつ、管理番号、枝番、支店の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 —