GROUP BYでの重複削除において、複数の条件を優先順位付で設定する

この記事はMicroAd Advent Calendar 2019の3日目の記事です。

はじめに

あるキーに対してレコードが重複する場合に、重複を削除したいことがあります。

よく使われる方法にGROUP BYで絞り込む方法や、WHERE句に一意になるような条件を記述する方法などがあるかと思います。

今回、少し工夫が必要な場面に遭遇したので、その解決策を紹介できればと思います。

状況

重複レコードに対して、以下のような優先順位でレコードを1つに絞り込みたい。なお、条件Cまでを判定すれば必ずレコードが一意に定まるものとします。

1. 条件Aを満たす
2. （条件Aでは1つに絞り込めない場合）条件Bを満たす
3. （条件A・条件Bでは1つに絞り込めない場合）条件Cを満たす

「レコードを絞り込むための条件に優先順位があり、それぞれをその順番通りに処理したい」という状況です。

面倒な点としては、「1つに絞り込めない」パターンとして「その条件を満たすものが複数存在する」場合と「その条件を満たすものが一つもない」場合の両方があり、WHEREやHAVINGでは条件を表現しづらいです。

重複行をまとめるためのGROUP BYをうまく使いたいですが、id >= hogeのような条件を直接指定することはできません。

解決策

条件を満たしているかどうかを0または1の値で表現し、複数の条件を優先順に文字列連結して表現します。
これをカラムに設定し、次のようにGROUP BYします。

SELECT
  key
  ,MAX(priority)
FROM (
  SELECT
    key
    ,CONCAT(
      -- 条件を満たしていれば1, 満たしていなければ0
      IF(condition_A, '1', '0'),
      IF(condition_B, '1', '0'),
      IF(condition_C, '1', '0')
    ) AS priority
  FROM
    xxx
) t
GROUP BY key

条件を0と1で表現したものを文字列連結にしてMAXを取ることで、存在する中で最も優先度の高いパターンのものが選択されます。

条件をカラムに落とし込むことでGROUP BY時に条件を適用させることが可能で、IFの部分に記述できる条件であれば何でも適用可能です。また、例えば「xの値が最大」という条件であればIFを使わず直接xの値を文字列連結するなど、応用も可能かと思います。

おわりに

GROUP BYでの重複削除において、複数の条件を優先順位付で設定する方法を紹介しました。

参考になれば幸いです。