sql - 需要有关 SQL 中复杂 Join 语句的帮助

Question

如何在日期数量稀疏的表和日期数量详尽的另一个表之间进行连接，以便稀疏日期之间的间隔采用前一个稀疏日期的值？

说明性示例:

PRICE table (sparse dates):
date        itemid  price
2008-12-04  1       $1
2008-12-11  1       $3
2008-12-15  1       $7


VOLUME table (exhaustive dates):
date         itemid  volume_amt
2008-12-04   1       12345
2008-12-05   1       23456
2008-12-08   1       34567
2008-12-09   1       ...
2008-12-10   1
2008-12-11   1
2008-12-12   1
2008-12-15   1
2008-12-16   1
2008-12-17   1
2008-12-18   1

想要的结果:

date       price  volume_amt
2008-12-04 $1     12345
2008-12-05 $1     23456
2008-12-08 $1     34567
2008-12-09 $1     ...
2008-12-10 $1
2008-12-11 $3
2008-12-12 $3
2008-12-15 $7
2008-12-16 $7
2008-12-17 $7
2008-12-18 $7

更新:

有几个人建议使用相关子查询来实现所需的结果。 (相关子查询 = 包含对外部查询的引用的子查询。)

这会起作用；但是，我应该注意到我使用的平台是 MySQL，相关子查询的优化很差。有什么方法可以在不使用相关子查询的情况下做到这一点？

Answer 1

这不像单个 LEFT OUTER JOIN 到稀疏表那么简单，因为您希望外连接留下的 NULL 被填充为最新的价格。

EXPLAIN SELECT v.`date`, v.volume_amt, p1.item_id, p1.price
FROM Volume v JOIN Price p1
  ON (v.`date` >= p1.`date` AND v.item_id = p1.item_id)
LEFT OUTER JOIN Price p2
  ON (v.`date` >= p2.`date` AND v.item_id = p2.item_id
    AND p1.`date` < p2.`date`)
WHERE p2.item_id IS NULL;

此查询将 Volume 与 Price 中所有较早的行进行匹配，然后使用另一个连接来确保我们只找到最近的价格。

我在 MySQL 5.0.51 上对此进行了测试。它既不使用相关子查询，也不使用分组依据。

编辑:更新了查询以匹配 item_id 和日期。这似乎也有效。我在 (date) 上创建了一个索引以及 (date, item_id) 上的索引和 EXPLAIN 计划是相同的。 (item_id, date)上的索引在这种情况下可能会更好。这是 EXPLAIN 输出:

+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref             | rows | Extra                                |
+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ALL  | item_id       | NULL    | NULL    | NULL            |    6 |                                      | 
|  1 | SIMPLE      | v     | ref  | item_id       | item_id | 22      | test.p1.item_id |    3 | Using where                          | 
|  1 | SIMPLE      | p2    | ref  | item_id       | item_id | 22      | test.v.item_id  |    1 | Using where; Using index; Not exists | 
+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------------------+

但是我有一个非常小的数据集，优化可能依赖于更大的数据集。您应该试验，使用更大的数据集分析优化。

编辑:我之前粘贴了错误的 EXPLAIN 输出。上面的一个得到了纠正，并显示了 (item_id, date) 的更好使用指数。