質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.62%

  • MySQL

    5687questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL文が不明です。来訪者の来訪頻度を得たいのですが。

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 818
退会済みユーザー

退会済みユーザー

eclipsで、JSPとJAVAを書いています。
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ページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

0

おっと新しい質問が立ってましたね。
前回のご質問は解決済となっていましたが、補足でSQLをちょっくら作って書いておいたので、よかったらひょっこりのぞいて試してみてください。
何かのヒントになればいいのですが…。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/07/30 08:13

    ありがとうございます。
    今から、そのSQLをためしてみます。
    お手数をおかけしました。

    キャンセル

  • 2015/07/31 16:39

    他の機能のJSP+JAVAを作り始めたので、2,3日、手付かずで申し訳ございません。
    今の作業が、終われば、すぐに、SQLを埋め込んで動かしたいと思います。

    キャンセル

  • 2015/07/31 19:33

    今日もできそうにないので、土日にやるつもりです。
    入社してから、1週間インフルエンザで休んだだけで、土日祝日、ゴールデンウィークとか一回も休んでないです。毎日の仕事が勉強なので、前進あるのみぃ~!!
    ※実は、夏は、暑いので、土日も、会社の寒い冷房くらいが、ここちよくて、避暑地として、
    会社で仕事してます(笑)。

    キャンセル

  • 2015/07/31 22:04

    仕事をしすぎると、体がついてきても心がもちませんよ--;
    それはさておき、パラメータとして会社IDを与えていますが、目的を見るに特に会社IDは必要ないような気がします。
    ある特定の会社(お得意様?)の人の中での来訪者数を知りたいのであれば必要ですが…。

    キャンセル

  • 2015/08/03 09:47

    「0.関数パラメーターで指定した会社IDの会社について、」を追加しました。
    特定の会社のデータを抽出したいので。

    キャンセル

  • 2015/08/03 23:07 編集

    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;

    …うーんこんな感じでしょうか。
    御多分に漏れず、動いたらラッキーでお願いします。

    キャンセル

  • 2015/08/05 06:32

    SQL文ありがとうございます。
    ポイントは、「6.TableBは、同じ顧客マスタIDが、複数存在する。=来訪回数分。」だと思っています。
    1)ここをカウントし、最近来た過去の一番一回レコードで、
    2)顧客マスタを紐づけて、
    3) 顧客が重ならないように抽出。
    となるのが最適と思っています。

    いただいたSQLでやってみます。

    今度は、2つに分けたほうがいいかもしれないですね。
    来訪マスタで、来訪数の多いお客様のIDでユニークなレコードを取り出して(どこへ保存???)
    そのIDで、顧客様を抽出(そんなにたくさんの、顧客IDをWHEREに指定できる??)
    やはり2つに分割は、難しそうですね。

    キャンセル

  • 2015/08/05 10:40 編集

    SQL文で、date_addの行でシンタックスエラーがでているので、あれこれやったり、インターネット調べているうちに気づきました。
    dateは、datetime型だから、エラーになることに。気づくのおそっ!!
    まだ、エラー取り中です。

    やりたいことは、下記です。
    「2ヶ月前からの、来院者の、来院回数の多い会社を、予約日の大きい順に、来院者でユニークになるように抽出する。」

    キャンセル

  • 2015/08/05 11:28

    ああ、datetime型だったんですね…。

    では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

    SQL動いて、抽出できるようになりました。
    抽出したいものは、
    「来社者でユニークに、来た回数の多い順に抽出される。また、最大の来訪日のデータが抽出される。」

    今は、動いた結果は、
    「来社者でユニークに、来た回数の多い順に抽出される。だが、どれか特定できないがある日の来訪日のデータが抽出される。」
    というところです。

    あと、最大の来訪日で、抽出されるようにするだけなのですが、朝からやってて、何もかわらないです。 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文のご提供をありがとうございました。

    本当に、心底たすかっており、感謝で一杯です。

    キャンセル

-7

回答がないため、閉じます。
みなさんもできないとうことは、
やはり、技術的に、難しいようなので、
自力で、時間をかけて、なんとかやってみます。

不可能と言われても、前進してやり続ける。
そうしないと、いつまでたっても、実現できないので。

と思っていたら、
ご回答のSQL文をいただいていました。
これでやってみます。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.62%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • MySQL

    5687questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。