SQL文が不明です。来訪者の来訪頻度を得たいのですが。
- 評価
- クリップ 0
- VIEW 940

退会済みユーザー
DBは、MySQLです。
JAVAの中で、SQL文を書いているのですが、
抽出条件が難しくて、SQL文をかけません。
どのように記述すればよろしいでしょうか?
抽出したい条件は、
0.関数パラメーターで指定した会社IDの会社について、
1.2ヶ月前からの来訪者の、
2.来訪回数の多い順に、
3.来訪日の大きい順に、
4.来訪者でユニークになるように
5.TableAとTableBの全項目を
抽出する。
6.もうSQL文的に、この条件を入れるのは、無理だとは思いますが、ここ一ヶ月来社していない人は、除外したいです。
情報:
1.TableAとTableBの紐付けは、
TableB.tableA_id = TableA.tableA_id。
2.レコードで、delete_flag = trueのものは、除外(レコードの論理削除したという印なので)。
3.TableAは、顧客マスタ。=顧客マスタID=お客様でユニーク。お客様の会社IDの項目もあり。
4.TableBは、来訪マスタ。=お客様が来た回数分のレコード(顧客マスタIDと日付、その他を持っている。
5.TableBは、来訪マスタIDでユニーク。
6.TableBは、同じ顧客マスタIDが、複数存在する。=来訪回数分。
下記SQLへ与えるパラメーター:iPara_company_id 会社ID
動かないですが、イメージ的に雰囲気っぽいものを
下記に、書いてみました。
=========================================================
// 2ヶ月前からの、来訪者の、来訪回数の多い順に、来訪日の大きい順に、来訪者でユニークになるように抽出する。
"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)"
// 2ヶ月前からの来訪者を、最新の来訪日で、一意で取り出す。
+ " 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
+ " )"
// 2ヶ月前からの来訪者の、来訪数をカウントし、多い順で、一意で取り出す。
+ " 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
入社してから、1週間インフルエンザで休んだだけで、土日祝日、ゴールデンウィークとか一回も休んでないです。毎日の仕事が勉強なので、前進あるのみぃ~!!
※実は、夏は、暑いので、土日も、会社の寒い冷房くらいが、ここちよくて、避暑地として、
会社で仕事してます(笑)。
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
ポイントは、「6.TableBは、同じ顧客マスタIDが、複数存在する。=来訪回数分。」だと思っています。
1)ここをカウントし、最近来た過去の一番一回レコードで、
2)顧客マスタを紐づけて、
3) 顧客が重ならないように抽出。
となるのが最適と思っています。
いただいたSQLでやってみます。
今度は、2つに分けたほうがいいかもしれないですね。
来訪マスタで、来訪数の多いお客様のIDでユニークなレコードを取り出して(どこへ保存???)
そのIDで、顧客様を抽出(そんなにたくさんの、顧客IDをWHEREに指定できる??)
やはり2つに分割は、難しそうですね。
2015/08/05 10:40 編集
dateは、datetime型だから、エラーになることに。気づくのおそっ!!
まだ、エラー取り中です。
やりたいことは、下記です。
「2ヶ月前からの、来院者の、来院回数の多い会社を、予約日の大きい順に、来院者でユニークになるように抽出する。」
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 編集
エラーが全部とれたのですが、抽出が常に0件なので、
どこかを触っているうちに、また、Existエラーとか、sintacsエラーとか、
いやいや、EXISTとか使ってないし?というエラーがでて、
また、エラー取りにもどりました。
初めてです、この微妙なプログラム??
もう当然、長くやっているので、
どの処理で何をやっているかを把握しているのですが、
それでも、エラーがでて。。。
SQLは、難しい???
もっと難しい、SQLは、サクサク動いているのに...
バックアップしておけばよかったと後悔してます...
毎日、朝、昼、夜と、JSPとJAVA開発の合間に、
ずっとまだ格闘しております。
当然、土日も出社して、格闘します。
2015/08/09 17:09
抽出したいものは、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
今は、動いた結果は、
「来社者でユニークに、来た回数の多い順に抽出される。だが、どれか特定できないがある日の来訪日のデータが抽出される。」
というところです。
あと、最大の来訪日で、抽出されるようにするだけなのですが、朝からやってて、何もかわらないです。 SELECT に MAX(date) をつければ、最大日が抽出されるかとおもったのですが、抽出順に、まったく変化なしで、驚きです。多少は、何か変化があってもいいと思うのですが。
2015/08/09 17:32 編集
本来の抽出目的は、「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
ですが、
とりあえず、できたのは、
「来社者でユニークに、来た回数の多い順に抽出される。また、今日を含むの過去最大の来訪日のデータが抽出される。」
付け加えたのは、下記の禁断のEXISTS文です。
EXISTSを付けたせいか、明らかに異常に抽出が遅くなりました。
====================================
// 今日に一番近い、過去の予約を抽出する条件
1 + " AND NOT EXISTS ("
2 + " SELECT * FROM 来訪者テーブル as tmp"
3 + " WHERE 来訪者テーブル.来訪者_id = tmp.来訪者_id"
4 + " AND tmp.date <= CURRENT_DATE"
5 + " AND 来訪者.date < tmp.date"
6 + " )"
====================================
2015/08/09 17:34
裏で、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
になるように、毎日、いろいろやってみます。
2015/08/09 17:43 編集
上記SQLの4行目を、今日から過去となっているので、
今日を2か月後にすれば、2か月後の予約から、最大の予約日を抽出になるので、
下記のようにしました。
================================
4 AND (tmp.date <= DATE_ADD(CURRENT_DATE, INTERVAL +2 MONTH))"
================================
事実上、2か月も先に予約するお客様は存在しないので、これで、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
が、達成できました。
「最大の来訪日」は、未来も含めてです。
このSQLの目的は、常連のお客様の抽出 なので、未来に予約があっても、その日も来ていただける日とカウントできます。
あとは、EXISTS で、異常に遅くなったのを改善できれば、良いのですが...
2015/08/09 18:51
1つのSQLで挙動が遅いようなら、あえて2つに分けるのも手ですね。
最大の来訪日のデータは別SQLで取るようにすればよいかもしれません。
2015/08/10 08:18
でも、その2つに分ける方法がわからないのです。
というのは、一つのSQL文で、何全件にもなるデータを
どうやって、何件になるが随時変化、どこに保存しておく、
など課題があって、複数にわけれないのです。
一応、本件は、クローズさせていただきます。
長い期間、私の格闘にお付き合い、良いアドバイスや、SQL文のご提供をありがとうございました。
本当に、心底たすかっており、感謝で一杯です。