PostgreSQLの集約関数で来店日付の間隔をらくらく集計

ある論文によると、顧客が優良顧客であるかそうでないかの定義に、顧客の来店間隔の標準偏差を用いることができるそうです。

また、RFM分析をやっていても、Rは現在からのスナップショットなので、一ヶ月に一回しか来ない人に対して「もう、一ヶ月も来ていないので離反している！」なんて言っても、もともと一ヶ月に一回しか来ていないので、そんなことは言えません。顧客の平均来店間隔ととの兼ね合いで見たいなんてことになります。

で、この平均来店間隔、来店間隔標準偏差（分散）、顧客の売上金額や顧客の来店回数なんかに比べるとSQLで実装するととても面倒です。
（平均来店間隔は来店回数の集計日数合計による割り算でできますが。。。）

PostgreSQLには独自の集計関数を定義する。CREATE AGGREGATE文があります。
こいつとPostgreSQLの配列機能、両方組み合わせると、相当便利で先ほどの平均来店間隔や来店間隔分散も簡単に計算できます。

そこで、顧客の平均来店間隔と来店間隔分散をPostgreSQLのファンクションとして実装しました。

--convert date to date[]
DROP FUNCTION date_aggregate(date[], date);
CREATE FUNCTION date_aggregate(date[], date) RETURNS date[] AS '
	SELECT array_append($1, $2)
' LANGUAGE SQL;

--after aggregate date, make it to interval and calculate avg
DROP FUNCTION fin_date_avg(date[]);
CREATE FUNCTION fin_date_avg(date[]) RETURNS numeric AS '
	select AVG(a.a - b.a) from
		(SELECT row_number() over(order by a), a from unnest($1) a order by a) a,
		(SELECT row_number() over(order by a), a from unnest($1) a order by a) b
	where a.row_number = b.row_number + 1
' LANGUAGE SQL;

--after aggregate date, make it to interval and calculate variance
DROP FUNCTION fin_date_variance(date[]);
CREATE FUNCTION fin_date_variance(date[]) RETURNS numeric AS '
	select variance(a.a - b.a) from
		(SELECT row_number() over(order by a), a from unnest($1) a order by a) a,
		(SELECT row_number() over(order by a), a from unnest($1) a order by a) b
	where a.row_number = b.row_number + 1
' LANGUAGE SQL;

--make aggregate function:interval average
DROP AGGREGATE date_interval_avg(date);
CREATE AGGREGATE date_interval_avg (date)(
  SFUNC = date_aggregate,
  STYPE = date[],
  FINALFUNC = fin_date_avg
);

--make aggregate function:interval variance
DROP AGGREGATE date_interval_variance(date);
CREATE AGGREGATE date_interval_variance (date)(
  SFUNC = date_aggregate,
  STYPE = date[],
  FINALFUNC = fin_date_variance
);

実際に使うときは以下の通り、date_interval_avg、date_interval_variance、に売上表の来店日付を渡すだけ。
金額の集計なんかと同様に関数として顧客の来店間隔を計算することができます。
もっと賢いやり方があるかもですが、ご参考までに。便利！

--to get customer's interval avg and interval variance
SELECT 
 c.customerid, 
 date_interval_avg(distinct f.saledate),
 date_interval_variance(distinct f.saledate),
 sum(f.salesamount)
 from sales f, customer C
where f.customerid = c.customerid　AND f.saledate between '2012/04/01' AND '2013/03/31' 
group by c.customerid;