タグの登録数をカウントするSQLサンプル - ErogameScape -エロゲー批評空間- の備忘録

タグの登録数をカウントするSQLサンプル

概要

新機能としてタグ機能が追加されました。
タグは一言感想と長文感想内に埋め込まれています。この為、通常通り、「SELECT * FROM table」 といった文法では上手くデータを扱えません。
ここで威力を発揮するのが、正規表現 Wikipedia です。
ここでは、PostgreSQLのPOSIX正規表現を使用しますので、PostgreSQLリファレンスの 9.4. 文字列関数と演算子 9.7.3. POSIX正規表現 が参考になるでしょう。また、配列を扱うので 9.17. 配列関数と演算子 も参考にしてください。

サンプルSQL

やり方は多岐に渡って存在するでしょうが、今回は以下のようなサンプルを提示します。

SELECT REGEXP_REPLACE(tag, '^#{1}|#{2}.*$', '', 'g') AS tagname
     , COUNT(*) AS COUNT
  FROM (SELECT UNNEST(
                 REGEXP_SPLIT_TO_ARRAY(
                     COALESCE(' '||hitokoto||' ','')
                  || COALESCE(' '||REGEXP_REPLACE(memo, '\(\r\n|\r|\n\)', ' ', 'g')||' ','')
                   , ' +')
               ) AS tag
          FROM userreview
         WHERE hitokoto ~ ' #'
            OR memo ~ ' #'
       ) AS foo
 WHERE tag ~ '^#{1}'
 GROUP BY REGEXP_REPLACE(tag, '^#{1}|#{2}.*$', '', 'g')
 ORDER BY COUNT DESC
 

サンプルSQLの解説

まず、一言感想か長文感想にタグが埋め込まれている物を抽出します。

SELECT COALESCE(' '||hitokoto||' ','')
    || COALESCE(' '||REGEXP_REPLACE(memo, '\(\r\n|\r|\n\)', ' ', 'g')||' ','') AS tag
  FROM userreview
 WHERE hitokoto ~ ' #'
    OR memo ~ ' #'
 LIMIT 100
 

タグの有無は、' #'という文字の有無で判定しています。
一言感想と長文感想のどちらかがNULLの場合、連結結果もNULLになってしまうので、COALESCEを使用します。
本来なら不要ですが、タグの前後の半角スペースを入れ忘れている方も見受けられるので、' 'を前後に追加します。
長文感想の場合は、改行コードも半角スペースに置換えています。

次に、この結果からタグを抽出するわけですが、今回はREGEXP_SPLIT_TO_ARRAYとUNNESTを使用しました。
REGEXP_SPLIT_TO_ARRAYは、ある文字列から正規表現にマッチする単語を配列で返します。
UNNESTは、配列要素を複数の行に分割します。
簡単なサンプルとしては、以下のような使い方ができます。

SELECT UNNEST(REGEXP_SPLIT_TO_ARRAY('1a2a3a4','a'))
 

これを利用したのがコチラ。

SELECT UNNEST(
         REGEXP_SPLIT_TO_ARRAY(
             COALESCE(' '||hitokoto||' ','')
          || COALESCE(' '||REGEXP_REPLACE(memo, '\(\r\n|\r|\n\)', ' ', 'g')||' ','')
           , ' +')
       ) AS tag
  FROM userreview
 WHERE hitokoto ~ ' #'
    OR memo ~ ' #'
 LIMIT 100
 

ここまで来れば、あとの処理は簡単でしょう。
'#'一文字で始まる行をWHERE句で抽出（※１）し、冒頭の'#'と'##'以降のコメント部分を削除して（※２）、GROUP BY句で集約するだけです。
（※１）WHERE tag ~ '^#{1}'
（※２）GROUP BY REGEXP_REPLACE(tag, '^#{1}|#{2}.*$', '', 'g')

もちろん、以下のようにtaglistテーブルを使用して集約することも可能です。
単純に考えれば、以下のようなコードが思いつきます。ですが、userreviewテーブルの参照回数が膨大になり実行コストも肥大化するため、実用に耐えられるものではありません。

SELECT tl.name
     , COUNT(*) AS COUNT
  FROM userreview AS ur
     , taglist AS tl
 WHERE ur.hitokoto ~ (' #'||tl.name||' | #'||tl.name||'##')
    OR ur.memo ~ (' #'||tl.name||' | #'||tl.name||'##')
 GROUP BY tl.name
 

SELECT tl.name
     , (SELECT COUNT(*)
          FROM userreview AS ur
         WHERE ur.hitokoto ~ (' #'||tl.name||' | #'||tl.name||'##')
            OR ur.memo ~ (' #'||tl.name||' | #'||tl.name||'##')
         ) AS COUNT
  FROM taglist AS tl
 GROUP BY tl.name
 

【管理】POSTボタンを生成しました。