ウィンドウ関数で作るデータプロダクト

検索編成部の兼山(@PENGUINANA_)です。

クックパッドではウィンドウ関数をデータプロダクトの開発に活用しています。 ウィンドウ関数を利用すると、行動ログを利用したプロダクトの開発や評価が簡単に始められます。 今回は「関連キーワード」を例にとりウィンドウ関数がどのように活用できるのかを紹介します。

関連キーワードとは

朝ごはんのレシピ 52750品 [クックパッド] 簡単おいしいみんなのレシピが224万品

検索画面に検索を補助するための関連キーワードが提示されています。

「朝ごはん」の検索画面の関連キーワード：

f:id:code46:20151218150853p:plain

レシピを検索していると、自分の欲しいレシピが簡単に見つからない時があります。

理由は様々ですが一例として以下の様な状況があると思います。

  • 「入力するのがめんどくさい」
  • 「さがしたいものがあまりイメージできていない」
  • 「それをさがす適切な検索語が思いつかない」

関連キーワードはこうした状況で、以下の情報をユーザーに提供します。

「朝ごはん」と検索した人は、
「朝ごはん 和食」「ホットケーキ」「フレンチトースト」も検索しています

ユーザーはみんなの朝食アイデアの共通解を知ることで、 入力の手間が省けたり、新しいアイデアを得ることができるのではないでしょうか。

ウィンドウ関数で実装してみよう

早速実装してみましょう。ウィンドウ関数を使うとシンプルに書けます。

必要なもの:

  • 以下のフィールドを持つアクセスログ
    • time(アクセス時刻)
    • unique_id(アクセスしたユーザーを一意に識別するID)
    • keyword(検索語)
    • controller, action(アクセスログから検索ログに絞り込むために利用。railsのcontroller, actionを想定しています)
  • Presto(Redshift, PostgreSQL、Hiveなども可）

実装

with query_transitions as (
select
  time,
  unique_id,
  keyword as origin_query,
  lead(keyword) over (partition by unique_id order by time) as next_query -- ここがウィンドウ関数です!
from
  access_log
where
  time >= 'yyyy-mm-dd' and time < 'yyyy-mm-dd'
  and controller = 'search' and action = 'show'
order by
  unique_id, time
)
select
  origin_query,
  next_query,
  count(distinct unique_id) as cnt
from
  query_transitions
where
  origin_query != '' and next_query != ''
  and origin_query != next_query
group by
  origin_query, next_query
order by
  cnt desc

実装完了です！

これを実行すると以下の様な結果が得られます。

f:id:code46:20151218150926p:plain

クックパッドではもっぱらTreasureDataかRedshiftからログを参照するため、 データのスケールに悩まされることも稀です。

ウィンドウ関数で遷移を整理

このSQLは何をしているのでしょうか？ ウィンドウ関数が仕事をしているのは最初のwithで定義されているquery_transitionsの定義の部分です。 以下の部分に注目してください。

select
  time,
  unique_id,
  keyword as origin_query,
  lead(keyword) over (partition by unique_id order by time) as next_query -- 次の行のkeywordが入る

lead()はウィンドウ関数のひとつです。5.13. Window Functions — Presto 0.130 Documentation

unique_idごとにグループ分けした上で、現在参照している行の「次の行」から任意のフィールドの値を取ってくることができます。

time unique_id keyword lead(keyword)
time id1 朝ごはん 朝ごはん トースト
time id1 朝ごはん トースト フレンチトースト
time id1 フレンチトースト N/A
time id2 デビルドエッグ デビルドエッグ 簡単
time id2 デビルドエッグ 簡単 N/A

参照している行に対して、lead()のカッコの中だけ時間が進む(リードする)と考えると分かりやすいかと思います。 ウィンドウ関数で「検索語」と「次の検索語」が1行に集められたならあとは簡単なSQLで集計するだけです。 注意点としては、次の行が見つからない時はブランクになるのでそういった行は今回は無視しています。

より詳細な解説は以下が参考になります。

データプロダクトを改善する

シンプルに始めるという観点からすると上々です。 *1 しかし実際には改善の余地があります。例えば、

関連のないキーワードを提案してしまう（例：「夜食→カレー」）

  • 【仮説1】 「夜食」を検索した翌日、「カレー」と検索されたログを拾っている

    • 対策1: セッション化して30分でタイムアウトしてみましょう。別々の検索セッションとして扱うことができます。

  • 【仮説2】 右往左往している人のセッションを拾っている

    • 対策2: 有用なセッションのみを利用して実行してみましょう。「セッションの選別」もウィンドウ関数を利用してSQLだけで行える場合があります。

これらはSQLとウィンドウ関数だけで実現できます。 SQLにこだわることはないですが、データを一箇所に集め、 SQLで試行錯誤できることを増やしておくとプロトタイピングが容易になります。

この記事では詳しくは触れませんが、 セッション化はユーザーの行動ログを「ひとつの目的をもったまとまり」として分割できます。 さらに、セッションの開始と終端を解釈することでセッションの選別も可能になります。 関連キーワードでは多用される手法で、論文も多数ありますので、興味のある方は探して読んでみてください。 ユーザーに公開するレベルにしようと思うとセッション化は効果的です。

まとめ

今回はウィンドウ関数を利用して関連キーワードを実装してみました。 「関連キーワード」「関連商品」「関連ユーザー」など ユーザーの遷移を整理することで実現できるものは多くあります。

「ウィンドウ関数・データプロダクト面白いなぁ」と思った方、「そんなの常識！もっとこうしたほうがいい！」と思った方、こちらまでよろしくお願いします。

クックパッドにはあなたを待っている課題とデータがいっぱいです！

*1:- Talk Summary: Building Great Data Products · Coding VC (日本語訳)

どんなことをやるにしてもこの記事に示されている教訓を体験することになると思います。毎朝読み返したいです。