SQL質疑応答スレ 15問目

idが数値だとして、その最大値 + 1 を返すストアドファンクションを作ってはいるけど

>>612
です。
新しいidはクエリーの呼び出し側でせっていして、古い方のｉｄをitem tabl
eからとってきて、挿入したかったのですが、ストアドプロシージャで作れました

mysql5.6
sim(id1,id2,sim,ts)
でタイムスタンプの最も古いものを最新のsimを持ったものに更新するストアドプロシージャを作ったのですが、290万回実行したいのに1800回実行したあたりで実行結果が反映されなくなってしまいます。
どうしたらよいですか？

>>616
誰に安価つけてんのかわからんがストアドいらんよ

>>617
何をしたいのか具体的に書いてくれ

>>618

最初はうまく行ったのに、作ったストアドプロシージャを呼び出しても0 rows affectedになってしまうんです。
ストアドプロシージャの中身はreviewテーブルのデータから相関係数を求めて、それをsimに入れるだけです

だめだこりゃ

DELIMITER //
CREATE PROCEDURE updateSimilarity()
BEGIN
DECLARE id1,id2 int unsigned;
DECLARE done int default 0;
DECLARE id1_ave,id2_ave,v1,v2,cov float default 0;
DECLARE rating1,rating2 tinyint unsigned default 0;
DECLARE result float default 0;
DECLARE cur CURSOR FOR
SELECT x.rating,y.rating
from (
SELECT *
from Review
where item_id=id1
) as x
,
(
SELECT *
from Review
where item_id=id2
) as y
where x.user_id = y.user_id;
DECLARE continue handler for not found set done=1;
SELECT item_id1,item_id2
from Similarity
order by ts asc
limit 0,1
into id1,id2;
SELECT AVG(rating)
from review
where item_id=id1
into id1_ave;
SELECT AVG(rating)
from review
where item_id=id2
into id2_ave;


open cur;
while done != 1 do
fetch cur into rating1,rating2;
set cov = (rating1-id1_ave)*(rating2-id2_ave)+cov;
set v1 = (rating1-id1_ave)*(rating1-id1_ave)+v1;
set v2 = (rating2-id2_ave)*(rating2-id2_ave)+v2;
end while;
close cur;
IF v1 != 0 OR v2 != 0 then
set result=cov/sqrt(v1)/sqrt(v2);
END IF;
update Similarity
set similarity=result
where item_id1=id1 and item_id2 = id2;
update Similarity
set similarity=result
where item_id1=id2 and item_id2 = id1;
END
//
DELIMITER ;

これを実行したのですが１８００回程度実行して以降テーブルに結果が反映されなくなりました。

IF v1!=0 and v2!=0 then
でした
書き間違えました

これ以上はmysqlスレに行ったほうがいいと思うけど、
show warnings;やshow errors; でメッセージとか出てないの？

>>623
けいこくもがエラーもないです
mysqlスレでも聞いてみます

自己解決しました。
類似度が０の場合、updateされた時にタイムスタンプが更新されないためでした。
ご迷惑をお掛けしました

accessの2000くらいの、古いバージョンなんですが、
いくつかのテーブルからフィールドを選んで結合したクエリがあって、
そのクエリの日付型フィールドから、既に開いてるフォームのテキストボックスに入力された日付に合致したレコードのみを取り出し、
Aフィールドの文字列によって、AフィールドとBフィールドのどちらかの値を取り出し、
新しいテーブルに書き込むということをしたいと考えてます

