Databricksのai_top_driversで「売上が増えた要因」を分解してみた

Posted at 2026-08-28

はじめに

SQLリファレンスを眺めていて、ai_top_drivers() という見慣れない関数を見つけました。ベータですが、2つの期間なり2つの集団なりを比べて、売上や件数といった数字がどれだけ動いたか、そしてその増減にどの切り口のどの値が効いていたのかを、影響の大きい順に並べて返してくれる関数です。いわゆる貢献分析 (contribution analysis) をSQL一発でやってくれます。

「先月より売上が落ちたのは、どの地域のどの商品のせいか」を調べる作業は、これまでGROUP BYを何本も書いて目視で比べる、という力技になりがちでした。それが関数呼び出し1回で済むなら、日々の運用がだいぶ変わります。

実際に samples.nyctaxi.trips で動かしてみたところ、ドキュメントのサンプルをそのまま実行した時点で「あれ、これは想定と違う」というポイントがありました。落とし穴を2つ踏んで、最後に答えにたどり着くまでを書いていきます。

ai_top_driversとは

やっていることは、ひとことで言えば 「比べたい2つのかたまりの間で数字がどれだけ動いたか」を、切り口ごとの内訳に分解する ことです。

具体例で考えます。この記事で使うNYCタクシーのデータでは、運賃の合計が2016年1月は131,996ドル、2月は136,853ドルでした。全体で4,857ドル増えています。知りたいのは「この4,857ドルの増加は、どこで起きたのか」です。特定の乗車エリアが伸びたのか、特定の距離帯なのか、それとも全体が満遍なく底上げされたのか。この記事は、最後までこの問いを追いかけます。

手でやるなら、乗車エリア別にGROUP BYして1月と2月を並べ、次は支払い方法別で同じことをして、さらに「乗車エリア×支払い方法」の組み合わせも見て、と切り口の数だけクエリが増えます。ai_top_drivers() はこの総当たりを関数側でやって、増減への効き方が大きい順に並べて返してくれます。

関数の中でやっていること自体は単純です。行を2つに分けて、指定された列の値ごとに集計し、その差を大きい順に並べる。それだけです。

こちらが決めるのは3つあります。

何と何を比べるか
どの列で分類して見るか
どれくらい小さいまとまりまで結果に出すか

この記事でつまずいたのも、すべてこの3つのどれかでした。

まず呼び出しの形を見る

部品の話をする前に、クエリの全体像を出しておきます。公式ドキュメントの例に説明を書き足したものです。

WITH input AS (
  SELECT
    pickup_zip,             -- 分類に使う列
    fare_amount,            -- 増減を見たい数値
    CASE ... END AS is_test -- 比較元か比較先かの印を付ける
  FROM samples.nyctaxi.trips
  WHERE ...
)
SELECT *
FROM ai_top_drivers(
  input       => TABLE(input),          -- 上で組み立てた入力テーブル
  metric      => 'fare_amount',         -- 増減を見たい列の「名前」
  is_test     => 'is_test',             -- 2つに分ける列の「名前」
  dimensions  => ARRAY('pickup_zip'),   -- 分類に使う列の「名前」
  aggregation => 'sum'                  -- 集計方法
)
ORDER BY ABS(change) DESC

2段構えです。前半のWITH句で入力テーブルを自分で組み立て、後半でそれを関数に渡します。分析に必要な列は、渡す前に自分で用意しておく という作りになっています。

引数はすべて名前付きで、引数名 => 値 の形で書きます。位置引数では渡せません。

もうひとつの特徴は、metric や is_test や dimensions に渡すのが列そのものではなく 列名の文字列 だという点です。metric => fare_amount ではなく metric => 'fare_amount' と書きます。ここは最初に戸惑いました。

この形を頭に入れた上で、決めるべき3つを順に見ていきます。

何と何を比べるか: is_test列を自分で作る

上のクエリで CASE ... END AS is_test と書いていた部分です。

is_test は 入力テーブルに自分で足しておく真偽値の列 です。関数が勝手に作ってくれるものではありません。1行ずつ「この行は比較元か、比較先か」を true / false で持たせておきます。

作り方は普通のCASE式です。ドキュメントの例では乗車日時を見て、1月の行なら false、2月の行なら true としています。

CASE
  WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-01-31' THEN false
  WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-02-01' AND '2016-02-29' THEN true
  ELSE NULL
END AS is_test

そのうえで、関数にはこの列の名前を文字列で渡します。

is_test => 'is_test'

