【SQL】サブクエリにて抽出した複数条件による集計

Question

###前提・実現したいこと
Oracle 11gの環境にてSQLについての質問です
サブクエリにて抽出した検索キーを使い
それぞれのキーで集計を行いたいと考えています。

下記以外の方法はありますか？

###発生している問題・エラーメッセージ
サブクエリにて帰ってくる値が複数件になるため
where句に直接指定することができません。

###該当のソースコード

SELECT
 SUM(COST)
FROM
  T1
WHERE
  CODE LIKE (
   SELECT
    REGEXP_REPLACE(CODE,'9.*'.'%')
   FROM
    T2
   WHERE
    CODE LIKE '%9'
  )

TABLE

T1
CODE COST
11111100 10
11111200 20
11111300 30
11111410 40
11111420 50
11111510 60
11111520 70
11111600 80
11111700 90
11111800 100

T2
CODE
11111100
11111200
11111300
11111410
11111420
11111499
11111510
11111520
11111599
11111600
11111700
11111800
11111999

サブクエリの結果
KEY
111114%
111115%
11111%

###取得したいデータ
90
130
550

###試したこと
CASE式を使用しようと考えましたが
集計キーが動的に変化するため有効でないと判断しました。

Comments

[takasima20]

少し具体的な例を出してもらえると回答がされやすいと思います。

Answer 1

JOIN句でLIKE自体の指定は出来た気がする。

SELECT
    T1.*
FROM
    T1 
    INNER JOIN ( 
        SELECT
            REGEXP_REPLACE(CODE, '9.*'.'%') AS SEARCH_KEY
        FROM
            T2 
        WHERE
            CODE LIKE '%9'
    ) TMP
    ON T1.CODE LIKE TMP.SEARCH_KEY

でもこれだけでは集約はうまくいかないはずなので、
以下のような感じで集約キー自体は疑似的に作成したら何とかなるかもしれません。
※現在手元に実行環境がないので、ミスってて動作しなかったらすみません^^;

SELECT
    MAX(TMP.SEARCH_KEY) AS SEARCH_KEY
,   SUM(T1.COST) AS TOTAL_COST
FROM
    T1 
    INNER JOIN ( 
        SELECT
            REGEXP_REPLACE(CODE, '9.*'.'%') AS SEARCH_KEY
        ,   ROW_NUMBER() OVER (ORDER BY CODE) AS GRP_ID
        FROM
            T2 
        WHERE
            CODE LIKE '%9'
    ) TMP
    ON T1.CODE LIKE TMP.SEARCH_KEY
GROUP BY
    TMP.GRP_ID

上記よりも下の方が安全そうな気がするので更に投下してみる

SELECT
    MAX(TMP.SEARCH_KEY) AS SEARCH_KEY
,   SUM(T1.COST) AS TOTAL_COST
FROM
    T1 
    INNER JOIN ( 
        SELECT
            T.SEARCH_KEY
        ,   ROW_NUMBER() OVER (ORDER BY T.SEARCH_KEY) AS GRP_ID 
        FROM
            ( 
                SELECT
                    DISTINCT REGEXP_REPLACE(CODE, '9.*'.'%') AS SEARCH_KEY 
                FROM
                    T2 
                WHERE
                    CODE LIKE '%9'
            ) T
    ) TMP
    ON T1.CODE LIKE TMP.SEARCH_KEY
GROUP BY
    TMP.GRP_ID

Comments

[TomoyaHirayama]

回答ありがとうございます。
参考になりました。動作検証します。

Answer 2

ごめんなさいOracleの環境がないので一般的な話になりますが
別のやり方という意味では

T2から取り出すデータを
^111114
^111115
^11111
としてREGEXPでリレーションするという考え方もあります
もちろん、こちらのほうが効率がわるいですが、
もうすこし複雑な条件が与えられるので状況にあわせて検討ください

いずれにしろ
90
130
550
だけ抽出したのでは集計単位がわからないですよね
WHERE句でサブクエリをリレーションするのではなく
FROM句で併記するかJOINするかしてサブクエリのCODEを利用した
GROUP BY をしたほうが良いと思いますが・・・

Comments

[TomoyaHirayama]

回答ありがとうございます。
参考になりました。

Answer 3

たんなる思いつきですが、こんな感じにしたら視点変わりますかね。

SELECT
 SUM(COST)
FROM
  T1, (
   SELECT
    REGEXP_REPLACE(CODE,'9.*'.'%')
   FROM
    T2
   WHERE
    CODE LIKE '%9'
  ) AS CODE_TABLE
WHERE
  CODE LIKE (CODE_TABLEをつかった条件？）

Answer 4

SQLだけだと厳しいかなあ。
たぶんアプリケーション側でT2を元にSQL作ってT1を集計
って形が素直な発想だと思います。
それがだめならストアドプロシージャを使って
T2をループしながらT1の合計を求める形か。

ちなみに、三つ目のKEYは他の二つを包含しているようですが、
そういうパターンもあるってことですかね?

Comments

[TomoyaHirayama]

回答ありがとうございます

>ちなみに、三つ目のKEYは他の二つを包含しているようですが、 そういうパターンもあるってことですかね?
T2.CODEの末尾９のもので集計したいと考えているので
内包するパターンも出てきます。

Answer 5

likeではなくinを使えばいいのでは？

Comments

[TomoyaHirayama]

回答ありがとうございます
言葉足らずでしたが、サブクエリにて抽出した検索キーは
前方一致にて集計したいと考えておりますので
今回はLIKEにて条件を指定致しました。