さしあたって日付に一致したレコードを取り出すとこからやりたいのですが
クエリの抽出条件に[forms]![フォーム名]![テキストボックス名]と書いて、
SQLで
OpenRecordset("クエリ名",dbopendynaset)
とするとパラメーターが足りないとエラーが返されます
次に、
OpenRecordset("SELECT * FROM クエリ名 where フィールド名 = # & [forms]![フォーム名]![テキストボックス名] & #, dbOpenDynaset,)
としてみると日付型が一致しないとエラーが出てしまいます

どー直せば期待する結果を得られるでしょうか

>>626
クエリ定義をSQLビューで表示して全部書いてみて
下のOpenRecordsetは"が閉じてないけど、ちゃんとプログラム通りに書いて

お世話になります、質問です。

あるテーブルに、ふりがな列と生年月日の列があります。
これを歳の若い順に50件出力したいのですが、
出力の順番はふりがな順(あいうえお順)にしたいのです。

こういうのってSQLだけでできますか？
(PostgreSQLです)

>>628
あ、すいません、もちろん副問い合わせとかなしでの話です。

もちろん　って普通サブクエリ使うだろ

余裕でできる→無理
に変わった瞬間を見た

>>628
select * from ユーザ order by 生年月日 desc, ふりがな limit 50;

それ生年月日順やん

>>633
select * from ユーザ order by date_part('years', age(生年月日)) desc, ふりがな limit 50;

>>632 >>634
ポスグレのorder byってこれでふりがな順になるの?逆におかしくね?

>>635
何がおかしいのか理解できない

サブクエリ以外でやれるDBっていったらaccessくらいしか思いつかん
通るか知らんがこんなイメージ

select * from (select * from ユーザ order by 生年月日 desc limit 50 ) order by ふりがな

あと、年の若い順だから>>634でdescはいらないね

>>637
なんだそのアホクエリーは

なんかOracleみたいだな

>>635
descが余分なのは置いといて、俺も>>634の何がおかしいのかわからんぞ。
年齢順・フリガナ順に並べて先頭50件だろ？

row_number() over　〜 って Postagre でも使えたっけ？

ん、ひょっとして、
・年齢順に並べて
・その先頭50件を
・フリガナ順で並べる
ってことか？結果として年齢順にならないソート。

>>641
生年月日順に若い方50件抜き出した上で、それを名前順にソート
>>637 は別名必須

若いほうじゃないか、まあどっちでもいいな
50件以降はいらないのだよね？

>>644
そういう要件なのか−。
でも、それって年齢順に並べたときに50件目と51件目が同じ年齢だったら破綻するんじゃないか？
50件目が、20歳、サイトウ
51件目が、20歳、アベ
で、アベが最終的に表示されなくていいのかって問題がある。

>>646
その場合は、19歳まで全部と、20歳をふりがな順にソートした(50-19歳までのレコード数)を抜き出して、
さらに全体をふりがな順に並べる、というのが要件なのか？

＞もちろん副問い合わせとかなしでの話です。

副問い合わせの意味わかって書いてるのかなあこれ、、、
SQL一発で済ませろ的な意味なんじゃないかと、、それじゃなきゃ無理だろｗ

> 歳の若い順に50件出力
をもっと正しく表して欲さないと終わらない話だなこれ

Window関数使えば出来るのかな？
まあ副問い合わせ禁止する理由がわからんが

>>637
それも普通は副問い合わせに入るんじゃね

select t1.age,t1.name,t1.seq
from user t1
left join user t2
on t1.age > t2.age
or (t1.age = t2.age and t1.name > t2.name)
or (t1.age = t2.age and t1.name = t2.name and t1.seq > t2.seq)
group by t1.age,t1.name,t1.seq
having count(1) < 50
order t1.name

動作確認はしてないが同姓同名、同年齢なら一意キーで若い方
という想定で作ってみたが、こんなんでどうだ？

レコード件数増えたら面白いことになりそうだな、これ

副問い合わせは許されんのに結合は許されるのだろうか

・mySQL5.5
・1つのカラムにカンマ区切りの数値が格納されている→1,2,3,4,5のような値
・指定した数値(複数有)がカラムに含まれているデータを取得したい

画面上にチェックボックスが複数あり、チェックされた項目に応じた
数値がカンマ区切りでカラムに格納されます。
別途、検索画面がありそのカラムに指定した数値(こちらもチェックボックスです)が
含まれているデータを抽出したいという処理です。
likeを使った曖昧検索のようなイメージです。

例えば
　レコードA　→　1,2,3,4,5
　レコードB　→　1,2,3,4

検索条件
　検索パターンA：2,3を選択　→　レコードAもBも抽出される
　検索パターンB：1,6を選択　→　レコードAもBも抽出される
　検索パターンC：5を選択　→　　レコードAだけ抽出される
　検索パターンD：6を選択　→　どちらのレコードも抽出されない

カンマで格納されているデータをカンマ区切りで分割するのは特に問題ありません。
チェックする項目毎にtinyint(1)型のカラムを作るという案もあったのですが
将来的に増えていく可能性もあるので、その案は取りやめになりました。

1つのテーブルだけで実装するという仕様があるものの、
データの持ち方がそもそもおかしいかもしれませんが、いい方法あったら教えて下さい。

in('dummy',',選択1,',',選択2,',',選択3,')

WHERE concat(',',カラム,',') regexp '\\,(2|3)\\,'
WHERE concat(',',カラム,',') regexp '\\,(1|6)\\,'
WHERE concat(',',カラム,',') regexp '\\,(5)\\,'
WHERE concat(',',カラム,',') regexp '\\,(6)\\,'

正規表現で出来るんじゃないかと始めて見たものの
結局俺には出来なくてカラムにカンマ付け足した。

カンマは正規表現で特殊な意味を持たないよ(=エスケープしなくていいよ)

で、今調べたけど、mysqlならこれでいいんじゃないの？
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

>>654です。
レス遅くなりましたが、回答ありがとうございます。

>>658
こちらが自分のイメージにぴったりでした！
ありがとうございましたー

SQLServerですけど
checksum は稀にコリジョン起きるという話ですが

select checksum('ab'),checksum('a-b')

これが同じ値を示します。
「-」の有無だけなのですが・・・

失礼しました
binary_checksumを使って解消しました

>>660
コリジョン(偶然の一致)じゃなくて一致する値を返してるだけだろ
'ab'と'a-b'が同じと判定される照合順序なら同じ値返してくれないと困るじゃないか

最初は binary_checksum なるものの存在を知らず、checksum がバイナリ比較かと思ってたんですよ
どうもすみません。。

ま、おかしいと思った時点で a--b a---bとか試してみることに気がつくべきだったな