SQL文が不明です。来訪者の来訪頻度を得たいのですが。
- 評価
- クリップ 0
- VIEW 849
退会済みユーザー
DBは、MySQLです。
JAVAの中で、SQL文を書いているのですが、
抽出条件が難しくて、SQL文をかけません。
どのように記述すればよろしいでしょうか？
抽出したい条件は、
０．関数パラメーターで指定した会社IDの会社について、
１．２ヶ月前からの来訪者の、
２．来訪回数の多い順に、
３．来訪日の大きい順に、
４．来訪者でユニークになるように
５．TableAとTableBの全項目を
抽出する。
６．もうSQL文的に、この条件を入れるのは、無理だとは思いますが、ここ一ヶ月来社していない人は、除外したいです。
情報：
１．TableAとTableBの紐付けは、
TableB.tableA_id = TableA.tableA_id。
２．レコードで、delete_flag = trueのものは、除外（レコードの論理削除したという印なので）。
３．TableAは、顧客マスタ。＝顧客マスタID＝お客様でユニーク。お客様の会社IDの項目もあり。
４．TableBは、来訪マスタ。＝お客様が来た回数分のレコード（顧客マスタIDと日付、その他を持っている。
５．TableBは、来訪マスタIDでユニーク。
６．TableBは、同じ顧客マスタIDが、複数存在する。＝来訪回数分。
下記SQLへ与えるパラメーター：iPara_company_id 会社ID
動かないですが、イメージ的に雰囲気っぽいものを
下記に、書いてみました。
=========================================================
// ２ヶ月前からの、来訪者の、来訪回数の多い順に、来訪日の大きい順に、来訪者でユニークになるように抽出する。
"SELECT TableB.*, TableA.*"
+ " FROM TableB JOIN TableA ON TableB.tableA_id = TableA.tableA_id" // TableAとBを連結
+ " WHERE"
+ " (TableA.company_id = " + iPara_company_id + ")" // 会社ID
+ " AND (TableB.delete_flag = false)"
+ " AND (TableA.delete_flag = false)"
// ２ヶ月前からの来訪者を、最新の来訪日で、一意で取り出す。
+ " AND (TableB.tableA_id, TableB.date)"
+ " IN ("
+ " SELECT TableB.tableA_id, max(TableB.date) FROM TableB"
+ " WHERE TableB.date > DATE_ADD (CURRENT_DAY, INTERVAL -2 MONTH)"// 2ヶ月前より
+ " GROUP BY TableB.tableA_id" // 来訪者ID
+ " )"
// ２ヶ月前からの来訪者の、来訪数をカウントし、多い順で、一意で取り出す。
+ " AND COUNT(TableB.tableA_id) AS visitCount"
+ " IN ("
+ " SELECT COUNT(TableB.tableA_id) FROM TableB"
+ " WHERE TableB.date > DATE_ADD (CURRENT_DAY, INTERVAL -2 MONTH)"// 2ヶ月前より
+ " ORDER BY visitCount DESC" // 来訪数が大きい順
+ " GROUP BY TableB.tableA_id" // 来訪者ID
+ " )"
+ " ORDER BY TableB.date DESC"; // 来訪日が大きい順
========================================================
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
checkベストアンサー
0
前回のご質問は解決済となっていましたが、補足でSQLをちょっくら作って書いておいたので、よかったらひょっこりのぞいて試してみてください。
何かのヒントになればいいのですが…。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
-7
みなさんもできないとうことは、
やはり、技術的に、難しいようなので、
自力で、時間をかけて、なんとかやってみます。
不可能と言われても、前進してやり続ける。
そうしないと、いつまでたっても、実現できないので。
と思っていたら、
ご回答のSQL文をいただいていました。
これでやってみます。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
2015/07/30 08:13
今から、そのSQLをためしてみます。
お手数をおかけしました。
2015/07/31 16:39
今の作業が、終われば、すぐに、SQLを埋め込んで動かしたいと思います。
2015/07/31 19:33
入社してから、１週間インフルエンザで休んだだけで、土日祝日、ゴールデンウィークとか一回も休んでないです。毎日の仕事が勉強なので、前進あるのみぃ～！！
※実は、夏は、暑いので、土日も、会社の寒い冷房くらいが、ここちよくて、避暑地として、
会社で仕事してます（笑）。
2015/07/31 22:04
それはさておき、パラメータとして会社IDを与えていますが、目的を見るに特に会社IDは必要ないような気がします。
ある特定の会社（お得意様？）の人の中での来訪者数を知りたいのであれば必要ですが…。
2015/08/03 09:47
特定の会社のデータを抽出したいので。
2015/08/03 23:07 編集
Bに例えば来訪目的などというカラムがあって、それも全部表示したいとなるとどうしても来訪者でユニークにはなりません。
（来訪者の「ご挨拶」、来訪者の「商談」など際限なくなってしまいますね…）
Aの全項目だけなら多分引っ張れます。
select a.* from tableA as a
inner_join (
select tableA_id, count(*)as cnt, max(date) as last_visit_date from tableB
where date >= date_add(current_date, INTERVAL -2 month)
group by tableA_id
order by cnt desc
and last_visit_date desc
) as b
on a.id = b.tableA_id
where a.company_id = iPara_company_id
and a.delete_flg = false
and b.delete_flg = false
order by b.cnt desc
and b.last_visit_date desc;
…うーんこんな感じでしょうか。
御多分に漏れず、動いたらラッキーでお願いします。
2015/08/05 06:32
ポイントは、「６．TableBは、同じ顧客マスタIDが、複数存在する。＝来訪回数分。」だと思っています。
１）ここをカウントし、最近来た過去の一番一回レコードで、
２）顧客マスタを紐づけて、
3) 顧客が重ならないように抽出。
となるのが最適と思っています。
いただいたSQLでやってみます。
今度は、２つに分けたほうがいいかもしれないですね。
来訪マスタで、来訪数の多いお客様のIDでユニークなレコードを取り出して（どこへ保存？？？）
そのIDで、顧客様を抽出（そんなにたくさんの、顧客IDをWHEREに指定できる？？）
やはり２つに分割は、難しそうですね。
2015/08/05 10:40 編集
dateは、datetime型だから、エラーになることに。気づくのおそっ！！
まだ、エラー取り中です。
やりたいことは、下記です。
「２ヶ月前からの、来院者の、来院回数の多い会社を、予約日の大きい順に、来院者でユニークになるように抽出する。」
2015/08/05 11:28
ではdateとしている日付の部分を、
date_format(date, '%Y-%m-%d')
と型キャストしてみてください。
2015/08/05 14:59
大変たすかります。
yu-riさんがいなかったら、今までのSQLもできなくて、
JAVAでゴリゴリロジックを書いていて、
なんだこのロジックの塊は！！
となっていたところです。
本当にありがとうございます。
なお、別の質問で、閉じたにも関わらず、もう来るな的なご指摘がありましたので、
私は、初心者過ぎて、文章も支離滅裂で、目障りなようです。
ですので、ここを最後の質問とさせていただきます。
yu-riさんや、ほかの教えてくださった皆様がすきでしたし、
わからないことをズバッとわかる人がいるってことに
驚いてとても、良い技術の掲示板だとおもって、ずっと利用させて
いただこうと思っていたのに、とても悲しいです（泣）。
2015/08/05 15:41
誰もが皆最初は初心者ですし、気に留める必要はないと思いますよ私は。
初心者の方が斬新なアイデアが出ることもありますし、熟練者がミスすることだってあります。結局のところ持ちつ持たれつかと。
とはいえ、とりあえずは目の前の課題をクリアしましょう。
2015/08/05 21:17
いつもサポートありがとうございます。
がんばります。
2015/08/05 21:56 編集
最初はSQLなんて未知の領域ですから、必要最低限の情報だけ取ってきてロジックで整理したほうが、他人が見ても理解が早いかと思いますので。
ちなみにエラーは具体的にどのあたりでどんなエラーが出ておりますでしょうか。
2015/08/06 09:01 編集
日にちが経ってしまったので、レコードを作り直して、テストしてみます。
うれしぃ～！！（笑顔）
エラーは、シンタックスエラーや構文エラーです。
でも、この変がエラーという感じで例外がでるので、実際のエラーの箇所は、どこなのかが、わからなかったのです。
しかたないので、一項目ずつ置き換えたり、コメントアウトしたり、
を何度も何度も繰り返して．．．
例外さんが、これがダメ！！とはっきり、エラーのテーブル項目名や、型が違う！！、使えないキーワード！！、カンマがない！！ とか、出してくれると助かったのですが。
2015/08/06 09:25
無事に進むとよいですね。
また分からないことがあればどんどん聞けばよいかと思いますよ。
2015/08/08 08:32 編集
エラーが全部とれたのですが、抽出が常に０件なので、
どこかを触っているうちに、また、Existエラーとか、sintacsエラーとか、
いやいや、EXISTとか使ってないし？というエラーがでて、
また、エラー取りにもどりました。
初めてです、この微妙なプログラム？？
もう当然、長くやっているので、
どの処理で何をやっているかを把握しているのですが、
それでも、エラーがでて。。。
SQLは、難しい？？？
もっと難しい、SQLは、サクサク動いているのに．．．
バックアップしておけばよかったと後悔してます．．．
毎日、朝、昼、夜と、JSPとJAVA開発の合間に、
ずっとまだ格闘しております。
当然、土日も出社して、格闘します。
2015/08/09 17:09
抽出したいものは、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
今は、動いた結果は、
「来社者でユニークに、来た回数の多い順に抽出される。だが、どれか特定できないがある日の来訪日のデータが抽出される。」
というところです。
あと、最大の来訪日で、抽出されるようにするだけなのですが、朝からやってて、何もかわらないです。 SELECT に MAX(date) をつければ、最大日が抽出されるかとおもったのですが、抽出順に、まったく変化なしで、驚きです。多少は、何か変化があってもいいと思うのですが。
2015/08/09 17:32 編集
本来の抽出目的は、「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
ですが、
とりあえず、できたのは、
「来社者でユニークに、来た回数の多い順に抽出される。また、今日を含むの過去最大の来訪日のデータが抽出される。」
付け加えたのは、下記の禁断のEXISTS文です。
EXISTSを付けたせいか、明らかに異常に抽出が遅くなりました。
====================================
// 今日に一番近い、過去の予約を抽出する条件
１ + " AND NOT EXISTS ("
２ + " SELECT * FROM 来訪者テーブル as tmp"
３ + " WHERE 来訪者テーブル.来訪者_id = tmp.来訪者_id"
４ + " AND tmp.date <= CURRENT_DATE"
５ + " AND 来訪者.date < tmp.date"
６ + " )"
====================================
2015/08/09 17:34
裏で、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
になるように、毎日、いろいろやってみます。
2015/08/09 17:43 編集
上記SQLの４行目を、今日から過去となっているので、
今日を２か月後にすれば、２か月後の予約から、最大の予約日を抽出になるので、
下記のようにしました。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
４ AND (tmp.date <= DATE_ADD(CURRENT_DATE, INTERVAL +2 MONTH))"
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
事実上、２か月も先に予約するお客様は存在しないので、これで、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
が、達成できました。
「最大の来訪日」は、未来も含めてです。
このSQLの目的は、常連のお客様の抽出 なので、未来に予約があっても、その日も来ていただける日とカウントできます。
あとは、EXISTS で、異常に遅くなったのを改善できれば、良いのですが．．．
2015/08/09 18:51
1つのSQLで挙動が遅いようなら、あえて2つに分けるのも手ですね。
最大の来訪日のデータは別SQLで取るようにすればよいかもしれません。
2015/08/10 08:18
でも、その２つに分ける方法がわからないのです。
というのは、一つのSQL文で、何全件にもなるデータを
どうやって、何件になるが随時変化、どこに保存しておく、
など課題があって、複数にわけれないのです。
一応、本件は、クローズさせていただきます。
長い期間、私の格闘にお付き合い、良いアドバイスや、SQL文のご提供をありがとうございました。
本当に、心底たすかっており、感謝で一杯です。