左の is_test は引数名、右の 'is_test' は列名 です。たまたま同じ綴りなので紛らわしいのですが、別物です。列名は何でもよく、is_february という名前で作ったなら is_test => 'is_february' と書きます。

引数名が is_test なのでA/Bテスト専用の関数に見えますが、そうではありません。関数がやるのは、この列で行を2つに分けて、それぞれ集計して差を取ることだけです。

is_test = false の行 → 比較元。ドキュメントでは「コントロールグループ」
is_test = true の行 → 比較先。ドキュメントでは「テストグループ」

ドキュメントの例は1月と2月なので「先月と今月」の比較ですが、「施策の前と後」「キャンペーン対象の店舗とそれ以外」「エラーになったリクエストとならなかったリクエスト」など、2つに分けられるものなら何でも入ります。この列をどう定義するかが、そのまま分析の問いになります。

なお NULL にした行は分析から外れます。上のCASE式で3月以降を ELSE NULL にしているのは、比較したい2か月だけを残すためです。

どの列で分類して見るか: dimensions

2つ目に決めるのが、どの列で分類するかです。乗車エリア別に見るのか、支払い方法別に見るのか。dimensions 引数に列名を渡します。ドキュメントでは「ディメンション」と呼ばれていますが、この記事では切り口と書きます。

分類してできた一つひとつのまとまり、たとえば「乗車エリアが10001の行」を、この記事では区分と呼びます。ドキュメントの言葉ではセグメントです。切り口と区分、この2語だけ押さえておけば以降は読めます。

なお input、metric、is_test の3つは必須ですが、dimensions は省略できます。省略すると、入力テーブルの中から相関の高い上位20列を関数側が自動で選びます。これが後半の山場になります。

どれくらい小さい区分まで出すか: min_support

3つ目が min_support です。その区分が分析対象の行のうち最低何割を占めていれば結果に出すか、という足切りの値を渡します。0から1の間で、デフォルトは0.05、つまり5%です。

5%に満たない小さな区分は、たとえ大きく動いていても結果に出てきません。切り口の値の種類が多いほど1区分あたりの母数は小さくなるので、この5%は思ったより効いてきます。

この記事では2回この足切りに引っかかります。結果の行数が思ったより少ないときは、まずここを疑うことになる と覚えておいてください。

返ってくるもの

結果は6列のテーブルです。1行が1つの区分に対応します。

列	内容
`contributor`	その区分が何なのか。`["列名=値"]` という文字列の配列
`metric_control`	その区分だけに絞ったときの、比較元 (1月) の合計値
`metric_test`	同じく比較先 (2月) の合計値
`change`	差分。metric_test − metric_control
`relative_change`	差分が比較元の何倍にあたるか。0.05なら5%増
`support`	その区分が分析対象の行全体のうち何割を占めるか

contributor には3種類の行が混ざって返ってきます。

["all"] — 全体。母集団まるごとで数字がいくら動いたかを表す1行
["pickup_zip=10001"] — 1つの列の値で絞った区分
["pickup_zip=10001", "payment_type=1"] — 2つの列の値を組み合わせた区分

読む順番は、まず ["all"] で全体の増減を押さえ、次に個別の区分の change がそのうちどれだけを説明しているかを見る、という流れになります。support は要するにその区分の母数の大きさなので、change が大きくても support が極端に小さければ、わずかな行がたまたま振れただけかもしれない、という判断ができます。

使う前の要件

ここは先に確認しておかないと、クエリを書いてから弾かれます。

Databricks Runtime 16.1以上
Photonが有効なProまたはサーバーレスSQLウェアハウス、もしくはPhoton有効のクラスター
ワークスペースが Predictive AI Functions プレビューに登録されていること

3つ目が抜けやすいところです。ワークスペース管理者がプレビューページから有効化する必要があります。

まず動かしてみる

先ほど省略記法で示したクエリを、そのまま動く形で書き下したものです。samples.nyctaxi.trips の2016年1月を比較元、2月を比較先として、乗車ZIPコード別に運賃合計の変化を分解します。

WITH input AS (
  SELECT
    pickup_zip,
    fare_amount,
    CASE
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-01-31' THEN false
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-02-01' AND '2016-02-29' THEN true
      ELSE NULL
    END AS is_test
  FROM samples.nyctaxi.trips
  WHERE DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-02-29'
)
SELECT *
FROM ai_top_drivers(
  input => TABLE(input),
  metric => 'fare_amount',
  is_test => 'is_test',
  dimensions => ARRAY('pickup_zip'),
  aggregation => 'sum'
)
ORDER BY ABS(change) DESC

