SQL Pivot创建NULL，而不是对DateTime值进行合并

Question

我的原始数据如下所示：

value   InsertedAt  timestamp   country model   ForecastRun origin  ID
10  2019-10-16 19:06:05.793 2019-10-11 21:00:00.000 Germany fundamentalmodel    3D8 manual  1065
10  2019-10-16 19:06:05.793 2019-10-11 22:00:00.000 Germany fundamentalmodel    3D8 manual  1066
10  2019-10-16 19:06:05.793 2019-10-11 23:00:00.000 Germany fundamentalmodel    3D8 manual  1067
13  2019-10-16 19:06:07.240 2019-10-11 21:00:00.000 France  fundamentalmodel    3D8 manual  1089
13  2019-10-16 19:06:07.240 2019-10-11 22:00:00.000 France  fundamentalmodel    3D8 manual  1090
13  2019-10-16 19:06:07.240 2019-10-11 23:00:00.000 France  fundamentalmodel    3D8 manual  1091

我想要基于“country”列进行透视。

这是我的问题：

SELECT timestamp, Germany, France
FROM dbo.Forecasts
PIVOT (
 AVG(value)
 FOR country IN (
    [Germany],
    [France])
) as pivot_table
ORDER BY timestamp DESC

我的预期结果是：

timestamp   Germany France
2019-10-11 23:00:00.000 10  13
2019-10-11 22:00:00.000 10  13
2019-10-11 21:00:00.000 10  13

我的实际结果是：

timestamp   Germany France
2019-10-11 23:00:00.000 10  NULL
2019-10-11 23:00:00.000 NULL    13
2019-10-11 22:00:00.000 NULL    13
2019-10-11 22:00:00.000 10  NULL
2019-10-11 21:00:00.000 10  NULL
2019-10-11 21:00:00.000 NULL    13

我在不同的数据上尝试了我的查询，它工作得很好。然而，在这种特殊情况下不是这样的。我错过了什么？

Answer 1

create table dbo.Forecasts
(
  value int not null,
  InsertedAt datetime not null,
  [timestamp] datetime not null,
  country varchar(100) not null,
  model varchar(100) not null,
  ForecastRun varchar(100) not null,
  origin varchar(100) not null,
  ID int not null
)
GO

insert into dbo.Forecasts
(value,InsertedAt,[timestamp],country,model,ForecastRun,origin,ID)
values
(10, '2019-10-16 19:06:05.793', '2019-10-11 21:00:00.000', 'Germany', 'fundamentalmodel', '3D8', 'manual',  1065)
,(10, '2019-10-16 19:06:05.793', '2019-10-11 22:00:00.000', 'Germany', 'fundamentalmodel', '3D8', 'manual',  1066)
,(10, '2019-10-16 19:06:05.793', '2019-10-11 23:00:00.000', 'Germany', 'fundamentalmodel', '3D8', 'manual',  1067)
,(13, '2019-10-16 19:06:07.240', '2019-10-11 21:00:00.000', 'France', 'fundamentalmodel', '3D8', 'manual',  1089)
,(13, '2019-10-16 19:06:07.240', '2019-10-11 22:00:00.000', 'France', 'fundamentalmodel', '3D8', 'manual',  1090)
,(13, '2019-10-16 19:06:07.240', '2019-10-11 23:00:00.000', 'France', 'fundamentalmodel', '3D8', 'manual',  1091)
GO


SELECT pt.[timestamp], pt.[Germany], pt.[France]
FROM (select f.value, f.[timestamp], f.country from dbo.Forecasts f) as x
PIVOT (
 AVG(x.value)
 FOR x.country IN (
    [Germany],
    [France])
) as pt
ORDER BY pt.[timestamp] DESC
GO

结果：

timestamp               Germany     France
----------------------- ----------- -----------
2019-10-11 23:00:00.000 10          13
2019-10-11 22:00:00.000 10          13
2019-10-11 21:00:00.000 10          13

(3 row(s) affected)

问题出在哪里？

FROM dbo.Forecasts

可以改为：

FROM (select f.value, f.[timestamp], f.country from dbo.Forecasts f) as x

必须列出将参与聚合的所有列，但不能超过聚合的列。ID列干扰了聚合，使源表中的每一行都在结果中放入一个值。如果在select中添加f.ID，则会重现该问题。

Answer 2

在Oracle DB中，您正在使用的sql对我来说很好

with aa as (select 10 value, '2019-10-11 21:00:00.000' timestamp, 'Germany' country     
from dual union all
select 10 value, '2019-10-11 22:00:00.000' timestamp, 'Germany' country from dual     union all
select 10 value, '2019-10-11 23:00:00.000' timestamp, 'Germany' country from dual     union all
select 13 value, '2019-10-11 21:00:00.000' timestamp, 'France' country from dual     union all
select 13 value, '2019-10-11 22:00:00.000' timestamp, 'France' country from dual     union all
select 14 value, '2019-10-11 23:00:00.000' timestamp, 'France' country from dual
)
SELECT *
FROM aa
PIVOT (
 AVG(value)
 FOR country IN (
    'Germany',
    'France'))  pivot_table
ORDER BY timestamp DESC;