mysql - 加入后在数据库中填充以下日期的值

Question

我实际上需要用存档中的下一个可用数据填充不存在的条目。

背景:所以我有一个记录，它每月归档每个状态。但是，它只会在有新值时更新，并将旧值保存到上个月。使用 MySQL v8。

如何用下一个填写时间段的相同ID的数据填写表格中的缺失值？

archivesDate='2019-08-01' 中的所有内容都故意包含 NULL 数据。这意味着下个月是数据开始的时间。

中间3个archivesDate='2019-09-01'需要填写以后相同statusID的下一条数据；在本例中，它来自 archivesDate='2019-10-01'。这可能是 10 月、11 月，或 future 的任何时间。它只需要填写下一个值。

我试过的，但这只是 ID 上的一个连接，这就是为什么 September 显示为 NULL。

SELECT
    al.statusID,
    sa.statusDate,
    sa.statusFlagsID,
    sa.statusPrice,
    al.archivesDate
FROM (
    SELECT 
        sid.statusID AS statusID,
        a.archivesID AS archivesID,
        a.archivesDate AS archivesDate
    FROM (
            SELECT 
                DISTINCT statusID 
            FROM statusArchive) sid
            CROSS JOIN archives a) al
LEFT JOIN statusArchive sa
    ON sa.statusID = al.statusID --This needs to be changed to get next value in cases where there is no value available
        AND sa.archivesID = al.archivesID --This needs to be changed to get next value  in cases where there is no value available
ORDER BY al.archivesDate, al.statusID

当前输出:

statusID statusDate statusFlagsID statusPrice archivesDate
1                                              2019-08-01
2                                              2019-08-01
3                                              2019-08-01
1                                              2019-09-01
2                                              2019-09-01
3                                              2019-09-01
1        2018-10-01  1            37           2019-10-01
2        2018-11-05  1            90           2019-10-01
3        2019-01-01  1            3            2019-10-01

期望的输出:

statusID statusDate statusFlagsID statusPrice archivesDate
1                                              2019-08-01
2                                              2019-08-01
3                                              2019-08-01
1        2018-10-01  1            37           2019-09-01
2        2018-11-05  1            90           2019-09-01
3        2019-01-01  1            3            2019-09-01
1        2018-10-01  1            37           2019-10-01
2        2018-11-05  1            90           2019-10-01
3        2019-01-01  1            3            2019-10-01

编辑:添加了模型源表(包含提供上述内容的所有信息):

源数据

文件

archivesID       archivesDate
1                2019-08-01
2                2019-09-01
3                2019-10-01

STATUSARCHIVE(将 s 替换为以下列名称中的状态，SO 格式问题抱歉)

注意:archivesID=2

中缺少条目

sArchiveID  sID  sFlagsID  sPrice  sDate       archivesID
1           1    NULL      NULL    NULL        1
2           2    NULL      NULL    NULL        1
3           3    NULL      NULL    NULL        1
4           1    1         37      2018-10-01  3
5           2    1         90      2018-11-05  3
6           3    1         3       2019-01-01  3

和DB-Fiddle here以及创建数据源的 SQL:

CREATE TEMPORARY TABLE archives (
  archivesID INT,
  archivesDate DATE
);
INSERT INTO archives (archivesID, archivesDate) VALUES (1, '2019-08-01');
INSERT INTO archives (archivesID, archivesDate) VALUES (2, '2019-09-01');
INSERT INTO archives (archivesID, archivesDate) VALUES (3, '2019-10-01');

CREATE TEMPORARY TABLE statusArchive (
  statusArchiveID INT,
  statusID INT,
  statusFlagsID INT,
  statusPrice DECIMAL,
  statusDate DATE,
  archivesID INT
);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID) 
    VALUES (1, 1, NULL, NULL, NULL, 1);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID) 
    VALUES (2, 2, NULL, NULL, NULL, 1);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID) 
    VALUES (3, 3, NULL, NULL, NULL, 1);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID) 
    VALUES (4, 1, 1, 37, '2018-10-01', 3);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID) 
    VALUES (5, 2, 1, 90, '2018-11-05', 3);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID) 
    VALUES (6, 3, 1, 3, '2019-01-01', 3);

Answer 1

快速修复:将您的评论翻译成代码。

AND sa.archivesID = al.archivesID -- This needs to be changed to get next value
                                  -- in cases where there is no value available

成为

AND sa.archivesID = (
    SELECT MIN(x.archivesID)
    FROM statusArchive x
    WHERE x.statusID = al.statusID
      AND x.archivesID >= al.archivesID
)

