ACCESSデータは５０００以上ある（抽出の件）

Question

データは５０００件以上あるのですが、以下で対応は難しいのではないでしょうか。

ソートでできますでしょうか？↓
http://oshiete.goo.ne.jp/qa/8755953.html

●質問した内容●
ACCESSを活用して累計（経過）を表現したい

質問者：jordan232011 投稿日時：2014/09/15 21:38  困ってます
ACCESSテーブルに以下情報が格納されています。

注文日　　　　会員番号　　商品名　　価格
2014/03/23　 123456 サンプル　　500円
2014/04/23 　123456 サンプル　　500円
2014/05/23 　123456 サンプル　　500円
2014/03/21 　456789 容器　　　 1000円
2014/04/21 　456789 容器　　　 1000円

上記をクエリーを利用して、以下の抽出をする方法は
ないでしょうか。
↓

(1)
会員番号　購入回数　価格　累積価格
123456　　　　　1 500 500
123456 　　 2 500 1000
123456 　　3 500 1500
456789 　　1 1000 1000 
456789 　　2 1000 2000

(2)
会員番号　購入回数　LTV
123456　　　　　 1 0
123456 　　 2 30
123456 　　 3 60
456789 　　 1 30
456789 　　2 60

※LTV：その回の注文日 - 前の注文日

※必要であれば先頭にAUTOナンバーをつけることも可能

※ACCESS２０１０を利用中

お手数ですが御教授いただけるとたすかります。。

30246kiku · Accepted Answer

以下の雰囲気でどうなりますか

大量のデータでは確認していないので、遅かったら捨ててください。
テーブル名を、★★ と仮定します

１）

クエリのSQLビューで以下を記述します

SELECT Q1.会員番号,
Count(*) AS 購入回数,
First(Q1.価格計) AS 価格,
Sum(Q2.価格計) AS 累積価格
FROM
(SELECT 会員番号, 注文日, Sum(価格) AS 価格計 FROM ★★ GROUP BY 会員番号, 注文日) AS Q1
INNER JOIN
(SELECT 会員番号, 注文日, Sum(価格) AS 価格計 FROM ★★ GROUP BY 会員番号, 注文日) AS Q2
ON Q1.会員番号=Q2.会員番号 AND Q1.注文日>=Q2.注文日
GROUP BY Q1.会員番号, Q1.注文日
;

同一会員が同じ日に複数注文する事があるとして
会員番号、注文日でグループ化して、価格の合計を求める
この求めた同士を同じ会員番号で、また、Q1.注文日>=Q2.注文日　で結び付け

Q1.会員番号　Q1.注文日　Q1.価格計　Q2.会員番号　Q2.注文日　Q2.価格計
123456　2014/03/23　¥500　123456　2014/03/23　¥500
123456　2014/04/23　¥500　123456　2014/04/23　¥500
123456　2014/04/23　¥500　123456　2014/03/23　¥500
123456　2014/05/23　¥500　123456　2014/05/23　¥500
123456　2014/05/23　¥500　123456　2014/04/23　¥500
123456　2014/05/23　¥500　123456　2014/03/23　¥500
456789　2014/03/21　¥1,000　456789　2014/03/21　¥1,000
456789　2014/04/21　¥1,000　456789　2014/04/21　¥1,000
456789　2014/04/21　¥1,000　456789　2014/03/21　¥1,000

これを元に、Q1.会員番号, Q1.注文日　でグループ化
その際、
購入回数は、Count(*)
価格は、First(Q1.価格計)
累積価格は、Sum(Q2.価格計)

表示結果は

会員番号　購入回数　価格　累積価格
123456　1　¥500　¥500
123456　2　¥500　¥1,000
123456　3　¥500　¥1,500
456789　1　¥1,000　¥1,000
456789　2　¥1,000　¥2,000

２）

クエリのSQLビューで以下を記述します

SELECT Q1.会員番号,
Count(Q2.会員番号)+1 AS 購入回数,
DateDiff('d', Nz(Max(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.注文日
;

同一会員が同じ日に複数注文する事があるとして
会員番号、注文日でグループ化しておきます
ここまでは、１）とほぼ同じで、価格は不要という点
結び付ける際、LEFT JOIN を用い、また、Q1.注文日>Q2.注文日　とします

Q1.会員番号　Q1.注文日　Q2.会員番号　Q2.注文日
123456　2014/03/23
123456　2014/04/23　123456　2014/03/23
123456　2014/05/23　123456　2014/04/23
123456　2014/05/23　123456　2014/03/23
456789　2014/03/21
456789　2014/04/21　456789　2014/03/21

これを元に、Q1.会員番号, Q1.注文日　でグループ化
その際、
購入回数は、Count(Q2.会員番号)+1
LTV は、DateDiff('d', Nz(Max(Q2.注文日),Q1.注文日), Q1.注文日)

表示結果は

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

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

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

※ LTV どちらも提示あった求めたい結果になりませんけど

mitarashi · Answer

#1です。下記の様な1万件のテストデータで試してみました。
会員は1000人になる様に生成してあります。会員毎の注文日の重複は無しとしました。
２番目のお題の方で、DoCmd.Openqueryの前後でGetTickCount APIを実行して時間を求めましたが、100msec未満でした。
(Win7Home(64bit)、Acc2010(32bit)、Core i5 3.2GHz）

会員番号　　注文日　　価格　　商品名
171975　　2014/1/23　　1400　　商品85
215570　　2014/1/25　　4000　　商品31
372857　　2014/1/10　　900　　商品70

#2さんの回答をみて、頭に集計クエリをもう一段加えてみても時間は大差ありませんでしたが、この様な多段階のクエリは、正しい実行時間評価ではなくなってしまうのかと疑問を抱きました。いずれにしても心配した程時間はかかりませんでしたので報告まで。
なお、会員番号は長整数型でも、文字列型でも実行時間に差はありませんでした。

ところで、各クエリでSQLの最後にORDER BY 会員番号を足すのが適当でしたので補足させていただきます。

mitarashi · Answer

購入回数→会員番号毎に、日付順につけた連番と考えるとD系関数で出来るのではないでしょうか。文字列型の連番生成くらいにしか使った事が無いのですが、トライしてみました。
テーブル、クエリ名は安直につけてあります。
１．最初のお題
SELECT Table1.会員番号, DCount("*","Table1","注文日<=#" & [注文日] & "# and 会員番号=" & [会員番号]) AS 購入回数, Table1.価格, Table1.注文日, DSum("価格","Table1","注文日<=#" & [注文日] & "# and 会員番号=" & [会員番号]) AS 累計
FROM Table1;
後で使う都合上注文日のフィールドも追加しています。気に入らなければ２で使い回しをしない専用のクエリを作成して下さい。

２．次のお題
SELECT query1.会員番号, query1.購入回数, query1.注文日, IIf([購入回数]=1,0,[注文日]-DLookUp("[注文日]","query1","購入回数=" & [購入回数]-1 & " and 会員番号=" & [会員番号])) AS LTV
FROM query1;
LTVは会員毎の、その回の注文日－前の注文日で計算しています。結果はご質問の数値と違っております。
こちらは注文日の表示の必要は無かったですが、時間切れなのであしからず。

D系関数は遅いという評判ですが、前述の様な経験値なので、実用的かどうかはわかりかねます。ご参考まで。

ACCESSデータは５０００以上ある（抽出の件）

#1です。

以下の雰囲気でどうなりますか

購入回数→会員番号毎に、日付順につけた連番と考えるとD系関数で出来るのではないでしょうか。

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

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

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

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