【帰ってきた】SQL質疑応答スレ 4問目

>>950
ここはSQLServerのスレじゃないよ

Microsoft SQL Server 総合スレ 5
http://pc11.2ch.net/test/read.cgi/db/1175091880/

>>951
共有ロック式の場合のRepeatable Readは、トランザクションが読み取ったレコード全部に
コミットまでの期間共有ロックをかけてゆくと考えればいい。
＞トランザクションBが表AのレコードBをUpdate更新して、コミット
この前にトランザクションAがレコードAを読み取っていればトランザクションBの更新はロックされ待機状態になる。
この前にトランザクションAがレコードAを読み取っていなければ競合は起きない。

マルチバージョニングの場合はシリアライザブルと同じ動作になる実装が多いと思う。Oracleとか。

>952
失礼しました。　そちらに質問しました。

なるほど。あなたは天才ですね。まぁ、言葉の定義の問題になっちゃうかも
しれまんせが、Repetable ReadはOracleとかのシリアラザブルのような動作が本来あるべき意味かなと
思いまして。よく、Repetable Readの説明で*実際にSELECT*文を同じ条件などで
検索させる説明がありますが、あくまで例で、本来はSELECT文を発行するかは関係なく、
Repeatable Readのトランザクションの*開始時点*の既に存在する行のみ
保証し、開始以降他によって追加された行(ファントム行)は保証しないのが、本来のRRなのかなと。
つまり、951の例では、トランザクショクンAの更新で、既にBによって更新され、
トランザクションの開始時点の行を保証できないから、エラーで落とす。

それであれば、よく、Read Committedレベルの時に、日付のタイムスタンプ見てロスト
アップデート防いだりしますが、RRだと本来は共有ロックとか実装方式に関係なく、
ロストアップデートとか考える必要がない。

と、共有ロック方式のRRは偽RRじゃないかなと。まぁ、すっきりしました。
ありがとうございます。

>>955
いや、ちょっと理解が違うような。

Repeatable Read と Serializable では、
あるトランザクションが一度読んだレコードが変更されないのはどちらも同じ。
（Read Committed では変更されることがありうる。
　そういう意味で>>956の理解は正しい。）

Repeatable Read と Serializable の違いは、
Repeatable Read ではあるトランザクションが最初にある条件で検索し、
後でもう一度同じ条件で検索したとき、レコードが増えていることが
ありうるのに対して、Serializable ではそれも許さない。

>>957
あ、すみません。語弊がありました。
>Repetable ReadはOracleとかのシリアラザブルのような動作が本来あるべき意味
はOracleのシリアライザブルみたいに、シリアライズ不可能ですみたいな感じに951の
例で、「トランザクションAが同じ表AのレコードBをUpdateで更新 」の時に、
リピータブル不可能ですとエラーで落とすべきという意味です。おっしゃるとおり、Serializableはファント行も許さない、
つまり、トランザクション開始時のデータの完璧なスナップショットを保証することと思ってます。

ただ、共有ロック式のRRはトランザクション開始時に既に存在していた行を保証するのではなく、
開始後、SELECTして一度読んだ行だけしか保証しないので、こういうトランザクションの
振る舞いをRRって言わない方がいいのかなと思ってただけです。まぁ、言葉の問題ですが。えらい混乱しました。

みなさんはＳＥなのかＰＧなのでしょうけど
具体的なビジョンはもってますか？

>>958
951の例で言えば「トランザクションAが同じ表AのレコードBをUpdateで更新」は
Repeatable ReadでもSerializableでも許される。
（トランザクションBが更新する前にトランザクションAが読んでいなければ。）

つまり、問題は開始時点でどうたったかじゃなくて一度読んだかどうか。

その上で、Repeatable Readでは一度読んだ「レコード」に対してだけ
ロックがかかるのに対して、Serializableでは一度検索した「条件」に対しても
ロックがかかるというイメージ。
Serializableでは他のトランザクションが一度検索した条件に抵触するレコードは
更新できない。

ただ、この違いを実装しているDBMSがあるかどうかは知らない。

教えてください
列1,列2
A, AA
A, AB
B, BA
B, BB
B, BC
とある時に、列1でグルーピングされたROWNUMのような値を
得るにはどうしたらよいでしょうか？
つまり、このような結果が欲しいです。

列1,列2,欲しい値
A, AA, 1
A, AB, 2
B, BA, 1
B, BB, 2
B, BC, 3

DBはOracle10gです。
>>881と似ている気がするのですが、もう少しシンプルです。

select 列1,列2, rank() over (partition by 列1 order by 列2) as ,欲しい値 
from xxxx order by 列1,列2

>>962
ありがとうございます。なるほど。

ついでに言うと、このような取り方もできますか？
列1,列2,欲しい値
A, AA, 1, 1
A, AB, 1, 2
B, BA, 2, 1
B, BB, 2, 2
B, BC, 2, 3

教えてもらった方法を参考に考えたのですが、
SELECT 列1, ROW_NUMBER() OVER(ORDER BY 列1)
GROUP BY 列1
の結果と、>>962で教えていただいた結果をJOINしようと思ったのですが、
もっと華麗な方法がありますか？
と、教えていただいた検索結果を結合しようと思ったのですが、

あるテーブルに以下のデータが入ってるとします。
（テストの点数と氏名が入ってるテーブルです））

---------------------
name, tokuten
---------------------
satou, 60
nakata, 70
takahasi, 80
hukuda, 90
nakamura, 100

