はじめに

データベースに大量の件数がある場合、ある検索では、インデックスや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のチューニングについて、マテリアライズド・ビューや分析関数を使ったデータマートによる、より実践的な方法をご紹介しました。データマートや分析関数などは、普段、あまり使われないものですが、業務によっては有益な場合がありますので、頭の片隅に置くと良いかもしれません。