●最終系：ACCESSクエリーの件！懇願ｍｍ

質問

ACCESSクエリーで教えていただきたいことございます！

以下に関連しております。大変恐縮です。
↓
http://okwave.jp/qa/q8756016.html

●したいこと●

累積回数、LTVを導きたいです（自動算出）。

●何から？●

以下データ群（テーブル名:table1）があります（会員番号はユニークをあらわします）。

注文日　　 会員番号　　商品名　　価格
2014/04/11 123456 パンツ　　1000 ←一番古い注文日の行（4/11開始）が先頭
2014/04/20 123456 靴下　　　3000
2014/05/23 456789 半袖　　　2000
2014/05/23 456789 靴下　　　3000
2014/05/29 456789 パンツ　　1000
2014/05/30 987654 靴下　　　3000
2014/06/09 987654 寝巻き　　5000
2014/06/15 987654 下駄　　　4000

●SQLクエリーで導きだしたいこと●

上記データ群を、SQL（クエリー）を使用して、

以下のように表現できないでしょうか。

注文日　　 会員番号　 購入回数　 LTV　　※商品名も表示できればそのままつけたい
2014/04/11 123456 　　　　 1　　　　 0
2014/04/20 123456 　 2 9
2014/05/23 456789 　　　　1　　　 　0
2014/05/23 456789 　 1 0
2014/05/29 456789 　 2 6
2014/05/30 987654 　　　　 1 0
2014/06/09 987654 　 2 10
2014/06/15 987654 　 　 3 16

※同じ注文日、同じ会員番号であれば、商品を複数購入しようが（複数行であっても）それは同日扱いにより購入回数は同じと数とする

※LTV：その回の注文日 - 最初(1回目)の注文日（最初の購入から見て何日経過か）

※ユニーク（会員番号）の方が、一度にいくつ購入したかは問わず、
何日おき（すべて初回を起点にした経過日とする）に来店したか、頻度と合わせて表現したい

※積み上がる累積金額の表示はなくてかまわないので、上記（購入回数、LTV）２つを表示できないでしょうか。

お知恵をいただければ幸いですｍｍ

LTV算出は以下のイメージが近いです。
↓↓↓↓↓↓↓↓↓↓↓
http://okwave.jp/qa/q8756016.html

なお、LTV を求める記述の Max(Q2.注文日) を Min(Q2.注文日) とすると
表示結果は、

会員番号　 購入回数　 LTV
123456　 1　 0
123456　 2　 31
123456　 3　 61
456789　 1　 0
456789　 2　 31

回答No.1

30246kiku · ベストアンサー

楽しいですか

テーブル名を ★★
以下をクエリのSQLビューで

SELECT Q1.注文日, Q1.会員番号,
Count(Q2.会員番号)+1 AS 購入回数,
DateDiff('d', Nz(Min(Q2.注文日),Q1.注文日), Q1.注文日) AS LTV
FROM
(SELECT 会員番号, 注文日 FROM ★★ GROUP BY 会員番号, 注文日) AS Q1
LEFT JOIN
(SELECT 会員番号, 注文日 FROM ★★ GROUP BY 会員番号, 注文日) AS Q2
ON Q1.会員番号=Q2.会員番号 AND Q1.注文日>Q2.注文日
GROUP BY Q1.会員番号, Q1.注文日;

変更部分はそうないですね
結果は

注文日　会員番号　購入回数　LTV
2014/04/11　123456　1　0
2014/04/20　123456　2　9
2014/05/23　456789　1　0
2014/05/29　456789　2　6
2014/05/30　987654　1　0
2014/06/09　987654　2　10
2014/06/15　987654　3　16

2014/05/23　456789　1　0
部分は２行必要＆商品名も欲しいのなら

SELECT Q4.注文日, Q4.会員番号, Q4.商品名, Q3.購入回数, Q3.LTV
FROM
(SELECT Q1.注文日, Q1.会員番号,
Count(Q2.会員番号)+1 AS 購入回数,
DateDiff('d', Nz(Min(Q2.注文日),Q1.注文日), Q1.注文日) AS LTV
FROM
(SELECT 会員番号, 注文日 FROM ★★ GROUP BY 会員番号, 注文日) AS Q1
LEFT JOIN
(SELECT 会員番号, 注文日 FROM ★★ GROUP BY 会員番号, 注文日) AS Q2
ON Q1.会員番号=Q2.会員番号 AND Q1.注文日>Q2.注文日
GROUP BY Q1.会員番号, Q1.注文日) AS Q3
LEFT JOIN ★★ AS Q4
ON Q3.会員番号=Q4.会員番号 AND Q3.注文日=Q4.注文日
ORDER BY Q4.会員番号, Q4.注文日
;

の表示は

注文日　会員番号　商品名　購入回数　LTV
2014/04/11　123456　パンツ　1　0
2014/04/20　123456　靴下　2　9
2014/05/23　456789　半袖　1　0
2014/05/23　456789　靴下　1　0
2014/05/29　456789　パンツ　2　6
2014/05/30　987654　靴下　1　0
2014/06/09　987654　寝巻き　2　10
2014/06/15　987654　下駄　3　16

※
> 一番古い注文日の行（4/11開始）が先頭

これについては考えてください。
現状、会員番号昇順を１番目に指定しているので、上記は、タマタマ
注文日昇順を１番目にすると、会員番号がバラバラになります

ユーザ定義関数でも作って、
注文日昇順にした際、会員番号が並ぶ様に細工してください。

処理的には、
会員番号でグループ化した際の最小注文日を求めて、
その注文日を昇順にした会員番号の並び順でソートする様に
ソート（ ORDER BY ）指定は、この関数の戻り値、次に注文日昇順で

※ 後だしで、会員番号の発行は小さい順、なら無駄な記述ですけど

※ 処理性能的にはどうなんですか？
　　何レコードのテーブルで、
　　購入回数の最大・平均、
　　クエリを開いて表示されるまで何秒とか

　　報告ある事を期待します

　　遅くて、使いものにならないので、カテゴリを変えて質問し直した
　　って事になるのでしょうか？

お礼

ありがとうございます。

＞
これについては考えてください。
現状、会員番号昇順を１番目に指定しているので、上記は、タマタマ
注文日昇順を１番目にすると、会員番号がバラバラになります

上記はあらかじめテーブルに会員番号順、注文日別でソートしたデータを組み込んでおこうと
おもいます、時間が限られているため。試してみます！結果のちほど。

※この返信が的はずれであればご指摘いただければ幸いです。

補足

30246kiku様

全く問題なかったです。多少クエリーの実行に時間を要しましたが４０万レコードでストレスなく
集計できました。また、あらかじめテーブルに会員番号順、注文日別でソートしたデータを組み込んでおくことで会員番号がバラバラになるリスクが回避できています。

秀逸、実用的です。ありがとうございました。