完整查询:

SELECT
    al.statusID,
    sa.statusDate,
    sa.statusFlagsID,
    sa.statusPrice,
    al.archivesDate
FROM (
    SELECT 
        sid.statusID AS statusID,
        a.archivesID AS archivesID,
        a.archivesDate AS archivesDate
    FROM (SELECT DISTINCT statusID FROM statusArchive) sid
    CROSS JOIN archives a
) al
LEFT JOIN statusArchive sa
    ON  sa.statusID = al.statusID
    AND sa.archivesID = (
        SELECT MIN(x.archivesID)
        FROM statusArchive x
        WHERE x.statusID = al.statusID
          AND x.archivesID >= al.archivesID
    )
ORDER BY al.archivesDate, al.statusID

结果:

| statusID | archivesDate | statusDate | statusFlagsID | statusPrice |
| -------- | ------------ | ---------- | ------------- | ----------- |
| 1        | 2019-08-01   |            |               |             |
| 2        | 2019-08-01   |            |               |             |
| 3        | 2019-08-01   |            |               |             |
| 1        | 2019-09-01   | 2018-10-01 | 1             | 37          |
| 2        | 2019-09-01   | 2018-11-05 | 1             | 90          |
| 3        | 2019-09-01   | 2019-01-01 | 1             | 3           |
| 1        | 2019-10-01   | 2018-10-01 | 1             | 37          |
| 2        | 2019-10-01   | 2018-11-05 | 1             | 90          |
| 3        | 2019-10-01   | 2019-01-01 | 1             | 3           |

View on DB Fiddle

注意:您应该在 statusArchive(statusID, archivesID [other columns]) 上有一个索引。

以下(较短的)查询也适用于给定的示例数据:

select 
  s.statusID,
  s.statusDate,
  s.statusFlagsID,
  s.statusPrice,
  a.archivesDate
from archives a
left join statusArchive s
  on s.archivesID = (
    select min(x.archivesID)
    from statusArchive x
    where x.archivesID >= a.archivesID
  )
order by a.archivesDate, s.statusID;

对于此查询，您应该在 statusArchive(archivesID) 上有一个索引。

结果:

| statusID | statusDate | statusFlagsID | statusPrice | archivesDate |
| -------- | ---------- | ------------- | ----------- | ------------ |
| 1        |            |               |             | 2019-08-01   |
| 2        |            |               |             | 2019-08-01   |
| 3        |            |               |             | 2019-08-01   |
| 1        | 2018-10-01 | 1             | 37          | 2019-09-01   |
| 2        | 2018-11-05 | 1             | 90          | 2019-09-01   |
| 3        | 2019-01-01 | 1             | 3           | 2019-09-01   |
| 1        | 2018-10-01 | 1             | 37          | 2019-10-01   |
| 2        | 2018-11-05 | 1             | 90          | 2019-10-01   |
| 3        | 2019-01-01 | 1             | 3           | 2019-10-01   |

View on DB Fiddle

但是 - 我不知道这是否会在任何可能的数据集上返回所需的结果，因为我只能从您的示例数据和结果中猜测确切的要求。

更新

如果您需要按相应的 archivesDate 排序的下一行，您将需要一个 ORDER BY ... LIMIT 1 子查询:

SELECT
    al.statusID,
    sa.statusDate,
    sa.statusFlagsID,
    sa.statusPrice,
    al.archivesDate
FROM (
    SELECT 
        sid.statusID AS statusID,
        a.archivesID AS archivesID,
        a.archivesDate AS archivesDate
    FROM (SELECT DISTINCT statusID FROM statusArchive) sid
    CROSS JOIN archives a
) al
LEFT JOIN statusArchive sa
    ON  sa.statusID = al.statusID
    AND sa.archivesID = (
        SELECT x.archivesID
        FROM statusArchive x
        JOIN archives y ON y.archivesID = x.archivesID
        WHERE x.statusID = al.statusID
          AND y.archivesDate >= al.archivesDate
        ORDER BY y.archivesDate ASC
        LIMIT 1
    )
ORDER BY al.archivesDate, al.statusID;

确保你有索引

• statusArchive(statusID [, other columns]) for x.statusID = al.statusID 以及 SELECT DISTINCT statusID FROM statusArchive
• archives(archivesID, archivesDate, [, other columns]) 对于条件 y.archivesID = x.archivesID 和 y.archivesDate >= al。 archivesDate 和 ORDER BY 子句 y.archivesDate ASC。