SQLなら俺に訊け [無断転載禁止]©2ch.net
初心者です。もしよろしかったら質問させてください。
CASE文
SELECT TAB1,TAB2,
CASE
WHEN TAB2<1000 THEN 'SMALL'
WHEN TAB2>1000 THEN 'BIG'
ELSE 'ERR'
END AS SIZE
FROM TABLE
WHERE TAB2>0
というコードなのですが、TAB1のように、CASE文で使用しないカラムをSELECTするのは何故なのでしょう? TAB1の値がほしいから
いらないなら書く必要すらない ありがとうございました。
すっきりしました。
SELECT文は新しテーブルを作るのではなくて
見やすいように表型に情報を表示してるだけなのですね いや概念的には表を作ってる
表から表を作ってる
そういった理解がないとSQLは理解できない
その表からまた別の表を作るのにも利用できる テーブル名:RUN_TIME
KEY | COL | DEF_TIME | UPD_FLG
AAA | BBB | 2017-01-01 08:54:21 | NULL
AAA | CCC | 2017-01-01 08:58:31 | NULL
AAA | DDD | 2017-01-01 09:04:25 | NULL
CCC | XXX | 2017-01-02 08:57:46 | NULL
CCC | ZZZ | 2017-01-02 08:59:26 | NULL
CCC | BBB | 2017-01-02 09:05:03 | NULL
…
というデータが毎日朝9時付近何件かあって、
その日の08:58:00〜09:02:00の間に含まれるレコードのUPD_FLGに'Y'を入れたいのですが
どういうUpdate文を書けば良いんでしょうか
update RUN_TIME set UPD_FLG='Y'
where DEF_TIME >= ??? and DEF_TIME <= ????
というようなSQLになるんだと思うのですが…。 調べたところ、
where DEF_TIME >= DATE_ADD(DATE(DEF_TIME),INTERVAL 538 MINUTE) and
DEF_TIME <= DATE_ADD(DATE(DEF_TIME),INTERVAL 542 MINUTE);
で行けたのですが、これって一般的なやり方でしょうか? オレなら普通に DEF_TIME を時分秒の文字列に変換してから比較する
時分固定でその日に該当する年月日時分をいちいち組み立てようとは思わない 日付まわりはDBによって違いがあるからなんとも言えんが、こんな可読性も実行効率も悪いSQL書いてたら説教ものだな 他人が書いたSQLでこんなのや
select T1.*
from T1 left outer join ON T1.aaa = T2.aaa
こんなのがあるんだけど
select X2.*
from X1 left outer join ON X1.aaa = X2.aaa
外部結合してるのに片方のテーブルのフィールドしか抽出してない
SQLが散見してるんだけどこれって全く外部結合する意味ないよな
意味不明すぎてイラッとしてきた T1.aaa = T2.aaa
これは、両方の表にあるものだけを取り出す。
片方の表だけにあるものは取り出さない 言いたいこともわからんのかw
過疎だしここもレベル低すぎてダメだな 意味無いことはないけど、多分意図してることじゃないよ ID:2K5MWtLk
小さいことにイライラしてるわいきなりキレだすわ気持ち悪っw 0.01_もキレてないけどね
このスレは初めて見て初めて書き込んだけど
こっちの言いたいことも理解できないパープリンしかいないうえに
二言目が人格攻撃とかレベル低すぎて俺にはもう無理だわw
低能 joinしてるテーブルとか書いてないし、ちゃんとSQL全部見て言ってるのかあやしいな
抽出してなくてもwhereとかで使ってれば意味はあるぞ 左外部結合だからT1のレコードは全部取り出される。
で、T1にあるフィールドしか取り出さないからT1そのものと一緒だな。
単に間違いか、以前はT2のフィールドも取り出してたけど編集を繰り返してる内にT2のフィールドが要らなくなってしかもT1そのもので良いことに気付いて無いとかかな。 一緒にはならないよ。T2のaaaが複数あるならそれだけ同じものが出てくるよ。 >>81
???
T1のフィールドしか取り出さないでしょ。 ああ、レコードのことね。
でもそんなことする意味有る? データパターンでわざと増幅させたい場合はありだな
意味がないならきちんとテストして取り除けばいいだけじゃん >>83だから、>>73に挙げられた情報だけじゃ判断できないよ
例えばT2がT1を色々加工した結果で、既にT2の値を外部に持ってて、それと突き合わせて比較したいためのSQLとか、かもしれない
想像するだけ無駄なので、仕事なら知ってる人を探しに行く方が良い
けど、特に何の効果も無いと結論づけるのだけは間違ってるよ。T2.aaaにunique制約があるかも分からないのだから 抽象化の機能が殆どなくて、クエリのいたるところににコピペが必要な部分が出てきて、アセンブリのような扱いになっているSQLに、未来はあるのでしょうか?
仕事で他人の書いたSQLが読み解けなくて、達人に学ぶ SQL徹底指南書を読んでいるんだけど、
「豪快に GROUP BY句にも SELECT句の CASE式をコピーしてやるのがポイントです」とか身の毛もよだつ文章で既に萎えつつあります >>86
SQL専門家ではなく、プログラマだが。
SQL自体は残ると思うよ。DB弄るのにはあれくらいがいい。
そもそもSQLを「手で打つ」事はあり得ないでしょ。普通はプログラミング言語経由だ。
だから抽象化したければプログラミング言語で出来るし、それで十分だ。
SQL自体に抽象化を持ち込むのは、俺は反対だね。
それはSQLでプログラミングすることに繋がっていくが、
現時点ですでにDB側にロジックを持ち込むのはろくな事にならない、ってことになってるだろ。
ある意味、プログラミング言語はロジックを記述する為に出来ているわけで、当然の話だが。
(ロジックを記述するならSQLよりプログラミング言語の方が断然いい)
それ以前に、そもそも、SQLを手で書く事がなくなりつつあるだろ。
物によってはクエリデザイナもあるし、フレームワーク等を導入した場合、隠蔽されたりする。
この意味で、SQL自体を学ぶ必要があるかは、微妙になりつつあるが。
とはいえ、
> 仕事で他人の書いたSQLが読み解けなくて
なら、リファクタするにしても読み解けるようになるまでは学ぶしかないが。
SQL自体が原始的なのは文句を言ってもしょうがないだろ。
事実として規格は古いし、DB向けのアセンブラなんだし。 ミクロサービスだとSQL要らんのよね
なのでormで楽したい CASE文のみで2番目に高い値を取得する方法はありますか?
groupbyなどは使えない制約下とさせてください。 Order by 〇〇 Limit 2で1番目捨てる SOQLってどうですか?
自由にJOIN出来ないってどうなのよって思うんですが。 SQLとMySQLの違いというか、関係ってどういう関係ですか?
MySQLがSQLの拡張? SQLは言語
MySQLは製品名、SQLでデータ操作ができる PythonでSQLiteのデータを更新中にコミットしないままプログラムが強制終了してしまった事があって
DBファイルが開けなくなったことがあったんですけど(それはバックアップを使って復帰させたんですけど)
もしバックアップを取ってなかった場合、どうやって復帰させればいいんでしょうか
-journalというファイルが残っているのでこれを使うのは予想できるのですが… Webの情報をSQLiteに書き込む処理は概ね慣れてきたんですけど
書き込んだ情報を見やすく表示したり、表示中の情報をクリックして関連項目を検索したり、
データを手動で書き換えたりするには
phpとhtmlを勉強して1からDB表示・操作システムを作るしかないんでしょうか? VScodeでMYSQL使いたいのですが、サーバ名がわかりません。
どなたか教えてください。 SQL初心者向けの書籍は何をまず読めばいいでしょうか? >>102
朝井淳さん
谷尻かおりさん
ミックさん
この辺の入門書を読んでおけばおk >>103
イラストで理解 SQLはじめて入門
朝井淳さん
スッキリわかるSQL入門第2版
中山清喬さん、飯田理恵子さん、フレアリンク監修
の2冊買ってきました。
谷尻さんとミックさんの本、後ほど確認してみたいと思います。 SQLは大学で習ったが、その時はどんな用途で使うのかさっぱりわからんかったわ。 SQL徹底指南第2版誰かmercariで安くで出品してよー MySQLで時間を管理し、一定時間過ぎたら、
レコードをの情報を更新したいのですが、
どの様にすればできるか教えてください。
1. このようなことがMySQL単体で可能なことなのかな?
2. 可能な場合どのようにすれば実現できるの?
イメージとしてはこんな感じです。
開始時のレコード001
ID, 開始 , 終了
001, 13:00 , 13:30 ← 13:00に開始
↓ 30分経過後(同一レコードの動き(自動更新))
終了時のレコード001
ID, 開始 , 終了
001, 0 , 0 ← 13:30になったからクリア >>107
定期的にスケジュールテーブルをチェックするイベント作ればできるよ
あと開始/終了時刻をクリアするよりもステータスを別のカラムに持たせたほうがいい
https://dev.mysql.com/doc/refman/8.0/en/create-event.html
リファレンス読んで分からないことがあればここじゃなくMySQLスレへどうぞ
https://mevius.5ch.net/test/read.cgi/db/1499949595/ >>108
ありがとうございます。
定期的にチェックするイベントですね。
試してみます。
今後はMySQLで聞きます。
https://mevius.5ch.net/test/read.cgi/db/1499949595/
(^人^)ありがとうございます。 XreaやSAKURAなどのMySQLは、ネットからデータを閲覧したりダウンロード
できるようになっているんだけど、あのコントロールはどのCGIでやってる?
WindowsマシンにMySQLをインストールした場合、同様にデータベースの
中身を見るのはどうしたらいい? たいていのアプリ・サービスは、サーバー/クライアント方式
Linux のシステム内で動いているものは、サーバーで、
それに、Windows などのパソコンからアクセスするものは、クライアント
例えば、AWS なら、AWS Command Line Interface(AWS CLI)みたいなものを、
Windows パソコンに入れて、それでサーバーにアクセスする
MySQL 用のクライアントも探してみれば? >>111
うーんと、Xreaなどのレンタルサーバーを借りると予めMySQLがインストール
されていて、確か、管理画面などからMySQLのデータベースの中に入っている
データを、普通のパソコンのブラウザからリモートで見られるようになっている。
それは、なんという名前のCGIを使っているか教えて欲しい。 例えば、AWS なら、AWS Command Line Interface(AWS CLI)みたいなものを、
自分のWindows パソコンに入れて、それでサーバーにアクセスする
たぶん、Xrea, SAKURA でも、
Xrea CLI, SAKURA CLI みたいな同様のツールがあるのじゃないの?
そのサービス事業者に聞いてみれば? >>114
なるほど。さんくす。
ついでに、Rubyでユーザー登録や認証をした結果をMySQLの中に
データとして貯めるようなサーバーサイドのCGIのライブラリ
みたいなものは有る? >>115
Rubyなんぞ知らんし、何をやりたいのかもよくわからんから、Rubyスレで訊け
認証用のフレームワークに何を選ぶかって話で、SQLは直接関係ない Ruby on Rails の devise という「gem」が、パスワードを入力したり
メールを送って登録するようなよくある認証パターンをやってくれて、
MySQLも使ってるそうだけど、Ruby on Rails使ったことないし、
登録した MySQL のデータを上手く扱えるか不安。 テーブル名にバインド変数使う事って出来ないの?
"CREATE TABLE IF NOT EXISTS ? (...);"
といった感じでテーブル名は後付けでバインドしたいんだけどsqlite3_prepare_v2が失敗する SQL ServerとOracleとDb2の3間を変換してくれるアプリとかありませんか? 質問スレ過疎ってたからこっちか
専ブラBB2Cでワッチョイを正規表現でNGしたいんだけどうまくいかない
第2オクテット(仮にXXとして)だけ固定でこれじゃダメだろうか
.*XX-.* sqlite3_blob_openで得た列ハンドルをsqlite3_reopen_blobで使いまわすとクエリ投げるより5倍近く高速だと判明したが
nullセルやrowが見つからないとハンドル失効しちゃってopenし直さないといけないんだな
open自体はそれなりに重いから失効が多いとパフォーマンス上の利点はほとんど無しか 2万レコードのテーブルの全行をそれぞれupdateで値を変更したいんだが、これってforでSELECTを2万回繰り返したりしたらまずいもんなの?
レンタルサーバー使ってるんだが べつにまずくはないよ。オーバーヘッドが大きいだけ。 >>125
ありがとうございます。めっちゃ助かりました 一個前のidからカラム取ってくることはできますか? 前とか後とかいう概念が、、、というのは置いといて
自分のidより小さいidの最大が該当のidだろ
あとはサブクエリなりwindow関数なりでとってこい レコードのデータが
AAA 1
AAA 2
BBB 3
BBB 4
BBB 5
CCC 6
CCC 7
AAA 8
AAA 9
BBB 10
BBB 11
ってなってるデータから
AAA 1
BBB 3
CCC 6
AAA 8
BBB 10
みたいなデータに変換したいのですがやる方法ってありますか どういうルールで変換するのか書かないで丸投げされてもな。
というかそれを自分で考えないからわかんないんだろう。 >>131
因果関係が逆。
それが宿題なら問題が糞だからやらなくていいし、学校を変えるべき。 すまん 俺が悪かった。
みんなありがとう
例えば、下記の時系列データがあったときに、nameごとにまとめたものを別のテーブルに入れたいんだよね。
nameがはまとめたときに、IDがもっとも若いものを格納したい。
SQLで一発取れたりするかな?
ロジックを考える必要ある?
ID(PK) name value
1 AAA 1
2 AAA 3
3 BBB 5
4 BBB 2
5 BBB 4
6 CCC 1
7 CCC 3
8 AAA 1
9 AAA 2
10 CCC 3
11 CCC 4
↓
ID(PK) name value
1 AAA 1
3 BBB 5
6 CCC 1
8 AAA 1
10 CCC 3 >>135
がやってくれたことがまさにやりたいことだわ
くわしくありがと 時刻昇順に並んでるデータでstatusが1〜4を一塊として
縦横変換したテーブルが欲しいんですけどSQLだけじゃムリですかね。
欲しいテーブルにあるrep_idは元のテーブルにない(生成したい)です。
元のテーブル
time status
10/1 0:01 1
10/1 1:34 2
10/1 2:00 4
10/1 22:00 2
10/1 23:32 3
10/2 1:02 4
欲しいテーブル
rep_id| status_1 | status_2 | status_3 | status_4
-----+----------+-----------+----------+--------
1 |10/1 0:01 | 10/1 1:34 | null | 10/1 2:00
2 |null | 10/1 23:00 | 10/1 23:32 | 10/2 1:02 時刻昇順に並んでるデータでstatusが1〜4を一塊として
縦横変換したテーブルが欲しいんですけどSQLだけじゃムリですかね。
欲しいテーブルにあるrep_idは元のテーブルにない(生成したい)です。
元のテーブル
time status
10/1 0:01 1
10/1 1:34 2
10/1 2:00 4
10/1 22:00 2
10/1 23:32 3
10/2 1:02 4
10/2 4:05 4
10/3 18:30 2
10/3 20:34 2
欲しいテーブル
rep_id| status_1 | status_2 | status_3 | status_4
-----+----------+-----------+----------+--------
1 |10/1 0:01 | 10/1 1:34 | null | 10/1 2:00
2 |null | 10/1 23:00 | 10/1 23:32 | 10/2 1:02
3 |null | null | null | 10/2 4:05
4 |null | 10/3 18:30 | null | null
5 |null | 10/3 20:34 | null | null SQLだけでやってみた
WITH
ブレークチェック AS (
SELECT
CASE WHEN LAG(status,1,NULL) OVER (ORDER BY [time]) < status
THEN 0
ELSE 1
END AS st,
[time]
FROM 元のテーブル),
開始終了 AS (
SELECT
[time],
(SELECT MIN([time]) FROM ブレークチェック WHERE st = 1 AND [time] > t.[time]) AS end_time
FROM ブレークチェック t
WHERE st = 1),
t1 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 1),
t2 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 2),
t3 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 3),
t4 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 4)
SELECT ROW_NUMBER() OVER (ORDER BY 開始終了.[time]) AS rep_id,
t1.[time] AS status_1,
t2.[time] AS status_2,
t3.[time] AS status_3,
t4.[time] AS status_4
FROM 開始終了
LEFT JOIN t1 ON t1.[time] >= 開始終了.[time] AND (t1.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL)
LEFT JOIN t2 ON t2.[time] >= 開始終了.[time] AND (t2.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL)
LEFT JOIN t3 ON t3.[time] >= 開始終了.[time] AND (t3.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL)
LEFT JOIN t4 ON t4.[time] >= 開始終了.[time] AND (t4.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL)
素直にループ回すほうがいいな >>140
ありがとうございます。SQLだけでも書けるんですね
足りないstatusを補完できないかとか集計キーを作ってからとか当初の目的と外れたところで発狂してました 検索やら削除、CRUDをしたあとに得られるデータの命名に
rowsとかitems、個々はrowやitem
と考えています。
さらに個々rowにはdeleteやupdateのメソッドが使えるよう処理しています
つまり純粋なデータというよりインスタンス化されています
ここで疑問が。
rowという命名がしっくりこない気がします
row.column名やらrow.delete()とかで使う分にはrowで良いと思いますが
rowオブジェクトのクラス名をRowにするのはぴんとこないのです
何かいい命名案はないでしょうか
もうItemでいいかなとも思ってます Row/Columnは行/列だから表(Table)に対しての名前はフィットしていると思うがな。
気に入らなければRecord/Flieldではどうかな? ありがとうございます >>143
一括置換しても問題なくいつでも変えられそうなのでとりあえずはRowにしときます 日本全国民テーブルから
年収960万円以下の世帯で、
かつ
18才未満の子供の数。
を抽出するSQLを書いて下さい >>145
日本人達テーブルには
マイナンバー、世帯主マイナンバー、生年月日、徴収税額
があるものとします。 >>142
Ruby on Rails のO/R マッパーでは、
命名規約で決まっているから、row みたいな抽象的な名前にならない
テーブル名が複数形のmy_books(snake case)なら、
各行は、単数型のmy_book を使えばよい
クラス名は、MyBooks(Pascal, upper camel)
ファイル名は、my_book.rb 便通はfirewallで5ch禁止してるからな
自宅から業務外で5chに質問描いてたらクビ selectはそこそこ速いのに(30秒くらいで処理終わる) updateにすると遅くなる(10分以上)要因って何が怪しいかわかる?
抽出条件は全く一緒 selectはそこそこ速いのに(30秒くらいで処理終わる) updateにすると遅くなる(10分以上)要因って何が怪しいかわかる?
抽出条件は全く一緒 >>152
インデックスがたくさんある
Tトリガーで重い処理してる
とか? selectとupdateで経過時間を比較してもあんまり意味ないよ
抽出条件は同じでもupdateは抽出後に1件1件更新処理が必要なんだから
件数が多ければそのくらいの差になっても何も不思議じゃない 複数のカラムのどれかに該当文字を含む行を知りたいが、
どのカラムだったかで区別したい時に、一発でやる方法ってある?
具体的には以下(2発)を一発にしたい。
SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION SELECT 1,target FROM sometable WHERE col1 MATCH 'str' AND col0 NOT MATCH 'str';
例えば、「どのORに当たったか」を教えてくれるスカラー関数 hitplace() があったとすると、
SELECT hitplace(),target FROM sometable WHERE col0 MATCH 'str' OR col1 MATCH 'str';
に出来るのだけど。
環境はPHP+SQLite。
出力は配列の配列、 [[col0に当たった行],[col1に当たった行]] の形式で、重複はなし。
この形式にサクッと変換出来るのなら、読み出した形式は上記とは違っても可。 CASE式使えばできるよ
アプリかDBの設計を見直したほうがいい可能性大 お早い回答ありがとう。
もっと色々試すが取り急ぎ。
新案1:
SELECT CASE WHEN col0 LIKE 'str' THEN 0 WHEN col1 LIKE 'str' THEN 1 ELSE -1 END AS col,target FROM sometable WHERE col>=0;
explainでは32、他だと文法エラーらしいがSQLiteだと通る。(参考 https://rainbow-engine.com/sql-howto-caseresult-where/)
explain query plan では
0|0|0|SCAN TABLE tags_bulk VIRTUAL TABLE INDEX 0:
新案2:
SELECT col,target FROM (SELECT CASE WHEN col0 LIKE 'str' THEN 0 WHEN col1 LIKE 'str' THEN 1 ELSE -1 END AS col,* FROM sometable) WHERE col>=0;
他ではこう書けと言われているもの。
explainでは32で、見た目中身も同じ。explain query plan も全く同じ。 元:
SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION SELECT 1,target FROM sometable WHERE col1 MATCH 'str' AND col0 NOT MATCH 'str';
explainでは同じく32だが、見た目は違う。が、読み方が分からない。
explain query planでは以下。見た目通り2周している。
1|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 9:
2|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 11:
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
MATCHはCASEの中では使えないらしくLIKEにしている。
(Error: unable to use function MATCH in the requested contextと出る。)
MATCHだとインデックスが使える分速いはずだが2周する分遅くなるのでどうなるか。
DBやアプリ構造の改善案は今のところ思いつかない。(変更は可能)
カラムはそれぞれ別の管理なので、先に合わせる事は出来ない。 新案3: UNION ALL
SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION ALL SELECT 1,target FROM sometable WHERE col1 MATCH 'str' AND col0 NOT MATCH 'str';
元はUNIONにB-TREEが必要でこの分無駄だった。
explainは23と軽くなった。explain query plan は以下。
1|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 9:
2|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 11:
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
単発で別々にクエリすればUNION ALL 分も取れるが、単に追加してるだけなのでPHP側で結合するよりは速いはず。
今のところこの辺か。他に何かあればよろしく。
とりあえず実装して試すが、差が出るほどDBの中身が埋まるまでにはだいぶかかるとは思う。 >>157
その案はどちらもテーブルスキャンになってインデックス使わない
SELECT句のCASE式とは別にWHERE句に本来の条件を書く
DB構造の話はカラムとして持つべきデータじゃなくて
子テーブルの行として持つべきデータかと思ったんだけど
全文検索でいずれかのカラムに検索キーワードが含まれてる行を
どのカラムかという情報も含めて抽出する用途なんであれば
そのままでいいんじゃないかと思う >>160
> その案はどちらもテーブルスキャンになってインデックス使わない
そうだが、今回はこれで仕様的にも問題ない。
俺が勘違いしてたのもあって言葉が混乱しているが、
SQLiteのFTSは全文検索仕様で、通常のインデックスは作成出来ない。
FTSで「インデックス」と言われてるのは全文検索用のキーワードインデックスで、
つまりMATCHがLIKEに比べて糞速いだけで、常に全row検索する仕様のようだ。
https://sqlite.org/fts3.html
(俺がFTSの「インデックス」をここでもそのままインデックスと表記したのが不味かった。
FTS用のテーブルでは通常のインデックスでの単rowからの検索は出来ない。
《rowidだけは使えるらしいが、使っても explain query plan では scan と表示された》)
> 子テーブルの行として持つべきデータかと思ったんだけど
あーなるほど、勘がいいね。
実は元々そういう構造になっていたのだが、
下部構造のサマリを作ってしまった方が楽だからそうしようとしていて、
col0は上部から与える名前、col1は下部のそれぞれの中身の寄せ集めになってる。
だからcol0を下部構造の各行のcol1相当部分に対して混ぜ込めば、下部構造で一発クエリ出来る。
そして元々そうしていたのだが、他の都合上、管理が面倒なので変更しようとしているところ。
しかしよく分かったね。まあ妙な事をしてるからか?
まあとにかくありがとう。
今回は下部構造のサマリなので、常に全文検索で問題ない。
言葉が混乱しててごめん。