删除 PIVOT 构造中具有空值的重复字段

Question

我有两个表：VALUES1_TABLE用于存储值，PARAM1_TABLE用于存储参数定义：

CREATE TABLE VALUES1_TABLE
([ID] int ,
 [Date] DATETIME ,
 [Value] float );
 GO
DECLARE @dt DATETIME;
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:00.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt , 200.09);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:03.450', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 201.01);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:06.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 200.85); 
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:09.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 200.12);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:12.450', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 201.02);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:15.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 200.45);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:00.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt , 200.19);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:03.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 201.54);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:06.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 200.95); 
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:09.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 200.12);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:12.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 201.82);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:15.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (100, @dt, 201.45);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:00.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 50);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:03.450', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 51);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:06.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 50.5); 
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:09.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 51);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:12.450', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 52);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:00:15.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 52.5); 
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:00.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 50.5);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:03.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 51.5);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:06.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 51.5); 
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:09.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 51.5);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:12.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 51);
 SET @dt=CONVERT(DATETIME, '2017-01-26 08:03:15.000', 121);
 INSERT INTO VALUES1_TABLE VALUES (101, @dt, 52.1);
CREATE TABLE PARAM1_TABLE
([ID] int ,
 [Name] NVARCHAR(20));
 GO
 INSERT INTO PARAM1_TABLE VALUES (100, 'Param1');
 INSERT INTO PARAM1_TABLE VALUES (101, 'Param2');

我想获取每个参数的 AVG 值，间隔为 3 分钟、1 小时或 1 天。我使用 PIVOT 语句编写了 T-SQL，但我为每个参数重复了具有 NULL 值的字段。此代码 T-SQL 代码：

DROP TABLE #t;
GO
DECLARE @dt_st DATETIME
DECLARE @dt_end DATETIME
DECLARE @dt_allend DATETIME
CREATE TABLE #t (dt_st DATETIME, dt_end DATETIME)
SET @dt_st =  CONVERT(DATETIME, '2017-01-26 07:00:00.000',121); 
SET @dt_end =  CONVERT(DATETIME, '2017-01-26 07:03:00.000',121); 
SET @dt_allend =  CONVERT(DATETIME, '2017-01-26 08:03:00.000',121); 
WHILE (@dt_st < @dt_allend) 
BEGIN
    INSERT INTO #t VALUES(@dt_st, @dt_end);
    --PRINT FORMAT(@dt_st, 'dd/MM/yyyy HH:mm:ss.fff', 'ru-RU') + '  ' + FORMAT(@dt_end,  'dd/MM/yyyy HH:mm:ss.fff', 'ru-RU' );;
    SET @dt_st = DATEADD(MINUTE, 3, @dt_st);
    SET @dt_end = DATEADD(MINUTE, 3, @dt_end);
END
SELECT * FROM (
    SELECT CONVERT(NVARCHAR(30),tm.dt_st, 121) + ' - ' + CONVERT(NVARCHAR(30),tm.dt_end, 121) AS [Time], d.[Value] AS [VALUE], [d].[ID] AS [ID], dev.Name AS [NAME]
    FROM #t tm
    LEFT JOIN [dbo].[VALUES1_TABLE] d ON 
    [d].[Date] >= dt_st AND d.[Date] < dt_end
    INNER JOIN [dbo].[PARAM1_TABLE] dev
    ON dev.id = d.[ID]
    WHERE d.[ID] IN (100, 101)
    ) innerQuery
PIVOT (AVG([VALUE]) FOR [NAME] IN ([Param1], [Param2])
  ) AS PivotTable

如何删除具有 NULL 值的字段并规范化输出？

更新1.我想删除每个时间间隔的重复行。见 - http://sqlfiddle.com/#!3/1a208/7

更新 2.我希望每个参数的 AVG 值以 3 分钟的间隔进行。使用上面的代码，我有以下输出：

Time                            ID  Param1  Param2
2017-01-26 08:00:00.000 - 2017-01-26 08:03:00.000   100 200.59  NULL
2017-01-26 08:00:00.000 - 2017-01-26 08:03:00.000   101 NULL    51.1666666666667
But I want some other output:
Time                            Param1  Param2
2017-01-26 08:00:00.000 - 2017-01-26 08:03:00.000   200.59  51.1666666666667

Answer 1

    SELECT CONVERT(NVARCHAR(30),abc.dt_st, 121) + ' - ' + CONVERT(NVARCHAR(30),ABC.dt_end, 121) AS [Time],
       ISNULL(AVG(CASE WHEN ABC.NAME = 'PARAM1' THEN ABC.VALUE END),0) AS PARAM1,
       ISNULL(AVG(CASE WHEN ABC.NAME = 'PARAM2' THEN ABC.VALUE END),0) AS PARAM2
 FROM  (
    SELECT A.ID,A.DATE,A.VALUE,B.DT_ST,B.DT_END,C.NAME FROM [VALUES1_TABLE] A  JOIN #T  B ON A.DATE BETWEEN  B.DT_ST AND B.DT_END
    JOIN [PARAM1_TABLE] C ON C.ID=A.ID) ABC
    GROUP BY DT_ST,DT_END

输出

time    PARAM1  PARAM2
Jan 26 2017  7:57AM-Jan 26 2017  8:00AM 200.09  50
Jan 26 2017  8:00AM-Jan 26 2017  8:03AM 200.532857142857    51.0714285714286