mysql - 如果数据不存在，是否可以获取最近间隔的数据？

Question

我有股票代码的历史数据。

我的问题是，如果所选日期的数据不存在，是否可以获取最近的可能数据的数据？

(我显示的是 1D、1WEEK、2WEEK、1MONTH 的数据)并且市场将在周六和周日放假，因此时间间隔不断变化。 (所以最接近的值与间隔匹配)

SELECT * from historical_data where symbol_name IN ('WOCKPHARMA','YESBANK') AND 
current_day =  DATE_SUB('2015-12-18', INTERVAL 6 DAY) ;

http://sqlfiddle.com/#!9/3693b/24

Answer 1

为股票定义最近的最近数据:

查询

select *
from historical_data hd
inner join 
(
SELECT symbol_name, max(current_day) as nearest_day
from historical_data 
where symbol_name IN ('WOCKPHARMA','YESBANK') 
AND current_day <= DATE_SUB('2015-12-18', INTERVAL 6 DAY) 
group by symbol_name
) nearest_data
on hd.symbol_name = nearest_data.symbol_name
and hd.current_day = nearest_data.nearest_day
;

输出

+-------------+----------------------------+----------+----------+---------+-----------+----------+---------------+-------------+----------------------------+
| symbol_name |        current_day         | open_val | high_val | low_val | close_val | last_val | prevclose_val | symbol_name |        nearest_day         |
+-------------+----------------------------+----------+----------+---------+-----------+----------+---------------+-------------+----------------------------+
| WOCKPHARMA  | December, 11 2015 00:00:00 |     1611 | 1620     | 1570.3  | 1581.25   | 1579     | 1602.1        | WOCKPHARMA  | December, 11 2015 00:00:00 |
| YESBANK     | December, 11 2015 00:00:00 |      709 | 713.7    | 672.25  | 680.6     | 683.45   | 707.1         | YESBANK     | December, 11 2015 00:00:00 |
+-------------+----------------------------+----------+----------+---------+-----------+----------+---------------+-------------+----------------------------+

sqlfiddle