結果はこうなりました。

contributor	metric_control	metric_test	change	relative_change	support
`["all"]`	131996.11	136852.66999999998	4856.559999999998	0.03679320549673773	1
`["pickup_zip=high (>10065)"]`	49900.3	52468.36	2568.0599999999977	0.051463818854796416	0.30920448282197316
`["pickup_zip=low (<=10017)"]`	42901.009999999995	45365.8	2464.790000000008	0.05745295973218366	0.3527466470696307
`["pickup_zip=medium (10017 — 10065)"]`	39194.8	39018.51	-176.29000000000087	-0.004497790523232696	0.3380488701083961

1行目の ["all"] が母集団全体です。2016年1月から2月にかけて運賃合計は131996.11から136852.67へ、4856.56 (3.7%増) 動いていた、と読みます。support が1になっているのが全体行の目印です。

問いに対する答えはこの下に並んでいるはずなのですが、ここで手が止まりました。

落とし穴1: ZIPコードが「量」として扱われる

contributor には pickup_zip=10001 のように個別のZIPコードが並ぶと思っていたのですが、返ってきたのは high (>10065)、low (<=10017)、medium (10017 — 10065) の3つだけでした。ZIPコードが大小の順で3分割されています。

ドキュメントの制限事項にこう書かれています。

数値識別子 (ZIPコードやIDなど) は量として扱われ、範囲にグループ化されることがあります。正確な値でセグメント化するには、数値識別子をSTRINGにキャストしてください。

dimensions の説明も対応していて、取りうる値の種類が少ない数値列はそのままの値で分類されますが、種類が多い数値列は自動的に低・中・高の3つの範囲にまとめられます。ZIPコードは数値型で、しかも種類が多い。関数から見れば「大小に意味のある量」に見えるわけです。

住所コード、商品ID、顧客IDあたりは全部これに該当します。IDとして扱ってほしい数値列は、明示的にSTRINGへキャストする のが正解です。

同じ pickup_zip が、型によってどう分割されるかを並べるとこうなります。

なお、分解そのものは正しく計算されています。3つの範囲の support を足すと1になり、change を足すと 2568.06 + 2464.79 - 176.29 = 4856.56 で ["all"] 行の変化量にぴったり一致します。母集団を漏れなくダブりなく分割した上で、加法的に寄与を配分している。計算は合っているのに、分割の軸が意図と違う というのがこの結果の厄介さです。

high (>10065) という表記は一見それらしく読めてしまうので、範囲にまとめられていることに気づかないまま「高ZIP帯のエリアが増収要因です」と報告してしまう。ZIPコードの大小に地理的な意味はほとんどないので、これは報告としてはほぼ無意味です。エラーが出ないぶん、気づきにくいタイプの間違いです。

STRINGにキャストして再実行する

WITH input AS (
  SELECT
    CAST(pickup_zip AS STRING) AS pickup_zip,
    fare_amount,
    CASE
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-01-31' THEN false
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-02-01' AND '2016-02-29' THEN true
      ELSE NULL
    END AS is_test
  FROM samples.nyctaxi.trips
  WHERE DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-02-29'
)
SELECT *
FROM ai_top_drivers(
  input => TABLE(input),
  metric => 'fare_amount',
  is_test => 'is_test',
  dimensions => ARRAY('pickup_zip'),
  aggregation => 'sum'
)
ORDER BY ABS(change) DESC

contributor	metric_control	metric_test	change	relative_change	support
`["all"]`	131996.11	136852.66999999998	4856.559999999998	0.03679320549673773	1
`["pickup_zip=10001"]`	5682.51	7217.5	1534.9899999999998	0.2701253495374403	0.05562189968767224
`["pickup_zip=10003"]`	6165	6713.5	548.5	0.08896999188969992	0.05396839977953335

今度は個別のZIPコードで返ってきました。10001は運賃合計が5682.51から7217.5へ、27%増えています。これなら「マンハッタンのこのエリアが伸びた」という具体的な話ができます。

ただし今度は行が減る

返ってきた区分が10001と10003の2つしかありません。ここで min_support が効いています。

10001の support は0.0556、10003は0.0540で、どちらもぎりぎり5%を超えています。他のZIPコードは個別に見ると5%に届かないので、まとめて結果から落ちているわけです。

