ACCESSデータで累計を表現したい

2014/09/15 22:20

このQ&Aのポイント

ACCESSデータは５０００以上ある（抽出の件）
ACCESSテーブルには注文日、会員番号、商品名、価格の情報が格納されています
会員番号ごとの購入回数や累積価格、LTV（前回の注文日からの経過日数）を求める方法を教えてください

jordan232011
お礼率35% (53/148)

オフィス系ソフト
回答数3
ありがとう数4

みんなの回答 （3）
専門家の回答

質問者が選んだベストアンサー

ベストアンサー

30246kiku
ベストアンサー率73% (370/504)

2014/09/16 17:05 回答No.2

以下の雰囲気でどうなりますか大量のデータでは確認していないので、遅かったら捨ててください。テーブル名を、★★ と仮定します１）クエリの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 どちらも提示あった求めたい結果になりませんけど

質問者

お礼 2014/09/16 22:15

ありがとうございます。一旦以下、 http://oshiete.goo.ne.jp/qa/8757208.html 累積回数、LTVに絞りたいのですが可能でしょうかMMMMM （金額箇所は不要）

その他の回答 (2)

mitarashi
ベストアンサー率59% (574/965)

2014/09/17 22:55 回答No.3

#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 会員番号を足すのが適当でしたので補足させていただきます。

質問者

お礼 2014/09/17 23:29

丁寧にありがとうございます！

mitarashi
ベストアンサー率59% (574/965)

2014/09/16 00:49 回答No.1

購入回数→会員番号毎に、日付順につけた連番と考えると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系関数は遅いという評判ですが、前述の様な経験値なので、実用的かどうかはわかりかねます。ご参考まで。