SQLなら俺に訊け [無断転載禁止]©2ch.net
抽象化の機能が殆どなくて、クエリのいたるところににコピペが必要な部分が出てきて、アセンブリのような扱いになっている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相当部分に対して混ぜ込めば、下部構造で一発クエリ出来る。
そして元々そうしていたのだが、他の都合上、管理が面倒なので変更しようとしているところ。
しかしよく分かったね。まあ妙な事をしてるからか?
まあとにかくありがとう。
今回は下部構造のサマリなので、常に全文検索で問題ない。
言葉が混乱しててごめん。 NOT MATCH が使えない。(Error: unable to use function MATCH in the requested context)
ただし動的エラーで、2つ目のクエリでcol1にMATCHの後、col0にNOT MATCHのチェックで落ちるようだ。
CASEの中でも使えないし、やはりMATCHはインデックスのようだ。
ところでMATCH NOTは使える。
新案4: MATCH NOT
SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION ALL SELECT 1,target FROM sometable WHERE col1 MATCH 'str -col0:str';
これでexplainは20、explain query plan は以下。
1|0|0|SCAN TABLE sometabke VIRTUAL TABLE INDEX 9:
2|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 11:
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
当面はこの新案4で行く予定。 メモ&報告
SQLModelにてRelationshipsがうまく機能しなかったが
結論としてSQLAlchemyのバージョンを変更したらうまくいった
https://github.com/tiangolo/sqlmodel/issues/327#issuecomment-1116983382
他のバージョンは試してないが
pip install SQLAlchemy==1.4.17
でいけた
#python #SQLModel >>164
クライアント側に結果セットを全部抱えなくてもいいのと、クエリが全部終わらなくても出てきたレコードから順に
処理を進められるんで、主に対象のレコードが多いバッチ処理なんかで使う。 SQLでDECLARE CURSORと書くサーバーサイドのカーソルと
DBドライバーのAPIが提供するクライアントサイドのカーソルは別のもの
前者はSQLで1行単位の処理をしたい場合に使うものだけどよほど特殊な事情がない限りは使わない
後者はクライアントがクエリの結果セットを塊単位でフェッチするのに使うもので呼び名が違うこともあるが一般的に使われてる
どちらも結果セット内の次の読み込み位置を指し示す抽象オブジェクトとしての役割は同じ 車テーブルがあって
車両番号 走行日 走行距離(出発) 走行距離(到着) 運転手
というカラムがあります。
各車両の現在の走行距離と、今運転手が乗っていれば運転手名が欲しいです。
乗っているという判断は
走行距離(出発)が入ってるけど到着は入ってない、という事になってます。
これってMAX関数で現在の走行距離とるクエリと、運転手データとるクエリとでJOINするしかないですよね? >>168
普通にCASEで行けそうだけど
現在の走行距離とは何?
運転手が乗ってなければどうしたいの? GROUP BYの集計項目以外のカラムの値(運転手等)をサブクエリ使わずに一発でできるかどうかという質問かと思ったが
運転手を表示する場合としない場合の分岐方法の話ならCASE式だね >>171
そういう意図でした!
caseでもう一度試行錯誤してみます! >>170
現在の運転手がいなかったらnullにしたいです。 SQL Serverで質問です。
テーブル1
テーブル2
テーブル3
テーブル4
…
とテーブルはどんどん増えます。
各テーブルにはIDという数値型のフィールドがあるのですが、
テーブル名とIDの最大値の一覧表にしたいです。
INFORMATION_SCHEMAを使ってテーブル名の一覧までは出せたのですが、
それらの各IDの最大値を産出するのはどう書けばいいのでしょうか? >>175
identity columnなら
SELECT IDENT_CURRENT (‘table_name’)かsys.identity_columnsのlast_value
違うなら
SELECT MAX(ID) FROM table_name >>176 ありがとうございます。
しかしながら
テーブル名 IDの最大値
テーブル1 6
テーブル2 1014
テーブル3 1990
テーブル4 74
テーブル5 1861
テーブル6 136
という感じに表示されてほしいです 一つのテーブルに対して、複数のクエリ(参照、更新)が同時に投げられても、テーブルロックされない限り並行して動くものなの? >>178
ロックはテーブルロックだけじゃないし
同時実行できるかはハードやDBMSの機能によるんだが
まあ基本的には複数のコネクションのクエリは並列して動くと考えていいんじゃね 全く同じクエリの処理してるのに、時間帯によって波があるのって何が原因かな?
夜中は10秒、朝9時くらいは1時間とかザラにある
しかも、毎日波がある
こんなに気まぐれなの? >>181
そんな情報だと時間帯によって何かが変化してるとしか言えない
頑張って 10秒と1時間越えで何が原因かあたりもつけられないってかなりヤバない?