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

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

ただいまの
回答率

90.12%

MySQLの日付と来社回数のSQL文が不明

解決済

回答 5

投稿 編集

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

退会済みユーザー

MySQLをJAVAで組むのに詰まっています。

■抽出したいもの
1.来社した人の回数と期間で、よく来社した人を
2.「1.」の人をTableAのcustomer_idでユニークに、
を上位から抽出したい。


下記のような構想SQLを考えたのですが、動くわけもありません。

どのように、SQL文を組めばよろしいでしょうか?


※テーブルは、2つで、
・TableBは、日付と来た人と、来た回数分だけのレコードを保持しています。
・TableAは、人の名前や住所などが入っています。

※TableBには、多数の人のレコードが入っており、
  Aさんの場合だと、Aさんが来た数だけレコード数が入っています。

==================================
SQL TableA.*, TableB.*
FROM TableA JOIN TableB ON
  (TableA.index == TableB.index)
AND (TableA.name == TableB.name) 
AND (TableB.delete_flag == false)
WHERE
(COUNT5回 <= (SELECT COUNT(*) FROM TableB WHERE (TableB.date >= (CURDATE() - 14日))))
※2週間に、5回以上来た人を抽出したい。

AND

(COUNT8回 <= (SELECT COUNT(*) FROM TableB WHERE (TableB.date >= (CURDATE() - 60日))))
※2ヶ月に、8回以上来た人を抽出したい。

AND

