Q&A
ありがとうございます。
今から、そのSQLをためしてみます。
お手数をおかけしました。
eclipsで、JSPとJAVAを書いています。
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"; // 来訪日が大きい順
========================================================
回答2件
0
回答がないため、閉じます。
みなさんもできないとうことは、
やはり、技術的に、難しいようなので、
自力で、時間をかけて、なんとかやってみます。
不可能と言われても、前進してやり続ける。
そうしないと、いつまでたっても、実現できないので。
と思っていたら、
ご回答のSQL文をいただいていました。
これでやってみます。
投稿2015/08/03 23:56
編集2015/08/04 00:50退会済みユーザー
総合スコア0
0
ベストアンサー
おっと新しい質問が立ってましたね。
前回のご質問は解決済となっていましたが、補足でSQLをちょっくら作って書いておいたので、よかったらひょっこりのぞいて試してみてください。
何かのヒントになればいいのですが…。
投稿2015/07/29 13:00
総合スコア634
他の機能のJSP＋JAVAを作り始めたので、２，３日、手付かずで申し訳ございません。
今の作業が、終われば、すぐに、SQLを埋め込んで動かしたいと思います。
今日もできそうにないので、土日にやるつもりです。
入社してから、１週間インフルエンザで休んだだけで、土日祝日、ゴールデンウィークとか一回も休んでないです。毎日の仕事が勉強なので、前進あるのみぃ～！！
※実は、夏は、暑いので、土日も、会社の寒い冷房くらいが、ここちよくて、避暑地として、
会社で仕事してます（笑）。
仕事をしすぎると、体がついてきても心がもちませんよ--;
それはさておき、パラメータとして会社IDを与えていますが、目的を見るに特に会社IDは必要ないような気がします。
ある特定の会社（お得意様？）の人の中での来訪者数を知りたいのであれば必要ですが…。
「０．関数パラメーターで指定した会社IDの会社について、」を追加しました。
特定の会社のデータを抽出したいので。
tableAとtableBが1対多な状態で、tableAとtableBを1対1であるかのように全項目を引っ張ってくるのは難しいかと思います。
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;
…うーんこんな感じでしょうか。
御多分に漏れず、動いたらラッキーでお願いします。
SQL文ありがとうございます。
ポイントは、「６．TableBは、同じ顧客マスタIDが、複数存在する。＝来訪回数分。」だと思っています。
１）ここをカウントし、最近来た過去の一番一回レコードで、
２）顧客マスタを紐づけて、
3) 顧客が重ならないように抽出。
となるのが最適と思っています。
いただいたSQLでやってみます。
今度は、２つに分けたほうがいいかもしれないですね。
来訪マスタで、来訪数の多いお客様のIDでユニークなレコードを取り出して（どこへ保存？？？）
そのIDで、顧客様を抽出（そんなにたくさんの、顧客IDをWHEREに指定できる？？）
やはり２つに分割は、難しそうですね。
SQL文で、date_addの行でシンタックスエラーがでているので、あれこれやったり、インターネット調べているうちに気づきました。
dateは、datetime型だから、エラーになることに。気づくのおそっ！！
まだ、エラー取り中です。
やりたいことは、下記です。
「２ヶ月前からの、来院者の、来院回数の多い会社を、予約日の大きい順に、来院者でユニークになるように抽出する。」
ああ、datetime型だったんですね…。
ではdateとしている日付の部分を、
date_format(date, '%Y-%m-%d')
と型キャストしてみてください。
フォローありがとうございます。
大変たすかります。
yu-riさんがいなかったら、今までのSQLもできなくて、
JAVAでゴリゴリロジックを書いていて、
なんだこのロジックの塊は！！
となっていたところです。
本当にありがとうございます。
なお、別の質問で、閉じたにも関わらず、もう来るな的なご指摘がありましたので、
私は、初心者過ぎて、文章も支離滅裂で、目障りなようです。
ですので、ここを最後の質問とさせていただきます。
yu-riさんや、ほかの教えてくださった皆様がすきでしたし、
わからないことをズバッとわかる人がいるってことに
驚いてとても、良い技術の掲示板だとおもって、ずっと利用させて
いただこうと思っていたのに、とても悲しいです（泣）。
うーん…そんな心無い人がいるのですか…。
誰もが皆最初は初心者ですし、気に留める必要はないと思いますよ私は。
初心者の方が斬新なアイデアが出ることもありますし、熟練者がミスすることだってあります。結局のところ持ちつ持たれつかと。
とはいえ、とりあえずは目の前の課題をクリアしましょう。
はい、今もエラー取りに、頑張ってます。
いつもサポートありがとうございます。
がんばります。
ロジックの塊になるのは悪ではないですよ。
最初はSQLなんて未知の領域ですから、必要最低限の情報だけ取ってきてロジックで整理したほうが、他人が見ても理解が早いかと思いますので。
ちなみにエラーは具体的にどのあたりでどんなエラーが出ておりますでしょうか。
やったぁ～！！、今日も朝早くから頑張っていたんですが、今、エラーがなくなりました。
日にちが経ってしまったので、レコードを作り直して、テストしてみます。
うれしぃ～！！（笑顔）
エラーは、シンタックスエラーや構文エラーです。
でも、この変がエラーという感じで例外がでるので、実際のエラーの箇所は、どこなのかが、わからなかったのです。
しかたないので、一項目ずつ置き換えたり、コメントアウトしたり、
を何度も何度も繰り返して．．．
例外さんが、これがダメ！！とはっきり、エラーのテーブル項目名や、型が違う！！、使えないキーワード！！、カンマがない！！ とか、出してくれると助かったのですが。
おめでとうございます^^
無事に進むとよいですね。
また分からないことがあればどんどん聞けばよいかと思いますよ。
ご連絡がおそくなってすみません。
エラーが全部とれたのですが、抽出が常に０件なので、
どこかを触っているうちに、また、Existエラーとか、sintacsエラーとか、
いやいや、EXISTとか使ってないし？というエラーがでて、
また、エラー取りにもどりました。
初めてです、この微妙なプログラム？？
もう当然、長くやっているので、
どの処理で何をやっているかを把握しているのですが、
それでも、エラーがでて。。。
SQLは、難しい？？？
もっと難しい、SQLは、サクサク動いているのに．．．
バックアップしておけばよかったと後悔してます．．．
毎日、朝、昼、夜と、JSPとJAVA開発の合間に、
ずっとまだ格闘しております。
当然、土日も出社して、格闘します。
SQL動いて、抽出できるようになりました。
抽出したいものは、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
今は、動いた結果は、
「来社者でユニークに、来た回数の多い順に抽出される。だが、どれか特定できないがある日の来訪日のデータが抽出される。」
というところです。
あと、最大の来訪日で、抽出されるようにするだけなのですが、朝からやってて、何もかわらないです。 SELECT に MAX(date) をつければ、最大日が抽出されるかとおもったのですが、抽出順に、まったく変化なしで、驚きです。多少は、何か変化があってもいいと思うのですが。
一応、まあまあのが、できました。
本来の抽出目的は、「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
ですが、
とりあえず、できたのは、
「来社者でユニークに、来た回数の多い順に抽出される。また、今日を含むの過去最大の来訪日のデータが抽出される。」
付け加えたのは、下記の禁断のEXISTS文です。
EXISTSを付けたせいか、明らかに異常に抽出が遅くなりました。
====================================
// 今日に一番近い、過去の予約を抽出する条件
１ + " AND NOT EXISTS ("
２ + " SELECT * FROM 来訪者テーブル as tmp"
３ + " WHERE 来訪者テーブル.来訪者_id = tmp.来訪者_id"
４ + " AND tmp.date <= CURRENT_DATE"
５ + " AND 来訪者.date < tmp.date"
６ + " )"
====================================
とりあえず、これで妥協して、プログラムを他者へも公開し、
裏で、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
になるように、毎日、いろいろやってみます。
ひらめきました！！
上記SQLの４行目を、今日から過去となっているので、
今日を２か月後にすれば、２か月後の予約から、最大の予約日を抽出になるので、
下記のようにしました。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
４ AND (tmp.date <= DATE_ADD(CURRENT_DATE, INTERVAL +2 MONTH))"
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
事実上、２か月も先に予約するお客様は存在しないので、これで、
「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」
が、達成できました。
「最大の来訪日」は、未来も含めてです。
このSQLの目的は、常連のお客様の抽出 なので、未来に予約があっても、その日も来ていただける日とカウントできます。
あとは、EXISTS で、異常に遅くなったのを改善できれば、良いのですが．．．
とりあえずは動いたようでなによりです。
1つのSQLで挙動が遅いようなら、あえて2つに分けるのも手ですね。
最大の来訪日のデータは別SQLで取るようにすればよいかもしれません。
はい、アドバイスありがとうございます。
でも、その２つに分ける方法がわからないのです。
というのは、一つのSQL文で、何全件にもなるデータを
どうやって、何件になるが随時変化、どこに保存しておく、
など課題があって、複数にわけれないのです。
一応、本件は、クローズさせていただきます。
長い期間、私の格闘にお付き合い、良いアドバイスや、SQL文のご提供をありがとうございました。
本当に、心底たすかっており、感謝で一杯です。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。