範囲でまとめられていたときは3つの区分で母集団を過不足なく分割できていましたが、個別値にした途端、拾えているのは全体の変化4856.56のうち 1534.99 + 548.5 = 2083.49、約43%だけになります。軸を正しくすると網羅性が落ちる。ここが悩ましいところです。

そして次の実行で、この足切りがもっと極端な形で出てきます。

落とし穴2: 件数で見たら内訳がゼロ行になった

金額ではなく乗車回数の変化を見てみます。集計を count にします。ここでも一つ作法があって、count を使うときも metric には数値列を渡す必要があります。ドキュメントでは定数列を作る方法が推奨されています。

WITH input AS (
  SELECT
    CAST(dropoff_zip AS STRING) AS dropoff_zip,
    1 AS trip_count,
    CASE
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-01-31' THEN false
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-02-01' AND '2016-02-29' THEN true
      ELSE NULL
    END AS is_test
  FROM samples.nyctaxi.trips
  WHERE DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-02-29'
)
SELECT *
FROM ai_top_drivers(
  input => TABLE(input),
  metric => 'trip_count',
  is_test => 'is_test',
  dimensions => ARRAY('dropoff_zip'),
  aggregation => 'count'
)
ORDER BY ABS(change) DESC

1 AS trip_count という書き方は最初は妙に見えますが、「何を数えるか」を列として明示する形だと理解すると納得できます。

contributor	metric_control	metric_test	change	relative_change	support
`["all"]`	10695	11077	382	0.03571762505843852	1

返ってきたのは全体の1行だけでした。 乗車回数は1月の10,695回から2月の11,077回へ382回増えていますが、その内訳がひとつも出てきません。

降車エリアをSTRINGにして個別の値で分類したので、どの降車エリアも単独では全行の5%に届かなかった。足切りを超える区分がゼロだったので、内訳の行が1つも残らなかったわけです。

エラーではないので気づきにくいのですが、["all"] しか返ってこない結果は「変化の要因が見つからなかった」ではなく「足切りで全部落ちた」のサイン と読むべきです。対処は min_support を明示的に下げること (min_support => 0.01 など) になります。

考えてみれば当然で、ニューヨークの乗車エリアは数十種類あります。均等に散らばっていれば1エリアあたり数%にしかならず、大半が5%に届きません。商品コード別、店舗別、顧客属性別といった実務でよく使う切り口は、たいてい値の種類がもっと多い。デフォルトのままだと内訳がほとんど返ってこない、という結果になりがちです。

ここまでで分かったのは、乗車エリアという切り口では答えが出ないということでした。エリアで分けると、粗すぎるか、細かすぎて足切りされるかのどちらかになる。

切り口を関数に選ばせたら答えが出た

そこで dimensions を省略します。使えそうな列を入力テーブルに入れておいて、どれで分類するかは関数に選ばせます。

WITH input AS (
  SELECT
    CAST(pickup_zip AS STRING) AS pickup_zip,
    CAST(dropoff_zip AS STRING) AS dropoff_zip,
    trip_distance,
    fare_amount,
    CASE
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-01-31' THEN false
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-02-01' AND '2016-02-29' THEN true
      ELSE NULL
    END AS is_test
  FROM samples.nyctaxi.trips
  WHERE DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-02-29'
)
SELECT *
FROM ai_top_drivers(
  input => TABLE(input),
  metric => 'fare_amount',
  is_test => 'is_test'
)
ORDER BY ABS(change) DESC

contributor	metric_control	metric_test	change	relative_change	support
`["all"]`	131996.11	136852.66999999998	4856.559999999998	0.03679320549673773	1
`["trip_distance=medium (1.2 — 2.36)"]`	29677.5	31820	2142.5	0.07219273860668857	0.30286606650744075
`["trip_distance=high (>2.36)"]`	79111	81222.52	2111.520000000004	0.02669059928454961	0.3453977585890134
`["pickup_zip=10001"]`	5682.51	7217.5	1534.9899999999998	0.2701253495374403	0.05562189968767224
`["trip_distance=low (<=1.2)"]`	23207.609999999997	23810.15	602.5400000000045	0.025963035400888096	0.35173617490354586
`["pickup_zip=10003"]`	6165	6713.5	548.5	0.08896999188969992	0.05396839977953335

上位を trip_distance が占めました。答えはエリアではなく、距離だった わけです。

trip_distance の3区分の change を足すと 2142.5 + 2111.52 + 602.54 = 4856.56。support の合計も1になります。つまりこの切り口だけで、4,857ドルの増加を過不足なく説明できています。中距離帯 (1.2から2.36マイル) が2,142ドル、長距離帯が2,112ドル。短距離帯はほぼ横ばい。2月は中距離以上の乗車が増えていた、という結論です。