(COUNT5回 <= (SELECT COUNT(*) FROM TableB WHERE (TableB.date >= (CURDATE() - 180日)))
※半年に、5回以上来た人を抽出したい。

OBDER BY COUNT DESK;

==================================



テーブル構造
==========================================================
■TableA : お客様情報

primary key
  ↓
customer_id company_id name   tel    address   ・・・・・
  1           1        山田   090-~ 東京都港区
  2           1        沢田   03-~  東京都千代田区
  3           1        菊池   03-~  東京都中央区
  4           2        鈴木   03-~  東京都渋谷区
  5           2        小林   03-~  東京都目黒区
  :           :         :       :       :

----------------------------------------------------------


TableAとTableBは、customer_idとcompany_idで紐付けできるが、複数一致。


■TableB : お客様の来社記録

primary key
  ↓
resertaion_id customer_id company_id date       memo ・・・・・
  1              1            1      2015-07-01 見積もり
  2              1            1      2015-07-07 下見
  3              1            1      2013-12-31 名刺交換
  4              2            1      2015-07-28 プレゼント
  5              2            4      2015-01-01 宝くじキャンペーン
  6              2            4      2013-12-31 大晦日祭り
  :              :            :       :          :

==========================================================





追記:2015/07/28 21:30
=========================================
作ったけど、抽出件数が、常に0件のSQL文。

関数に与える引数 ⇒ iParaCompany_id


            String strSQL =
                    // 2週間に5回以上来た人
                    "SELECT TableA.*, TableB.*"
                    + " FROM TableA"
                    + " JOIN ("
                    + " SELECT"
                    + "   COUNT(*) AS CNT"        // 来た回数
                    + ",  '2週間に5回以上'"
                    + " FROM TableB"
                    + "   WHERE TableB.date > DATE_ADD(CURRENT_DATE() , INTERVAL -2 WEEK)"
                    + "   GROUP BY"
                    + "   HAVING SUM(count) >= 5"
                    // 2ヶ月に8回以上来た人
                    + " UNION ALL"
                    + " SELECT"
                    + "   COUNT(*) AS CNT"        // 来た回数
                    + ", '2か月に8回以上'"
                    + " FROM TableB"
                    + "   WHERE TableB.date > DATE_ADD(CURRENT_DATE() , INTERVAL -2 MONTH)"
                    + "   GROUP BY"
                    + "   HAVING SUM(count) >= 8"
                    // 半年に5回以上
                    + " UNION ALL"
                    + " SELECT"
                    + "   COUNT(*) AS CNT"        // 来た回数
                    + ", '半年に5回以上'"
                    + " FROM TableB"
                    + "   WHERE TableB.date > DATE_ADD(CURRENT_DATE() , INTERVAL -6 MONTH)"
                    + "   GROUP BY"
                    + "   HAVING SUM(count) >= 5"
                    + ") TableB"
                    + " ON TableB.TableA_id = TableA.TableA_id"
                    + " WHERE"
                    + "     (TableB.company_id   = " + iParaCompany_id + ")"    // 会社ID
                    + " AND (TableB.delete_flag = false)"
                    + " AND (TableA.delete_flag = false)"
                    + " ODER BY"
                    + " CNT DESC";
=========================================
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

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

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • kutsulog

    2015/07/28 16:56

    ※テーブルは、2つで、TableBに日付と来た数だけのレコードを保持しています。
    ・TableBの来た数とは同じ人が1日のうちに何回来たか?という意味であっていますか?

    FROM TableA JOIN TableB ON (TableA.index == TableB.index)
    ・TableB.indexがTableA.indexに対応していて誰が来たかを示しているということであっていますか?

    キャンセル

  • 退会済みユーザー

    退会済みユーザー

    2015/07/28 17:25

    そうです、TableBは、多数の人のレコードが入っており、Aさんの場合だと、Aさんが来た数だけレコード数が入っています。

    キャンセル

回答 5

checkベストアンサー

0

こんな感じでしょうか?
SELECT
      A.*
    , B.*
FROM TableA A
JOIN (
    -- 2週間以内に5回以上来社した人を検索
    SELECT
          customer_id  -- <- 結合キーなので省略できません
        , company_id   -- <- 結合キーなので省略できません
        , delete_flag     -- <- 結合する時にデータを絞るために使います省略できません
        , COUNT(*) AS CNT -- 来た回数
        , '2週間に5回以上'  -- <- 検出条件を判別するための文字列です必要なければ省略してください
    FROM TableB
    WHERE date > date_add(current_date, interval -2 week) -- <- 来社日が今日の2週間前より後(2週間以内)
    GROUP BY
          customer_id  -- <- 省略不可
        , company_id  -- <- 省略不可
        , delete_flag    -- <- 省略不可
    HAVING SUM(count) >= 5 -- <- 来社5回以上
    UNION ALL
    -- 2ヶ月以内に8回以上来社した人を検索
    SELECT
          customer_id  -- <- 結合キーなので省略できません
        , company_id   -- <- 結合キーなので省略できません
        , delete_flag     -- <- 結合する時にデータを絞るために使います省略できません
        , COUNT(*) AS CNT -- 来た回数
        , '2か月に8回以上'  -- <- 検出条件を判別するための文字列です必要なければ省略してください
    FROM TableB
    WHERE date > date_add(current_date, interval -2 month)  -- <- 来社日が今日の2ヶ月前より後(2ヶ月以内)
    GROUP BY
          customer_id  -- <- 省略不可
        , company_id  -- <- 省略不可
        , delete_flag    -- <- 省略不可
    HAVING COUNT(*) >= 8  -- <- 来社8回以上
    UNION ALL
    SELECT
          customer_id  -- <- 結合キーなので省略できません
        , company_id   -- <- 結合キーなので省略できません
        , delete_flag     -- <- 結合する時にデータを絞るために使います省略できません
        , COUNT(*) AS CNT -- 来た回数
        , '半年に5回以上'  -- <- 検出条件を判別するための文字列です必要なければ省略してください
    FROM TableB
    WHERE date > date_add(current_date, interval -6 month) -- <- 来社が今日の6ヶ月前より後(半年以内)
    GROUP BY
          customer_id  -- <- 省略不可
        , company_id  -- <- 省略不可
        , delete_flag    -- <- 省略不可
    HAVING COUNT(*) >= 5  -- <- 来社5回以上
) B  -- <- TableBとは別の集合なので実在のテーブル名を別名にはできません
ON A.customer_id = B.customer_id
AND A.company_id = B.company_id
AND B.company_id = [iParaCompany_id]
AND B.delete_flag = false
WHERE A.delete_flag = false
ORDER BY
      CNT DESC

※動作確認はしていません(^^;

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/07/29 20:04

    ご助言ありがとうございます。横からでも縦からでも、大変たすかります。
    ありがとうございます。
    本当にたすかました。ありがとうございます。

    キャンセル

  • 2015/07/29 20:25

    だめです、どこを直しても、コンパイルエラーになるので、
    ちょっと、断念するしかなさそうです。
    私の本来は、ご提供いただいた情報は、極力活用する。ご提供してくださった方の手間を考えると、当たり前のことです。
    ですが、まったくエラー改善に進歩がありません。

    別のSQLを考えるしかなさそうです。

    せっかく、SQL文をご提供いただいたのに、何時間かかっても、それを埋め込めずにエラー...、私、ダメですね、開発者失格というより、人の行為を無駄にして、人として失格です。反省のきもちばかりがあたまをよぎります。

    せっかく、SQL文を書いてくださったのに、本当に申し訳ございません。
    SQL文がわるいのではなく、エラーと特定できない私の不行き届きと技術不足が悪いのです。本当に心から、謝罪しております。ごめんなさい。
    お手数をおかけしました。(泣)

    今から、別のSQLを考えます。

    キャンセル

  • 2015/07/29 20:26

    一日がかりだったのに、断念してしまって...

    キャンセル

0


※テーブルは、2つで、TableBに日付と来た数だけのレコードを保持しています。 テーブルAは、人の名前や住所などが入っています。

JAVA云々関係ない話となりますが、これを読む限りではTableAとTableBを紐づけるものが何もないので、抽出しようがありません。ある程度テーブル構造などを開示いただければ、アドバイスもしやすいかと思います。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/07/29 20:18

    今、丁度、開発の合間にいます。
    他の開発チームが追いついてこれてないので、
    少し余裕があるので、過去に、普通に抽出して仮動作させていた本SQLを
    今のうちに、本来の目的の、抽出ができるように修正しようと
    考え、行動しています。

    JSPとJAVAを作っているので、端末画面より先をいかないといけないのですが、
    画面チームがまだ、追いつけないので、今のうちに。。。です。

    えっ、CNT。。。、ありがとうございます。
    私にとっては、その間違いは、気づきにくいので、教えて頂けて、幸いです。
    お手数をおかけして、申し訳ありません。

    キャンセル

  • 2015/07/29 21:52

    解決済となっていますが、道半ばなようですので。

    一発で取り出そうと思ったら、
    ①2週間に5回以上来たお客様
    ②2か月に8回以上来たお客様
    ③半年に5回以上来たお客様
    という順番でお客様をズラリと並べればよいわけですよね。
    とすれば①②③の順番でUNIONすれば解決…というわけで即興でこんな感じか…。

    ----------
    select
    a.name as 'お客様名',
    b.count(*) as '来社回数',
    'お得意様' as '顧客区分',
    from tableA as a
    inner join tableB as b
    on a.customer_id = b.customer_id
    where
    b.date between date_add(current_date, interval -14 day) and current_date
    and
    '来社回数' >= 5
    group by
    b.customer_id
    order by
    '来社回数' desc
    union
    select
    a.name as 'お客様名',
    b.count(*) as '来社回数',
    '上顧客' as '顧客区分',
    from tableA as a
    inner join tableB as b
    on a.customer_id = b.customer_id
    where
    b.date between date_add(current_date, interval -2 month) and current_date
    and
    '来社回数' >= 8
    group by
    b.customer_id
    order by
    '来社回数' desc
    union
    select
    a.name as 'お客様名',
    b.count(*) as '来社回数',
    '顧客' as '顧客区分',
    from tableA as a
    inner join tableB as b
    on a.customer_id = b.customer_id
    where
    b.date between date_add(current_date, interval -6 month) and current_date
    and
    '来社回数' >= 5
    group by
    b.customer_id
    order by
    '来社回数' desc;
    ----------

    動いたらラッキー程度でお願いします。

    キャンセル

  • 2015/07/30 08:27

    ありがとうございます。
    今から、プログラムに埋め込んで、実際にためしてみます。

    キャンセル

0

すでに指摘が出てますけども、テーブルがどんな感じかわからないと厳しいですね。

あくまで想像する範囲ですが・・
TableAとTableBを indexとnameで紐づけしている様に見えはしますが、
いかんせん WHERE条件から以降にはTableBの回数集計のみになっていて、
対象となるべきキーが無いようです。
まずはTableBを 関連付けるためのキー単位で集約(GROUP BY)させてから
TableAにJOINしたほうが良さそうですね。

それぞれやり方はあると思うので、よい方法を探してみてください。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/07/28 17:31

    実際には、結びつけるキーは、3つの複合キーです。
    あまり沢山かくと、ごちゃごちゃになり、みにくくなると思い、
    少し簡潔に書いています。

    キャンセル

  • 2015/07/28 18:13

    kutsulogさんがスバっと書かれてますね(´∀`)
    ぜひとも参考にしてみてください。
    いくら難しい複合キーであっても、
    キーを把握できていれば大丈夫ですよ。

    キャンセル

  • 2015/07/29 08:33

    はい、とても早くかいていただけたし、
    すらばらしい、SQLです。
    世の中には、すごい人がいらっしゃいますね。

    キャンセル

0

ぼくなら、こんな難しいSQLを考えて時間を費やすより、2週間で5回以上きたケース、2ヶ月で8回きたケース、半年で5回きたケースのSQLを別々に発行させてとっとと仕事を進めます。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/07/29 16:16

    そうですか。
    それぞれ条件を与えるとそれに応じた順位をとってくる単純なメソッド作ったほうが効率的で、使い勝手も良くなると思うんですが、ポリシー的なものがあるんでしたら仕方ありません。
    頑張ってください!

    キャンセル

  • 2015/07/29 16:16

    エラーが全部とれ、SQLがうごきました。

    キャンセル

  • 2015/07/29 20:11 編集

    いえ、初心者ですので、ポリシーなんていえるものは、ないです。
    過去の書き込みをみていただければ、わかると思いますが、
    単に、最適を求めているのです。
    ・Java Docの書き方の最適は?(=出力時に、eclipsでエラーがでずに、最良に読みやすい書き方は?)
    ・Java のコーディングの最適は?(=一般的なものは?)
    とつきつめてきました。

    なので、ここでも、一つのSQLで行きたいという願望でいますが、
    それが不可能で、読みにくくく、メンテナンスの悪いものなら、
    気兼ねなく分割法でいきます。
    ※教えていただいたSQL文は、長いだけで、単純なので、難しいとは考えておりません。とはいえ、自分では到底考えつかない、すごいSQL文です。でも、読むほうは大丈夫です。=コメントもいっぱいかけるので、後継者への遺産としても、大丈夫。

    キャンセル

0

期待結果はどんなのでしょうか?

初めに添付されているSQLでは、「2週間に5回以上、且つ2ヶ月に8回以上、且つ半年に5回以上来社」と、全ての条件を満たす人を抽出。(2週間以内に5回来ていても、2ヶ月の合計も5だと集計から漏れる)
そして全ての条件を満たす人を訪問回数の多い順に並べようとしているように見えます。

翻ってSteveG氏のSQLは、「2週間に5回以上か、2ヶ月に8回以上か、半年に5回以上来社」のようにどれかの条件を満たす人を全て抽出。(2ヶ月以内の来社はゼロでも、過去半年に5回以上の来社履歴があれば集計に加える。)
そして、各条件を満たす人をごっちゃ混ぜにして訪問回数の多い順に並べようとしてるように見えます。
※2週間に5回以上来ている人より、半年間に5回以上来ている人のほうが期間が長い分上位に来やすいと思います。また、2週間以内に5回以上来たAさんと、過去半年に5回以上来たAさんのように、同じ人が複数回登場するかも知れません。

yu-ri氏のコメントにあるSQLは、「2週間に5回以上の来社のグループと、2ヶ月に8回以上来社のグループと、半年に5回以上来社のグループ」を別々に集計して、それぞれのランキングを作成する。という方向に見えます。

現在作成中のコードはSteveG氏のSQLを元にしたものだと思いますが、集計の方向性は大丈夫でしょうか?
添付されている作成中のコードには"GROUP BY"の後ろに集計要素が指定されていないため、シンタックスエラーになりそうです。エラー無く戻りはゼロ件とありましたが、添付ミスでしょうか?

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/07/29 08:31

    添付は、そのままなので、添付ミスはありません。
    レコードのほうを再度確認し、
    ソースコードに、抽出直後に、本当に抽出されていないか、
    System.out.println()を入れて確認中です。

    キャンセル

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

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