SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 16問目
http://echo.2ch.net/test/read.cgi/db/1447160858/ 不利っつっても、比較が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でいけるでしょうか？よろしくお願いします。 >>332
SELECT S1.col_a, S1.col_b, S1.col_c
FROM 'テーブル名' S1 , 'テーブル名' S2
WHERE S1.col_a = S2.col_a
AND (S1.col_b <> S2.col_b OR S1.col_c <> S2.col_c)
ORDER BY S1.col_a ;
間違ってたらごめん >>332
グループ化で複数レコードが存在存在するnameを排除すればいい。 複数レコードが存在するレコードを削除すればよい、ではなくてか。
having count(*) = 1 みたいに。 まとめると
SELECT col_a, col_b, col_c FROM テーブルデータ GROUP BY (col_a, col_b, col_c) HAVING COUNT(*) = 1
こうかね？
ORDER BYもいるとは思うけど
DISTINCTは name2 0 2 も1件でちゃうような select * from テーブルデータ where col_a in (select col_a from テーブルデータ group by col_a,col_b,col_c having count(*) = 1); a,b,c以外にも表示したいときはそうなるか
まあ (a,b,c) in (select a,b,c ｆrom 〜 ) とかになるだろうけども 項目A,B,C,Dの値を入れ替えたいです。
・DBMS名とバージョン：postgreSQL 8.4.13
・テーブルデータ
A B C D
1 2 3 4
1 2 3 4
2 3 4 1
このテーブルのAの値をBに、Bの値をCに、Cの値をDに、Dの値をAに入れたいです。
A B C D
4 1 2 3
4 1 2 3
1 2 3 4
としたいです。
UPDATE TABLENAME SET A = D, B = A, C = B, D = C;
でよいのでしょうか。よい場合、変更する項目数が50位でも大丈夫でしょうか。
検索したところ、２項目の入れ替えはこれでよいようなのですが、
複数（多数）の場合でもよいものか教えていただきたいです。
よろしくお願いします。 >>342
ありがとうございました。自信を持って（？）作業します。 >>344
大丈夫
updateが完全に完了するまでは古いレコードは残っていて（そうしないとrollbackできない）、
>>341のクエリは、更新前のレコードをold、更新後のレコードをnewとするなら、
UPDATE TABLENAME SET new,A = old.D, new,B = old.A, new,C = old.B, new,D = old.C
というような処理が行われる レスありがとうございます。
>>344 >>345
検索して調べたときに知ったのですが、
postgreSQL,SQLserver,おそらくoracleは大丈夫。
MySQLは、左から順に評価するので、たぶんBはDの値になるようです。
みなさん一時項目を使ったり、足し算引き算をしたりして工夫されているようです。 mysqlは(1,2,3,4) -> (4,4,4,4)になるよ
クソ え、SQLってこの程度のことも規約で決まってなかったのか >>345
>>346
質問者じゃないけど、参考になる例題でした。 一時テーブルを作成して、更新後の並びになるようにコピーする
元テーブルのレコードを削除して、一時テーブルからコピーする
なんてやるのはどうなんだろう？
create temporary table tmp select d as a,a as b, b as c, c as d from TABLENAME;
delete from TABLENAME;
insert into TABLENAME select * from tmp; 【テンプレ】
・DBMS名とバージョン ： mysql Ver 14.14 Distrib 5.1.73,
・テーブルデータ ： 添付画像をご覧ください
・欲しい結果 ； 添付画像をご覧ください
http://fast-uploader.com/file/7055447564296/
・説明
※添付画像では、col1被り数の1行目（セル番地で言うとおB5セル）を例に取っています）
※添付画像の、数式表示欄を見ていただますようお願いいたします。（Excelの式が入っております。）
DB上にテーブルがあり、code1、code2、code3と列があります。
code1、code2、code3の、全ての行の「どこか」でデータが被っています。被っていない所もあります。
被っているのは、同一列だったり、別の列の違う行だったり、はてまて、同じ行の別の列だったり様々です。
これを、col1被り数、col2被り数、col3被り数のように、「被ってる行」をカウントしたいんですが、
方法がさっぱり思いつきません。
何卒ご教示くださいますよう、お願いいたします。 >>350
SQLが通るかどうかは置いといて、結果の並び順は保証されていないので
キーを使うなどしないと「たまたま」動作したということになると思います。 >>351
元テーブル名が分からなかったので、partsと仮定した
http://ideone.com/wZK0bA >>353
ありがとうございます！印刷して家宝にします！！！ >>354
列の並び順を替えるにしても、行の順が元テーブルと違っちゃうかも
しれませんよという話です。
キーの部分を書くのが面倒で省略したということかもしれません。 行の順序が変わったとしても、それが何か影響を与えるとは思わないんだが 初めてVPSで構築しています。
MySQL設定でハマってます。
Pleskだと/etc/my.cnfや/etc/php.d/mysql.iniは無視されるのでしょうか？
my.cnfに書いてみたんですが、どうも反映されてないようです。
MySQLTunerを実行してみると
failed to execute: SELECT VERSIONのようなのが鬼のように表示され、
General recommendationsに下記のように表示されてます。
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_open_cache (> 400)
performance_schema = OFF disable PFS
innodb_file_per_table=ON
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.
(=0)は0にしなさい。
(> 1M)は1MB以上に指定しなさい。
それに合わせてmy.confに入れてみたんですが、
これが全く変わりません。 ■ このスレッドは過去ログ倉庫に格納されています
