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

Question

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

以下に関連しております。大変恐縮です。
↓
http://oshiete.goo.ne.jp/qa/8756016.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://oshiete.goo.ne.jp/qa/8756016.html

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

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

30246kiku · Accepted Answer

楽しいですか

テーブル名を ★★
以下をクエリの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 ）指定は、この関数の戻り値、次に注文日昇順で

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

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

報告ある事を期待します

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

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

楽しいですか

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング