SQL & javaでやっている処理なのですが、もしSQLのみで出来たら素敵だなと思い質問します。
日付,天気,気温
0801,晴れ,25
0802,晴れ,24
0803,雨,20
0804,雨,19
0805,雨,20
0806,雨,20
0807,晴れ,26
このようなテーブルがあった場合に
日付,天気,気温,連続雨記録
0801,晴れ,25,0
0802,晴れ,24,0
0803,雨,20,1
0804,雨,19,2
0805,雨,20,3
0806,雨,20,4
0807,晴れ,26,0
0809,雨,19,1
0810,雨,21,2
のように番号を振ったり
日付,天気,気温,連続雨記録(20度以上)
0801,晴れ,25,0
0802,晴れ,24,0
0803,雨,20,1
0804,雨,19,0
0805,雨,20,1
0806,雨,20,2
0807,晴れ,26,0
0809,雨,19,0
0810,雨,21,1
のように条件をつけて番号を振る事SQLはどのように書けばよいでしょうか?
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/07/09 11:59
回答6件
0
SQLで作成してみました。
sql
1CREATE TABLE `weth` ( `hiduke` date, `tenki` text, `kion` int); 2insert into `weth` values 3('20170801','晴れ',25), 4('20170802','晴れ',24), 5('20170803','雨',20), 6('20170804','雨',19), 7('20170805','雨',20), 8('20170806','雨',20), 9('20170807','晴れ',26), 10('20170809','雨',19), 11('20170810','雨',21); 12 13SELECT DATE_FORMAT(t4.`hiduke`,'%m%d') `日付`, t4.`tenki` `天気`, t4.`kion` `気温`, 14 CASE WHEN `tenki`=`tenki2` AND `tenki`='雨' THEN @n:=@n+1 ELSE 15 CASE WHEN `tenki`='雨' THEN @n:=1 ELSE @n:=0 END END `連続雨記録` 16FROM ( 17 SELECT t1.*, `tenki2`, `kion2` FROM `weth` t1 18 LEFT JOIN 19 (SELECT DATE_ADD(`hiduke`, INTERVAL 1 DAY) `hiduke`, `tenki` `tenki2`, `kion` `kion2` FROM `weth`) t2 20 USING(`hiduke`) ORDER BY t1.`hiduke` 21) t4, (SELECT @n:=0) t5; 22 23 24SELECT DATE_FORMAT(t4.`hiduke`,'%m%d') `日付`, t4.`tenki` `天気`, t4.`kion` `気温`, 25 CASE WHEN `tenki`=`tenki2` AND `tenki`='雨' AND `kion`>=20 AND `kion2`>=20 THEN @n:=@n+1 ELSE 26 CASE WHEN `tenki`='雨' AND `kion`>=20 THEN @n:=1 ELSE @n:=0 END END `連続雨記録(20度以上)` 27FROM ( 28 SELECT t1.*, `tenki2`, `kion2` FROM `weth` t1 29 LEFT JOIN 30 (SELECT DATE_ADD(`hiduke`, INTERVAL 1 DAY) `hiduke`, `tenki` `tenki2`, `kion` `kion2` FROM `weth`) t2 31 USING(`hiduke`) ORDER BY t1.`hiduke` 32) t4, (SELECT @n:=0) t5;
投稿2017/08/12 15:48
編集2017/08/12 15:49総合スコア4070
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/08/12 16:12
2017/08/12 16:21
2017/08/12 16:31
2017/08/12 16:45 編集
2017/08/12 17:14 編集
2017/08/12 17:04
退会済みユーザー
2018/09/16 22:27
2018/09/17 00:59
2018/11/17 07:05
退会済みユーザー
2019/01/02 02:48
0
日付が必ず連続しているという前提であれば、
各日付に対して
- 連続記録の条件をみたす場合、
0. その日より前の日付かつ、連続記録の条件を満たさない日付の最大を取り、- その日付との日数の差分を取る
- 連続記録の条件をみたさない場合、
0.0
を取る
ようにすれば、意図通りの結果が得られます。
sql
1SELECT 2 w.*, 3 IF ( 4 w.wether = '雨', 5 ( 6 SELECT DATEDIFF(w.`date`, x.`date`) 7 FROM wethers AS x 8 WHERE x.`date` < w.`date` 9 AND x.wether <> '雨' 10 ORDER BY `date` DESC 11 LIMIT 1 12 ), 13 0 14 ) AS continuous_rain, 15 IF ( 16 w.wether = '雨' AND w.temperature >= 20, 17 ( 18 SELECT DATEDIFF(w.`date`, x.`date`) 19 FROM wethers AS x 20 WHERE x.`date` < w.`date` 21 AND NOT (x.wether = '雨' AND x. temperature >= 20) 22 ORDER BY `date` DESC 23 LIMIT 1 24 ), 25 0 26 ) AS continuous_rain_20 27FROM wethers AS w;
実行結果
http://sqlfiddle.com/#!9/17331ed/1
ただし、質問文にご提示のように日付に歯抜けがある場合、
0807,晴れ,26,0 0809,雨,19,1 <- 0808 が抜けている 0810,雨,21,2
上の方法ではうまく行きません。
実行結果(2017-08-09 と 2017-08-10 のcontinuous_rain
列の値がおかしい)
http://sqlfiddle.com/#!9/7ee589/1
---
いずれにせよ、MySQL はこのような「行の順番」を考慮した集計処理を苦手としている上に、
回答に上げた SQL文は「相関サブクエリ」と呼ばれるもので、
レコード数が多くなると耐えられないほど遅くなる可能性が高いです。
http://nippondanji.blogspot.jp/2009/03/mysql_25.html
そのため、集計テーブルを作り、日時のバッチ処理でその集計テーブルにデータを保存しておくか、
他の回答者様の提言どおり Java側で処理したほうが、シンプルに実装できると思います。
投稿2017/08/12 07:58
総合スコア4791
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
こちらのサイトで詳しく解説されているので、参考にしてみてはいかがでしょうか?
投稿2017/08/12 03:23
総合スコア3095
0
ベストアンサー
A.Ichi様のテーブルとデータをお借りしました。
2パターンほど記載しておきます。
1パターン目はKiyoshiMotokiさんの歯抜けOK版です。
(同一日付の重複も一応OKにしてますが、重複の際の連続ルールは適当です)
2パターン目は同一日付の重複はない前提で、
A.Ichiさんの抽出を更に1つにした感じです。(同一日時OKにする場合、V1の中身でhidukeで集約)
どうするかは、細かい仕様次第ですが、個人的には2パターン目の方が好みです。
SQL
1SELECT DATE_FORMAT(T1.`hiduke`,'%m%d') as `日付` 2 , T1.tenki as `天気` 3 , T1.kion as `気温` 4 , ifnull( 5 ( 6 SELECT COUNT(distinct T2.hiduke) 7 FROM weth T2 8 WHERE T2.hiduke > ( 9 SELECT MAX( T3.hiduke ) as hiduke 10 FROM weth T3 11 WhERE T1.tenki = '雨' 12 AND T3.tenki <> '雨' 13 AND T3.hiduke < T1.hiduke 14 ) 15 AND T2.hiduke <= T1.hiduke 16 AND T2.tenki = '雨' 17 ) 18 , 0 19 ) as `連続雨記録` 20 , ifnull( 21 ( 22 SELECT COUNT(distinct T4.hiduke) 23 FROM weth T4 24 WHERE T4.hiduke > ( 25 SELECT MAX( T5.hiduke ) as hiduke 26 FROM weth T5 27 WHERE T1.tenki = '雨' 28 AND T1.kion >= 20 29 AND ( 30 T5.tenki <> '雨' 31 OR T5.kion < 20 32 ) 33 AND T5.hiduke < T1.hiduke 34 ) 35 AND T4.hiduke <= T1.hiduke 36 AND T4.tenki = '雨' 37 AND T4.kion >= 20 38 ) 39 , 0 40 ) as `連続雨記録(20度以上)` 41FROM weth T1 42ORDER BY T1.hiduke 43; 44 45 46SELECT DATE_FORMAT(V1.`hiduke`,'%m%d') as `日付` 47 , V1.tenki as `天気` 48 , V1.kion as `気温` 49 , ( 50 CASE 51 WHEN V1.tenki = '雨' 52 AND V1.mae_tenki = '雨' THEN @n:=@n+1 53 WHEN V1.tenki = '雨' THEN @n:=1 54 ELSE @n:=0 55 END 56 ) as ame 57 , ( 58 CASE 59 WHEN V1.tenki = '雨' 60 AND V1.kion >= 20 61 AND V1.mae_tenki = '雨' 62 AND V1.mae_kion >= 20 THEN @m:=@m+1 63 WHEN V1.tenki = '雨' 64 AND V1.kion >= 20 THEN @m:=1 65 ELSE @m:=0 66 END 67 ) as ame20 68FROM ( 69 SELECT T1.hiduke 70 , T1.tenki 71 , T1.kion 72 , T2.tenki as mae_tenki 73 , T2.kion as mae_kion 74 FROM weth T1 75 LEFT JOIN weth T2 ON( 76 T2.hiduke = ( T1.hiduke - INTERVAL 1 DAY ) 77 ) 78 ORDER BY T1.hiduke 79 ) V1 80 , ( SELECT @n:=0 ) T3 81 , ( SELECT @m:=0 ) T4 82;
あと、記載しませんでしたが、「2日以上連続していない場合、1ではなく0にする」については、
そんなに難しくないので、記載してません。
(考え方としては、当日がOKで前日と翌日がNGの場合、0とするという表現を付け加えるだけです。)
投稿2017/08/13 17:35
総合スコア760
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2018/09/16 22:28
2018/09/17 01:14
2018/09/17 17:49 編集
2018/11/17 07:05
退会済みユーザー
2019/01/02 02:47
2019/04/06 14:07
2019/04/06 22:29 編集
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。