ECサイトなどで「おすすめ商品」や「関連商品」などが表示されることがあるが、このような機能を実装する場合、過去の購入履歴から「一緒に買われやすい商品」を求める必要がある。

この「一緒に買われやすい商品」を調べることをバスケット分析と言うが、今回はSQLで併売率を求めてバスケット分析をおこなう方法を紹介する。

併売率の求め方

併売率は「ある商品が買われた時、同一注文内に特定の商品が入っている確率」を表すので、計算式は以下のとおりとなる。

$$併売率 = \frac{AとBを買った}{Aを買った}$$

特定の商品を買った注文数を求める

段階を踏んで併売率を求めていこう。

まずは特定の商品を買った注文数をSELECT文で求める。

SELECT
	od.item_id
	, COUNT(DISTINCT o.order_id) as order_count
FROM
	order_details as od
	LEFT JOIN orders as o
	ON od.order_id = o.order_id
WHERE
	o.order_time
		BETWEEN timestamp '2014-04-01 00:00:00'
		AND timestamp '2014-04-30 23:59:59'
GROUP BY
	od.item_id
;

分析をする際はどの期間について調べるかも重要なので、今回はWHERE句の条件として2014/4で絞り込んでいる。

これで商品ごとの注文数を求めることができた。

二つの商品を一緒に買っている注文数を求める

二つの商品を一緒に買っている注文数は、まず注文テーブルをセルフジョインすることから始まる。

ジョインの条件として、まずは注文IDが同じもので絞り、次に同じ商品の組み合わせを除外するようにしている。

SELECT
	l.item_id
	, r.item_id as item_id2
	, COUNT(DISTINCT l.order_id) as order_count
FROM (
	SELECT o.order_id, o.order_time, od.item_id
	FROM
		order_details as od
		LEFT JOIN orders as o
		ON od.order_id = o.order_id
) l
INNER JOIN (
	SELECT o.order_id, o.order_time, od.item_id
	FROM
		order_details as od
		LEFT JOIN orders as o
		ON od.order_id = o.order_id
) r
ON l.order_id = r.order_id
	AND l.item_id <> r.item_id
WHERE
	l.order_time
		BETWEEN timestamp '2014-04-01 00:00:00'
		AND timestamp '2014-04-30 23:59:59'
GROUP BY
	l.item_id
	, r.item_id
;

これで一つの注文によって注文された商品の組み合わせを求めることができる。

両方のクエリを合わせて併売率を求める

最後に、ここまで紹介した二つのクエリを合わせて、先述の式に則り併売率を求めていく。

SELECT
	c.item_id
	, c.item_id2
	, CAST(c.order_count as real) / i.order_count as confidence
FROM (
	SELECT
		l.item_id
		, r.item_id as item_id2
		, COUNT(DISTINCT l.order_id) as order_count
	FROM (
		SELECT o.order_id, o.order_time, od.item_id
		FROM
			order_details as od
			LEFT JOIN orders as o
			ON od.order_id = o.order_id
	) l
	INNER JOIN (
		SELECT o.order_id, o.order_time, od.item_id
		FROM
			order_details as od
			LEFT JOIN orders as o
			ON od.order_id = o.order_id
	) r
	ON l.order_id = r.order_id
		AND l.item_id <> r.item_id
	WHERE
		l.order_time
			BETWEEN timestamp '2014-04-01 00:00:00'
			AND timestamp '2014-04-30 23:59:59'
	GROUP BY
		l.item_id
		, r.item_id
) c
INNER JOIN (
	SELECT
		od.item_id
		, COUNT(DISTINCT o.order_id) as order_count
	FROM
		order_details as od
		LEFT JOIN orders as o
		ON od.order_id = o.order_id
	WHERE
		o.order_time
			BETWEEN timestamp '2014-04-01 00:00:00'
			AND timestamp '2014-04-30 23:59:59'
	GROUP BY
		od.item_id
) i
ON c.item_id = i.item_id
;

クエリは長くなったがやっていることは単純で、二つのSELECT文で得た結果を結合し併売率を求めているだけ。

これで、ここまでの結果をもとにバスケット分析を進めていくことができるようになった。