はじめに
データベースに大量の件数がある場合、ある検索では、インデックスやCPU数では大きく速度向上できない場合があります。その場合、いろいろなパフォーマンス・チューニングがあるのですが、今回は、SQLで「顧客ごとの直近の製品の購買データの検索を速くする方法」（小ネタ）をご紹介します。
サンプルのデータは、Oracle Database の SHスキーマのSALES表（約90万件）を使います。
DESC SH.SALES
名前 Nullかどうか タイプ
------------- -------- ------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
CHANNEL_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
QUANTITY_SOLD NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)
普通に検索するSQL
このデータから、顧客ごとの直近の製品の購買データを検索したい場合は、顧客ごと製品ごとの最大日時をGROUP BYしたサブクエリと、元の表を結合した次のようなSQLになります。
SELECT
SALES.CUST_ID,
SALES.PROD_ID,
SALES.TIME_ID,
SALES.CHANNEL_ID,
SALES.PROMO_ID,
SALES.QUANTITY_SOLD,
SALES.AMOUNT_SOLD
FROM
(SELECT CUST_ID, MAX(TIME_ID) TIME_ID
FROM SH.SALES
GROUP BY CUST_ID) RCNT,
SH.SALES
WHERE RCNT.CUST_ID = SALES.CUST_ID
AND RCNT.TIME_ID = SALES.TIME_ID;
このようなSQLは回数が少ないと、それほど問題にはならないのですが、実行回数が多くなると、全体の処理時間が長くなってしまう場合があります。
データマート（中間テーブル）を作る
この課題の解決方法は、単純にデータマート（中間デーブル）を作ることです。顧客ごとの直近の製品の購買データだけを持つ表を作ります。今回はマテリアライズド・ビューとして作ります。実体化した表にすることで、処理コストが大きい GROUP BY の実行を１回できます。レコード件数が少ない表を検索することで、処理全体の検索コストを小さくし、検索処理の実行回数が多い場合でも、全体の処理時間を短くすることができます。
（ここでは、CUST_ID と TIME_ID の組み合わせが、ユニークになると仮定しています）
CREATE MATERIALIZED VIEW SALES_RCNT_M_VIEW AS
SELECT
SALES.CUST_ID,
SALES.PROD_ID,
SALES.TIME_ID,
SALES.CHANNEL_ID,
SALES.PROMO_ID,
SALES.QUANTITY_SOLD,
SALES.AMOUNT_SOLD
FROM
(SELECT CUST_ID, MAX(TIME_ID) TIME_ID
FROM SH.SALES
GROUP BY CUST_ID) RCNT,
SH.SALES
WHERE RCNT.CUST_ID = SALES.CUST_ID
AND RCNT.TIME_ID = SALES.TIME_ID;
このマテリアライズド・ビューはレコード件数が約5万件と少なくなりました。
分析関数（ウィンドウ関数）を使ったデータマート
上のSQLはMAX関数を使いましたが、分析関数（ウィンドウ関数）を使うこともできます。CUST_ID ごとに TIME_ID を降順にならべて番号を割り当てています。
CREATE MATERIALIZED VIEW SALES_RCNT_M_VIEW2 AS
SELECT
SALES.CUST_ID,
SALES.PROD_ID,
SALES.TIME_ID,
SALES.CHANNEL_ID,
SALES.PROMO_ID,
SALES.QUANTITY_SOLD,
SALES.AMOUNT_SOLD
FROM
(SELECT
CUST_ID,
TIME_ID,
ROW_NUMBER() OVER(PARTITION BY CUST_ID ORDER BY TIME_ID DESC) RN
FROM SH.SALES
) RCNT,
SH.SALES
WHERE
RN = 1
AND RCNT.CUST_ID = SALES.CUST_ID
AND RCNT.TIME_ID = SALES.TIME_ID;
ウインドウ関数を使うと、上位何個までといった指定を「RN = 1」の条件式を変えることで実現できます。また、RANGE を使うと、顧客の最新購買日から遡って30日までのデータだけといった絞り込みもできます。
おわりに
この記事では、SQLのチューニングについて、マテリアライズド・ビューや分析関数を使ったデータマートによる、より実践的な方法をご紹介しました。データマートや分析関数などは、普段、あまり使われないものですが、業務によっては有益な場合がありますので、頭の片隅に置くと良いかもしれません。