SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 16問目
http://echo.2ch.net/test/read.cgi/db/1447160858/ >>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に入れてみたんですが、
これが全く変わりません。 straceしてみてどの設定ファイル読んでるか確認してみたら？ 久しぶりに来た半初心者なのですが、上の方の議論で出てた会計年度の話は、単に引き算を利用してはいけなかったんでしょうか
4ヶ月引いて１日足して、としてやれば安定して通常年度に戻せる気がするのですが 日付から年度を求めたい、という話？
そういうのでもいいけどそれ間違ってるからね どの話をしようとしているのか分からん
4ヶ月引いて1日足すというだけでも
30日に1日足したら31日になるのか1日になるのかどう判断するんだ？ >>363
ほんとだ
なんで足す１出てきたし４なんですかね
なんにせよありがとう 例えば、左から右に行って、
途中でジェイってなって、そのまま終わったらいいかと思うんですが。
新しいSQLの概念というか。 間違えた。 >>386じゃなく>>366だった^^; >>372
月末の次の日が1日じゃないケースってなに？ >>371の言う「翌月の1日前」が「翌月の1日の1日前」という意味なら、ってことだろ。 おまえ、そんな頭のレベルでよくSQL云々出来るなぁ IPAの試験が、読解力を試されるような問題だらけになるわけだ。 複数の同一形式のcsvからデータを読み取る時、普通は↓こんな風に定義するけど、
[001.csv]
[002.csv]
[003.csv]
Col1=F1 Char Width 255
Col1=F2 Char Width 255
ユニオンで縦連結する時は、↓こうじゃないと定義内容が反映されない。
[001.csv]
Col1=F1 Char Width 255
Col1=F2 Char Width 255
[002.csv]
Col1=F1 Char Width 255
Col1=F2 Char Width 255
[003.csv]
Col1=F1 Char Width 255
Col1=F2 Char Width 255
何で? ・tableA
日付、名前、国語、算数、英語
5/1 赤木 100、100
5/1 三井 50、70
5/1、桜木、40、20
6/1 赤木 100、100
6/1 三井 50、50
6/1、桜木、20、40
7/1 赤木 100、100
7/1 三井 70、70
7/1、桜木、50、50
・tableB
採点開始日、名前
7/1、桜木
5/1、赤木
6/1、三井
・採点平均
名前、国語平均、英語平均
赤木 100、100
三井 60、60
桜木 50、50
↑のテーブルAのデータを
テーブルBの採点開始日からの採点平均をだしたい
↓で大丈夫だろうか? あらかじめJoinしておいたほうがレスポンス的にはよいのかな？
SELECT tableA.名前,AVG(tableA.国語）AS 国語平均,AVGtableA.英語）AS 英語平均
FROM tableA,tableB
WHERE tableA.日付 >= tableB.採点開始日
AND tableA.名前,tableB.名前 >>381
tableA は
日付、名前、国語、英語
です。 >>381
SELECT tableA.名前,AVG(tableA.国語）AS 国語平均,AVG(tableA.英語）AS 英語平均
FROM tableB
inner join tableA
on tableA.日付 >= tableB.採点開始日
AND tableA.名前=tableB.名前
group by tableA.名前
でいいんじゃないかな >>383
ありがとうございます
join したほうがいいのかな
これを参考にしてやってみます。 >>384
データ量はたぶんたかだか数万レコード程度だろうから、どんなやり方でもパフォーマンス的には気にする必要ないと思うよ >>383
横からだが、fromとwhereで結合しても、joinで結合しても
書き方が違うだけで同じだぞ
パフォーマンス気にするなら、使ってるDBMSの実行計画読めるようにならないと
事前に結合した実データ(のテーブルやビュー)用意するんじゃなければ
SQLの書き方では差がでないのが原則 じじいが嘘を広めていることもあるから混乱するんだよな。 tableAのデータがあった場合、tableBの結果と、tableCのビューが欲しいです。
tableAの補習が入った場合は学校にいくまでの間はすべて補習の時間になります。
まったく書き方が見当がつかないのでアドバイスお願いします
tableA
時間、学校、部活、補習
2017/6/1 06:00:00、NULL、OK、NULL
2017/6/1 07:00:00、NULL、OKL、NULL
2017/6/1 08:00:00、OK、NULL、NULL
2017/6/1 09:00:00、OK、NULL、NULL
2017/6/1 10:00:00、OK、NULL、NULL
2017/6/1 11:00:00、OK、NULL、NULL
2017/6/1 12:00:00、OK、NULL、NULL
2017/6/1 13:00:00、NULL、OK、OK
2017/6/1 14:00:00、NULL、OK、NULL
2017/6/1 15:00:00、NULL、OK、NULL
2017/6/1 16:00:00、OK、OK、NULL
2017/6/1 17:00:00、OK、NULL、NULL
2017/6/1 18:00:00、NULL、OK、NULL
2017/6/1 19:00:00、NULL、OK、NULL
2017/6/1 20:00:00、NULL、OK、NULL
2017/6/1 21:00:00、NULL、OK、NULL
tableB
時間、活動
2時間、部活
5時間、学校
3時間、補習
2時間、学校
4時間、部活
tableC
時間、活動
7時間、学校
6時間、部活
3時間、補習 他人にわかる説明ができるようになったら解決するんじゃないかな。 >>388
tableBとtableCはビューB,、ビューCをだしたいに訂正します。
ビューCはビューBの部活、学校、補習の合計時間をだします。
tableA は1時間間隔で 活動した予定にOKが付きます
６時と７時は部活をやっているので２時間になります。
そのあと学校が５時間
その次は部活と補習がOKになっていますが
補習がOKなったら、学校がOKになるまで補習の時間なので
補習が２時間になります。
この流れで↓の結果が欲しいです。
ビューB
時間、活動
2時間、部活
5時間、学校
3時間、補習
2時間、学校
4時間、部活
ビューC
7時間、学校
6時間、部活
3時間、補習
SQLだけで書くのは見当がつかないのでアドバイスお願いします。 SQLだけで書けないと思ったのにSQLスレなのか
DBだけでやれない想定として、どういう風に実装予定なの？
ざっくりでいいからさ(Java使って、とかWindows上でとか) SQLだけでできるかわからないので質問しました。
今回のような内容はSQLでやるべきではない?SQLでできてもものすごくめんどくさい?
の状態です。似たような内容を何件か取得したいと思っているのでしりたいです。
SQLだけでビューB、ビューCをだせるなら、Windows上のアプリ でそれを取得してCSVデータにするのが簡単だと思っています。
SQLだけで無理ならtableAのデータからCSVデータを作成のつもりです。 ビューとして取る必要があるの？
画面に表示したいとか？
取得したデータをそうしたいの？ ビューとして取れるようにしておけばそのままCSVにだすだけで簡単なのと、
画面に表示したいと思っています。
取得したデータの操作は考えていません。 データが絶対に１時間間隔で抜けはないってならSQLだけでできるんじゃね
俺ならBはtableA を時間でソートして、ホストアプリでブレークチェックしながらカウントして表示するけどな >>388
は１時間間隔でなっていますが
秒単位で間隔は一定ではないです。
すみません。
Windows上のアプリはあまり動作増やしたくないなと思っていたんですが
SQLだけでやろうとすると
大変?って感じなのかな 普通データベースの動作を増やさないように工夫すべきなんだけどな > は１時間間隔でなっていますが
> 秒単位で間隔は一定ではないです。
意味不明だし後出しフラグ立ってるしすまんが抜けさせてもらうわ mdbファイルをDSNで一般に公開する方法を教えてください。
perl公開ならiisを使えばよいことは分かります。
pdf公開ならftpサーバを使えばよいことは分かります。
mdbファイルはiisを使って公開できるのでしょうか？
iisには接続文字列の設定がありますが意味が分かりませんでした。
odbcad32.exeはネットワーク越しは無理みたいでしたし。
ACCESSというお高いソフトにはmdbファイルを公開できる
サーバ機能が含まれているのでしょうか？ >>402
データベース（ファイル）を一般公開してはいけません mdbはファイル共有型だから
そのmdbファイルをファイル共有できるようにすればOK
とレスしてみたけど
そのレベルで一般に公開するのはやめとけ さすがにイントラでってことなんだろうけど、それでも公開はまずいっしょ ■ このスレッドは過去ログ倉庫に格納されています