このテーブルからSQLで、以下の結果を導き出したいです。
---------------------
name, tokuten
---------------------
nakamura, 100
hukuda, 90
takahasi, 80
その他, 65
---------------------

上位三名のnameとtokutenを表示し、
四位以降は、"その他"でまとめて平均値をだします。
このようにするSQLはどのように書けばよろしいでしょうか？

MySQLを使っております。
よろしくお願い致します。

cursor cur is
select * from A
union all
select * from B
for update

て書くとforうｐ句は書けﾈｰﾖって怒られるんだけど、もしかして ROWID を
ｾﾚｸﾂするようにして、where current of cur って書いてるところを
where rowid = rec.rowid って書くと超いい感じじゃね？
やべ、ｽｹﾞｰ発見しちゃったかも。

>>964
SELECT CASE WHEN cnt=4 THEN 'その他' ELSE name END AS name,tokuten FROM (
  SELECT cnt , avg(tokuten) AS tokuten FROM (
   SELECT (SELECT CASE WHEN count(*)+1 < 4 THEN count(*)+1 ELSE 4 END FROM Table WHERE tokuten > T1.tokuten)AS cnt ,* FROM Table AS T1
  )AS T2 GROUP BY cnt
) AS T3 LEFT JOIN Table USING(tokuten) ORDER BY tokuten DESC;

見落としてた。
MySQLで動くかどうかは知らない。

>>963
DENSE_RANK OVER(ORDER BY 列1)

>>968
DENSE_RANK！！
列1の順位を同じ順位を飛ばさないように付与すればいいのか！思いつきませんでした。
結合しなくて済んで、すごくシンプルになりました。
ありがとうございます。

SELECT 列1,列2,
DENSE_RANK OVER(ORDER BY 列1) as G1
RANK() OVER (PARTITION BY 列1 ORDER BY 列2) as G2
FROM XXX ORDER BY 列1,列2

テーブル名　d_tbl

YMD
20101010
20091010
20080808
20071010
20070928
20070303
20061220
20061014
20060907

上のようなデータが入ったテーブルから
2006年の10月だけを取り出すにはどうしたらいいのでしょう。
よろしくお願いします。

>>970
select YMD from d_tbl where YMD like '200610*';

>>971
%じゃね？

>>971
すみません間違いました。
何年かは関係なく、10月を取り出す場合はどうしたらいいのでしょうか。

YMD like '____10%'

substring(YMD, 5, 2) = '10'

>>974-975
SUBSTRの場合も教えて頂き、ありがとうございます。

どちらも9999年までしか使えないなｗ

YMD like '%10__'

すみません、SQLの第一、二、三正規系などが初心者向けに
解説されているサイトはないでしょうか？
意味がわからなく困っています・・・・参考書四でもさぱーりです。。。
因みに、やさしく学ぶ１ＳＱＬです

つデータベースの教科書

>>980
うざい

すいません。SQLを教えてください。

ID int
TYPE int
DATE datetime
からなるテーブルがありまして、以下のようにデータが入ってます。
| id | type | date |
| 1 | 1 | 2007-10-26 08:00:00 |
| 2 | 1 | 2007-10-26 09:00:00 |
| 3 | 1 | 2007-10-26 10:00:00 |
| 4 | 2 | 2007-10-26 11:00:00 |
| 5 | 2 | 2007-10-26 12:00:00 |
| 6 | 2 | 2007-10-26 13:00:00 |
| 7 | 3 | 2007-10-26 14:00:00 |
| 8 | 3 | 2007-10-26 15:00:00 |
| 9 | 3 | 2007-10-26 16:00:00 |

これをTYPE別にdateが最新の一件ずつで取りたいのですが、
どのようにしたら良いでしょうか？

上記の例の場合
| 3 | 1 | 2007-10-26 10:00:00 |
| 6 | 2 | 2007-10-26 13:00:00 |
| 9 | 3 | 2007-10-26 16:00:00 |
を取りたいと思います。

お手数おかけしますがご教授の程よろしくお願いします。

typeをgroup化してdateでmaxを取る

>>983
レスありがとうございます。

select type,max(date) from table group by type;
これでtype別のdateは取れますが、
idが欲しい場合はどうしたらよいでしょうか？

select id,type,date
from table
where date in (
select max(date) date from table group by type
);
これだとダメですよね。

>>984
ﾎﾟｶｰﾝ
>>980

すみません、質問させてください
aの重複値を無視して下のようにカウントしたいのですが
どのように書いたら良いのでしょうか？
ご教示お願いします

a b
------
1 1999
2 1999
3 2000
3 2000
4 2001
5 2001
5 2001

b　　count(*)
--------
1999 2
2000 1
2001 2

count(*) by b

間違えたgroup by b

>>986
SELECT b,count(*) FROM (SELECT a,b FROM Table GROUP BY a,b) AS T1 GROUP BY b;

select b, count(*) from (select distinct a, b from Table) group by b;

皆様ありがとうございます

>>988
その方法だと重複がカウントされてしまいました

>>989>>990
今MySQL 5.0.45で実験してるんですが、サブクエリが何故かエラーになってしまいました
ちょっと環境について調べてきます

すみません、完璧でしたテーブル名をタイポしてました
皆様ありがとうございます

サブクエリは必須みたいですね、勉強になりました

次スレ
http://pc11.2ch.net/test/read.cgi/db/1193486961/

>>993乙

こっち埋めるか

埋め

ume

梅

998

1000

>>999

このスレッドは１０００を超えました。
もう書けないので、新しいスレッドを立ててくださいです。。。