加速字符串字段的连接

Question

我有一个数据表，如下面的tableA示例所示。日期列的格式为字符串。关闭列是一个整数，ticker被格式化为字符串。
我试图在mysql数据库上运行下面的查询，这需要很长时间。我能做些什么来加快速度，比如改变日期列的格式，或者添加索引还是主键?ticker和date的组合应该是一个唯一的值，date字段是一个时间戳，它目前只是格式化为字符串。

代码:

select avg((a.close-b.close)/b.close) as avg_annual_returns,
a.ticker
from tableA a 
join tableA b
on cast(a.date as date)=date_add(cast(b.date as date),interval 365 DAY)
and a.ticker=b.ticker
where b.close is not null
group by a.ticker

为多

+--------+-----+------+
|date    |close|ticker|
+--------+-----+------+
|2/1/2019|5    |abc   |
+--------+-----+------+
|2/3/2019|7    |efd   |
+--------+-----+------+
|2/4/2019|3    |hij   |
+--------+-----+------+

更新答:

select ticker,date, ( -1 +
a.close / max(a.close) over (partition by ticker 
order by date
range between interval 365 day preceding and interval 365 day preceding
) 
) as annual_returns              
from tableA a
) b where annual_returns is not null
group by ticker

Answer 1

如果您想要与一年前的差异，那么使用窗口函数。在此之前，先修复数据模型!不要将日期存储为字符串。所以:

alter table talbeA modify column date date;

然后得到一年前的收盘价:

select( -1 +
a.close / max(a.close) over (partition by ticker 
order by date
range between interval 365 day preceding and interval 365 day preceding
)
)               
from tablea a;

您不必担心NULL的值，因为AVG()会忽略它们。

这是一个db<>小提琴

Answer 2

问题就在这里:

on cast(a.date as date)=date_add(cast(b.date as date),interval 365 DAY)

它的两边都不是"sargeable"，所以它不能使用任何索引。

假设date是数据类型DATE，则此工作:

ON a.date = b.date - INTERVAL 1 YEAR

,

INDEX(ticker, date)   -- in this order.

注:1 YEAR将在2月28日左右打嗝;365 DAY每4年打嗝366天。

也改变

where b.close is not null

WHERE b.ticker IS NOT NULL

(功能上它们是相同的，但我在索引中选择了一列，以防万一。)好吧，有没有close为NULL的行?

哦，另一个问题。由于周末的原因，每周只有3 - 4天可以找到与上一年相匹配的日子。