データ分析とインテリジェンス

フラグの作り方・考え方

フラグは「条件を満たすかどうか」をみる

会員や店舗などある属性であるかどうかを示すのにフラグが使われる。集計やBIでも多用するので作り方や考え方をまとめる。

フラグとは「条件を満たすかどうか」を見るものなのでcaseを使えば簡単に作れる。数値型で1/0で作る例は以下のようになる。空欄も異常値もNULLも「NO」にまとめられるのですっきりする。

もし未設定やNULLを理由があってそのまま残して起きたいのであれば元のカラムとフラグのカラムを両方使えばいい(が、まぎらわしいのであまりおすすめはしない)。

#caseでフラグを作る
select
case when 条件 then 1 #条件を満たした場合は1
else 0 #そうでなければ全部0
end

フラグのパターン

作り方にはいろいろなパターンがあり、筆者が見たことのあるものを以下にざっと挙げる。

Yes No データ型
1 0 数値
1 null 数値
1 0 文字列
1 null 文字列
Y N 文字列
T F 文字列
TRUE FALSE ブール

フラグは形式を揃える

テーブルによってデータ型や表記の形式が変わると混乱するので揃えた方がいい。

クエリをコピーしたらエラーになった。原因を探ったら元のクエリでは数値型だったフラグが、使おうとしたテーブルでは文字列型だった、なんてことは1度や2度ではない。

ひどい場合は同じテーブルの中に数値型と文字列型とブール型のカラムが混在していたりする。とにかく全部同じ形式にしよう。

おすすめは数値型で1/0でフラグを作る

揃えるなら「数値型で1/0」をお勧めしたい。「条件を満たしたら1、あとは全部0」だ。

数値型にしておくと以下の集計にすぐ使える。他のデータ型だと集計によっては都度case文を使って1/0に変換する必要がある。あとで変換を行うなら最初から1/0の数値型にしておけばいい。

  • 条件を満たすレコードは「フラグ=1を指定」
  • 条件を満たす件数を見たければ「SUM(フラグ)」
  • 少なくとも1つ条件をみたしているかは「MAX(フラグ)」
  • 全て条件を満たしているかは「MIN(フラグ)」
  • 比較したければ「集計キーに含める」
  • 複数条件は「フラグ同士を掛け算」

複数条件は「フラグ同士を掛け算」の説明

複数条件は「フラグ同士を掛け算」だけだと伝わらないかもしれないので軽く説明しておく。

IDごとに「1月購入フラグ(flag_jan)」「2月購入フラグ(flag_feb)」があったとしよう。では「1月と2月の両方で購入したIDの数」はどうするか。

where句で両方1を取ってレコードを絞り込むのでもできるし、flag_janとflag_febを集計キーに入れて各パターンで集計する方法もある。

もう1つ、フラグを全部掛け算すれば「全部1の場合は1、それ以外は0」を利用して集計がもできる。さらに「1-フラグ」で1と0が逆転することも使うといろいろなパターンが作れる。

#フラグの掛け算で集計
select
sum(flag_jan*flag_feb) #1月と2月の両方で購入
sum(flag_jan*(1-flag_feb)) #1月に購入あり、2月に購入無し
sum((1-flag_jan)*(1-flag_feb)) #1月と2月の両方で購入なし
from tbl
group by id

3つ以上のフラグでも同様。全パターンの組み合わせはフラグが多いとパターン数が膨大になってしまうがこの方法なら必要な集計だけができる。「1月購入あり+3月購入無し(2月はどっちでもいい)」とかも簡単に追加できる。

エンジニアからは不評だった

以前にエンジニアとも議論したことがあるが数値型で0/1にするのはどうもダメらしい。システムのためのDBと考え方が違うからなのだろうが、いろいろ言われても正直さっぱり理解できなかった。

DWHでフラグにした方が楽なのだが、エンジニアと折り合いがつかないならデータマートを作る際に数値型で0/1にするのがいいだろう。

追記:おそらくエンジニアと整備や利用の設計への考え方の違いが原因。なのでまずどういった認識をしているか合わせるところから始めよう。確かめずにお互いの考えだけぶつけ合っても良い結果にはつながらない。

いつものことながら。。。

普段当たり前に使っている話を書いただけなのにそこそこの量になった。いつどうやって身に着けたか覚えていない。まとめて教えてもらった記憶は無い。

なのでこれからの人には役立つかもしれない。が、これが一番良い方法なのかもよくわからないので鵜呑みはしないようにしてほしい。

 Twitter  

データ整備