时间戳

首页 > 解决方案 > MySQL选择以NULL分隔的数据系列之间的日期范围

mysql - MySQL选择以NULL分隔的数据系列之间的日期范围

问题描述

这是解决更复杂问题的一部分。

有一个数据表：

+------------+------+----------+-----------+
| date       | data | data_max | data_diff |
+------------+------+----------+-----------+
| 2017-01-02 |    2 |        2 |      NULL |
| 2017-01-03 |    4 |        4 |      NULL |
| 2017-01-04 |    1 |        4 |        -3 |
| 2017-01-05 |    3 |        4 |        -1 |
| 2017-01-06 |    1 |        4 |        -3 |
| 2017-01-07 |    4 |        4 |      NULL |
| 2017-01-08 |    5 |        5 |      NULL |
| 2017-01-09 |   -2 |        5 |        -7 |
| 2017-01-10 |    0 |        5 |        -5 |
| 2017-01-11 |   -5 |        5 |       -10 |
| 2017-01-12 |    6 |        6 |      NULL |
| 2017-01-13 |    4 |        6 |        -2 |
+------------+------+----------+-----------+

data_diff我想为每个数据子集分别计算最小值和最大值。每个数据子集都以 NULL 开头（但最后一个可能不以 NULL 结尾，而是以数据结尾）我还需要每个数据子集的开始和结束日期，以便稍后用于计算最小值、最大值。我想获取日期范围：

+----------------+--------------+
| diff_date_from | diff_date_to |
+----------------+--------------+
| 2017-01-04     | 2017-01-06   |
| 2017-01-09     | 2017-01-11   |
| 2017-01-13     | 2017-01-13   |
+----------------+--------------+

如果您想获取示例数据，请使用以下查询：

CREATE TABLE IF NOT EXISTS `test`
(
   `date_time` DATETIME UNIQUE NOT NULL,
   `data` INT NOT NULL
)
ENGINE InnoDB;

INSERT INTO `test` VALUES
('2017-01-02', 2),
('2017-01-03', 4),
('2017-01-04', 1),
('2017-01-05', 3),
('2017-01-06', 1),
('2017-01-07', 4),
('2017-01-08', 5),
('2017-01-09', -2),
('2017-01-10', 0),
('2017-01-11', -5),
('2017-01-12', 6),
('2017-01-13', 4)

;

SELECT 
    DATE(`date_time`) AS `date`,
    `data`,
    `data_max`,
    IF(`data` < `data_max`,  - (`data_max` - `data`), NULL) 
    AS `data_diff`

FROM
    (
    SELECT 
        `date_time`,
        `data`,
        MAX(`data`) OVER (ORDER BY `date_time` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `data_max`
    FROM
        `test`
    ) t
;

是否可以编写一个提供上述日期范围的查询？还是必须应用程序或某种技巧？

也许带有 OVER 的窗口函数会有所帮助，但我不知道如何在非 NULL 的当前行和从 NULL 前面的行开始的前行之间指定其窗口边界。这完全可行吗？

有RANGE用于设置窗口边界文档的运算符 看起来很有希望：

PRECEDING：对于 ROWS，边界是当前行之前的 expr 行。 对于 RANGE，边界是值等于当前行值减去 expr 的行；如果当前行值为 NULL，则边界是该行的对等点。

和另一部分：

前 10 和后 10 之间的按 X ASC 范围排序

帧从 NULL 开始并在 NULL 处停止，因此仅包含值为 NULL 的行。

但我不明白inlcuding only rows with null。也许对于日期范围2017-01-02，2017-01-03但对于2017-01-03如何2017-01-07来？

标签： mysqldatasetdate-range

解决方案

我一直在研究您的“更复杂的问题”（仍在寻找答案），但这是解决此问题的方法。鉴于您使用的是窗口函数，我假设您使用的是 MySQL 8，因此也可以使用 CTE：

WITH cte AS (SELECT DATE(`date_time`) AS `date`,
                    `data`,
                    MAX(`data`) OVER (ORDER BY `date_time` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `data_max`
             FROM `test`),
cte2 AS (SELECT `date`,
                `data`,
                `data_max`,
                CASE WHEN `data` < `data_max` THEN `data` - `data_max` END AS `data_diff`
         FROM cte)
SELECT `data_max`, 
       MIN(CASE WHEN `data_diff` IS NOT NULL THEN date END) AS diff_date_from,
       MAX(CASE WHEN `data_diff` IS NOT NULL THEN date END) AS diff_date_to
FROM cte2
GROUP BY `data_max`
HAVING diff_date_from IS NOT NULL

输出：

data_max    diff_date_from  diff_date_to
4           2017-01-04      2017-01-06
5           2017-01-09      2017-01-11
6           2017-01-13      2017-01-13

dbfiddle 上的演示

推荐阅读