質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.51%

  • SQL

    3010questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • Oracle Database 11g

    219questions

    Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。

集約関数と分析関数の併用はできる?

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 3,114

lolohacker

score 21

質問

集約関数と分析関数を使用した時に、集約関数で集約したい項目が複数のレコードに分かれてしまうことを解決したいです。

・集約関数を使用せず、分析関数と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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • lolohacker

    2017/08/14 23:53

    「・集約関数を使用せず、分析関数とDISTINCT句を使用」は集計関数を使用しないパターンで希望する出力結果を実現しました。集計関数と分析関数を使用したかたちで希望する出力結果を実現することは可能かどうかということなのですが上手く伝わりましたでしょうか?言葉足らずで申し訳ありません。。。

    キャンセル

  • sazi

    2017/08/15 00:44

    一応確認ですが、「ITEM_IDが同じならPARENT_NOは同じ」で合っていますか?

    キャンセル

  • lolohacker

    2017/08/15 01:04

    情報不足で申し訳ありません。ITEM_IDが同じでもPARENT_NOは異なる場合がある前提です。質問を書き直しました。

    キャンセル

回答 3

checkベストアンサー

+2

やりたいのはこういうことでしょうか

SELECT
    ITEM_ID
    ,SUM(QTY)
    ,SUM(SUM(QTY)) OVER (PARTITION BY PARENT_NO)
FROM
    TABLE_A
GROUP BY
    ITEM_ID
    ,PARENT_NO
;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/15 22:49

    成程、ネストさせれば良かったんですね。流石です。
    これが一番コスト低そうですね。

    キャンセル

  • 2017/08/17 23:34

    実際にこちらのSQLを参考にして実装させていただきました。
    ありがとうございます。

    キャンセル

0

集約関数と分析関数を併用して実現することは可能でしょうか?

⇒質問の中に「希望通りの出力結果が得られる」とあるので、
質問の内容がいまいち分かりませんが、記載されている通り可能です。

1つもSQLにもっと近い形(?)で
希望する出力結果にする場合、
group by の項目から「QTY」を外すと良いと思います。

(↑上記は質問文修正前の回答)

質問文修正後、追加

-- これはアリ?
SELECT    V1.ITEM_ID
    ,    SUM( V1.sum_item_qty )
    ,    MAX( V1.sum_parent_qty )
FROM    (
            SELECT    T1.ITEM_ID
                ,    SUM(T1.QTY)                                    as sum_item_qty
                ,    SUM(T1.QTY) OVER (PARTITION BY PARENT_NO)    as sum_parent_qty
            FROM    TABLE_A T1
            GROUP BY
                    T1.ITEM_ID
                ,    T1.QTY
                ,    T1.PARENT_NO
        ) V1
GROUP BY V1.ITEM_ID

-- ↓これでもOK?エラーかもですが。
SELECT    T1.ITEM_ID
    ,    SUM(T1.QTY)                                    as sum_item_qty
    ,    MAX( SUM(T1.QTY) OVER (PARTITION BY PARENT_NO) )    as sum_parent_qty
FROM    TABLE_A T1
GROUP BY    T1.ITEM_ID

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/14 20:30

    回答ありがとうございます。
    質問が漠然としていました。申し訳ありません。

    集約関数と分析関数を使用したSQLで上記の希望する出力結果を出力することは可能でしょうか?
    ということです(あまり変わってないかもしれませんが・・・)。

    GROUP BYから「QTY」を外すと、「ORA-00979: GROUP BYの式ではありません。」というエラーが出ます。

    キャンセル

  • 2017/08/14 22:10

    あ、ちょっと意味が分かった(気だけ)かもしれませんが、
    質問文のSQLと希望する出力結果は正しいですか??

    SQL上、「PARENT_NO毎にQTYが集約されてほしい」と記載がありますが、
    「希望する出力結果」にある6という数字はどこから来たのでしょう?

    とりあえず、質問の意図として、
    「異なる集約単位(group byしたい項目)で、
     集約関数と分析関数の併用は可能か」という意味に捉えましたが、
    だとしても可能なハズ。。。あれ?

    上記対応でなぜエラーが出るか原因不明ですが、
    別の方法を取ると、NOTHER_ACKさんの「併用」の条件として、
    副問い合わせをOKとするのであれば、
    1つ目のSQLを副問い合わせとして、
    その結果をさらに集約等する形であれば抽出はできると思います。
    (まぁ、distinctと大差ないですが)

    キャンセル

  • 2017/08/14 22:24

    ああああ 大変申し訳ございません!誤植です!
    PARENT_NO毎ですので「6」ではなく「16(5+8+3)」でした!

    今、手元にoracleの環境がないので試せませんが、今日試したときは
    エラーになりました。それでエラーにならないようにGROUP BYの条件を
    「ITEM_ID、QTY、PARENT_NO」にしたのですが、集約されず、
    上記の出力結果になるという経緯です。

    キャンセル

  • 2017/08/14 22:52

    ああああw (たぶん、ほぼ)理解しました!
    改めて回答すると、副問い合わせ等を使えば、できます。

    より正確な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例を回答に追加)

    キャンセル

  • 2017/08/14 23:32 編集

    ご指摘ありがとうございます。
    以後、気を付けます。

    SQL拝見しました。
    なるほどです。明日試してみたいと思います。

    かなり質問の趣旨とはずれてしまい恐縮なのですが、もう一点意見をお聞かせください。
    問い合わせた結果が何万件単位になる場合はやはり、分析関数を使用して

    SELECT DISTINCT
    ITEM_ID
    , SUM(QTY) OVER (PARTITION BY ITEM_ID)
    , SUM(QTY) OVER (PARTITION BY PARENT_NO)
    FROM
    TABLE_A

    とした方がパフォーマンスがよくなるでしょうか?

    キャンセル

