主页 / dba / 问题 / 157603
Accepted
64k
Asked: 2016-12-09 10:10:42 +0800 CST

MYSQL 包括缺失值使用以前最近的记录

  • 772

给定一个表格，其中包含一段时间内每一天的日期

CREATE TABLE `tbl_calendar` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl_calendar` (`date`)
    VALUES
        ('2016-12-10'),
        ('2016-12-09'),
        ('2016-12-08'),
        ('2016-12-07'),
        ('2016-12-06'),
        ('2016-12-05'),
        ('2016-12-04'),
        ('2016-12-03'),
        ('2016-12-02'),
        ('2016-12-01')
 ;

以及一个包含不同类型的值的表，在没有填充它们的随机日期缺少值。

CREATE TABLE `tbl_values` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL DEFAULT '0',
  `date` date DEFAULT NULL,
  `value` double(15,2) DEFAULT '0.00',
   PRIMARY KEY (`value_id`),
   KEY `type_id_date` (`type_id`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl_values` (`type_id`, `date`, `value`)
VALUES
    (100, '2016-12-02', 1),
    (100, '2016-12-04', 2),
    (100, '2016-12-06', 3),
    (100, '2016-12-08', 4),
    (100, '2016-12-10', 5)
;

如何使用该类型的最新记录在 SELECT 中返回缺失天数的值？这是我到目前为止所拥有的。

SELECT 
    v1.type,
    c.date,
    v1.value

FROM 
   tbl_calendar c

   LEFT JOIN tbl_values v1 ON (
            v1.type_id = 100 
        AND v1.date <= c.date 
   )

   LEFT JOIN tbl_values v2 ON (
            v2.type_id = 100
        AND v2.date < c.date
        AND v2.date > o1.date 
   )

WHERE 
       v1.date = c.date 
    OR v2.date IS NULL

此查询的问题在于，当该日期有值时，它会返回以前的最新记录值，以及具有正确值的记录。

预期产出

在此处输入图像描述

a_vlad 的答案在结果上是正确的，但性能不佳，这是预期的。

SELECT
    t1.date,
    (SELECT v1.type_id FROM tbl_values v1 where v1.date <= t1.date ORDER BY v1.date desc limit 1) as `type`,
    (SELECT v1.`value` FROM tbl_values v1 where v1.date <= t1.date     ORDER BY v1.date desc limit 1) as `value`

FROM tbl_calendar t1
HAVING `type` IS NOT NULL

在此处输入图像描述

我使用的最终解决方案是使用 a_vlad 查询来创建汇总表。然而，事实证明它并没有提高系统的性能（因为以前缺失的值是在 PHP 循环中填充的，结果速度一样快）

mysql

2 个回答

  1. 如果我正确理解了这个问题，那么您需要没有直接值的滞后值。

    允许我有点强迫症。避开在任何 ANSI 或 ODBC 标准或 MySQL 保留字中作为保留字的任何表或字段名称。我也会放弃 tbl_ 前缀。

    我已将您的date字段重命名为Perioddate

    尝试这个

    SELECT  v2.type_id,DT.PeriodDate, V2.my_value
FROM (
    SELECT C.PeriodDate,MAX(V1.PeriodDate) AS PrecedingDate
    FROM tbl_calendar AS C
        LEFT JOIN tbl_values AS V1
        ON C.PeriodDate>=V1.PeriodDate
        AND V1.type_id = 100
    GROUP BY C.PeriodDate
) AS DT
    INNER JOIN  tbl_values AS V2
    ON DT.PrecedingDate = V2.PeriodDate
    AND V2.type_id = 100

    它的工作方式是派生表构建日期列表和值表中的最高日期，该日期小于或等于日历表中的条目。如果没有相同的值，则最高日期将是前一个日期。

    关于索引，请确保您最有选择性的列排在第一位，即 PeriodDate 列，然后是您的 type_id。

    除了代理键之外，auto_increment 列还能为您提供什么？如果 PeriodDate 和 type_id 应该是唯一的，那么将其作为您的主键。

    我不知道 MySQL 中的查询优化器是否对唯一键做了任何特殊的事情，但它肯定在其他数据库平台上做了。

    • 4
  2. Best Answer

    查询的正确形式是：

    SELECT
    t1.date,
    (SELECT v1.type_id FROM tbl_values v1 where v1.type_id = 100 AND v1.date <= t1.date ORDER BY v1.date desc limit 1) as `type`,
    (SELECT v1.`value` FROM tbl_values v1 where v1.type_id = 100 AND v1.date <= t1.date ORDER BY v1.date desc limit 1) as `value`

FROM tbl_calendar t1
having `type` IS NOT NULL

    结果为：

    2016-12-02  100 1.00
2016-12-03  100 1.00
2016-12-04  100 2.00
2016-12-05  100 2.00
2016-12-06  100 3.00
2016-12-07  100 3.00
2016-12-08  100 4.00
2016-12-09  100 4.00
2016-12-10  100 5.00

    但是，请再次查看您的查询和预期结果。

    您希望在结果（类型、日期、值）中包含什么：

    SELECT 
    c.date,

    o1.value_id AS "o1.value_id",
    o2.value_id AS "o2.value_id",

    o1.type_id AS "o1.type_id",
    o2.type_id AS "o2.type_id",

    o1.date AS "o1.date",
    o2.date AS "o2.date"

    查询计划

    • 1

相关问题