SQL查询计算股票均价的一些疑问

[lm287lm287]

各位SQL达人，请教有一个计算股票均价的问题，

1. 现有数据表：
class name date open high low close volume amount
TD Ag(T+D) 5/27/2011 8170 8285 7986 8220 2353550 19193200640
TD Ag(T+D) 5/26/2011 8101 8485 8101 8335 2161338 17893718016
TD Ag(T+D) 5/25/2011 7810 7985 7810 7970 1635332 12943652864
TD Ag(T+D) 5/24/2011 7590 7735 7544 7719 880242 6732090880
TD Ag(T+D) 5/23/2011 7699 7706 7530 7538 702958 5350213120
TD Ag(T+D) 5/20/2011 7665 7740 7545 7693 1415364 10828949504
TD Ag(T+D) 5/19/2011 7525 7797 7451 7673 2083480 15965707264

2.想计算出的结果是：
class name date MA5 MA10 MA20 MA30 MA60

其中 MA5计算方式是 5天连续收盘价的AVG, 同理MA10，MA20。指的是10天，20天的收盘价移动平均。
查询MA5 T-SQL的语句是：


select b.class,b.name,b.date,avg(b.clo_2) as MA5
From (
select t1.*,t2.clo as CLO_2 from data_RN t1 inner join data_RN t2 on t1.name= t2.name and t2.rn<=t1.rn and t2.rn>=t1.rn-4
)b
group by b.class,b.name,b.rn,b.date
Order by name,date desc

3.现在的问题是，如何讲MA5到MA60一次性的计算出来呢？ 如果不用T-SQL直接计算，这种情况是不是应该使用SSIS？Thanks in advance,

[htl258_Tony]

and t2.rn<=t1.rn and t2.rn>=t1.rn-4

这个rn 哪来的

[lm287lm287]

哦。不好意思，这个给忘了。。。

RN是 Row number，有一个view是给原始数据排序用的，

SELECT *, ROW_NUMBER() OVER (partition BY name
ORDER BY date) AS rn
FROM [history_data].[dbo].[data]

[--小F--]

貌似是个行转列

[lm287lm287]

[Quote=引用 4 楼 cd731107 的回复:]

SQL code
--close是收盘价吗?换了个sql2000下的收盘价的算法，不知道对不对
select class,name,date,
MA5=(select top 5 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc)/5,
MA10=(select top 10 ……
[/Quote]

多谢回复，你的意思是还是拿TSQL直接解决，是吧，
这个tSQL有错误。。

Msg 512, Level 16, State 1, Line 1
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

[cd731107]

[Quote=引用 5 楼 lm287lm287 的回复:]
引用 4 楼 cd731107 的回复:

SQL code
--close是收盘价吗?换了个sql2000下的收盘价的算法，不知道对不对
select class,name,date,
MA5=(select top 5 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc)……
[/Quote]

--再试试下面
select class,name,date,
MA5=(select  sum([close]) from (select  top 5 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/5,
MA10=(select sum([close]) from (select top 10 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/10,
MA20=(select sum([close]) from (select top 20 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/20,
MA30=(select sum([close]) from (select top 30 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/30,
MA60=(select sum([close]) from (select top 60 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/60
from data_RN b

[cd731107]

--close是收盘价吗?换了个sql2000下的收盘价的算法，不知道对不对
select class,name,date,
MA5=(select  top 5 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc)/5,
MA10=(select top 10 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc)/10,
MA20=(select top 20 [close] from  a where a.name=b.name and a.date<=b.date order by a.date desc)/20,
MA30=(select top 30 [close from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc)/30,
MA60=(select top 60 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc)/60
from data_RN b

[lm287lm287]

多谢，明白你的思路了，
现在能正常执行了，但是又有了一个问题，拿MA5举例，因为用了top，所以数据的前4行MA5是不准确的，有没有办法在T sql里直接跳过计算MA5前4行，直接留空。。。 MA10留空前9行，MA20,MA30,60以此类推

MA5
TD Ag(T+D) 2006-11-03 3390.200000
TD Ag(T+D) 2006-11-02 2712.800000
TD Ag(T+D) 2006-11-01 2029.000000
TD Ag(T+D) 2006-10-31 1349.400000
TD Ag(T+D) 2006-10-30 674.800000

[cd731107]

--要用到你前面的rn
select class,name,date,
MA5=case  when rn%5 = 0 then (select sum([close]) from (select  top 5 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/5
    else null end,
MA10=case when rn%10 =0 then (select sum([close]) from (select top 10 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/10
    else null end,

MA20=case when rn%20 =0 then (select sum([close]) from (select top 20 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/20
    else null end,
MA30=case when rn%30 =0 then (select sum([close]) from (select top 30 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/30
    else null end,
MA60=case when rn%60 =0 then (select sum([close]) from (select top 60 [close] from data_RN a where a.name=b.name and a.date<=b.date order by a.date desc) c)/60
    else null end
from data_RN b

[lm287lm287]

试了一下，结果变成MA5以5为周期计算均值了，MA10以10为周期计算了。。。


MA5
stock 000300 2005-04-08 NULL
stock 000300 2005-04-11 NULL
stock 000300 2005-04-12 NULL
stock 000300 2005-04-13 NULL
stock 000300 2005-04-14 993.090000
stock 000300 2005-04-15 NULL
stock 000300 2005-04-18 NULL
stock 000300 2005-04-19 NULL
stock 000300 2005-04-20 NULL
stock 000300 2005-04-21 959.718000
stock 000300 2005-04-22 NULL
stock 000300 2005-04-25 NULL
stock 000300 2005-04-26 NULL
stock 000300 2005-04-27 NULL
stock 000300 2005-04-28 934.984000

[-晴天]

select 
(select avg(close) from(select top 5 close from tb order by date desc)t) as MA5,
(select avg(close) from(select top 10 close from tb order by date desc)t) as MA10,
(select avg(close) from(select top 20 close from tb order by date desc)t) as MA20,
(select avg(close) from(select top 30 close from tb order by date desc)t) as MA30,
(select avg(close) from(select top 60 close from tb order by date desc)t) as MA60

[-晴天]

--如果上市公司名叫name,要从一个总表中得到各股票的均价,则
select name,
(select avg(close) from(select top 5 close from tb where name=a.name order by date desc)t) as MA5,
(select avg(close) from(select top 10 close from tb where name=a.name order by date desc)t) as MA10,
(select avg(close) from(select top 20 close from tb where name=a.name order by date desc)t) as MA20,
(select avg(close) from(select top 30 close from tb where name=a.name order by date desc)t) as MA30,
(select avg(close) from(select top 60 close from tb where name=a.name order by date desc)t) as MA60
from tb a

[cd731107]

[Quote=引用 9 楼 lm287lm287 的回复:]
试了一下，结果变成MA5以5为周期计算均值了，MA10以10为周期计算了。。。


MA5
stock 000300 2005-04-08 NULL
stock 000300 2005-04-11 NULL
stock 000300 2005-04-12 NULL
stock 000300 2005-04-13 NULL
stock 000300 2005-04-14 993.……
[/Quote]
试了一下，结果变成MA5以5为周期计算均值了，MA10以10为周期计算了。。。
你不是这个意思吗

[adiag]

这种方式还i是很慢的，如果数据是500万行（所有数据）
建议用累加的方式实现，比如先算ma3,再用ma2+ma3
再算ma10=ma5+ma5(2)
这样还是挺快的

[lm287lm287]

多谢几位的帮助，这几天看了AS的DM之后觉得这个可能拿DM做比较靠谱。。