我在任何地方都找不到解决这个问题的办法.我有一个包含每日数据的表，该表有四列浮点值.请看这里的图片
我想计算每周的价格，这样开盘价就是从一周开始的价格，即DATE_SUB(Running_Date，Interval 5 Workday)，而High和Low是过go 5个工作日的最高点和最低点.收盘是最后一个收盘(所以不需要计算).
这是对每周价格数据的计算，如下所示
我想在这里一只股票是这样运作的
SELECT
*,
( SELECT max( High ) FROM dat WHERE stock = "AAPL" AND `Date` >= date_sub( Curdate( ), INTERVAL 8 DAY ) ) AS High
FROM
dat
WHERE
stock = "AAPL"
ORDER BY
`Date` DESC
但其中Curdate()实际上是表‘dat’的日期，并且在每一行中都在变化.此处仅添加当前日期的每周.
因此，我希望四列wOpen、wHigh、wLow、wCLose如下所示
这看起来像是每周分区上的窗口函数:
select d.*,
first_value(open) over(partition by stock, yearweek(tradeDate, 1) order by tradeDate) as wOpen,
max(high) over(partition by stock, yearweek(tradeDate, 1) order by tradeDate) as wHigh,
min(low) over(partition by stock, yearweek(tradeDate, 1) order by tradeDate) as wLow,
last_value(close) over(partition by stock, yearweek(tradeDate, 1) order by tradeDate) as wClose
from dat d
诀窍是按股票和交易week对数据进行分区；yearweek(tradeDate, 1)根据周一开始的周(根据您的样本数据推断)提供给定日期的年/周.
剩下的只是窗口:第一个开盘值和最后一个收盘值可以用first_value()和last_value访问，窗口min/max给出运行的最小值和最大值.
如果要将结果限制在特定的日期范围内，只需在查询中添加一个where子句.
如果您运行的是MySQL5.x，其中窗口函数不可用，一种替代方法是使用两个相关的子查询.如果您有很多行要处理，这可能会大大降低效率:
select d.*,
(
select open
from dat d1
where d1.stock = d.stock and d1.tradeDate <= d.tradeDate and d1.tradeDate >= d.tradeDate - interval weekday(d.tradeDate) day
order by d1.tradeDate limit 1
) as wOpen,
(
select max(high)
from dat d1
where d1.stock = d.stock and d1.tradeDate <= d.tradeDate and d1.tradeDate >= d.tradeDate - interval weekday(d.tradeDate) day
) as wHigh,
(
select min(low)
from dat d1
where d1.stock = d.stock and d1.tradeDate <= d.tradeDate and d1.tradeDate >= d.tradeDate - interval weekday(d.tradeDate) day
) as wLow,
(
select close
from dat d1
where d1.stock = d.stock and d1.tradeDate <= d.tradeDate and d1.tradeDate >= d.tradeDate - interval weekday(d.tradeDate) day
order by d1.tradeDate desc limit 1
) as wClose
from dat d