Oracle、典型的なSQLタスク。 列にリストされた値のテーブル行への変換

ハリウッドへようこそ。 今日のヒロインを紹介します







最初に、これらの美しさの夫に誰がいたかを見つけます。 そして、単純な特殊効果の助けを借りて、彼らが互いに結婚した順序であなたを紹介します。 若いバージンにとって、この記事は特に興味深いものになります。



ベーステーブルを作成して入力する

create table hollywood as with t (id, actress, husbands) as ( select 1, ' ', '  ,   ,  ' from dual union all select 2, ' ', null from dual union all select 3, ' ', ' ' from dual ) select * from t; alter table hollywood add primary key (id);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ID	女優	夫
1	アンジェリーナ・ジョリー	ジョニー・リー・ミラー、ビリー・ボブ・ソーントン、ブラッド・ピット
2	シャーリーズ・セロン
3	ペネロペクルス	ハビエル・バルデム

この表は、アンジェリカが3回結婚したことを示しています。 彼女の夫は、女優との結婚順にセパレーターを介して列にリストされています。 セパレータがコンマであり、その後のスペースが単なるゴミであることに同意しましょう。



リサ・セロンはまったく結婚したことがなく（市民結婚はカウントされません）、どうやら彼女はまだIT担当者を待っています。 だから、それは注意すべきであり、急いでする方法-女性はすでに、ほぼ40です。



まあ、ペネロペクルス-一度だけ結婚しました。 なんてつまらない。



しかし、これはすべて前奏曲ですが、実際には次の結果を得る必要があります

女優	夫	番号夫P / P
アンジェリーナ・ジョリー	ジョニー・リー・ミラー	1
アンジェリーナ・ジョリー	ビリー・ボブ・ソーントン	2
アンジェリーナ・ジョリー	ブラッド・ピット	3
シャーリーズ・セロン
ペネロペクルス	ハビエル・バルデム	1

基本的に、listagg関数を使用してグループ化と集計の反対を実行する必要があります。



単純から複雑に移行します。 そもそも、同様の問題を検討することを提案します。つまり、テーブルセットのセパレータを使用して、単一の行から数値を抽出します。

 with digit_str as ( select '10,20,30,40,50,100' as str from dual ) select regexp_substr(str, '(\d+)(,|$)', 1, rownum, 'c', 1) ok from digit_str connect by level <= regexp_count(str, '\d+(,|$)')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

取得するもの：

わかった
10
20
30
40
50
100

クエリを理解するには、正規表現関数の仕組みを知る必要があります（ help ）。 リクエストのアイデアは次のとおりです。

• レベルごとの接続を使用して、新しい行が生成されます。
• regexp_countを使用して、区切り文字間の文字列内の数値の数が計算されます-この数値は、文字列ジェネレーターの上限を定義します。
• Regexp_substrは、文字列から数値を抽出します。 行内のパターンの出現回数（レギュラーシーズンの4番目の引数）は、疑似列rownumの値-生成された行の添え字の数に対応します。 rownumの代わりに、levelを使用できます。結果は同様になります。

この問題を解決する方法は他にもあります。たとえば、次のとおりです。

 select to_number(column_value) from xmltable('10,20,30,40,50,100')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

また、正規表現の代わりに、標準の文字列関数の組み合わせを使用することもできますが、それでも正規表現を使用したクエリについて説明します。



HOLLYWOODテーブルでは、一連の数字ではなく、高貴な夫の名前を扱います。 ただし、上記の方法に従って、regexp_count関数を使用してカウントし、regexp_substr関数を使用して取得することもできます。 次に、 バナナをピクルスにするためのレシピを思い出して、将来の数がわかっているときに文字列を生成する方法のいずれかを選択する必要があります。 例として、3番目と5番目の方法を使用します。 ただし、最適な生成方法を選択するときは、ユーザーのコメント xtenderに注意する必要があります。



アプローチを組み合わせると、次のようになります。



特殊効果番号1。

 select actress, trim(regexp_substr(husbands, '(.+?)(,|$)', 1, level, 'c', 1)) as husband, nvl2(husbands, level, null) as husb_no from hollywood connect by prior id = id and prior dbms_random.value is not null and level <= regexp_count(husbands, '.+?(,|$)') order by id, 3
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

すべては大丈夫です-女の子は幸せに結婚しています。 リサ・セロンを除く全員。 リサのような人のために、リクエストでnvl2を使用しました。



特殊効果番号2。

 select h.actress, trim(regexp_substr(h.husbands, '(.+?)(,|$)', 1, x.column_value, 'c', 1)) as husband, nvl2(h.husbands, x.column_value, null) as husb_no from hollywood h, table(cast(multiset(select level from dual connect by level <= regexp_count(h.husbands, '.+?(,|$)')) as sys.odcinumberlist)) x
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

それはコレクションを通しての決定でした。



UPD： Oracle 12cのもう1つの素晴らしい特殊効果＃3が描かれました

 select h.actress, trim(regexp_substr(h.husbands, '(.+?)(,|$)', 1, xn, 'c', 1)) as husband, nvl2(h.husbands, xn, null) as husb_no from hollywood h, lateral(select level n from dual connect by level <= regexp_count(h.husbands, '.+?(,|$)')) x
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

明らかに、問題を解決するための多くのオプションがあります。 1つまたは別のオプションの選択は、好みとパフォーマンスの問題です。 私がオスカーを持っていたら、コードの面で最も簡潔で、パフォーマンスの面で最も最適なものを提供する人にそれを渡すことをheしなかったでしょう。



それまでの間、すべて。 またね