扩展压缩数据

Question

为了简单起见，我决定只在值发生变化时才存储它。

+------------+--------+-------+
|    date    | entity | value |
+------------+--------+-------+
| 2019-01-01 |      1 |    10 |
| 2019-01-01 |      2 |    15 |
| 2019-01-01 |      3 |    11 |
| 2019-02-12 |      1 |     9 |
| 2019-03-01 |      2 |    13 |
| 2019-03-05 |      1 |    10 |
| 2019-03-05 |      3 |    10 |
+------------+--------+-------+

但是，在每天的基础上，我需要在任何给定日期使用当前值，基本上将上一个表(理想情况下作为视图)转换为：

+------------+----------+----------+----------+
|    date    | entity-1 | entity-2 | entity-3 |
+------------+----------+----------+----------+
| 2019-01-01 |       10 |       15 |       11 |
| 2019-01-02 |       10 |       15 |       11 |
|    ...     |    ...   |    ...   |   ...    |
| 2019-02-11 |       10 |       15 |       11 |
| 2019-02-12 |        9 |       15 |       11 |
| 2019-02-13 |        9 |       15 |       11 |
|    ...     |    ...   |    ...   |   ...    |
| 2019-02-28 |        9 |       15 |       11 |
| 2019-03-01 |        9 |       13 |       11 |
| 2019-03-02 |        9 |       13 |       11 |
| 2019-03-03 |        9 |       13 |       11 |
| 2019-03-04 |        9 |       13 |       11 |
| 2019-03-05 |       10 |       13 |       10 |
| 2019-03-06 |       10 |       13 |       10 |
| 2019-03-07 |       10 |       13 |       10 |
| 2019-03-08 |       10 |       13 |       10 |
+------------+----------+----------+----------+

或者，如果比较简单，只需获取给定日期的值，然后基本上得到一行。

我一直在看窗口函数和这个很棒的枢轴教程，但到目前为止还没有运气.

Answer 1

用户定义的变量可以将稀疏的同位表转换为全范围视图，如下所示：

SELECT a.date
       IF(b.value IS NULL, @e1, @e1 := b.value) AS entity1
       IF(c.value IS NULL, @e2, @e2 := c.value) AS entity2
       IF(d.value IS NULL, @e3, @e3 := d.value) AS entity3
  FROM fullrange AS a  -- single-column auxiliary table with all the dates in the range
  LEFT JOIN tbl  AS b  ON b.date = a.date AND b.entity = 1    -- for entity-1  
  LEFT JOIN tbl  AS c  ON c.date = a.date AND c.entity = 2    -- for entity-2
  LEFT JOIN tbl  AS d  ON d.date = a.date AND d.entity = 3    -- for entity-3  
 ORDER BY a.date ASC

唯一的要求是，在范围的第一天，所有实体都应该具有非空值。

Answer 2

序列引擎

这里有一个使用MariaDB的特殊序列存储引擎 (默认情况下从10.1安装)来创建日期范围的解决方案。您可以更改seq_0_to_70以适应您在用例中需要的日期范围。

SET @entity_1 := NULL, @entity_2 := NULL, @entity_3 := NULL;

SELECT q2.date, 
    @entity_1 := IFNULL(q2.entity_1, @entity_1) entity_1,
    @entity_2 := IFNULL(q2.entity_2, @entity_2) entity_2,
    @entity_3 := IFNULL(q2.entity_3, @entity_3) entity_3
FROM (
  SELECT q1.date, 
    max(IF(t1.entity=1, t1.value, NULL)) entity_1, 
    max(IF(t1.entity=2, t1.value, NULL)) entity_2, 
    max(IF(t1.entity=3, t1.value, NULL)) entity_3 
  FROM (
        SELECT '2019-01-01' + INTERVAL seq DAY as `date` 
        FROM seq_0_to_70
  ) q1
  LEFT OUTER JOIN t1 ON q1.date=t1.date
  GROUP BY q1.date 
) q2
ORDER BY q2.date;

这是一个[医]小提琴作为证据。

递归CTE

如果您希望使用更符合标准的解决方案，并且也可以使用MySQL 8，那么可以尝试这样的方法，其中日期范围是由递归CTE生成的。这需要MariaDB 10.2或更高版本。

SET @entity_1 := NULL, @entity_2 := NULL, @entity_3 := NULL;

SELECT q2.date, 
  @entity_1 := IFNULL(q2.entity_1, @entity_1) entity_1,
  @entity_2 := IFNULL(q2.entity_2, @entity_2) entity_2,
  @entity_3 := IFNULL(q2.entity_3, @entity_3) entity_3
FROM (
  SELECT q1.date, 
    max(IF(t1.entity=1, t1.value, NULL)) entity_1, 
    max(IF(t1.entity=2, t1.value, NULL)) entity_2, 
    max(IF(t1.entity=3, t1.value, NULL)) entity_3 
  FROM (
    WITH RECURSIVE dates AS (     
      SELECT '2019-01-01' AS date  
      UNION ALL     
      SELECT dates.date + INTERVAL 1 DAY AS date
      FROM dates
      WHERE dates.date < '2019-03-12'
    ) 
    SELECT d.date
    FROM dates d
  ) q1
  LEFT OUTER JOIN t1 ON q1.date=t1.date
  GROUP BY q1.date 
) q2
ORDER BY q2.date;

[医]小提琴在这里。