pickup_zip を指定して見ていたときには、この切り口は視界に入っていませんでした。エリアの話だと思い込んで dimensions を固定していたら、たどり着かなかった見立てです。当たりがついていない段階では、省略して関数に選ばせた方がいい というのが今回の実感でした。逆に、定型の報告として見る切り口を固定したい場面では、明示した方が結果が安定して比較もしやすくなります。

trip_distance も数値列なので、ここでも low / medium / high の3範囲にまとめられています。ただし距離は本当に「量」なので、この分割は妥当です。落とし穴1と同じ挙動でも、意味がまったく違います。

なお、入力に含めた dropoff_zip は1行も出ていません。個別の値にすると min_support に届かなかったためで、落とし穴2と同じ現象です。

実務でどう効くか

貢献分析そのものは新しい考え方ではありません。BIツールの機能として持っているものもありますし、地道にGROUP BYを回しても答えは出ます。

それでもSQL関数として使えることには意味があって、

分析がクエリとして残るので、Gitで管理でき、他の人がレビューできる
ジョブに載せて定期実行し、結果をテーブルに書き出せる
Unity Catalogの権限管理の内側で完結する。データがどこかに出ていかない
結果がテーブルなので、AI/BIダッシュボードやアラートの入力にそのまま使える

このあたりが効いてきます。特に「毎週の数字の振り返りの前に、増減の要因候補を自動で洗い出しておく」という使い方は、そのままジョブにできます。人がやると準備に半日かかっていた作業です。

注意しておきたいのは、この関数が返すのは 寄与の大きさであって、因果ではない ことです。今回でいえば「中距離の乗車が増えていた」までは言えますが、「なぜ増えたのか」は答えていません。あくまで調査の出発点を示してくれるものとして扱うのが健全だと思います。

ベータ版の制限事項

ドキュメントに挙がっている制限をまとめておきます。

metric 列または is_test 列がNULLの行は、分析前に落とされる
count distinct と median の集計は非対応
MAP列とSTRUCT列は切り口に使えない
数値識別子 (ZIPコード、IDなど) は量として扱われ、範囲にグループ化されることがある。正確な値で分類するにはSTRINGにキャストする

1つ目は地味に効きます。NULLが落ちるということは、NULL自体が変化の要因である場合 (「あるカラムの欠損が先月から増えた」など) を、この関数では拾えないということです。事前に COALESCE で明示的な値に置き換えておくのが実務的な回避策になります。

count distinct が使えないので、利用者数の変化要因、といった分析は今のところ対象外です。ここはGAまでに対応してほしいところ。

まとめ

ai_top_drivers() を試して分かったことをまとめます。

比較元と比較先の差分を、切り口の値ごとに分解して大きい順に並べてくれるテーブル値関数。引数はすべて名前付きで渡す
is_test は関数が用意するものではなく、自分でCASE式で作って入力テーブルに足しておく列
ZIPコードやIDのような数値識別子は「量」として扱われ、low / medium / high の3範囲にグループ化される。個別の値で見たいならSTRINGにキャストする
範囲に分割されても計算自体は正しく、各区分の change の合計は ["all"] 行の変化量と一致する。間違うのは分割の軸であって数字ではない
min_support のデフォルト0.05 (5%) は実データにはかなり厳しい。個別の値で分類すると大半が足切りされ、["all"] の1行しか返らないこともある
["all"] しか返ってこないのは「要因が見つからなかった」ではなく「足切りで全部落ちた」のサイン
dimensions を省略すると相関の高い列が自動で選ばれる。今回はこれで答えが出た。指定していなかった trip_distance が上位を占め、増収の要因は距離だった
aggregation => 'count' のときも metric には数値列が必要。1 AS trip_count のような定数列を用意する
metric と is_test がNULLの行は落ちる。NULLそのものが変化要因のケースは拾えない
Predictive AI Functionsプレビューの有効化とPhotonが必要

一番の収穫は、自分で切り口を決めない方が答えに近づいた、という点でした。エリア別に見るものだと思い込んで dimensions を固定していた間は、範囲にまとめられたり足切りされたりで、ずっと答えの手前をうろうろしていました。省略して関数に選ばせた瞬間に距離という切り口が出てきて、それで説明がついた。仮説を持って臨むのは大事ですが、この関数に関しては先に持ちすぎない方が良さそうです。