SQLなら俺に訊け [無断転載禁止]©2ch.net
>>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時間越えで何が原因かあたりもつけられないってかなりヤバない? マシンのメモリなどの、何かのリソースが少ないのかも
例えばメモリが少ないと、1クリックしてから処理されるまで、1分掛かる。
遅すぎて何もできない バラバラな日付が入っているテーブルから、
特定日以前3日間のレコードを取るSQL教えてください
hogeテーブル
id,date
5,2022-06-30
4,2022-06-19
3,2021-12-24
2,2021-06-03
1,2021-01-02
ここから2022-06-19以前の3日間のレコード
2022-06-19
2021-12-24
2021-06-03
を取りたいです その日付より小さくて、その日付ー3日より大きいっていうwhere条件書くだけだと思うが
日付の扱いはDBMSによって差が大きいからこれ以上はちゃんと環境書け >>187
where date <= ‘2022-06-19’
order by date desc
limit 3;
上から3行取る方法はDBMSによって違うのでマニュアルを読んで >>188
その日付ー3日は、
日付がバラバラなのでできません
>>189
これでできそうです
ありがとうございます ああ、三日じゃなくて3件分のデータが欲しいのか
日付にダブりがないなら>>189さんの方法で
ダブりがあるならdistinctなりgroup byなりしてからだな
>>190
ソートしないでできるならぜひその方法を教えてくれ 3日が3レコードという意味なのかどうかからわからない。 >>192
自分より大きくて指定日付以下であるレコードが3件未満となるものを選ぶ。 >>195
なるほど。件数がゼロ件になるやつの考慮がいるけど、いけるのはいけそうだな
SELECT * FROM hoge T WHERE
(SELECT count(*) FROM hoge
WHERE hoge.[date] >t.date AND hoge.[date]<='2022-06-19'
) < 3 AND t.[date]<='2022-06-19'
こんな感じか
これならオフセット取れないとかwindow関数使えないDBとかでも動くな >>190
ソートしろと言われているのにソートしないのは問題だが逆に
ソートしろと言われてないのにソートするのは構わんだろ >>197
無駄なソートはRDBMSによっては致命的な性能問題を引き起こす。 >>198
「無駄な」ソートはな
order by 書けば絶対ソートするとでも思ってるんだろうか
つかおまえソートしない方法提示できてないだろ
テーブルスキャン何回もやるぐらいならソート1回のほうがましなことも多いぞ
まあなんにせよインデックス構成とオプティマイザ次第だけどな TOP/LIMIT/FETCH FIRST + ORDER BY使ったtop-Nクエリが
相関サブクエリ使ったtop-Nクエリより遅くなるようなメジャーなDBMSがあるの? 内部の処理の説明をSQLの構文で説明するのは、知識がなさすぎると思うぞ。
少なくとも業務システムでは嫌われる。 構文の話してるヤバい奴ってどれ?
というか、こういうどこに向かって話してるのかわからないようなのもなんかヤバい。 つかDB板の連中って何でそれが重い処理なのかまるで理解してないよね
しかも呼び出し側含めた全体最適化なんてする気もないようだし、
あいつらはSQLを手打ちでもしてるのだろうか? まるですでに重いと決まっているかのような物言い。これはヤヴァイ。 インデックスを張ってる状態での189は間違いなく軽い
DB屋はDBで済ませる傾向があるとは聞くし、
クエリプランナで最高に最適化されてリングバッファ的に動くのなら196も確かに軽いが、
そうならない場合は糞重い
俺はそこまでDBのことは知らないので、
189が第一選択肢で、速度の問題があればインデックスを張る
これで問題がある場合は、自前でカーソルをとってリングバッファを実装する
DB屋なら196がそれぞれのDBで軽いか重いか把握した状態で使うのだとは思う
ただそれは俺らプログラマとはだいぶ視点が違うと感じた ソートの話は相関サブクエリを使えという話じゃなく
指定日以前の任意の3レコードなのか(ソート無し)
指定日以前の直近の3レコードなのか(ソート有り)
という違いを指摘したかったんでしょ それもあるとは思ったが、187だとほぼid通りの順なのだろうし、
191で文句も言ってないので、おそらくログ的な物のラスト3行を取りたいのだろうよ
なら俺は208で言ったとおりにする
(なお俺は189ではない)
それを(実際どうなるのかは知らんが)糞遅くなりそうなSQLを出すのもいかがなものかと
質問者が悪いと言えばそうだが、
質問者のレベル推定も含めての回答にしないと、ここでは成り立たない
「ソートして良い」と勝手に決めるのが問題なら、
「ソートしてはいけない」と勝手に決めるのもまた問題でしかない
なら聞けばいいのに、それもしない
(なお、しても意味は通じず、余計に面倒になるのは見えているので、
今回のようにいきなり189で問題ないとも思うが)
なんというか、この辺の空回り具合は、文化の違いを俺は感じたよ
ただまあ俺らが特殊なのかもしれないけどな
この板では基本的にエスパー出来ないと回答は無理だし SQLで4桁文字列'hhmm'とDatetime型を結合する方法って分かる?
例:
’1234’ char型
’2022/07/12 00:00:000’ datetime型 があったときに、
↓
2022/07/12 12:34:000 datetime型 みたいに結合したいんだけど。。 とりあえず'1234'を12と34の数字にして、元の日付に12時間と34分を足せばいいんじゃねふ
日付まわりはDBMSによって違うから
日付時刻をサポートしてるDBMSなら指定の時間、分を足す関数かなんかがあるだろうからそれ調べて使え >>211
普段、日時の計算をどうやっているのか、むしろ疑問ですね。
古い本でいいから古本を買えよ! 複数のselect count(※)を1つのファイルに出力する方法教えてください。。 SQLで急にファイル出力とか言われても何のこっちゃ分からんわ
シェルスクリプトで普通にappendでもしろや explain analyzeを付けると1秒、付けないと30秒かかるクエリがあるのですが一般的には何が原因なのでしょうか >>220
両方同じようにクエリが実行されてるのなら
結果セットの転送コストがめちゃくちゃ大きいんじゃないの? SQL Server 2022 正式リリース(米国11/16日)
MSDNダウンロードサイトでDeveloper EditionのISOリリースを確認 phpMyAdmin使ってます。
型が合わないデータの入力があったとき、
拒否するか、無理やりデフォルトの値を登録するか。
それを設定するコンパネとかありますか? ストアドプロシージャって初心者用解説サイトとかで戻り値のない関数って言われてるけど普通にRETURNできるやん
歴史的な背景でもあるんか
それとも解説者か俺があほなんか >>224
一部のDBMSの古いバージョンではOUT/INOUT引数を使わないと値を返せなかったんだよ >>224
それはファンクション(関数)とストアドプロシージャの違いがあまりないDBMSの場合 ファンクションは戻り値があるプロシージャという意味 VBもAda言語もそうだけど、ファンクションプロシージャとサブプロシージャをわけているのは、プログラミング言語ではめずらしくない。
C言語、C言語に影響を受けた言語だと、わざわざ戻り値なしをvoidと書いて明確にする。
ファンクションは戻り値を利用するもの。戻り値を呼び出し側が無視できる仕様かどうかの問題。 SQL標準的にはプロシージャに戻り値があるかどうかはimplementation defined
戻り値があったほうが便利なので多くのDBMSベンダーはscalar valueとresult setのどちらも返せるようにしてる
一方(ストアド)ファンクションはSQL標準で戻り値が必須でscalar valueのみと定められている
プログラミング言語でファンクションとプロシージャを区別してたのは太古の昔の話
今ではもうそんな区別に価値はなくなってる
SQL標準で区別されてるのは利用方法や利用する場所が基本的に違うため MySQL系はファンクションだと、COMMITなどのトランザクション制御ができないので、あまり考えずにファンクションで統一などしてはならない。 >>233
前半と後半で違う説明をしているね。
ファンクションは「関数」だ。
OUTパラメータを参照するのは「手続き」という処理だ。
プロシージャで戻り値とOUTパラメータを返す場合は、ちゃんと仕様書がないとわけのわからないものになる。 そもそも、DBMSの指定や前提もなく
>ストアドプロシージャって初心者用解説サイトとかで戻り値のない関数
とか書いてあるなら、そのサイトに問題があるだけの話
まあ、ちゃんと前提を読んでないか理解してない読み手の問題の可能性もあるが 関数やプロシージャのことを「メソッド」と書いている書籍もあるくらいだからな。勝手な名前をつけるやつはたくさんいる。 ざっくり言うと
ファンクションは1つのSQL文の中でSELECT句やWHERE句などの一部として繰り返し利用する機能を定義したもの
プロシージャはSQL文を使ったひとまとめの処理をSQL文の一部としてではなく外部から繰り返し呼び出せるように定義したもの
これが最も根本的な違い
ファンクションでトランザクション制御しようと考えてしまったりデータベースを更新しようと考えてしまう人はこの根本的違いを理解してない そもそもプロシージャは手続きという処理という意味で、ファンクション(関数)は機能という結果を返す処理の意味。 全然違う
またいい加減なことを言って振り出しに戻すのやめろ ファンクションプロシージャは結果を戻り値で返す
サブプロシージャはOUTパラメータ(引数)で返す
SQLの関数は関数(ファンクションプロシージャ)で実装しないとSQLの構文が破綻する >>241
だから、オラクル社はAda言語をそのまま流用しただけだよ。
ANSIはファンクションの方を先に標準SQLに組み込んだだけ。
ファンクションとサブを区別しているかどうかは、Ada言語を踏襲しているかどうかの違い。
C言語のように戻り値を無視する構文がいいのか悪いのかという話だよ。 perl の戻り値の仕組みは面白いと思ったのは古い思い出 noSQLのスレはないの
オブジェクト指向データベースとかはsql使うのかな