php - 完成 : for Z companies, 计算 A 在 X 天内的平均值。我现在怎么: return value of B for day X for company with highest average of A?

Question

(我将首先解释我的问题。下表(任何示例查询)可在 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相加来计算总的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 个“dateranges”，获胜者是公司 4(平均 bullsihness 2.14 和 returnpct 0.50)和公司 2(bullsihness 1.67，returnpct 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 天的 returnpct。

谁可以帮助我解决我在这里遇到的一些问题？非常感谢任何帮助:-)

Answer 1

以下查询对您想要执行的操作有很大帮助。它计算 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。仅出于原因，我会赞成这个问题。