我的表结构
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| date | date | YES | | NULL | |
| open | double | YES | | NULL | |
| high | double | YES | | NULL | |
| low | double | YES | | NULL | |
| close | double | YES | | NULL | |
| vol | double | YES | | NULL | |
| adj | double | YES | | NULL | |
| id | int(10) | NO | PRI | NULL | auto_increment |
+--------------+---------+------+-----+---------+----------------+
create table t (select * from quote order by date desc);
我要在这个新产生的表上面,计算涨幅,我是这样想的,
select (t1.close-t.close)/t.close as zhangfu from t,ti where t1.date=t.date+1
但是不行,因为 t1.date t.date 的关系不一定是 t1.date=t.date+1,中间可能出现节假日。
请问,如何解决呢?实际上,按照order by date desc,每后面一个的数据,去和前面一个计算就可以,但是如何表达呢?
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| date | date | YES | | NULL | |
| open | double | YES | | NULL | |
| high | double | YES | | NULL | |
| low | double | YES | | NULL | |
| close | double | YES | | NULL | |
| vol | double | YES | | NULL | |
| adj | double | YES | | NULL | |
| id | int(10) | NO | PRI | NULL | auto_increment |
+--------------+---------+------+-----+---------+----------------+
create table t (select * from quote order by date desc);
我要在这个新产生的表上面,计算涨幅,我是这样想的,
select (t1.close-t.close)/t.close as zhangfu from t,ti where t1.date=t.date+1
但是不行,因为 t1.date t.date 的关系不一定是 t1.date=t.date+1,中间可能出现节假日。
请问,如何解决呢?实际上,按照order by date desc,每后面一个的数据,去和前面一个计算就可以,但是如何表达呢?
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
from
(
select t.date ,t.close ,min(t1.date) as t1d
from quote t left join quote t1 on t.date<t1.date
group by t.date ,t.close
) k right join quote t on k.t1d=t.date
LZ忽略了一个问题 就是最后一笔记录是没有增长率 我这里已经处理了 最后一笔显示为未知
from
(
select t.date ,t.close ,min(t1.date) as t1d
from quote t left join quote t1 on t.date<t1.date
group by t.date ,t.close
) k right join quote t on k.t1d=t.date
| symbol | date | open | high | low | close | vol | adj | id |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+
| YHOO | 2009-01-30 | 11.86 | 12.15 | 11.51 | 11.73 | 21508900 | 11.73 | 7976767 |
| YHOO | 2009-01-29 | 12.02 | 12.24 | 11.73 | 11.74 | 22124100 | 11.74 | 7976768 |
| YHOO | 2009-01-28 | 12.23 | 12.38 | 11.92 | 12.24 | 35686800 | 12.24 | 7976769 |
| YHOO | 2009-01-27 | 11.2 | 11.37 | 10.85 | 11.34 | 33708200 | 11.34 | 7976770 |
| YHOO | 2009-01-26 | 11.26 | 11.28 | 10.81 | 11.17 | 16469800 | 11.17 | 7976771 |
| YHOO | 2009-01-23 | 10.9 | 11.55 | 10.86 | 11.32 | 15864000 | 11.32 | 7976772 |
| YHOO | 2009-01-22 | 11.35 | 11.51 | 10.9 | 11.28 | 17201700 | 11.28 | 7976773 |
| YHOO | 2009-01-21 | 11.17 | 11.59 | 11.08 | 11.59 | 15892200 | 11.59 | 7976774 |
| YHOO | 2009-01-20 | 11.72 | 11.8 | 11.01 | 11.01 | 18692000 | 11.01 | 7976775 |
| YHOO | 2009-01-16 | 11.87 | 11.97 | 11.44 | 11.59 | 24783700 | 11.59 | 7976776 |
| YHOO | 2009-01-15 | 12.32 | 12.35 | 11.22 | 11.61 | 25247500 | 11.61 | 7976777 |
| YHOO | 2009-01-14 | 12.26 | 12.53 | 11.81 | 12.41 | 23595200 | 12.41 | 7976778 |
| YHOO | 2009-01-13 | 12.09 | 12.79 | 11.78 | 12.1 | 25720400 | 12.1 | 7976779 |
| YHOO | 2009-01-12 | 13.09 | 13.1 | 12.08 | 12.22 | 19976900 | 12.22 | 7976780 |
| YHOO | 2009-01-09 | 13.42 | 13.56 | 12.9 | 13.13 | 19281000 | 13.13 | 7976781 |
| YHOO | 2009-01-08 | 12.37 | 13.07 | 12.31 | 13.07 | 13929800 | 13.07 | 7976782 |
| YHOO | 2009-01-07 | 12.71 | 13.16 | 12.45 | 12.71 | 24995900 | 12.71 | 7976783 |
| YHOO | 2009-01-06 | 12.96 | 13.24 | 12.88 | 13 | 10056000 | 13 | 7976784 |
| YHOO | 2009-01-05 | 12.72 | 13.01 | 12.39 | 12.86 | 11989900 | 12.86 | 7976785 |
| YHOO | 2009-01-02 | 12.17 | 12.85 | 12.12 | 12.85 | 9514600 | 12.85 | 7976786 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+
就这个表格吧,如何增加涨幅这一列?用close计算,4楼的代码没有通过
select (t1.close-t.close)/t.close as zhangfu from t,ti where t1.date=t.date+1
也不能使用id ,因为有的数据不是这样,同一个symbol,id数值依次加一的。
我假设你的表就是从quote出发(你把你要从发的表替换掉这个quote就可以了)我的t1,t 这些都是这个表的别名....
from
(
select tt.date ,tt.close ,min(t1.date) as t1d
from t tt left join quote t1 on tt.date<t1.date
group by tt.date ,tt.close
) k right join t tt on k.t1d=tt.date
-> from
-> (
-> select t.date ,t.close ,min(t.date) as t1d
-> from t t1 left join quote t1 on t.date<t1.date
-> group by t.date ,t.close
-> ) k right join quote t on k.t1d=t.date ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(10)) end as zhangfu
from
(
select t.date ,t.close ,min(t.date) as t1d
' at line 1
from
(
select tt.date ,tt.close ,min(t1.date) as t1d
from t tt left join quote t1 on tt.date<t1.date
group by tt.date ,tt.close
) k right join t tt on k.t1d=tt.date
拿着这个代码去试试~
-> from
-> (
-> select tt.date ,tt.close ,min(t1.date) as t1d
-> from t tt left join quote t1 on tt.date<t1.date
-> group by tt.date ,tt.close
-> ) k right join t tt on k.t1d=tt.date ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as varchar(10)) end as zhangfu
from
(
select tt.date ,tt.close ,min(t1.date)' at line 1
select (tt.close-k.close)/k.close as zhangfu
from
(
select tt.date ,tt.close ,min(t1.date) as t1d
from t tt left join quote t1 on tt.date<t1.date
group by tt.date ,tt.close
) k right join t tt on k.t1d=tt.date
这样呢~
from
(
select tt.date ,tt.close ,min(t1.date) as t1d
from t tt left join t t1 on tt.date<t1.date
group by tt.date ,tt.close
) k right join t tt on k.t1d=tt.date
可以成功获得结果,但是有两个问题
1.如果k.close=0,要有处理
2.可否简要谈谈这段代码,我理解不了,想了半天?
from
(
select tt.date ,tt.close ,min(t1.date) as t1d
from t tt left join t t1 on tt.date<t1.date
group by tt.date ,tt.close
) k right join t tt on k.t1d=tt.date
这样~
from
(
select tt.date ,tt.close ,min(t1.date) as t1d
from t tt left join t t1 on tt.date<t1.date
group by tt.date ,tt.close
) k right join t tt on k.t1d=tt.date
必须是这样,原文第一行的as zhangfu end as zhangfu 需要修改成end as zhangfu
另外,烦请简单讲讲这段sql我不大懂!!
select tt.date ,tt.close ,min(t1.date) as t1d
from t tt left join t t1 on tt.date<t1.date
group by tt.date ,tt.close
看看结果
然后再套外面的结果 自己先试试
time0=Time.now
require 'rubygems'
require "mysql"
dbh = Mysql.real_connect("localhost", "root", "qqqqqq")
dbh.query("use nasdaq")
a=[]
b=[]
a[0]=[]
a[1]=[]
result1=dbh.query("select * from quote where symbol='YHOO' order by date desc;")
dbh.query("create table zf (select * from quote where symbol='YHOO' order by date desc);")
dbh.query("alter table zf add increase real;")
result1.each do |row|
a[0]<<row[1]
a[1]<<row[5].to_f
end
a[1][0..-2].each.with_index{|item,idx| b<<format("%.4f",(item-a[1][idx+1])/(item))}
a[0][0..-2].each.with_index{|item,idx| dbh.query("update zf set increase=#{b[idx]} where date='#{a[0][idx]}';") }
puts Time.now-time0
如果您懂ruby的话,就明白,上面调用mysql,或者一个原始数据表,对这个表操作,产生新的列,并将这列的数值插入一个新的表。
其中增加列,计算increase列的值,将这个值update到数据库,都是ruby调用mysql语句,所有过程仅仅花费15秒。
比如这个列子上 在date ,close 等字段建立索引~
create index idx1 on t(close);
create index idx2 on t(date);
执行速度提高了一些,从34秒到22秒
3591 rows in set (22.47 sec)
还是比我用其他语言写的程序实现慢,现在我明白为何股票软件都是按照文件来操作,这样更快。
begin
select case when k.close=0 then '' else (tt.close-k.close)/k.close end as zhangfu
from
(
select tt.date ,tt.close ,min(t1.date) as t1d
from t tt left join t t1 on tt.date<t1.date
group by tt.date ,tt.close
) k right join t tt on k.t1d=tt.date;
end;
$$delimiter ;