SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 16問目
http://echo.2ch.net/test/read.cgi/db/1447160858/ つか、いたるところで会計年度を意識するようなシステムなら、会計年度カラムを作っとけばいいよな >>232
where句も入れて何回繰り返すのさ
面倒くさいとは思わないの？
単発の作業ならわからんでもないが会計年度みたいなのは1回じゃすまないだろ
>>231
日付型でデータを持ったテーブルがあったり
日付型に徐々に移行したい場合に同じ名前で扱えるとうれしいから
会計年度って概念をDBに反映させる一つの手段 >>234
> 面倒くさいとは思わないの？
いや、まったく
>>232レベルのクエリなら1０秒もかからずタイプできるだろ >>234
> where句も入れて何回繰り返すのさ
ストアドでも似たようなもんだろ。
select fiscal_year(year, month), sum(hoge) from fuga group by fiscal_year(year, month)
しかもストアド使うと、year, monthにインデックスあっても使われないし。 kantomi@qiita_banned < ストアド！ストアド！ >>235
面倒くさいと思わないならいいんじゃね
>>236
asで名前つけとけばgroup byでは繰り返す必要ないよ
たとえ繰り返しが必要だったとしても概念をその名前で直接扱える状態と
毎回展開しなきゃいけない状態では全く意味が違うんだけどね
インデックスは必要ならはればいい >>233
算出できる情報を別途カラム作って持たせるのは最終手段 >>180 重み付けの定石
>>182 単年度でしか動かないクソ
>>185 意図が分かりやすい
>>217 ビジネスロジックをDBMSに持たせる派とアプリに持たせる派で戦争勃発 >>234
fiscal_year(year, month)って関数自体はまだなにもオーバーロードしてないだろうが。 >>241のどこが馬鹿なのか論理的に説明できないやついるの？ >>240
分解せずに日付型で持っていればよかったって事だな
以降>>188へ戻る 管理上のオーバーヘッドってのがユーザ側の話なのかシステム側の話なのかしらんが
関数オーバーロードができたとして、それがビューより管理が重いとか信じられん
会計年度が必要ならそう言うビュー作れ、で終わりじゃないのか インデックス張るなら、ロシア方式が一番いいよな
トリガーで仕掛けておけば気にしなくて済むし >>239
> インデックスは必要ならはればいい
本末転倒だな
普通にクエリ書けばインデックス使えるのに、ストアドにしようと思うとさらに何かしないといけなくなる
つか、ストアドの結果にインデックス貼れないDBもあるんじゃね？ >>239
> asで名前つけとけばgroup byでは繰り返す必要ないよ
その手段が使えるDBでは、>>232も同じことが言える
> 毎回展開しなきゃいけない状態では全く意味が違うんだけどね
例えば四半期ごとの集計がほしいとか、移動平均がほしいとかいうことになったら、
そのたびにストアド作るのか？
増殖しまくりだな ん？ >>232 だとインデックス使われないだろ
>>217-218 >>185 のような関数だともっと使われない
>>182 のように or あると、うまく使ってくれるか怪しい >>251
> ん？ >>232 だとインデックス使われないだろ
where書いてないからね
>>232は、集計がしんどいという奴へのレス
>>182 のように or あると、うまく使ってくれるか怪しい
大抵のRDBMSだったらうまく使ってくれるんじゃね？ それより決算月が変わる可能性について考えたほうがいいと思う >>251
> >>217-218 >>185 のような関数だともっと使われない
こういうバカ避けのためにわざわざ「性能は知らん」って書いてあるのにバカはそれすら理解できないんだな w
インデックス使いたいならdate型にしとけよ >>254
year, monthにインデックス張ればいいだろ
アホか ビューのコストが高いというのがparserの話だとしたら、単純なビューなら最近では全く問題にならない
まぁ、大抵の場合、クエリ1回につき＋1ms未満だろう。
（さらには、直近のparse結果をcacheしている場合もある） >>256
> year, monthにインデックス張ればいいだろ
インデックス張っても関数の引数にしちゃったら使われない >>256
バカってこれだから...
複数の列に張るより単一の方が有利だろう
そんなことも理解できないのかよ w そんな理由だったらオレもアンタをバカ呼ばわりしていいかな 不利っつっても、比較が1回か2回かって違いくらいしかないだろ。 ここを2つに分けるかどうかの是非はともかく
キーが2つになるから絡むつにしようとかおかしいだろｗ
まあこのケース、月なんて12通りしか無いんだし複合キーで >複数の列に張る
こういう言い方しているところを見ると、複合インデックスの仕組みをわかってないんだろう。 yearのインデックスとmonthのインデックスを別々に張りそう >>250
>その手段が使えるDBでは、>>232も同じことが言える
そんなんわざわざ言わんでもわかっとるがな〜ww
>増殖しまくりだな
増殖して何か問題あるの？ >>246
システム側の話
組織構造なんかも大きく影響するから環境による
導出列はビューを使うっていう規約があってそれに従ってるようなところならオーバーヘッドも少ないだろうね
ただ関数よりビューのほうが管理の厳格度というか管理レベルが高いのは一般的じゃないのかな？
管理レベルが高ければその分のオーバーヘッドはかかる
それに同じような年・月で持ってるテーブルが10個に増えたら10個ビューを追加することになる
そういうのが嫌だからComputed Column的な機能があるDBが多いと思ってる >>264
お前こそわかってないだろ w
>>180, >>182 みたいに複合インデックスの各々の列を別々に大小比較で使うとインデックス使えないぞ 使われないインデックスと、使えないお前らの脳ミソ達。 >>182は(orは別として)yearとmonthの複合インデックスの教科書的な例だと思うが。
つか、>>180と>>182を同列に並べている時点でお里が知れる。 >>267
管理レベルってのがなんだかわからんが
ビューの方がより厳密に適用される分、問い合わせ時のシステム的なオーバーヘッドは小さいだろ
実行計画もより最適化できる
つか、ユーザー側／システム側って切り分けが、どっちも人員の切り分けだと思ってる？
>それに同じような年・月で持ってるテーブルが10個に増えたら
同じものが分散して存在してたら、その時点でテーブル設計が間違ってるわ >>270
実行計画見てみ
複合インデックスの仕組み知ってたらそんなアホな発想はできないよ おまえこそ見てみろと言いたいが。
念のためだが、ヘボいオプティマイザがorのせいでスキャン範囲の限定に失敗するのは
「複合インデックスの各々の列を別々に大小比較で使うとインデックス使えない」てのとは
関係ないからな。 >>274
> orのせいで
バカを晒すのはほどほどにしろよ 今どきは>>182でも(year,month)のインデックスがあれば
（そしてそれを使ったほうが速いとDBMSが判断すれば）
使うDBMSのほうが多いと思う
パフォーマンスに関しては昔の定石を今はあまり気にしなくても
良くなっていることが多いので必ず実機で試してから語ったほうがいい >>276
それはyearを使わずにmonthだけだった場合だな。こんな初歩的な勘違いしてたのか。 >>278
バカはこれだから...
わざわざ
> インデックスの構造をより深く見ていくと、この理由がはっきりします。
って言うところまで引用してるだからその下読めよ
理解できるかどうかは知らんけど w year, monthにインデックスの件だけど、PostgreSQLなら使われるけどなぁ
ほかのDBだと使われなかったりするのか？ create table hoge (year integer, month integer, amount integer);
create index hoge_idx on hoge(year, month);
select year, month, sum(amount)
from hoge where (year = 2017 and month > 3) or (year = 2018 and month < 4)
group by year, month;
実行計画：
https://explain.depesz.com/s/rwx 実行計画見るような人なら当然わかっているだろうけど、統計情報がとられていなかったり
選択されるレコードの割合ががテーブル全体に対して大きい場合はインデックススキャンが
選択されないから注意な。 インデックス使われないマン ＝
オーバーロードマン ＝
Computed Columnマン まさか今どき、インデックスがあれば必ず使うとか思ってるんだろうか
ルールベースとコストベースの違いも分かってない？ >>285
おいこら
インデックス使われないマンと一緒するなよw お前らが馬鹿なのは使う必要のないインデックスについて延々と話してることだけどな >>282
それ >>287 が書いてる通り Bit Map Index Scanだよ
URL読めばわかると思うけど
> ビットマップスキャンは、一度に全てのタプルへのポインタをインデックスから取り出し、
----
インメモリな「ビットマップ」データ構造を使ってソートし
----
> 物理的なタプル位置の順にテーブルのタプルにアクセスします。
つまりyearとmonthに対して個別にインデックス張っときゃソートなんて要らない
要するに >>264-265 をディスってるだけなんだが w 結論
>year, monthにインデックス張ればいいだろ
>アホか まあ、日付型とか言ってる奴は決算月の考慮どうするんだろうと思うわ >>297
決算月と日付型との間に問題があるなら後学のために披露しては？
それか質問なら>>1読んでからどうぞ >>287
データ量が多いというのは1億レコードとかそんな単位？
>>282は100万件だったけど、1000万件でも同じだよ。
>>288
そういう話じゃない。
ストアド作って、where fiscal_year(year, month) = 2017とやると通常はインデックスは使われないが、
普通にクエリ書けば、適切な場面でインデックスが使われるという話だ。
> ルールベースとコストベースの違いも分かってない？
PostgreSQLの話になるが、PostgreSQLにはルールベースのオプティマイザはないよ。
>>295
コメントの意図がわからないんだが。
> つまりyearとmonthに対して個別にインデックス張っときゃソートなんて要らない
それ、monthに対するインデックスの意味ないよね。 >>303
> データ量が多いというのは1億レコードとかそんな単位？
> >>282は100万件だったけど、1000万件でも同じだよ。
マジで仕組みを理解してないんだな w
そっちのデータ量じゃなくてインデックスの方
要するに年と月の数の話
> それ、monthに対するインデックスの意味ないよね。
ないと本気で思ってるなら真面目にもう少し勉強した方がいいと思う >>304
> マジで仕組みを理解してないんだな w
わかるような単語使いましょう。ちゃんと用意されてるんだから。
> 要するに年と月の数の話
カーディナリね。
> ないと本気で思ってるなら真面目にもう少し勉強した方がいいと思う
どういう意味があるんだ？ >>305
> カーディナリね。
それを言うならカーディナリティな
> わかるような単語使いましょう。
でかいブーメラン乙 w
きちんと理解せずに背伸びするからそう言う間違いをしちゃうんだよ
> どういう意味があるんだ？
普通にインデックスとして使われるだけだが？
なぜmonthは使われないと思ったんだ？ >>306
> それを言うならカーディナリティな
知ってるなら最初から使おうな。
> なぜmonthは使われないと思ったんだ？
使われないから意味ないんだよ。
まぁ、monthを軸にした検索をすれば使われるだろうが、今回の流れとは関係ないね。
俺がコメントを続けた意味が理解できてないようだから、再説明しとこう。
>>295
>>282
> それ >>287 が書いてる通り Bit Map Index Scanだよ
> URL読めばわかると思うけど
これが意味不明なんだが。
bitmap index scanだから何？ 脇道の細かい議論しても仕方がないから、論点を絞ろう。
君が主張したいのはこれか？
>>259
> 複数の列に張るより単一の方が有利だろう
それに対する俺の主張は、「year, monthに複合インデックス貼る方が有利」。
理由は、
・インデックスが全くない場合は、seq scanになり、論外
・yearのみにインデックスを張った場合、「2017年度」のデータを参照するとき、2年分のデータを読み1年分のデータを捨てる必要がある
・monthのみのインデックスには意味がない
・year, monthにインデックスを張れば、>>179のような会計年度別集計などの場合にインデックスが使われる（もちろん、使った方がコスト的に有利な場合）
・（おまけ）year, monthにインデックス張っても、where fiscal_year(year, month) = 2017などとするとインデックスが使われなくなる
・（さらにおまけ）PostgreSQLには、関数インデックスという機能があり「fiscal_year(year, month)」に対してインデックスを貼ることができる
・（蛇足）そこまでするなら、普通にクエリ書け >>307
> 知ってるなら最初から使おうな。
ちゃんとした知識持ってる奴なら >>287 のリンク先読めばわかるし
それでわからんような奴にカーディナリティとか言ってもしょうがないだろ
さすがに中途半端にカーディナリとか言う知ったかさんの存在までは想定しとらん
> 使われないから意味ないんだよ。
なぜ使われないんだ？
に対して「使われないから」って日本語大丈夫か？
> まぁ、monthを軸にした検索をすれば使われるだろうが、今回の流れとは関係ないね。
>> (year=2017 and month>=4) or (year=2018 and month<=3)
で関係ないと考える奴にどう説明しろと？
> bitmap index scanだから何？
>>287 のリンク先読めよ
それでもわからないと言うから >>295 でも説明してる
さらにそれでもわからんと言うならわからない箇所を引用してくれ
すごく中途半端な知識で語ってるようだからどこがわからんのか予測できないし >>308
> ・year, monthにインデックスを張れば、>>179のような会計年度別集計などの場合にインデックスが使われる（もちろん、使った方がコスト的に有利な場合）
複合インデックスの話だよね？
それならBit Map Index Scanになるから実行時にインデックスデータについてソート処理が走るんだよ？
そこ理解してる？
ちなみに俺は
> インデックス使いたいならdate型にしとけよ
って言ってるから普通にIndex Scanするだけなのでソート処理なんて要らんから なんでBit Map Index Scanになるのが当然のような言い方なんだか。 そろそろ結論出して終わりにしてください
結論がまとまらないなら､両論併記で良いと思います お互い相手のことを馬鹿だと思っているなら
馬鹿相手にムキになっている自分を恥じたほうがいいと思うが いや既に結論出てるけど理解できない人が食い下がってるだけ >>180で答え出てるから後は設計スレでしてくれ
閑古鳥鳴いてるからウェルカムだぞ >>310
> それならBit Map Index Scanになるから実行時にインデックスデータについてソート処理が走るんだよ？
> そこ理解してる？
その「ソート処理」は、計画ノード種別の「ソート」じゃなくて、Bitmap Index Scanのアルゴリズム上、実装コードで
ソートが必要だということじゃないの？
実際、>>282の実行計画には、「ソート」はないわけで。
で、アルゴリズム上、ソートが必要だとして、何か問題でも？
> > インデックス使いたいならdate型にしとけよ
> って言ってるから普通にIndex Scanするだけなのでソート処理なんて要らんから
Index Scanの場合も、aggregateするときに、実装コードでソートが必要な気がするが。
（ソートせずに何回もループしてもいいが、多分ソートするんじゃないかと思う） >>309
> なぜ使われないんだ？
なぜもクソも使わないんだよ。
> >> (year=2017 and month>=4) or (year=2018 and month<=3)
> で関係ないと考える奴にどう説明しろと？
関係ないね。
関係あるというなら、テストデータ作って実行計画出してみな。
> すごく中途半端な知識で語ってるようだからどこがわからんのか予測できないし
俺がお前に言いたい言葉だな。 親切なので、year, monthに個別にindexを張った場合の実行計画を取ってみた。
https://explain.depesz.com/s/UapJ
書き忘れたが、
> インデックス使いたいならdate型にしとけよ
大本の話は会計年度で集計するときの話。
date型なら会計年度を取得して集計する必要があって、そこでストアドやビルトイン関数使うと
日付カラムにindexあっても使われないって話な。
さらに言えば、会計年度カラム追加しろとかいう話なら、今のままで複合インデックスつけて普通に
検索しろってこった。
（何度ループするんだよ） さらにおまけ。
# \d fuga
テーブル "public.fuga"
列 | 型 | 修飾語
--------+---------+--------
dt | date |
amount | integer |
インデックス:
"fuga_idx" btree (dt)
explain analyze verbose select sum(amount) from fuga where dt between '2013-04-01' and '2014-03-31';
実行計画：
https://explain.depesz.com/s/533s
Bitmap Index Scanになってますが。 これにもレスしとこう。
前提として、seq scanではパフォーマンス的に問題があるレベルのレコード数の場合。
>>316
> >>180で答え出てるから後は設計スレでしてくれ
whereで式を使うと、そのカラムにインデックスがあっても使われない。
> Seq Scan on public.hoge (cost=0.00..30406.00 rows=5000 width=12) (actual time=0.028..253.216 rows=100600 loops=1)
> Output: year, month, amount
> Filter: ((((hoge.year * 100) + hoge.month) >= 201604) AND (((hoge.year * 100) + hoge.month) <= 201703))
> Rows Removed by Filter: 899400
> Execution time: 288.702 ms
なお、PostgreSQLには式インデックスという機能があって、それを作ればインデックスが使われる。
create index hoge_calc_idx on hoge((year*100+month));
> Bitmap Index Scan on hoge_calc_idx (cost=0.00..106.42 rows=5000 width=0) (actual time=13.776..13.776 rows=100600 loops=1)
> Index Cond: ((((hoge.year * 100) + hoge.month) >= 201604) AND (((hoge.year * 100) + hoge.month) <= 201703))
> Execution time: 74.346 ms >>324
まあ、微粒子レベルで俺が間違ってる可能性があるからな >>325
お前が>>323なら、おかしいのはお前の相手の方だから心配すんな
>>268からずっとおかしい
相手するだけ無駄 今時、コストベースがどうこうとか言う奴だからな。
10年以上前にちょろっとDB触ったレベルの奴じゃね？ ・ストアドにしてオーバーロードしろ
・インデックス使いたいならdate型にしろ
・date型にしないなら個別インデックスにしろ
・Bit Map Indexガー
・ソートガー
全部同じやつでしょ
最初からおかしい >>329
式なんか使わずに普通にクエリ書けと何度言ったら Local and global coordinate system ・Postgresql 8.4
・テーブルデータ
｜col_a｜col_b｜col_c
-----------------
name1 1 0
name1 0 3
name2 0 2
name2 0 2
name3 0 3
name3 0 4
・欲しい結果
｜col_a｜col_b｜col_c
-----------------
name1 1 0
name1 0 3
name3 0 3
name3 0 4
・説明
列col_aの文字列が同じで、col_bとcol_cの数値が一致しないタプルを取り出したいのですが
どのようなSQLでいけるでしょうか？よろしくお願いします。 ■ このスレッドは過去ログ倉庫に格納されています
