SQLだけでは無理だと思っていたMySQLのSELECTで使えるテクニック

UNIX_TIMESTAMP/NOWで時間比較を操る

大抵の場合、日時を扱うレコードはDATETIME型のフィールドにデータを格納しておくものだが、複雑な絞り込みや算出をする場合はUNIX_TIMESTANP関数を使うと便利だ。

reg_timeという登録日時を格納するDATETIME型のuserテーブルがあったとしよう。

    SELECT * 
    FROM user 
    WHERE UNIX_TIMESTAMP(reg_time) >= (UNIX_TIMESTAMP(NOW()) - 600)

タイムスタンプに変換した後は秒による比較が可能になるので、このようにすると現在時刻から5分以内に登録されたユーザーを取得できる。
もちろんMySQLスタイルのDATETIME文字列(2018-01-18 10:09:57 等)をアプリケーション側で用意してSQLに組み込んでもいいが、そのような文字列を柔軟に用意するのが面倒なケースではこのようなDBだけで完結する方法を用いるのもアリだろう。

さらに、このuserテーブルに最終ログイン時刻を格納するlogin_timeというDATETIME型のフィールドがあった場合は、

    SELECT UNIX_TIMESTAMP(login_time) - UNIX_TIMESTAMP(reg_time) AS time 
    FROM user

とするとユーザーのサービス利用時間が秒で取得できる。

CASE/THEN/ELSE/ENDでデフォルト値を設定したり、分類を大雑把にする

一般的なプログラミング言語とは多少使いどころが違うが、SQLに用意されている条件分岐もうまく使えば有用なケースがある。

画像パスをpathというVARCHAR型のフィールドに保存しているmediaというテーブルがあったとしよう。
さらに、サムネイルとしてthumbにmediaテーブルのid(オートインクリメント設定のPRIMARYフィールド想定)を格納しているpostテーブルを考える。

    SELECT id, path, 
    FROM post 
    LEFT JOIN media 
    ON post.thumb = media.id

とすればサムネイルとして画像パスを取得できるが、mediaテーブルの該当レコードが削除されていたり、post.thumbかmedia.idのどちらかがNULLを許容していた場合はpathにNULLが返ってきてしまう。

大抵はアプリケーション側のコードで対応可能かとは思われるが、場合によっては必ずpathに画像パスが入っていて欲しいこともあるだろう。

そんなときに、SQL側でNULLをデフォルトの画像パスに置き換える例が以下だ。

    SELECT 
    id, 
    CASE WHEN post.thumb IS NULL 
      THEN 'http://noji.dev/hoge/fuga/piyo.jpg' 
      ELSE post.thumb END AS path 
    FROM post 
    LEFT JOIN media 
    ON post.thumb = media.id

こうしておけばpathがNUNLだった場合はデフォルトの画像パスとしてhttp://noji.dev/hoge/fuga/piyo.jpgが返るようになる。

さらに別のケースを考えてみよう。都道府県のデータとして名前をnameというVARCHAR型のフィールド、北海道 = 1,関東 = 4といった分類をareaというINT型のフィールドに格納しているprefectureというテーブルがあったとする。

仮にareaおける北海道が1、東北が2、北陸が3、関東が4だったとすると、

    SELECT name
    FROM prefecture 
    WHERE area > 5 
    ORDER BY area DESC

とすれば4つのエリアに属する都道府県のみがareaによってソートされて出力される。一覧画面などではこのSQLのDESC部分をASCとトグルさせる機能を作るケースも多いだろう。

さて、追加機能として「東北と北陸とそれ以外」でソートする機能を作る場合はどうだろうか。
一見、北海道と関東は1と4で数値が離れてしまっているため、SQLの力では簡単にソートできないように思われる。

    SELECT 
    name, 
    CASE area 
      WHEN 2 THEN 2 
      WHEN 3 THEN 3 
      ELSE 0 END AS area 
    FROM prefecture 
    WHERE area > 5 
    ORDER BY area DESC

しかし、このようにareaが2か3以外のレコードを全て0扱いにしてしまえばアプリケーション側で並び替えせずともDB側で並び替えが完結する。

INNER JOIN/CEIL/RAND/MAXでランダムなレコードを取得

レコード数が少ない場合はそこまで苦労しないが、巨大なデータを抱えるテーブルからランダムなレコードを取得するという処理は意外と時間がかかる。
だが、以下のSQLを使えば比較的低負荷でランダムなレコードを取得できるだろう。

オートインクリメント設定のPRIMARY KEYとしてidフィールドを持つproductテーブルを例に考えてみる。

    SELECT product.* 
    FROM product 
    INNER JOIN ( 
      SELECT CEIL(RAND() * (SELECT MAX(id) FROM product)) AS rand_id 
    ) AS tmp 
    ON product.id = tmp.rand_id

RAND関数は0から1の間からランダムな浮動小数点値を返すので、それをproductテーブル内で最大のidに掛けてCEIL関数で切り上げてやればランダムなIDが取得できる。
それら処理で取得したランダムなIDで内部結合してやればランダムなレコードが一件取得できるのだ。

なお、これはidに歯抜けが無いテーブルでのみ使えるテクニックだ。物理削除される可能性があるテーブルでは使えないので注意。

GROUP BY/HAVING/COUNTで従属するレコードを○個持っているレコードに限定する

従属テーブルを含めた絞り込みが単発のテーブルの絞り込みより面倒なのは周知の事実だが、中々に面倒なのが「直属の部下を3人持つ社員」といったような絞り込み条件だ。

一筋縄ではいかなそうな絞り込み条件だが、一般的なWHERE句ではなくHAVING句を使用することでこの要件は達成できる。

例として、社員番号としてオートインクリメント設定のPRIMARY KEYであるidフィールド、上司のidを格納するbossフィールドを持つemployeeテーブルを考えてみよう。

    SELECT employee.* 
    FROM employee 
    LEFT JOIN employee AS subordinate 
    ON employee.id = subordinate.boss 
    GROUP BY employee.id 
    HAVING COUNT(subordinate.id) = 3

このようにすることで直属の部下をちょうど3人持つ社員のみを取得できる。
HAVING COUNT(subordinate.id) = 3の=を不等号に変えてやれば範囲指定も可能だし、勿論COUNTだけでなくSUMやMAX、MIN等も使用できるので従属テーブルの複雑な絞り込みを行うときはHAVINGの存在を思い出して欲しい。

まとめ

サーバーサイドのプログラミングを学んだ当初はSQLでできることの知識が少なく、結合すらも知らなかった頃はPHPの力でゴリゴリのループを回していたこともあったが、勉強すればするほどSQLはパワフルな言語だと気づかされるし、魅力的だと感じるものだ。

ただ、アプリケーション側での処理に任せた方が良いケースも沢山あるのも最近学んだことの一つである。

速さは正義であり、良いサービスの第一条件であるわけだが、それによって複雑怪奇なSQLを書くと後で修正できなくなるなんていうのは恐ろしいことだ。

今後のエンジニア人生、速度と修正コスト、実装の手間や実行環境の互換性等、色んな要素のバランス感覚を身に着けたいと思う今日この頃である。