MySQL SQL文 仮想カラムに番号を振りたい

質問

f1、id、name、price の株価データがhistoryテーブルに入っています。
f1は発注番号です。「id,name=株券」は複数あるため、ひとつの株券だけを見ると発注番号はランダムになります。

SELECT * FROM (SELECT f1,id,name,price FROM history WHERE id = 2001 ORDER BY f1 DESC limit 0,10) as AA ORDER BY f1 ASC

上記のSQL文により、株ID=2001番の直近10回の売買価格(推移)を取得することが可能です。

f1 　　id 　　name 　　price
14585 2001 日本製粉　521
14595 2001 日本製粉　523
14607 2001 日本製粉　527
14684 2001 日本製粉　524
14705 2001 日本製粉　520
14771 2001 日本製粉　519
14795 2001 日本製粉　518
14800 2001 日本製粉　516
14804 2001 日本製粉　517
14882 2001 日本製粉　519

やりたいこと

この直近の取引10回に関し、若い順に必ず0から9の番号を振る関数(下記の仮想カラムzeroを追加する)を作りたいと思っています。

f1 　　id 　　name 　　price　zero
14585 2001 日本製粉　521 　　0
14595 2001 日本製粉　523 　　1
14607 2001 日本製粉　527 　　2
14684 2001 日本製粉　524 　　3
14705 2001 日本製粉　520 　　4
14771 2001 日本製粉　519 　　5
14795 2001 日本製粉　518 　　6
14800 2001 日本製粉　516 　　7
14804 2001 日本製粉　517 　　8
14882 2001 日本製粉　519 　　9

株によっては10回未満の取引のものもあり、その場合はたとえば4回しか取引のない株の場合はzeroの値は0から3を振ることになります。

SELECT * FROM (SELECT f1,id,name,price FROM history WHERE id = 2001 ORDER BY f1 DESC limit 0,10) as AA ORDER BY f1 ASC

を改良して作ろうとしましたがいいアイデアが浮かびません。
どなたか助けて頂けませんでしょうか? よろしくお願いいたします。

回答No.2

yambejp · ベストアンサー

０からはじまる連番を得たいだけならこんな感じ

SELECT *,(select @a:=@a+1 from (select @a:=-1) as dummy) as zero from tbl

select @a:=-1のところは初期値マイナス１の値を与えておきます

お礼

ありがとうございます。
SELECT *,(select @a:=@a+1 from (select @a:=-1) as dummy) as zero from ((SELECT * FROM (SELECT * FROM history WHERE id = 2001 ORDER BY f1 DESC limit 0,10) as AA ORDER BY f1 ASC) as AB)
で出来ました。
本当に助かりました。感謝です!!

回答No.1

noname#212058

複数レコードにまたがって分析する必要があるデータを取得
するのに、関数(ファンクション)を使う『方式』自体が良く
ありません。

SELECT *, myFunction(f1,id) FROM history …
みたいな感じで使いたいということですよね？
無理やり作ることは不可能ではないですが、これでは取得される
1レコード毎に myFunction が呼び出されるので、myFunction
内で集約処理をするとパフォーマンスが死にます。方針転換した
ほうが良いです。

お礼

ご指導有難うございます。
パフォーマンスについては検証してみます。
大変勉強になりました。
有難うございました。