ORACLEのSELECT文で質問： 一つのテーブル内の数値項目について、当該レコード自体の値と 同テーブル内の同一分類の集計結果を併記する方法として 最適な手立ては どういうものですか？

Question

SELECT Z.場所コード, Z.品名コード, Z.ロットＮＯ, Z.在庫数 FROM T_ZAIKO Z ORDER BY Z.場所コード, Z.品名コード, Z.ロットＮＯ

同一品名コードの在庫が　ロットＮＯ違いで　他拠点に在庫されています。同一品名かつ同一のロットＮＯが　他拠点に跨いで在庫されているケースもありえるかも....知れません。
上記のSELECT文では　場所コード・ロットＮＯを明細の単位に　在庫数が　表示されますが、こちらに　全国での　同一品名の総在庫数を表示させるとした場合で　質問です。

今までは　総計表示を達成する対象が　同じ対象のテーブルに格納されていても　別SELECT文・副問合せから結果を得てきました。

SELECT Z1.場所コード, Z1.品名コード, Z1.ロットＮＯ, Z1.在庫数、Z2.総在庫 FROM T_ZAIKO Z1 LEFT OUTER JOIN 
(SELECT Z.品名コード, SUM(Z.在庫数) 総在庫数 FROM T_ZAIKO Z GROUP BY Z.品名コード) Z2 ON Z2.品名コード=Z1.品名コード ORDER BY Z1.場所コード, Z1.品名コード, Z1.ロットＮＯ

これ以上にスマートな方法は　ないのでしょうか？

そろそろ　そこそこのレスポンスで　結果が出れば良い　というレベルから卒業したく　恥を忍んで　お聞きします。よろしくお願いします。

Answer 1

見辛いので、整形します。
以下が質問のSQLです。

SELECT  Z1.場所コード, Z1.品名コード, Z1.ロットＮＯ, Z1.在庫数, Z2.総在庫 
FROM  T_ZAIKO Z1 LEFT OUTER JOIN (
        SELECT  Z.品名コード, SUM(Z.在庫数) 総在庫数 
        FROM  T_ZAIKO Z 
        GROUP BY  Z.品名コード
      ) Z2 
      ON  Z2.品名コード=Z1.品名コード 
ORDER BY Z1.場所コード, Z1.品名コード, Z1.ロットＮＯ

自己結合の場合、Window関数を使用すると、簡潔かつ高速です。

SELECT  場所コード, 品名コード, ロットＮＯ, 在庫数
      , sum(在庫数) over(partition by 品名コード) as 総在庫 
FROM  T_ZAIKO
ORDER BY 場所コード, 品名コード, ロットＮＯ

Comments

[saya24]

saziさん、早速のご返答ありがとうございます。
やはり　便利な機能があるのですね、聞いてよかったです。

質問の掲載用・主旨を単純に伝えるため　ごく簡単なSQL文にして　お問い合わせしましたが　手元の課題は　もう少し　難易度が高い発行文です。
確認・適用まで　少しお時間を頂戴致します。

まずは　お礼まで、本当に助かります！

[saya24]

saziさん、適用できました。気分最高！
今まで　同じ検索対象を並べることに、バカっぽいなぁ　とは思いつつも　目前の課題を急ぎ対応することに迫られ　最適策の追求を怠ってきた自分がいました。
今後は　こちらを活用していきます、ありがとうございました。

[sazi]

Window関数の使用できないバージョンのDBMSもありますから、Window関数を利用せずに実現できていた事は、理屈も理解できていますし、無駄ではありませんよ。