0

質問されている、希望する出力結果のうちPARENT_NO=6という値が、TABLE_Aのどこをどうしたら出力されるのかよく分かりません。

(PARENT_NO)ごとのQTYの計と(ITEM_ID,PARENT_NO)ごとのQTYの計を一覧にしたものとして回答すると以下の様になるかと。

with iv_sub as (
  select ITEM_ID,PARENT_NO, sum(QTY) as subtotal 
  from TABLE_A
  group by ITEM_ID,PARENT_NO
)
select *
     ,(select sum(subtotal) from iv_sub where PARENT_NO=sub.PARENT_NO) as total
from iv_sub as sub

質問修正後追加その1

TABLE_Aの行毎に、その行のPARENT_NOと同じデータの集計をサブクエリーで取り、そのサブクエリーをITEM_IDとPARENT_NOの集計でグルーピングし、QTYの集計を取る。

select ITEM_ID
     , sum(QTY) as item_total
     , parent_total
from (
  select ITEM_ID, QTY
       , (select sum(QTY) from TABLE_A where PARENT_NO=item.PARENT_NO) as parent_total 
  from TABLE_A as item
) as caluc
group by ITEM_ID, parent_total

質問修正後追加その2

修正後の結果であれば、最初に書いたSQLでも良さそう。
分析関数を使うとしたら、

select ITEM_ID, PARENT_NO, subtotal
     , sum(subtotal) over(partition by PARENT_NO) as total
from (
  select ITEM_ID, PARENT_NO, sum(QTY) as subtotal 
  from TABLE_A
  group by ITEM_ID,PARENT_NO
) as sub


になるかな。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/14 22:43

    回答ありがとうございます。
    上記のSQLも参考にさせていただきます。
    誤植修正しましたので、確認していただけると助かります。

    キャンセル

  • 2017/08/14 22:49

    上記は質問を修正される前の回答です。
    まだ希望する形が不明ですので質問本文に依頼コメントしています。

    キャンセル

  • 2017/08/15 13:45

    PARENT_NOとITEM_IDが逆になっていたので修正

    キャンセル

  • 2017/08/15 15:13

    質問修正後追加その2←拝見しました。
    確かにこれなら私の希望する結果が得られます。
    ありがとうございます。

    あともう一つ質問してもよろしいでしょうか?
    (メインの質問の内容とはかなりずれてしまい恐縮ですが)

    TABLE_Aのレコードが何万件単位の場合に希望する出力結果を得るにはどのSQLを使用したら一番処理速度が速いでしょうか?データの内容によりけりで一概にこれだとは言えないかもしれませんが、ご意見聞かせていただけると嬉しいです。

    キャンセル

  • 2017/08/15 19:06 編集

    データ分布やインデックスの状況によって変化するのでどちらとは言えません。
    実行計画で確認して、可能ならチューニングを行った方が良いかと思います。
    コストについては、「分析関数を使用しているSQL」<「(最初の)with式を使用しているSQL」じゃないかと(感覚的ですが)思います。

    キャンセル

  • 2017/08/17 23:33

    ありがとうございます。参考になりました。
    実行計画で確認してみたいと思います。

    キャンセル

同じタグがついた質問を見る

  • SQL

    3010questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • Oracle Database 11g

    219questions

    Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。