MySQL查询难题--查找最近的日期

Question

我到处找遍了，还没有找到一种聪明的方法来处理这件事，尽管我确信这是可能的：

一份历史数据表载有季度信息：

CREATE TABLE Quarterly (
unique_ID INT UNSIGNED NOT NULL,
date_posted DATE NOT NULL,
datasource TINYINT UNSIGNED NOT NULL,
data FLOAT NOT NULL,
PRIMARY KEY (unique_ID));

另一个历史数据表(非常大)包含每天的信息：

CREATE TABLE Daily (
unique_ID INT UNSIGNED NOT NULL,
date_posted DATE NOT NULL,
datasource TINYINT UNSIGNED NOT NULL,
data FLOAT NOT NULL,
qtr_ID INT UNSIGNED,
PRIMARY KEY (unique_ID));

qtr_ID字段不是填充数据库的每日数据提要的一部分--相反，我需要用Quarterly.unique_ID行ID追溯填充daily中的qtr_ID字段，使用该数据源在Daily.date_posted上的最新季度数据。

例如，如果季度数据是

101 2009-03-31 1 4.5

102 2009-06-30 1 4.4

103 2009-03-31 2 7.6

104 2009-06-30 2 7.7

105 2009-09-30 1 4.7

每天的数据是

1001 2009-07-14 1 3.5？

1002 2009-07-15 1 3.4及&

1003 2009-07-14 2 2.3 ^^

那我们就会想要?？qtr_ID字段将被指定为'102‘作为该数据源在该日期的最新季度，&&也将为'102'，^^将为'104’。

这些挑战包括，这两个表(特别是日表)实际上都很大，无法标准化以消除重复的日期或以其他方式进行优化，而且对于某些日条目，没有前面的季度条目。

我尝试过各种联接，使用datediff (其中的挑战是查找datediff的最小值大于0)，以及其他尝试，但是对我来说没有什么效果--通常我的语法会在某个地方崩溃。任何想法欢迎-我会执行任何基本的想法或概念，并报告。

Answer 1

只需使用以下内容对季度id进行子查询：

(
 SELECT unique_ID 
 FROM Quarterly 
 WHERE 
     datasource = ? 
     AND date_posted >= ? 
 ORDER BY
     unique_ID ASC
 LIMIT 1
)

当然，这可能不会给您提供最好的性能，并且假设日期按顺序添加到季度(否则是order by date_posted)。然而，它应该能解决你的问题。

您将在INSERT或UPDATE语句上使用此子查询作为Daily表的qtr_ID字段的值。

Answer 2

以下内容似乎完全按照预期工作，但肯定很难看(对同一个DATEDIFF！进行了三次调用)，也许通过看到一个工作的查询，可能有人可以进一步减少或改进它：

UPDATE Daily SET qtr_ID = (select unique_ID from Quarterly
WHERE Quarterly.datasource = Daily.datasource AND
DATEDIFF(Daily.date_posted, Quarterly.date_posted) = 
(SELECT MIN(DATEDIFF(Daily.date_posted, Quarterly.date_posted)) from Quarterly
WHERE Quarterly.datasource = Daily.datasource AND
DATEDIFF(Daily.date_posted, Quarterly.date_posted) > 0));

Answer 3

在对这个查询做了更多的工作之后，我最终比原来的概念有了很大的性能改进。最重要的改进是在“每日”和“季度”表中创建索引--在“每日第一”中，使用BTREE创建索引(数据源，date_posted)和(date_posted，数据源)，使用散列创建索引(数据源)，在第一季中，我做了同样的事情。这太过分了，但它确保了我有一个查询引擎可以使用的选项。这使得查询时间减少到原来的1%以下。(！)

然后，我了解到，在我的特殊情况下，我可以使用MAX()代替ORDER和LIMIT，所以我使用对MAX()的调用来获得适当的unique_ID，这使查询时间减少了大约20%。

最后，我了解到，使用InnoDB存储引擎，我可以分割我正在用任何一个查询更新的每日表块，这使得我可以使用少量的方便和脚本来多线程查询。并行处理工作良好，各线程线性地缩短了查询时间。

因此，基本的查询比我的第一次尝试执行起来要好1000倍，它是：

UPDATE Daily
SET qtr_ID =
(
  SELECT MAX(unique_ID)
  FROM Quarterly
  WHERE Daily.datasource = Quarterly.datasource AND
        Daily.date_posted > Quarterly.dateposted
)
WHERE unique_ID > ScriptVarLowerBound AND
      unique_ID <= ScriptVarHigherBound
;