ACCESSデータで累計を表現したい
- ACCESSデータは5000以上ある(抽出の件)
- ACCESSテーブルには注文日、会員番号、商品名、価格の情報が格納されています
- 会員番号ごとの購入回数や累積価格、LTV(前回の注文日からの経過日数)を求める方法を教えてください
- ベストアンサー
ACCESSデータは5000以上ある(抽出の件)
データは5000件以上あるのですが、以下で対応は難しいのではないでしょうか。 ソートでできますでしょうか?↓ http://okwave.jp/qa/q8755953.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ナンバーをつけることも可能 ※ACCESS2010を利用中 お手数ですが御教授いただけるとたすかります。。
- jordan232011
- お礼率35% (53/148)
- オフィス系ソフト
- 回答数3
- ありがとう数4
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
以下の雰囲気でどうなりますか 大量のデータでは確認していないので、遅かったら捨ててください。 テーブル名を、★★ と仮定します 1) クエリの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 2) クエリの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.注文日 ; 同一会員が同じ日に複数注文する事があるとして 会員番号、注文日でグループ化しておきます ここまでは、1)とほぼ同じで、価格は不要という点 結び付ける際、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 どちらも提示あった求めたい結果になりませんけど
その他の回答 (2)
- mitarashi
- ベストアンサー率59% (574/965)
#1です。下記の様な1万件のテストデータで試してみました。 会員は1000人になる様に生成してあります。会員毎の注文日の重複は無しとしました。 2番目のお題の方で、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
- ベストアンサー率59% (574/965)
購入回数→会員番号毎に、日付順につけた連番と考えるとD系関数で出来るのではないでしょうか。文字列型の連番生成くらいにしか使った事が無いのですが、トライしてみました。 テーブル、クエリ名は安直につけてあります。 1.最初のお題 SELECT Table1.会員番号, DCount("*","Table1","注文日<=#" & [注文日] & "# and 会員番号=" & [会員番号]) AS 購入回数, Table1.価格, Table1.注文日, DSum("価格","Table1","注文日<=#" & [注文日] & "# and 会員番号=" & [会員番号]) AS 累計 FROM Table1; 後で使う都合上注文日のフィールドも追加しています。気に入らなければ2で使い回しをしない専用のクエリを作成して下さい。 2.次のお題 SELECT query1.会員番号, query1.購入回数, query1.注文日, IIf([購入回数]=1,0,[注文日]-DLookUp("[注文日]","query1","購入回数=" & [購入回数]-1 & " and 会員番号=" & [会員番号])) AS LTV FROM query1; LTVは会員毎の、その回の注文日-前の注文日で計算しています。結果はご質問の数値と違っております。 こちらは注文日の表示の必要は無かったですが、時間切れなのであしからず。 D系関数は遅いという評判ですが、前述の様な経験値なので、実用的かどうかはわかりかねます。ご参考まで。
お礼
丁寧な回答にこころより御礼申し上げます。 いただいた方法を試してみます。
関連するQ&A
- ACCESSを活用して累計(経過)を表現したい
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ナンバーをつけることも可能 ※ACCESS2010を利用中 お手数ですが御教授いただけるとたすかります。。
- 締切済み
- オフィス系ソフト
- ●最終系:ACCESSクエリーの件!懇願mm
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)2つを表示できないでしょうか。 お知恵をいただければ幸いですmm 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
- ベストアンサー
- その他MS Office製品
- ACCESS SQLクエリーの活用について
以下データ群(テーブル名: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 そして、 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.注文日 ; 達人からアドバイスいただいていた 上記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 と出ます(GOOD!!!)。 上記に付随して、 このクエリーが参照しているテーブルの他の列(例:氏名、都道府県)もある(集計対象ではない)場合でそれも追加で(列名:氏名、都道府県を)表現するためには、 SELECT Q4.注文日, Q4.会員番号, Q4.商品名, Q3.購入回数, Q3.LTV の構文箇所に2つの列名も手打ちで追加する必要がありますでしょうか。 (テーブル列名すべてを表示する・・といった個別に列名を指定しないで表示する方法はあるのでしょうか<列名が多いときに手間が若干かかる)
- ベストアンサー
- その他MS Office製品
- 歯抜けとなっている特定の会員だけを特定したい
注文日 会員番号 商品名 価格 注文番号 購入回数 2014/3/2 123345678 お好み焼き 550 201401123 1 上記行毎の注文データがxlsで1万件(行)あります。 (「購入回数」は日別で注文された累積件数をあらわします。1=初回 2=2回目 最大5迄ふられている) <問題> 同じ会員番号のデータで、購入回数[1]がないにもかかわらず、購入回数2以降~から存在する不要な データが混ざっています。 この購入回数1がないのに、同じ会員番号で購入回数が2~5いずれかをふられている行(会員)だけを ピックアップする方法はないでしょうか。xls上でもアクセスにインポートしてからの 処理でもどちらでもかまわないのですが。 いわゆるこの不要データを削除(クリーニング)したいのです。 お知恵をいただけるとありがたいですmm
- ベストアンサー
- Excel(エクセル)
- 再 ACCESSのソートについて(必要なものだけ
テーブルにあるデータを以下のようにクエリーでソートしたく。 会員番号をユニークIDとする ○テーブルにあるデータ 注文日 会員番号 購入額 購入回数 2014/03/05 12345678 3675 10 2014/03/05 12345678 3675 9 2014/03/05 98765241 3675 3 上記について会員番号が同一であれば、購入回数が 最大のものだけを行表示させる(以下)ことは 可能でしょうか。 注文日 会員番号 購入額 購入回数 2014/03/05 12345678 3675 10 2014/03/05 98765241 3675 3 会員番号が重複している行は購入回数が最大の数の行だけ残したい。 (重複ありなし混在で1万ほどレコードがある) お手数をおかけします!
- ベストアンサー
- オフィス系ソフト
- ACCESSかエクセルで抽出したいmm(再掲載)
以下出荷データ(xls.)があるのですが、同じ「会員番号」で見て、購入回数別で 「商品名・区分の両方ないしはいずれか」が1回目と変わっているデータ(会員番号)は 1回目含めて除外、ないしは目印をつけたいの ですが、EXCEL、ないしはACCESSで行う方法ないでしょうか。 ※浮気をしないで一貫しておなじ「商品名・区分」を貫いているお客(恋人)だけを 引っ張り出したいmm 購入回数1しかない顧客は浮気対象ではない(除外しない) 以下は出荷履歴の一部を切り取ったものです(これを活用したい) EXCELでできてしまうのか?ACCESS SQLがいいのか? 注文日 会員番号 商品名 区分 購入回数 2014/03/01 12345678 セレクト 通常購入 1 2014/04/01 98765432 通常商品 定期購入 1 2014/04/10 33333333 通常商品 通常購入 1 2014/05/20 77777777 セレクト 通常購入 1 2014/06/20 77777777 セレクト 通常購入 2 2014/07/20 77777777 セレクト 通常購入 3 2014/08/20 77777777 シリーズ 通常購入 4 2014/09/20 77777777 セレクト 通常購入 5 方法悩んでおりますmmmm 商品名・区分はそれぞれ商品名が上記3種類、区分は2種類しかないので、 これを数字に変換してもかまわない(整理ができればいいので)。 さすがに難しいですよねmmmm
- ベストアンサー
- Excel(エクセル)
- Access 重複するデータの処理
Access2007を利用して以下の様な事が出来ませんか? 会員番号,来店月 10000,201004 10000,201005 10000,201006 10001,201004 10001,201006 こういったデータを 会員番号,来店月 10000,201004,201005,201006 10001,201004,201006 この様に、グループ化される会員番号の行にそれぞれ配置したいのですが、方法を思いつきません。 また、以下の方法でも良いです。 会員番号,来店月 10000,201004201005201006 10001,201004201006 1列として、処理してしまう。 こちらも方法は私では思いつきませんでした。 よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- ACCESSクエリーについて(条件抽出等)
以下のACCCESSテーブルデータがあります。 ID 利用日 利用回数 利用金額 c0096 2013/10/02 1 2000 c0096 2013/10/04 2 3000 c0096 2013/10/11 3 4000 c0096 2013/11/11 4 2200 c0096 2013/11/22 5 1000 c0096 2013/12/02 6 4000 c0135 2013/12/03 1 2100 c0135 2013/12/07 2 4100 これをIDがかぶっているものについては1行とし、 かつ以下、 ・直近利用日 ・利用回数・・・同一ID行数で判断 ・利用金額・・・累積額 ・経過日数・・・直近利用日 - 一番古い利用日 の条件でクエリー抽出したいのですが、可能でしょうか。 (SQL使用を希望) ※難しければ経過日数の箇所はなくても構わないです。 イメージ ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ ID 直近利用日 利用回数 利用金額 経過日数 c0096 2013/12/02 6 16200 61 c0135 2013/12/07 2 6200 4
- ベストアンサー
- その他MS Office製品
- Access2007で会員番号の利用回数をカウント
Access2007を用いて会員番号の利用回数をカウントしたいのですが御教授頂けないでしょうか? 下記のように会員番号ごとにカウントしたいです。 会員番号 - カウント値 1001 - 1 1002 - 1 1003 - 1 1003 - 2 1003 - 3 1004 - 1 1005 - 1 1005 - 2 よろしく御願いします。
- ベストアンサー
- その他(データベース)
- Accessで会員番号の利用回数をカウントしたい
皆様。こんにちは。 Access2007を用いて会員番号の利用回数をカウントしたいのですが御教授頂けないでしょうか? 下記のように会員番号ごとにカウントしてしたいです。 会員番号 - カウント値 1001 - 1 1002 - 1 1003 - 1 1003 - 2 1003 - 3 1004 - 1 1005 - 1 1005 - 2 よろしく御願いします。
- 締切済み
- その他(ITシステム運用・管理)
お礼
ありがとうございます。 一旦以下、 http://oshiete.goo.ne.jp/qa/8757208.html 累積回数、LTVに絞りたいのですが可能でしょうかMMMMM (金額箇所は不要)