从现有数据生成缺失数据

Question

我有一个单位的每小时水平数据，它的状态和价值。单位的状态和价值在一定时间间隔后发生变化(不一定在每小时)。我想从现有数据生成每小时一次的数据。例如:我有如下输入：

​

所需产出如下：

​

请查找以下脚本，以提供所需的输入和输出：

输入

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

这是用于Server的。

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

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

这是被颠覆的。如果该行缺少数据，则在cte字段中左转联接，如ID、Name和Status为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

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

Answer 2

如果有Server > 2012，则可以使用LEAD查找下一个日期的值以及生成中间行的计数表：

设置

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

查询：

;WITH Tally -- Generate Tally Table
As
(
    SELECT ROW_NUMBER() OVER (ORDER BY num.n) - 1 AS number
    FROM 
        (VALUES (1), (2),(3),(4),(5),(6),(7),(8),(9),(10)) num(n)
    CROSS APPLY
        (VALUES (1), (2),(3),(4),(5),(6),(7),(8),(9),(10)) num2(n)
    CROSS APPLY
        (VALUES (1), (2),(3),(4),(5),(6),(7),(8),(9),(10)) num3(n)
),
MyRows
As
(
    SELECT  Id, Name, Status, CDate, CHour, Value
              -- Turn Date to DateTime
            , DATEADD(HH,CHour, CAST(CDate AS DateTime)) AS FullDate
              -- Get next date time 
            , DATEADD(HH,LEAD(CHour) OVER (PARTITION BY Name ORDER BY CDate, CHour)
            , CAST(LEAD(CDATE) OVER (PARTITION BY Name ORDER BY CDate, CHour)AS DateTime))  AS NextFullDate
    FROM #ChannelData 
)
SELECT  Id, Name, [Status], 
        CAST(DATEADD(HH, number, FulLDate) AS Date) AS CDate,
        DATEPART(HH,DATEADD(HH, number, FulLDate)) AS CHour,
        Value
FROM MyRows
CROSS APPLY Tally
WHERE 
    DATEADD(HH, number, FullDate) < COALESCE(NextFullDate, DATEADD(hh, 1, FullDate)) 
ORDER BY 
    CAST(DATEADD(HH, number, FulLDate) AS Date),
    DATEPART(HH,DATEADD(HH, number, FulLDate))