Q&A
※テーブルは、２つで、TableBに日付と来た数だけのレコードを保持しています。
・TableBの来た数とは同じ人が1日のうちに何回来たか?という意味であっていますか?
FROM TableA JOIN TableB ON (TableA.index == TableB.index)
・TableB.indexがTableA.indexに対応していて誰が来たかを示しているということであっていますか?
MySQLをJAVAで組むのに詰まっています。
■抽出したいもの
１．来社した人の回数と期間で、よく来社した人を
２．「１．」の人をTableAのcustomer_idでユニークに、
を上位から抽出したい。
下記のような構想SQLを考えたのですが、動くわけもありません。
どのように、SQL文を組めばよろしいでしょうか？
※テーブルは、２つで、
・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日))))
※２週間に、５回以上来た人を抽出したい。
AND
(COUNT8回 <= (SELECT COUNT(*) FROM TableB WHERE (TableB.date >= (CURDATE() - 60日))))
※２ヶ月に、８回以上来た人を抽出したい。
AND
(COUNT5回 <= (SELECT COUNT(*) FROM TableB WHERE (TableB.date >= (CURDATE() - 180日)))
※半年に、５回以上来た人を抽出したい。
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
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
作ったけど、抽出件数が、常に０件のSQL文。
関数に与える引数 ⇒ iParaCompany_id
String strSQL = // ２週間に５回以上来た人 "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" // ２ヶ月に８回以上来た人 + " 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";
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
そうです、TableBは、多数の人のレコードが入っており、Aさんの場合だと、Aさんが来た数だけレコード数が入っています。
回答5件
0
ぼくなら、こんな難しいSQLを考えて時間を費やすより、２週間で５回以上きたケース、２ヶ月で８回きたケース、半年で５回きたケースのSQLを別々に発行させてとっとと仕事を進めます。
投稿2015/07/28 12:28
総合スコア162
それを、JAVA上で、マージするんですね。
もったいない。
せっかく、通常処理より早く動くデータベースという存在があり、
その中で、SQL文というものを作れば、抽出できるのに、
手で加工する。
よく、そういう人います。
でも、悪くない方法です。
SQL文が組めないなら、その方法しかありません。
でも、SQL文を作れる人がいて、教えていただけるなら、
その方にすがって、SQL文内部で行うのが、
プログラマーとして、スマートな処理と判断できます。
もし、あなたがプログラマーなら、
いつも自分の持っている技術内でしか、プログラムを組まないというより、
新しいことへチャレンジする精神がないと、
新しい言語や、方式がでたときに、対応できませんよ。
とえらそうにいていますが、私は、こうして、人にすがって、
SQLを頂いている、あなたより、ダメなプログラマーです。
でも、このサイトで教えていただいたSQLや、各種方法などは、
分析して、自分の身に付けています＝成長していく。
それも私の学習方法の一つです。
以前にも長いSQLを書きましたが、後継者の為に、
いくつか、SQL文の区切りのよいところで、
説明文を複数行いれ、後継者も一目でやっていることが
わかるようにしています。
そうですか。
それぞれ条件を与えるとそれに応じた順位をとってくる単純なメソッド作ったほうが効率的で、使い勝手も良くなると思うんですが、ポリシー的なものがあるんでしたら仕方ありません。
頑張ってください！
エラーが全部とれ、SQLがうごきました。
いえ、初心者ですので、ポリシーなんていえるものは、ないです。
過去の書き込みをみていただければ、わかると思いますが、
単に、最適を求めているのです。
・Java Docの書き方の最適は？（＝出力時に、eclipsでエラーがでずに、最良に読みやすい書き方は？）
・Java のコーディングの最適は？（＝一般的なものは？）
とつきつめてきました。
なので、ここでも、一つのSQLで行きたいという願望でいますが、
それが不可能で、読みにくくく、メンテナンスの悪いものなら、
気兼ねなく分割法でいきます。
※教えていただいたSQL文は、長いだけで、単純なので、難しいとは考えておりません。とはいえ、自分では到底考えつかない、すごいSQL文です。でも、読むほうは大丈夫です。＝コメントもいっぱいかけるので、後継者への遺産としても、大丈夫。
0
期待結果はどんなのでしょうか？
初めに添付されているSQLでは、「２週間に５回以上、且つ２ヶ月に８回以上、且つ半年に５回以上来社」と、全ての条件を満たす人を抽出。(2週間以内に5回来ていても、2ヶ月の合計も5だと集計から漏れる)
そして全ての条件を満たす人を訪問回数の多い順に並べようとしているように見えます。
翻ってSteveG氏のSQLは、「２週間に５回以上か、２ヶ月に８回以上か、半年に５回以上来社」のようにどれかの条件を満たす人を全て抽出。(2ヶ月以内の来社はゼロでも、過去半年に5回以上の来社履歴があれば集計に加える。)
そして、各条件を満たす人をごっちゃ混ぜにして訪問回数の多い順に並べようとしてるように見えます。
※2週間に5回以上来ている人より、半年間に5回以上来ている人のほうが期間が長い分上位に来やすいと思います。また、2週間以内に5回以上来たAさんと、過去半年に5回以上来たAさんのように、同じ人が複数回登場するかも知れません。
yu-ri氏のコメントにあるSQLは、「２週間に５回以上の来社のグループと、２ヶ月に８回以上来社のグループと、半年に５回以上来社のグループ」を別々に集計して、それぞれのランキングを作成する。という方向に見えます。
現在作成中のコードはSteveG氏のSQLを元にしたものだと思いますが、集計の方向性は大丈夫でしょうか？
添付されている作成中のコードには"GROUP BY"の後ろに集計要素が指定されていないため、シンタックスエラーになりそうです。エラー無く戻りはゼロ件とありましたが、添付ミスでしょうか？
投稿2015/07/28 19:15
編集2015/07/28 19:28
総合スコア2068
添付は、そのままなので、添付ミスはありません。
レコードのほうを再度確認し、
ソースコードに、抽出直後に、本当に抽出されていないか、
System.out.println()を入れて確認中です。
0
ベストアンサー
こんな感じでしょうか?
SQL
1SELECT 2 A.* 3 , B.* 4FROM TableA A 5JOIN ( 6 -- 2週間以内に5回以上来社した人を検索 7 SELECT 8 customer_id -- <- 結合キーなので省略できません 9 , company_id -- <- 結合キーなので省略できません 10 , delete_flag -- <- 結合する時にデータを絞るために使います省略できません 11 , COUNT(*) AS CNT -- 来た回数 12 , '2週間に5回以上' -- <- 検出条件を判別するための文字列です必要なければ省略してください 13 FROM TableB 14 WHERE date > date_add(current_date, interval -2 week) -- <- 来社日が今日の2週間前より後(2週間以内) 15 GROUP BY 16 customer_id -- <- 省略不可 17 , company_id -- <- 省略不可 18 , delete_flag -- <- 省略不可 19 HAVING SUM(count) >= 5 -- <- 来社5回以上 20 UNION ALL 21 -- 2ヶ月以内に8回以上来社した人を検索 22 SELECT 23 customer_id -- <- 結合キーなので省略できません 24 , company_id -- <- 結合キーなので省略できません 25 , delete_flag -- <- 結合する時にデータを絞るために使います省略できません 26 , COUNT(*) AS CNT -- 来た回数 27 , '2か月に8回以上' -- <- 検出条件を判別するための文字列です必要なければ省略してください 28 FROM TableB 29 WHERE date > date_add(current_date, interval -2 month) -- <- 来社日が今日の2ヶ月前より後(2ヶ月以内) 30 GROUP BY 31 customer_id -- <- 省略不可 32 , company_id -- <- 省略不可 33 , delete_flag -- <- 省略不可 34 HAVING COUNT(*) >= 8 -- <- 来社8回以上 35 UNION ALL 36 SELECT 37 customer_id -- <- 結合キーなので省略できません 38 , company_id -- <- 結合キーなので省略できません 39 , delete_flag -- <- 結合する時にデータを絞るために使います省略できません 40 , COUNT(*) AS CNT -- 来た回数 41 , '半年に5回以上' -- <- 検出条件を判別するための文字列です必要なければ省略してください 42 FROM TableB 43 WHERE date > date_add(current_date, interval -6 month) -- <- 来社が今日の6ヶ月前より後(半年以内) 44 GROUP BY 45 customer_id -- <- 省略不可 46 , company_id -- <- 省略不可 47 , delete_flag -- <- 省略不可 48 HAVING COUNT(*) >= 5 -- <- 来社5回以上 49) B -- <- TableBとは別の集合なので実在のテーブル名を別名にはできません 50ON A.customer_id = B.customer_id 51AND A.company_id = B.company_id 52AND B.company_id = [iParaCompany_id] 53AND B.delete_flag = false 54WHERE A.delete_flag = false 55ORDER BY 56 CNT DESC 57
※動作確認はしていません(^^;
投稿2015/07/28 09:04
編集2015/07/28 15:06総合スコア985
さっそく、組み込んでやってみます。
ありがとうございます。
すごいSQL文ですね、凡人の私には、これは無理でした。
とにかくやってみます。
TableBの来た回数の計上の仕方に勘違いがあったので修正しました。
修正ありがとうございます。なんとなく、またみにきてよかった。
今も埋め込み作業と前後の処理をかいているところです。
何度か、レコードを変えてテストしていますが、
抽出件数が、０件です。
コンパイルエラーなどには、なりません。
SQL文もなんどかみなおしたのですが、再度見直して、どうすれば、動くか確認してみます。
★テーブル例を追記しておきました。
以下の変更を加えました
・HAVING句の条件誤りを修正
・各行の解説コメントを追記
・提示の列名に合わせて修正
なんどもお手数をおかけして、なんともうしてよいやら。
本当にありがとうございます。
とてもご親切で、開発者として、情報をいただいている身として、心底、感動しております。
※めんどくさくなったら、放置してください。
せかっく、いただいた情報なので、なんとか。。。、
絶対に、なんとか、してみせます。
■抽出したいもの について質問ですが
条件を整理すると
2週間に5回以上来社している人も、2か月で8回以上来社している人も
必ず半年に5回以上来社していることになりますよね？
customer_idでユニークにするなら半年に5回以上来社している人を
半年の期間内で来社回数の多い順に取得ではいけないのでしょうか?
また、customer_idはcompany_idごとにユニークではなく
全companyu_idを通してユニークなのでしょうか?
ご指摘のとおりにできます。
トヨタ自動車の社員が、複数人きます。
ホンダ自動車の社員が、複数人きます。
１．会社情報は、会社テーブルに入っています。
２．来社したユーザーの名前や電話番号は、TableAに入っています。
３．来社した日と、来社で行った作業は、TableBに入っています。
顧客テーブルTableAには、
・ユニークなID=customer_idが、プライマリーキーです。
・company_idも入っています。どこの会社の社員かの紐付けの為。
来社で行ったことテーブルTableBは、
・visit_id・・・がプライマリーキーでユニークです。
・customer_id
・company_id
・来社日付date
・memo
などが入っています。
上記から、
「全company_idを通してユニーク」です。山田さんは、世界で一人だけなので。（同じ苗字の人はいますが、そこは無視し。
抽出件数が０件の理由がわかりました。
SQL文でシンタックスエラーがでていて、例外で拾っていたのですが、呼出元で、エラーフラグを立てるのを忘れ、正常終了と判断したため、０件で正常となっていました。
大変、申し訳ありません。今、シンタックスエラーをとっているところです。
⇒ SQLException:Syntax error or access violation, message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WEEK) GROUP BY customer_id, company_id, delete_flag HAVING SUM(count) \u003E= 5 UN' at line 1"
SELECT
A.company_id
, A.customer_id
, B.CNT
FROM TableA A
JOIN (
SELECT
customer_id
, COUNT(*)
FROM TableB
WHERE delete_flg = false
AND date > date_add(current_day, interval -6 month) -- 期間半年
GROUP BY
customer_id
HAVING COUNT(*) >= 5 -- 来社5回以上
) B
ON A.customer_id = B.customer_id
WHERE A.delete_flg = false
ORDER BY
B.CNT DESC
半年に5回以上来社している人を来社回数の多い順
customer_idが単独で主キーならこれだけで事足りそうな気がします
ご指摘の通り変なSQLの仕様です。
いっそのこと、半年以内に、何回きたかの１文で、おわらせればよいのですが、
最近良く来る人は、良いお得意様として、抽出の上のほうへ表示。
毎週こないけども、毎月１度はくるお客様も、下のほうへですが、それなりのお得意様として、拾いたい。＝こういうお得意様を拾うために、このSQLが複雑になってしまっています。
といった感じで、お得様にも長期間感覚ですが、来てくださる方は、ほどほどのお得意様として確保したいのです。
尚、回数や月数などは、現実の抽出状況を見ながら、適切になるように、
変えて行きます。＝現実が、まだ見えていないので、今の回数や期間は、デフォルトとして一応設定してある。
従って、
kutsulogさんのおっしゃるとおり、お得意様が絞れるなら、まとめてもよいかもしれません。
新しい、SQL文をありがとうございます。
今のSQLのシンタックスエラーがとれて、抽出がうまくいかない場合、
この新しいSQLを使わせて頂きます。
PS.半年に、５回以上来社しても、最近来なくなったお客様は、お得意様ではなくなるので、はずしたいから、この３段階にしてありました。
変なSQLの仕様で、大変申し訳ございませんでした。
色々お手数をおかけして、申し訳ございませんでした。
でも、大変たすかっております。本当に、心から感謝しております。
ありがとうございました。
それでは2週間、2か月間、半年間の来社回数もとってきて
2週間の来社回数が多い順
2週間の来社回数が同じなら2か月間の来社回数が多い順
2週間の来社回数も2か月間の来社回数も同じなら半年間の来社回数が多い順
ということで下のようなSQLにしてみました
SELECT
A.company_id
, A.customer_id
, B.two_week
, B.two_month
, B.half_year
FROM TableA A
JOIN (
SELECT
customer_id
, SUM(
CASE WHEN date > date_add(current_day, interval -2 week)
THEN 1
ELSE 0
END
) two_week-- 2週間に来た回数
, SUM(
CASE WHEN date > date_add(current_day, interval -2 month)
THEN 1
ELSE 0
END
) two_month -- 2か月間に来た回数
, COUNT(*) half_year -- 半年間に来た回数
FROM TableB
WHERE delete_flg = false
AND date > date_add(current_day, interval -6 month) -- 期間半年
GROUP BY
customer_id
HAVING COUNT(*) >= 5 -- 5回以上来社
) B
ON A.customer_id = B.customer_id
ORDER BY
B.two_week
, B.two_month
, B.half_year
※まだ、前のSQLのシンタックスエラーがとれていないので、前のが動くのか試せてないのですが、↓の行で発生しているところまではつかめました。この行をコメントアウトすると次に「Unknown column 'count'」となるので、まずは、↓の行のシンタックスエラーをさがしているところです。
「+ " WHERE date > DATE_ADD(CURRENT_DATE, INTERVAL -2 WEEK)" // 来社日が今日の2週間前より後(2週間以内)」
シンタックエラーの原因がわかりました「WEEK」です。このキーワードが存在しないようです。「 -2 WEEK」 ⇒ 「-12 DAY」にします。
まさか、このキーワードが使えないとは、思いもよらず、あちこち、少しずつ変えて、
やってました。
で、インターネットで、調べたかぎりでは、WEEK は、無いようです。
-12 DAYにしたら、ここでのエラーは、なくなりました。
次に、下記エラーの対応をします。
「SQLException:null, message from server: "Unknown column 'count' in 'having clause'"」
エラー箇所は、下記なので、がんばって解決します。
「+ " HAVING SUM(count) >= 5" // 来社5回以上」
countという、レコード項目がないので、どう対応してよいのか全くわからなかったので、
他の２つ同じ書き方で、下記のようにしたらコンパイルが通りました。抽出結果がかわるかもしれないのですが、一応、突破しました。
「+ " HAVING COUNT(*) >= 5" // 来社5回以上」
今は、次のコンパイルエラーの対応中です。
「SQLException:Base table or view not found, message from server: "Unknown table 'TableA'"」
但し、TableBについては、" SELECT A.* , B.*"としているにもかかわらず、
TableBは、dbRes.getInt("からム名");などで、取得できるのは、下記３項目だけのようです。
それ以外の、カラム名を指定すると、その項目は、レコードに存在しないとエラーになります。
customer_id
company_id
delete_flag
ということなので、もうあと一歩のところのような気がしますが、
一旦、あきらめて、「2015/07/29 12:04」に書いていただいたSQLを今度は、
実装してみます。
開発で時間が無いとは言え、せっかく教えて頂いたSQLは、
全部試してみないと、書いていただいた人に失礼ですし、
動くか動かないかも、実装して、やってみないと、私にはわかりません。
（SQL文を書いていただいた方は、動くかな？と思い書いていただいたと思うので、
それを実際にやらないのは、もったいないし、失礼にあたると思ってます。）
■「2015/07/29 12:04」のSQL文
まずは、実装し、コンパイルしたら、下記エラーが発生しましたので、
エラーをとる作業に入ります。
「SQLException:Syntax error or access violation, message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(current_day, interval -14 day) THEN 1 ELSE 0 END ) two_week, SUM( CASE WHEN dat' at line 1"」
↑このシンタックスエラーは、取るのが難しいですね。全コマンドが正しいか、他のに一つずつおきかえたり、ネットで、コマンドがあるか、確認しながらなので、夜なべ作業になりそうです。
どうもかなり苦労されておられるようで。
横から失礼します。
current_day → current_date
単に日付関数のスペルミスかと思いますがいかがでしょうか。
ご助言ありがとうございます。横からでも縦からでも、大変たすかります。
ありがとうございます。
本当にたすかました。ありがとうございます。
だめです、どこを直しても、コンパイルエラーになるので、
ちょっと、断念するしかなさそうです。
私の本来は、ご提供いただいた情報は、極力活用する。ご提供してくださった方の手間を考えると、当たり前のことです。
ですが、まったくエラー改善に進歩がありません。
別のSQLを考えるしかなさそうです。
せっかく、SQL文をご提供いただいたのに、何時間かかっても、それを埋め込めずにエラー．．．、私、ダメですね、開発者失格というより、人の行為を無駄にして、人として失格です。反省のきもちばかりがあたまをよぎります。
せっかく、SQL文を書いてくださったのに、本当に申し訳ございません。
SQL文がわるいのではなく、エラーと特定できない私の不行き届きと技術不足が悪いのです。本当に心から、謝罪しております。ごめんなさい。
お手数をおかけしました。（泣）
今から、別のSQLを考えます。
一日がかりだったのに、断念してしまって．．．
0
すでに指摘が出てますけども、テーブルがどんな感じかわからないと厳しいですね。
あくまで想像する範囲ですが・・
TableAとTableBを indexとnameで紐づけしている様に見えはしますが、
いかんせん WHERE条件から以降にはTableBの回数集計のみになっていて、
対象となるべきキーが無いようです。
まずはTableBを 関連付けるためのキー単位で集約(GROUP BY)させてから
TableAにJOINしたほうが良さそうですね。
それぞれやり方はあると思うので、よい方法を探してみてください。
投稿2015/07/28 08:24
総合スコア50
実際には、結びつけるキーは、３つの複合キーです。
あまり沢山かくと、ごちゃごちゃになり、みにくくなると思い、
少し簡潔に書いています。
kutsulogさんがスバっと書かれてますね(´∀｀)
ぜひとも参考にしてみてください。
いくら難しい複合キーであっても、
キーを把握できていれば大丈夫ですよ。
はい、とても早くかいていただけたし、
すらばらしい、SQLです。
世の中には、すごい人がいらっしゃいますね。
0
※テーブルは、２つで、TableBに日付と来た数だけのレコードを保持しています。
テーブルAは、人の名前や住所などが入っています。
JAVA云々関係ない話となりますが、これを読む限りではTableAとTableBを紐づけるものが何もないので、抽出しようがありません。
ある程度テーブル構造などを開示いただければ、アドバイスもしやすいかと思います。
投稿2015/07/28 07:42
総合スコア634
「TableA JOIN TableB ON
(TableA.index == TableB.index)
AND (TableA.name == TableB.name) 」
では、不明ということですか？
SQLよくわからないのですが、
ONの後に、テーブルを結びつける 条件式が必要（＝書いてある）と認識していました。
誤解していました。
もっと詳しくキーやテーブル構造例を書くようにします。
編集ありがとうございます。おかげでなんとなくですが構造がみえました^^
SQLは最初誰でも苦戦するものです。私なんてまだER図がうまく書けない(笑)
それと、だんだん「上手な質問の仕方」が分かってくると思います。頑張ってください。
SQLについてはちょっと練ってみます。
教えていたたので、がんばって、動くようにします。
ありがとうございます。
おてすうおかけしてすみません。
でも、すごいSQL文ですね、自分では、絶対に、考え付かないです。
すばらしいです。
度々の追記、ありがとうございます。
本題ですが、シンプルに３つのSQLを組み立てた方が早いかと思います。
最近フレームワークばかりで生SQL書いてないので間違いがあるかと思いますが（汗
引数は「今日の日付」「さかのぼった日付」「来社回数」の３つになります。
以下、使いまわすSQL
---
select a.name as customer_name, b.count(customer_id) as cnt from tableB as b
inner join tableA as a
on b.customer_id = a.customer_id
where b.date between (さかのぼった日付) and (今日)
and cnt >= (来社回数)
group by customer_name（←これはいらんかもしれない
order by cnt desc;
---
ざっと以上です。
これで引数を変えて3回SQL発行してやれば目的のものが得られるかと思います。
間違ってたらごめんなさい（汗
そうですか、一つでスマートに行きたかったのですが、
でも、一体型のSQLでもっと、
レコードの確認、
SQL文の誤記の確認、
をして、やってみます。
それでも、だめなら、なにか方法を考えます。
※おそらく、私の提示している、条件やテーブルが悪いと思うので。
長く難しいSQL文は、区切りのよいところへ、何をやっているかしっかりコメントを入れて、後継者が一目でわかるようにしています。
データが1万件程度なら大した違いは生まれませんが、100万件とか1000万件とかになってくるとSQL構文（まあDBのチューニングになってしまうかもですが）をしっかり考えなければなりません。
SQLが複雑だと、データ取得までに時間がかかって結果的にプログラム処理より遅い場合だって多々あります。
要は得られる結果が期待通りのものかどうかが大事ですよ。
1つのSQLで期待通りの結果が得られた時で結構ですので、
１）SQLを作るところから１つのSQLで情報を取得して画面表示が完了するまで
２）SQLを作るところから３つに分けて情報を取得して、JAVAで成形して画面表示完了するまで
の２つの処理速度を調べてみてください。
またその結果を得られるまでに使った実時間も考えてみるとよいでしょう。
個人的に作っているものであれば別に時間がかかろうが問題ないですが、仕事で変にこだわるとかえって作業効率を悪くしますしねぇ…。
追記：
これは私見なので読み飛ばしてもらって構いません。
後継者のことを考えるならSQLを３つに分けることをお勧めします。
その長いSQLを読み解ける人ならコメントは必要ないですし、難しい人ならコメントを入れても何の処理なのか理解するまでに時間がかかるでしょうから。
貴重なご意見ありがとうございます。
その点も踏まえて考慮してあります。
データがあまりに古くなると、参照することがなくなるので、
別のサーバーへバックアップするなど、
全て考慮済みです。
データベースの各テーブルの予測件数算出、
容量算出、バックアップタイミング、削除タイミング、
など、仕様段階で決め、
それにあった、サーバーを購入してあります。
いや…サーバ云々ではなく開発効率の話を…まあいいか（汗
ちなみにご提示いただいているSQLですが、
SUM(count) → CNT
な気がします。
今、丁度、開発の合間にいます。
他の開発チームが追いついてこれてないので、
少し余裕があるので、過去に、普通に抽出して仮動作させていた本SQLを
今のうちに、本来の目的の、抽出ができるように修正しようと
考え、行動しています。
JSPとJAVAを作っているので、端末画面より先をいかないといけないのですが、
画面チームがまだ、追いつけないので、今のうちに。。。です。
えっ、CNT。。。、ありがとうございます。
私にとっては、その間違いは、気づきにくいので、教えて頂けて、幸いです。
お手数をおかけして、申し訳ありません。
解決済となっていますが、道半ばなようですので。
一発で取り出そうと思ったら、
①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;
----------
動いたらラッキー程度でお願いします。
ありがとうございます。
今から、プログラムに埋め込んで、実際にためしてみます。
あなたの回答
tips
プレビュー
