Q&A
「・TABLE_A1, TABLE_A2等に分けてそれぞれ集計した結果を結合」とありますが、結合する条件は何ですか？また、これは別々のSQLで行った結果をマージしたという意味で良いですか？
###質問
集約関数と分析関数を使用した時に、集約関数で集約したい項目が複数のレコードに分かれてしまうことを解決したいです。
・集約関数を使用せず、分析関数とDISTINCT句を使用
・TABLE_A1, TABLE_A2等に分けてそれぞれ集計した結果を結合
↑の二つは試しました。
集約関数と分析関数を併用して実現することは可能でしょうか？
(不可能であれば不可能という回答をいただきたいと思っています。)
ご教示よろしくお願い致します。
###ソースコードとテーブル ※抜粋
SELECT ITEM_ID , SUM(QTY) ← ITEM_ID毎にQTYが集約されてほしい , SUM(QTY) OVER (PARTITION BY PARENT_NO) ← PARENT_NO毎にQTYが集約されてほしい FROM TABLE_A GROUP BY ITEM_ID , QTY , PARENT_NO
TABLE_A
| ITEM_ID | QTY | PARENT_NO |
|---|---|---|
| ITEM1 | 5 | 100 |
| ITEM1 | 8 | 100 |
| ITEM3 | 3 | 100 |
| ITEM1 | 2 | 200 |
| ITEM1 | 7 | 200 |
| ITEM2 | 1 | 200 |
| ###出力結果 | ||
| ITEM_ID | SUM(QTY) | SUM(QTY) OVER (PARTITION BY PARENT_NO) |
| :-- | --: | --: |
| ITEM1 | 5 | 16 |
| ITEM1 | 8 | 16 |
| ITEM3 | 3 | 16 |
| ITEM1 | 2 | 10 |
| ITEM1 | 7 | 10 |
| ITEM2 | 1 | 10 |
| ###希望する出力結果 | ||
| ITEM_ID | ITEM_ID毎のQTY | PARENT_NO毎のQTY |
| :-- | --: | --: |
| ITEM1 | 13 | 16 |
| ITEM3 | 3 | 16 |
| ITEM1 | 9 | 10 |
| ITEM2 | 1 | 10 |
ITEM_ID=1 の重複が取り除かれた状態
###試したこと
・集約関数を使用せず、分析関数とDISTINCT句を使用
SELECT DISTINCT ITEM_ID , SUM(QTY) OVER (PARTITION BY ITEM_ID) , SUM(QTY) OVER (PARTITION BY PARENT_NO) FROM TABLE_A
↑にすることで希望する出力結果が得られることは確認済
・TABLE_A1, TABLE_A2等に分けてそれぞれ集計した結果を結合
SELECT A1.ITEM_ID ,A1.ITEM_ID毎のQTY ,A2.PARENT_NO毎のQTY FROM ( SELECT ITEM_ID ,PARENT_NO ,SUM(QTY) AS ITEM_ID毎のQTY FROM TABLE_A GROUP_BY ITEM_ID ,PARENT_NO )A1 ,( SELECT PARENT_NO ,SUM(QTY) AS PARENT_NO毎のQTY FROM TABLE_A GROUP_BY PARENT_NO )A2 WHERE A1.PARENT_NO = A2.PARENT_NO
###補足情報
・DBのバージョン：Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
質問にSQL追記しました。そうですね、追記した内容でいうとPARENT_NOで結合しました。
「・集約関数を使用せず、分析関数とDISTINCT句を使用」で希望する結果が得られたとありますが、では質問する意図は何？ってことになります。多分たまたまPARTITION BY PARENT_NOの方の数値が異なっていてDISTINCTにより行として生成されただけのような気がします。
「・集約関数を使用せず、分析関数とDISTINCT句を使用」は集計関数を使用しないパターンで希望する出力結果を実現しました。集計関数と分析関数を使用したかたちで希望する出力結果を実現することは可能かどうかということなのですが上手く伝わりましたでしょうか？言葉足らずで申し訳ありません。。。
一応確認ですが、「ITEM_IDが同じならPARENT_NOは同じ」で合っていますか？
情報不足で申し訳ありません。ITEM_IDが同じでもPARENT_NOは異なる場合がある前提です。質問を書き直しました。
回答3件
0
ベストアンサー
やりたいのはこういうことでしょうか
sql
1SELECT 2 ITEM_ID 3 ,SUM(QTY) 4 ,SUM(SUM(QTY)) OVER (PARTITION BY PARENT_NO) 5FROM 6 TABLE_A 7GROUP BY 8 ITEM_ID 9 ,PARENT_NO 10;
投稿2017/08/15 13:28
総合スコア1149
成程、ネストさせれば良かったんですね。流石です。
これが一番コスト低そうですね。
実際にこちらのSQLを参考にして実装させていただきました。
ありがとうございます。
0
質問されている、希望する出力結果のうちPARENT_NO=6という値が、TABLE_Aのどこをどうしたら出力されるのかよく分かりません。
(PARENT_NO)ごとのQTYの計と(ITEM_ID,PARENT_NO)ごとのQTYの計を一覧にしたものとして回答すると以下の様になるかと。
SQL
1with iv_sub as ( 2 select ITEM_ID,PARENT_NO, sum(QTY) as subtotal 3 from TABLE_A 4 group by ITEM_ID,PARENT_NO 5) 6select * 7 ,(select sum(subtotal) from iv_sub where PARENT_NO=sub.PARENT_NO) as total 8from iv_sub as sub
質問修正後追加その１
TABLE_Aの行毎に、その行のPARENT_NOと同じデータの集計をサブクエリーで取り、そのサブクエリーをITEM_IDとPARENT_NOの集計でグルーピングし、QTYの集計を取る。
SQL
1select ITEM_ID 2 , sum(QTY) as item_total 3 , parent_total 4from ( 5 select ITEM_ID, QTY 6 , (select sum(QTY) from TABLE_A where PARENT_NO=item.PARENT_NO) as parent_total 7 from TABLE_A as item 8) as caluc 9group by ITEM_ID, parent_total
質問修正後追加その２
修正後の結果であれば、最初に書いたSQLでも良さそう。
分析関数を使うとしたら、
SQL
1select ITEM_ID, PARENT_NO, subtotal 2 , sum(subtotal) over(partition by PARENT_NO) as total 3from ( 4 select ITEM_ID, PARENT_NO, sum(QTY) as subtotal 5 from TABLE_A 6 group by ITEM_ID,PARENT_NO 7) as sub
になるかな。
投稿2017/08/14 13:35
編集2017/08/15 04:55総合スコア25184
回答ありがとうございます。
上記のSQLも参考にさせていただきます。
誤植修正しましたので、確認していただけると助かります。
上記は質問を修正される前の回答です。
まだ希望する形が不明ですので質問本文に依頼コメントしています。
PARENT_NOとITEM＿IDが逆になっていたので修正
質問修正後追加その２←拝見しました。
確かにこれなら私の希望する結果が得られます。
ありがとうございます。
あともう一つ質問してもよろしいでしょうか？
(メインの質問の内容とはかなりずれてしまい恐縮ですが)
TABLE_Aのレコードが何万件単位の場合に希望する出力結果を得るにはどのSQLを使用したら一番処理速度が速いでしょうか？データの内容によりけりで一概にこれだとは言えないかもしれませんが、ご意見聞かせていただけると嬉しいです。
データ分布やインデックスの状況によって変化するのでどちらとは言えません。
実行計画で確認して、可能ならチューニングを行った方が良いかと思います。
コストについては、「分析関数を使用しているSQL」＜「（最初の）with式を使用しているSQL」じゃないかと（感覚的ですが）思います。
ありがとうございます。参考になりました。
実行計画で確認してみたいと思います。
0
集約関数と分析関数を併用して実現することは可能でしょうか？
⇒質問の中に「希望通りの出力結果が得られる」とあるので、
質問の内容がいまいち分かりませんが、記載されている通り可能です。
1つもSQLにもっと近い形（？）で
希望する出力結果にする場合、
group by の項目から「QTY」を外すと良いと思います。
（↑上記は質問文修正前の回答）
質問文修正後、追加
SQL
1-- これはアリ？ 2SELECT V1.ITEM_ID 3 , SUM( V1.sum_item_qty ) 4 , MAX( V1.sum_parent_qty ) 5FROM ( 6 SELECT T1.ITEM_ID 7 , SUM(T1.QTY) as sum_item_qty 8 , SUM(T1.QTY) OVER (PARTITION BY PARENT_NO) as sum_parent_qty 9 FROM TABLE_A T1 10 GROUP BY 11 T1.ITEM_ID 12 , T1.QTY 13 , T1.PARENT_NO 14 ) V1 15GROUP BY V1.ITEM_ID 16 17-- ↓これでもOK?エラーかもですが。 18SELECT T1.ITEM_ID 19 , SUM(T1.QTY) as sum_item_qty 20 , MAX( SUM(T1.QTY) OVER (PARTITION BY PARENT_NO) ) as sum_parent_qty 21FROM TABLE_A T1 22GROUP BY T1.ITEM_ID
投稿2017/08/14 09:27
編集2017/08/14 13:53
総合スコア760
回答ありがとうございます。
質問が漠然としていました。申し訳ありません。
集約関数と分析関数を使用したSQLで上記の希望する出力結果を出力することは可能でしょうか？
ということです(あまり変わってないかもしれませんが・・・)。
GROUP BYから「QTY」を外すと、「ORA-00979: GROUP BYの式ではありません。」というエラーが出ます。
あ、ちょっと意味が分かった（気だけ）かもしれませんが、
質問文のSQLと希望する出力結果は正しいですか？？
SQL上、「PARENT_NO毎にQTYが集約されてほしい」と記載がありますが、
「希望する出力結果」にある６という数字はどこから来たのでしょう？
とりあえず、質問の意図として、
「異なる集約単位（group byしたい項目）で、
集約関数と分析関数の併用は可能か」という意味に捉えましたが、
だとしても可能なハズ。。。あれ？
上記対応でなぜエラーが出るか原因不明ですが、
別の方法を取ると、NOTHER_ACKさんの「併用」の条件として、
副問い合わせをOKとするのであれば、
１つ目のSQLを副問い合わせとして、
その結果をさらに集約等する形であれば抽出はできると思います。
（まぁ、distinctと大差ないですが）
ああああ 大変申し訳ございません！誤植です！
PARENT_NO毎ですので「6」ではなく「16(5+8+3)」でした！
今、手元にoracleの環境がないので試せませんが、今日試したときは
エラーになりました。それでエラーにならないようにGROUP BYの条件を
「ITEM_ID、QTY、PARENT_NO」にしたのですが、集約されず、
上記の出力結果になるという経緯です。
ああああｗ （たぶん、ほぼ）理解しました！
改めて回答すると、副問い合わせ等を使えば、できます。
より正確なSQLを求める場合、
TABLE_A に同一ITEM_IDで異なるPARENT_IDがあった場合、
どういう結果が良いか、記載しておいた方が良いですね。
（例）TABLE_Aに以下のデータがあった場合
ITEM_ID:2 QTY:2 PARENT_NO:200
ITEM_ID:2 QTY:4 PARENT_NO:300
また、副問い合わせも使うのがNGの場合、併用は難しい（現時点では思いつかない）です。
なので、今回のケースでは、
・TABLE_A1, TABLE_A2等に分けてそれぞれ集計した結果を結合」
がシンプルで良いかもです。
（SQL例を回答に追加）
ご指摘ありがとうございます。
以後、気を付けます。
SQL拝見しました。
なるほどです。明日試してみたいと思います。
かなり質問の趣旨とはずれてしまい恐縮なのですが、もう一点意見をお聞かせください。
問い合わせた結果が何万件単位になる場合はやはり、分析関数を使用して
SELECT DISTINCT
ITEM_ID
, SUM(QTY) OVER (PARTITION BY ITEM_ID)
, SUM(QTY) OVER (PARTITION BY PARENT_NO)
FROM
TABLE_A
とした方がパフォーマンスがよくなるでしょうか？
あなたの回答
tips
プレビュー
