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

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

ただいまの
回答率

90.12%

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

解決済

回答 2

投稿 編集

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

退会済みユーザー

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/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.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる