mysql - 从现有数据生成缺失数据

Question

我有一个单位的每小时级别数据，及其状态和值(value)。单位的状态和值会在一定时间间隔(不一定每小时)后发生变化。我想从现有数据生成每小时级别的数据。举例来说:我有输入，例如:

所需的输出是:

请查找以下脚本以获取所需的输入和输出:

输入

SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-25' AS CDate,'22' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'2' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'5' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'8' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'11' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'13' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'16' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'20' AS CHour,1.0 AS Value

输出:

SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-25' AS CDate,'22' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-25' AS CDate,'23' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'0' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'1' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'2' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'3' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'4' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'5' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'6' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'7' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'8' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'9' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'10' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'11' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'12' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'13' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'14' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'15' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'16' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'17' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'18' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'19' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'20' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'21' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'22' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'23' AS CHour,1.0 AS Value

Answer 1

这是针对 SQL Server 的。

在这里，我使用提供行号的 CTE 来区分之前的日期和时间。CTE 选择与您的小时格式交叉连接的不同日期:0 到 23。

select 语句中是识别前一行的算法。我使用之前的行号将 CTE 内部连接到自身。通过内部联接，这会删除第一个数据输入之前的较早日期。

这是子查询。如果该行缺少数据，则通过 LEFT JOIN，cte 字段(如 ID、名称和状态)将为 NULL。合并选择原始(非空)数据，否则按行号选择最新数据。

DECLARE @temp TABLE (ID tinyint, Name varchar(100), Status tinyint, CDate date, CHour tinyint, Value decimal(12,1))
INSERT INTO @temp(ID, Name, Status, CDate, CHour, Value)
VALUES (3, 'CName1', 0, '2017-10-25', 22, 0.5)
      ,(3, 'CName1', 1, '2017-10-26',  2, 0.5)
      ,(3, 'CName1', 0, '2017-10-26',  5, 0.5)
      ,(3, 'CName1', 1, '2017-10-26',  8, 0.5)
      ,(3, 'CName1', 0, '2017-10-26', 11 ,0.5)
      ,(3, 'CName1', 1, '2017-10-26', 13 ,0.5)
      ,(3, 'CName1', 1, '2017-10-26', 16 ,1.0)
      ,(3, 'CName1', 2, '2017-10-26', 20 ,1.0)
;
WITH cte AS
(
SELECT ROW_NUMBER() OVER(ORDER BY dT.CDate2, dT.CHour2) [theOrder]
      ,*      
  FROM (
        SELECT DISTINCT T.CDate [Cdate2], dT.CHour2
          FROM @temp T 
               CROSS JOIN (SELECT 0 [CHour2] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
                           UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 
                           UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 
                           UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 
                           UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 
                          ) AS dT --joins for any missing hours
       ) AS dT LEFT JOIN @temp T ON dT.Cdate2 = T.CDate AND T.CHour = dT.CHour2
)               

SELECT COALESCE(dT.ID, cte2.ID) [ID]
      ,COALESCE(dT.[Name], cte2.[Name]) [Name]
      ,COALESCE(dT.[Status], cte2.[Status]) [Status]
      ,dT.Cdate2 [Cdate]
      ,dT.CHour2 [CHour]      
      ,COALESCE(dT.[Value], cte2.[Value]) [Value]

  FROM (
        SELECT C1.*
              ,(SELECT MAX(theOrder)
                  FROM cte C2
                 WHERE C2.theOrder <= C1.theOrder AND C2.ID IS NOT NULL
               ) [maxorder]
          FROM cte C1
       ) AS dT INNER JOIN cte cte2 ON dT.maxorder = cte2.theOrder

此输出与您请求的输出匹配。