(我将首先解释我的问题。下表(任何示例查询)可在http://sqlfiddle.com/#!2/8ec17/4)
我有一张库存信息表,如下所示:
sp100_id _date bullishness returnpct
----------------------------------------------
1 2011-03-16 1.01 -0.33
1 2011-03-17 0.85 -1.28
1 2011-03-18 0.89 1.25
1 2011-03-21 1.46 1.21
1 2011-03-22 0.39 -2.53
2 2011-03-16 3.07 1.27
2 2011-03-17 2.09 -0.80
2 2011-03-18 0.91 -0.12
2 2011-03-21 1.50 0.00
2 2011-03-22 2.62 1.10
3 2011-03-16 0.73 -1.13
3 2011-03-17 1.13 1.21
3 2011-03-18 1.12 0.45
3 2011-03-21 1.00 1.01
3 2011-03-22 1.00 -0.53
4 2011-03-16 0.40 1.10
4 2011-03-17 2.40 0.03
4 2011-03-18 3.16 -0.10
4 2011-03-21 0.86 0.50
4 2011-03-22 1.00 0.10
我需要的是:
- 每连续3天,计算每家公司的平均牛市(
sp100_id
) - 取平均乐观度最高的公司的第3天
returnpct
并存储 - 最后,通过将存储的
returnpct
s相加来计算总returnpct
本例中有5个_date
,因此必须制作连续几天的福利林对:
2011-03-16
、2011-03-17
、2011-03-18
2011-03-17
、2011-03-18
、2011-03-21
(请注意,2011-03-19不在表中)2011-03-18
、2011-03-21
、2011-03-22
回到我需要的东西:
- 前三天,"赢家"是2号公司,平均牛市指数为(3.07+2.09+0.91)/3=2.0233,第三天回报率为-0.12。对于其他2个"日期范围",获奖者是4号公司(平均bullsihness 2.14,returnpct 0.50)和2号公司(Bullsihness1.67,returnpc 1.10)
- 应存储-0.12、0.50、1.10的值
- 那么总的returnpct将是-0.12+0.50+1.10=1.48,应该从查询(或脚本)中返回
问题1:考虑到上面的例子,我的查询返回第一天的returnpct
(1.27),而不是第三天的returnpct
(-0.12)。我如何更改这一点?
问题2:在sqlfiddle中,我对连续3天的第一组进行了硬编码。我如何自动化(也许使用php),这样我就不必手动键入所有查询了?请注意,表中缺少日期。与示例中一样,脚本应该只取表中的下一个可用日期(因此在2011-03-18
之后是2011-03-21
,而不是2011-03-19
,因为它不在表中)
问题3:在这个例子中,我使用了连续3天,但理想情况下,脚本可以很容易地更改为使用任何其他连续天数(例如,2、4或8)。因此,应分别存储第2天、第4天或第8天的返回值。
谁能帮我解决我在这里遇到的一些问题?非常感谢您的帮助:-)
以下查询对您想要做的事情有很大帮助。它计算3天的平均值,然后按最高平均值在每个日期内订购:
SELECT s.sp100_id, s._date,
(s.bullishness+splus1.bullishness+splus2.bullishness)/3 as avgb,
splus2.returnpct
FROM (select s3.*,
(select min(_date)
from stocks s4
where s4.sp100_id = s3.sp100_id and
s4._date > s3.dateplus1
) as dateplus2
from (select s.*,
(select min(_date)
from stocks s2
where s2.sp100_id = s.sp100_id and
s2._date > s._date
) as dateplus1
from stocks s
) s3
) s left outer join
stocks splus1
on s.sp100_id = splus1.sp100_id and
s.dateplus1 = splus1._date left outer join
stocks splus2
on s.sp100_id = splus2.sp100_id and
s.dateplus2 = splus2._date
order by 2, 3 desc
在这一点上,使用mysql会变得乏味。在支持分析/windows函数和"with"语句的数据库中,这会容易得多(这几乎是除mysql之外的所有数据库:例如,Oracle、Postgres、DB2、SQL Server)。
在MySQL中,只需一个查询就可以完成(3),但这很麻烦。您可能希望在应用程序层中执行此操作。
顺便说一句,感谢您设置SQL Fiddle。仅凭理由,我会对这个问题投赞成票。