在sql中使用每日数据的季度数据

Question

我试着用每日股价和他们的财务报表来做一些专题设计。

假设stock_price表列看起来是这样的，并且每天都在更新行。

OPEN | HIGH | LOW | CLOSE | VOL | DATE
1    | 3    | 0.8 | 1.2   | 5   | 2020-10-1
1.2  | 1.3  | 1   | 1.28  | 3   | 2020-10-2
1.4  | 2.1  | 1.4 | 2.0   | 5   | 2020-10-3
...
0.9  | 1.3  | 0.7 | 1     | 3   | 2021-1-8
1.2  | 1.5  | 1   | 1.4   | 3   | 2021-1-9

financial_statements表列如下所示

REVENUE | EPS | PUBLISHED_DATE
0.8     | 0.8 | 2020-7-1           < Q2 data
1       | 1   | 2020-10-2          < Q3 data
1.6     | 1.6 | 2021-1-9           < Q4 data

那我想要得到这个

OPEN | HIGH | LOW | CLOSE | VOL | DATE       | REVENUE_X_10
1    | 3    | 0.8 | 1.2   | 5   | 2020-10-1  | 8
1.2  | 1.3  | 1   | 1.28  | 3   | 2020-10-2  | 10        < Q3 statement published this day
1.4  | 2.1  | 1.4 | 2.0   | 5   | 2020-10-3  | 10
...                                                      < all these rows use Q3 data
0.9  | 1.3  | 0.7 | 1     | 3   | 2021-1-8   | 10        
1.2  | 1.5  | 1   | 1.4   | 3   | 2021-1-9   | 16        < Q4 statement published this day

这在MySQL中是可能的吗？

Answer 1

我认为您只是想要一个相关的子查询：

select sp.*,
       (select fs.revenue * 10
        from financial_statements fs
        where fs.published_date <= sp.date
        order by fs.publichsed_date desc
        limit 1
       ) as fs_revenue_times_10
from stock_price sp