查找最接近日期的数据？

发布于 2024-08-12 06:16:28 字数 744 浏览 3 评论 0原文

我有一个

CREATE TABLE `symbol_details` (
  `symbol_header_id` int(11) DEFAULT NULL,
  `DATE` datetime DEFAULT NULL,
  `ADJ_NAV` double DEFAULT NULL
)

包含约 20,000,000 个条目的表。现在我想找到一个最接近季度末的 ADJ_NAV 值，仅用于一个 symbol_header_id：

SET @quarterend = '2009-3-31';

SELECT  symbol_header_id AS she, ADJ_NAV AS aend FROM symbol_details
WHERE
 symbol_header_id = 18546 
 AND DATE= (
# date closest after quarter end
SELECT DATE FROM symbol_details
WHERE ABS(DATEDIFF(DATE, @quarterend)) < 10
AND DATE<=@quarterend
AND symbol_header_id = 18546 
ORDER BY  ABS(DATEDIFF(DATE, @quarterend)) ASC LIMIT 1)

当我运行内部“选择日期”查询时，它会快速返回。只需运行填充了正确日期的外部查询而不是子查询也可以很快完成。但是当我运行整个过程时，它花了很长时间 - 出了什么问题？

原文

I have a table

CREATE TABLE `symbol_details` (
  `symbol_header_id` int(11) DEFAULT NULL,
  `DATE` datetime DEFAULT NULL,
  `ADJ_NAV` double DEFAULT NULL
)

with ~20,000,000 entries. Now I want to find the ADJ_NAV value closest to the end of the quarter for just one symbol_header_id:

SET @quarterend = '2009-3-31';

SELECT  symbol_header_id AS she, ADJ_NAV AS aend FROM symbol_details
WHERE
 symbol_header_id = 18546 
 AND DATE= (
# date closest after quarter end
SELECT DATE FROM symbol_details
WHERE ABS(DATEDIFF(DATE, @quarterend)) < 10
AND DATE<=@quarterend
AND symbol_header_id = 18546 
ORDER BY  ABS(DATEDIFF(DATE, @quarterend)) ASC LIMIT 1)

When I run the inner "select date" query it returns quickly. Just running the outer query with the correct date filled in instead of the subquery also finishes very quick. But when I run the whole thing it takes forever - something is wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

看海 2024-08-19 06:16:28

优化器似乎在正确评估语句并找到最有效的计划方面存在一些问题。（在 Oracle 中，我会要求您更新统计信息，但我不确定优化器在 MySQL 中如何工作。）

我会尝试一些其他方式来表达您的语句，看看什么对优化器最有意义：

显式连接 immer 和外部查询的两个 symbol_header_id
尝试使用 SELECT max(date) .. 而不是 'Order By Limit 1'
尝试对 symbol_details 进行自连接

希望有一个有用的想法在这里。

回复收藏 0 原文

南风起 2024-08-19 06:16:28

你也许可以不用子查询。只需抓住第一行：

SELECT *
FROM symbol_details
WHERE DATE <= @quarterend
AND symbol_header_id = 18546
ORDER BY DATE DESC
LIMIT 1

You can probably do without the subquery. Just grab the first row:

SELECT *
FROM symbol_details
WHERE DATE <= @quarterend
AND symbol_header_id = 18546
ORDER BY DATE DESC
LIMIT 1

回复收藏 0 原文

我要还你自由 2024-08-19 06:16:28

尝试：

   SELECT t.symbol_header_id,
          COALESCE(t.adj_nav, '0.0') 'adj_nav'
     FROM SYMBOL_DETAILS t
LEFT JOIN (SELECT sh.symbol_header_id,
                  MAX(sh.date) 'max_date'
             FROM SYMBOL_DETAILS sh
            WHERE ABS(DATEDIFF(sh.date, @quarter_end)) < 10
              AND sh.date <= @quarter_end) x ON x.symbol_header_id = t.symbol_header_id
                                            AND x.max_date = t.date
   WHERE t.symbol_header_id = 18546

Try:

   SELECT t.symbol_header_id,
          COALESCE(t.adj_nav, '0.0') 'adj_nav'
     FROM SYMBOL_DETAILS t
LEFT JOIN (SELECT sh.symbol_header_id,
                  MAX(sh.date) 'max_date'
             FROM SYMBOL_DETAILS sh
            WHERE ABS(DATEDIFF(sh.date, @quarter_end)) < 10
              AND sh.date <= @quarter_end) x ON x.symbol_header_id = t.symbol_header_id
                                            AND x.max_date = t.date
   WHERE t.symbol_header_id = 18546

回复收藏 0 原文

~没有更多了~